Research: Splitting Coalesce queries into 2 queries performance testing #6714

Summary
Our current SeekCoalescePaginator relies on COALESCE, but we do not have functional indexes that make these operations performant. As a result, SA queries are degrading in performance.
This ticket investigates whether splitting these queries into two separate paths — one targeting null values and one targeting non‑null values — provides a significant performance improvement.

What We’re After
Evaluate whether replacing the existing single‑query pattern (sorting via COALESCE) with a two‑query approach can reduce execution time and improve index utilization. Specifically:

Determine whether isolating nulls and non‑nulls allows the underlying indexes to be used correctly.
Benchmark the performance difference between:

The current COALESCE‑based paginator
A split-query strategy (null bucket + non‑null bucket)

Assess feasibility, maintainability, and any downstream risks of adopting the split-query approach.

Related Ticket(s)
[ ] #6662

Action Item(s)

• Reproduce slow performance and capture baseline metrics (execution time, query plan).
• Prototype the “split queries” approach:

Query A: records where sortable field IS NULL
Query B: records where sortable field IS NOT NULL

• Document findings and recommend whether to adopt the new pattern.
• If beneficial, create follow‑up implementation tickets.

Completion Criteria

• A clear recommendation (including risks + tradeoffs) is produced.