The ORDER BY column is indexed and the query ends in FETCH FIRST 20 ROWS ONLY, yet it reads millions of rows. Why?

## Ordering is a property that can be lost When an index supplies ordering, that ordering is a property of the row stream leaving the scan. It has to survive every operator between that scan and the row limit at the top. Most query authors internalise the first half of the rule — pick an `ORDER BY` the index can produce — and forget the second, which is where production surprises come from. ## Operators that discard order An operator preserves order only if it emits rows in the sequence it received them. Filters and simple projections do. These generally do not: - **Duplicate elimination by hashing** — `DISTINCT`, and `UNION` (which deduplicates), build a hash structure and emit in whatever sequence it yields. - **Hash aggregation** — grouping via a hash table destroys input order; a sorted grouping strategy may preserve it, but you cannot assume which one the engine picks. - **Hash joins** — the probe side's order is generally not preserved, and the build side must be fully consumed first. - **Any operator that materialises its input** before emitting, including another sort. When the order is gone, the engine restores it with a sort placed above that operator. Since a sort is blocking, the row limit above it cannot stop anything early: the whole intermediate result is produced first. ## Ordering by a value that does not exist yet A related and very common case is ordering by something computed after the base rows are read: ```sql SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id ORDER BY n DESC FETCH FIRST 20 ROWS ONLY; ``` No index on `orders` can hold the order of a per-customer count, because that count is not stored anywhere. Every group must be formed before the top twenty can be identified. The same applies to ordering by a window function's result, by a `CASE` expression, or by a computed score. If the ordering key is derived, expect a sort and size the query accordingly — usually by filtering the input hard rather than hoping the limit will save you. ## The ordering that survives but does not help The second failure mode is subtler because the plan contains no sort at all: ```sql -- index on events(created_at); status = 'ARCHIVED' matches one row in 100,000 SELECT * FROM events WHERE status = 'ARCHIVED' ORDER BY created_at DESC FETCH FIRST 20 ROWS ONLY; ``` The engine walks `created_at` backwards, checks `status` on each row, and throws almost all of them away. It stops as soon as twenty rows pass — but with that selectivity, twenty survivors sit around two million entries deep. Worse, this plan's cost depends on *where in time* the matching rows happen to be. It behaves beautifully when archived events are recent and catastrophically after the data shifts, which is why it so often appears as "the query that was fast for a year". The plan looks innocent; the giveaway is a huge count of rows removed by the filter beneath an ordered scan, or an actual row count far above the limit. ## The row limit does not bound the work of a sort Worth stating plainly because candidates repeatedly assume otherwise: a row limit above a sort reduces what is *returned*, never what is *read*. Engines optimise it into a bounded top-N sort — keep an n-element structure, discard losers immediately — which cuts memory and comparisons and avoids spilling, and is a genuine win. It does not reduce the input scan by a single row. ## Author-side remedies What you can do without touching the schema: - **Order and cut before the join.** If the ordering key belongs to one table and the join only adds detail columns, do the ordered, limited selection in a derived table and join to that result. The join then processes twenty rows instead of ordering millions. - **Remove the deduplication.** A `DISTINCT` that exists only to undo join fan-out can often be replaced by a semi-join idiom, which keeps the driving table's order intact. - **Do not order by derived values when a stored equivalent exists** — ordering by a stored timestamp instead of a computed rank keeps the index in play. - **Narrow the input** when the ordering must be sorted, so the sort is over thousands of rows rather than millions. - **Make the ordering keys bare columns with consistent directions**, so nothing below the limit reintroduces a sort for a reason you control. What you cannot fix in query text is the selective-filter walk: making that cheap requires an index whose keys carry both the filter column and the ordering column, which is index-design work. Your job is to diagnose it precisely and hand over the exact predicate-plus-ordering pair, rather than reporting "the query is slow". ## Confirming which one you have Run the plan with execution statistics and look at two things: is there a sort node above the scan, and how many rows actually flowed through the scan compared with the twenty returned? A sort node means the order was destroyed or never available; a scan with millions of actual rows and no sort means the order held but the filter ate it. The remedies are different, so it is worth the thirty seconds to tell them apart.