Why can an index on events(created_at, id) not serve ORDER BY created_at DESC, id ASC without sorting?

## How an index scan produces order The leaf level of a B-tree index is a sorted, doubly linked chain of entries. Because it is doubly linked, an engine can traverse it in either direction, and that is the whole source of "free" ordering. Traversing forwards emits entries in declared key order. Traversing backwards emits them in the exact mirror image of that order. There is no third traversal that flips some keys and leaves others alone — the chain has one sequence, and you read it one way or the other. ## The all-or-nothing direction rule For an index declared on `(created_at, id)` — both keys ascending, the default — the two available sequences are: ```sql -- CREATE INDEX ix_events_at_id ON events (created_at, id); ORDER BY created_at ASC, id ASC -- forward walk, free ORDER BY created_at DESC, id DESC -- backward walk, free ORDER BY created_at DESC, id ASC -- neither: sort ORDER BY created_at ASC, id DESC -- neither: sort ``` Generalised: an `ORDER BY` list can be index-supplied only if every key's direction equals the corresponding index key's direction, or every key's direction is the opposite of it. Mixing is what costs you the sort. With a single ordering key the rule is invisible, because one key is trivially either "all same" or "all flipped" — which is why so many developers first meet the trap when they add a tie-breaker to a descending sort. ## Why it bites exactly there The canonical shape is a newest-first feed with a stable tie-break: ```sql SELECT * FROM events ORDER BY created_at DESC, id ASC FETCH FIRST 20 ROWS ONLY; ``` The intent is reasonable: newest first, and among events sharing a timestamp, the lower id first. But it silently converts a twenty-entry backward walk into a full sort of every row the filter admits, and the row limit no longer bounds the work. Writing `ORDER BY created_at DESC, id DESC` restores the backward walk. The result differs only in the sequence of rows that share a `created_at` value — usually a detail nobody has an opinion about, and worth trading for the sort. There is one case where you cannot simply flip: when the tie-break direction is load-bearing. If a client pages by remembering the last `(created_at, id)` it saw and asking for rows after it, the comparison predicate and the `ORDER BY` directions must agree, so you cannot flip one without reworking the other. ## What you can and cannot fix in the query text Tricks that work in a `WHERE` clause do not transfer here. Negating a numeric column (`ORDER BY created_at DESC, -id`) is an expression, and an expression discards the index order just as surely as the mixed direction did — you have traded one sort for another. There is no portable rewrite that turns a genuinely mixed ordering into an index walk. What exists instead is on the schema side: most engines let `CREATE INDEX` declare a direction per key, so an index on `(created_at DESC, id ASC)` makes the mixed list a forward walk (and `(created_at ASC, id DESC)` its free reverse). Choosing to add such an index is index-design work with its own write cost, so as the query author your first move is to check whether the fully reversed list is acceptable. ## NULL placement rides along Where NULLs sit relative to real values is part of the physical order too. Engines differ on the default — and in some, the default placement itself flips between `ASC` and `DESC`. If you write an explicit `NULLS FIRST` or `NULLS LAST` that disagrees with how the index physically stores them, you can reintroduce the sort even when the columns and directions line up. If the ordering columns are nullable and you have specified placement explicitly, verify with the plan rather than reasoning it out. ## Diagnosing it The symptom is distinctive: a query with a small row limit whose cost is proportional to the table, on columns that are indexed, and a plan showing a sort node above the scan. When you see that, read the `ORDER BY` directions against the index's declared directions before suspecting anything more exotic — mixed directions and an expression on an ordering key are the two most common causes of a sort that "should not be there".