The interview question. A merchant dashboard asks for the most recent 50 orders whose status is either PAID or REFUNDED. A developer creates an index on (merchant_id, status, created_at DESC), but the plan still includes a sort. Explain what order the index actually provides. Design an index and query that can serve the first page efficiently, then discuss what changes if a merchant has millions of older orders.
Assume PostgreSQL B-tree indexes, a stable merchant ID, and no requirement that all merchants see a single global order. The page should be sorted by created_at DESC, then by a unique order ID so ties have a deterministic position. If the task instead required ordered output for one exact status, the best key order might be different.
Think first
A support dashboard requests the 50 newest tickets for one organization with status in (OPEN, ESCALATED), ordered by updated_at DESC and ticket_id DESC. An index on (org_id, status, updated_at DESC, ticket_id DESC) still produces a Sort. Explain why, propose a better first-page path and describe what happens for a rare status and for pagination.
Take a few minutes to form an approach, then compare it with the worked explanation.
Reveal the worked explanation
An ordered index has several orders inside it
Look at the key order:
(merchant_id, status, created_at DESC)
For one merchant, the index groups entries by status first. Within each status, created_at runs from newest to oldest. That means we get one ordered sequence for PAID and another for REFUNDED, not one globally time-ordered sequence across both.
Reading the PAID range then the REFUNDED range yields 14:10, 13:57, 12:20, 14:08, 14:02, 11:42. The fourth record is newer than the second. A query requesting the newest combined records needs to merge or sort those sequences. The exact execution plan is a planner decision, but the compound index alone does not certify the required order. PostgreSQL documents index ordering and multicolumn behavior separately. (Index ordering, multicolumn indexes)
Within one merchant the status-first index has two time-ordered runs, while the requested output interleaves them by creation time
Notice how the question differs from the preceding lesson. We are not deciding whether an index is selective enough to beat a table scan. We are deciding whether the order emitted by the access path matches the order the API promised.
Move time ahead of status when the screen needs time
If the important API is “show the newest orders for this merchant, optionally filtering status,” an index beginning with (merchant_id, created_at DESC, id DESC) deserves consideration:
CREATE INDEX orders_merchant_recent
ON orders (merchant_id, created_at DESC, id DESC);
For one merchant, this index provides a single timeline with a stable tie-breaker. The executor can scan from newest to oldest, checking whether each order's status is in the accepted set. If status is available only from the heap, those checks need table visits. A variant using INCLUDE (status) may let the index supply the filter value, although index-only execution still depends on MVCC visibility. Part 5 of 5 covers that visibility boundary.
This alternative has a new cost. If the user filters for a rare status that appears in only one out of every 10,000 recent orders, the scan may pass a huge number of entries before finding 50 matches. A status-first index could locate those entries cheaply and sort a smaller candidate set instead. There is no universally optimal order of columns independent of the query mix.
The right design may use two intentional access paths. A status-specific workflow gets (merchant_id, status, created_at DESC, id DESC) with an equality predicate on one status. The combined timeline gets (merchant_id, created_at DESC, id DESC) and applies the allowed-status filter. Indexes are not free, so that is justified only when both paths matter enough.
LIMIT 50 changes the tradeoff
Sorting 50 rows is trivial. Finding those 50 is where the time goes. If an ordered index allows PostgreSQL to stop after visiting roughly 55 recent entries, the work can be tiny. If a filter leaves only one match in 10,000 entries, an ordered scan might read hundreds of thousands of entries to return the page. That is why we should collect how each merchant's recent status mix behaves, not only the average across all merchants.
A bitmap heap scan may find relevant rows efficiently but loses the output order, so it typically requires an explicit sort. Conversely, a direct B-tree index scan can preserve key ordering if the query's requested order matches the usable key order. ORDER BY ... LIMIT is especially sensitive to this choice, as PostgreSQL's index ordering documentation explains.
For a status-first design, the database could also produce top candidates from each status and merge them. That can be useful if the workload is narrow and the application or query planner can implement the merge safely. It adds complexity, particularly when there are more status values or when pagination spans the two sequences. It should be an evaluated option, not a magical promise that two indexes eliminate sorting.
Paging past the first fifty
Offset paging looks attractive:
OFFSET 100000 LIMIT 50
But walking past the first 100,000 qualifying entries still costs work, and inserts can move rows between pages. For an ordered merchant timeline I would prefer keyset pagination using the final (created_at, id) from the preceding page:
WHERE merchant_id = :merchant
AND (created_at, id) < (:last_time, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50
This example assumes non-null created_at and id values and a stable sort tuple. Nullable sort keys require an explicit null-ordering and cursor predicate; the row comparison above is not a universal null-safe cursor. The query still needs the correct status filter when appropriate. The cursor must be tied to that filter and sort contract. This design offers stable movement through a sort order, but not necessarily a frozen snapshot of a changing dataset. New records may appear before the cursor, and deletions can remove entries the client would otherwise have seen.
The interview answer should start with the actual key order and the output order, not a memorized rule to put every equality column first. Equality before range is a useful heuristic for locating rows, but it does not by itself solve every ordering query.
Now imagine both indexes are healthy. The same prepared query is very fast for most merchants but unexpectedly slow for the largest merchant. The SQL text does not change. What changed in the planner's view of the data?
Go deeper
Mixed directions, nulls, and practical validation
A B-tree can scan forward or backward. For mixed sort directions, such as created_at DESC, id ASC, the index definition may need an explicit mixed ordering. Null ordering can also matter if the sort fields are nullable. Do not assume that a reverse scan supports an arbitrary combination of ascending and descending keys.
Use EXPLAIN (ANALYZE, BUFFERS) with realistic merchant_id and status distributions. Compare whether the plan contains Sort, Incremental Sort, or Index Scan, how many rows are examined to return fifty, and how many heap reads occur. A plan without a Sort is not necessarily faster if it must examine half a tenant's history to locate rare statuses.
Keyset cursors require a unique ordering tuple. If timestamp alone is not unique, two orders with the same timestamp can be skipped across page boundaries. An immutable id or another unique key makes the order deterministic. If timestamps or sort keys can be edited after creation, the cursor contract needs additional care.
Check your reasoning
State the ordering of the index entries inside each status prefix.
Demonstrate with a concrete interleaved timestamp example why two status ranges need merging or sorting.
Choose between a time-first ordered scan and status-first selective scans based on the filter mix.
Give a unique tie-breaker and stable keyset cursor for deep pagination.
Measure candidate rows examined and avoid declaring a no-Sort plan automatically faster.
Common mistake: Applying equality-before-range as a universal index-column ordering rule without considering the requested sort.
Read the answer explanation
No, because time is sorted within each status group, not across the union of groups. — The status prefix separates the runs. Global timestamp order needs a merge, sort, or a different key ordering.
Apply it elsewhere
A social feed shows posts tagged NEWS or SPORTS in one newest-first timeline. Which index orders and read paths would you test?
Compare your approach
Compare tag-first targeted probes with merged results against a time-first scan and filtering. Use a tie-breaker, account for tag frequency, and explain how deep pages are handled.
One more constraint
Your index was ideal for one status. Product now adds an ANY-of-twenty-status filter. What should change?
Quick revision
An index on (merchant_id, status, created_at) gives time order within each status, not across multiple statuses. A combined feed may still need sorting or merging. If the API primarily wants newest orders regardless of status, I would consider (merchant_id, created_at DESC, id DESC) and filter as I scan, but I would measure how many entries it examines when the requested status is rare. If the API filters one exact status, status-first may be better. For deep pages I would use keyset pagination with a unique tie-breaker rather than large offsets. The deciding factor is the actual query contract and key order, not merely having all columns in an index.
