Which ORDER BY lists can an index on orders(customer_id, created_at) supply without a sort?
answer
- think about what sequence the index walk emits
- only some sublists of the key list qualify
- the leading key can be pinned by a filter
- sequence in the ORDER BY list is decisive
- predicate order in WHERE changes nothing
basics
~20 sThose whose keys form a leading prefix of the index keys, in the same sequence and with consistent directions: ORDER BY customer_id, and ORDER BY customer_id, created_at. Adding an equality filter on customer_id also frees ORDER BY created_at alone.
solid answer
~50 sAn index walk emits rows in key order, so the ordering you request has to be implied by that order. Concretely: the `ORDER BY` list must be a **leading prefix** of the index key list, in the same left-to-right sequence — `ORDER BY customer_id` and `ORDER BY customer_id, created_at` qualify, `ORDER BY created_at` and `ORDER BY created_at, customer_id` do not. There is one important extra case: if the query pins a leading key with an equality predicate, that key is constant throughout the matched range, so `WHERE customer_id = 42 ORDER BY created_at` is also index-ordered. Directions must all match the index or all be reversed. Adding a key the index does not have (`ORDER BY customer_id, status`) generally reintroduces a sort, though some engines can sort incrementally within each group of equal leading keys.
code
sql · 8 lines-- CREATE INDEX ix_orders_cust_created ON orders (customer_id, created_at);
SELECT * FROM orders ORDER BY customer_id; -- served by the index
SELECT * FROM orders ORDER BY customer_id, created_at; -- served by the index
SELECT * FROM orders ORDER BY created_at; -- not a leading prefix: sort
SELECT * FROM orders ORDER BY created_at, customer_id; -- wrong sequence: sort
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at; -- leading key pinned: served
SELECT * FROM orders WHERE customer_id > 100 ORDER BY created_at; -- range on leading key: sortgo deeper
Learn the shape of the rule: the columns you sort by must line up with the start of the index's column list, in the same sequence. Sorting by the second key alone does not qualify.
Be able to justify the prefix rule from what an index walk emits, and to explain the equality-pinned-leading-key case and why a range predicate does not give the same benefit.
Demonstrate that you verify rather than assume: read the plan for a sort node, know that predicate order in WHERE is meaningless, and understand what incremental sort buys when the ORDER BY runs past the key list.
Own the boundary between rewriting a query to fit existing indexes and asking for a new one. Index count has a write cost, so the default answer should be to shape the ORDER BY, with new indexes justified by measured workload.
## What "index order" means to the query author A B-tree index built on `(customer_id, created_at)` stores its entries sorted by `customer_id` first and, within each `customer_id` value, by `created_at`. Scanning it yields rows in exactly that composite sequence. The whole question of index-friendly `ORDER BY` is therefore: *is the ordering my query asks for already implied by that sequence?* If yes, the engine can stream rows and skip the sort. If no, it must sort. ## The prefix rule The ordering the index produces is `customer_id, created_at`. Any ordering that is a **leading prefix** of that list is satisfied by the same walk, because a sequence sorted by `(a, b)` is also sorted by `a` alone. ```sql -- CREATE INDEX ix_orders_cust_created ON orders (customer_id, created_at); SELECT * FROM orders ORDER BY customer_id; -- prefix: no sort SELECT * FROM orders ORDER BY customer_id, created_at; -- exact match: no sort SELECT * FROM orders ORDER BY created_at; -- not a prefix: sort SELECT * FROM orders ORDER BY created_at, customer_id; -- wrong sequence: sort SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at; -- leading key fixed: no sort ``` The reverse direction does not hold: an index on `(created_at)` alone cannot serve `ORDER BY customer_id, created_at`, because the rows are grouped by the wrong key. ## Equality-bound leading keys The last line above is the case candidates most often miss. When `customer_id = 42` is an equality predicate, the scan is confined to a contiguous stretch of the index where `customer_id` never varies. Within that stretch the entries are ordered by `created_at`, so `ORDER BY created_at` is already satisfied. The same reasoning extends: with keys `(a, b, c)` and `WHERE a = 1 AND b = 2`, the ordering `ORDER BY c` comes free. It is **equality** that does this — a range predicate such as `customer_id > 100` leaves the leading key varying across the scanned range, so `ORDER BY created_at` alone is no longer implied and the sort comes back. ## Sequence matters; textual predicate order does not Two different "orders" are easy to conflate. The order of columns in the `ORDER BY` list is semantic and decisive — it *is* the ordering being requested. The order in which predicates appear in the `WHERE` clause is not: `WHERE customer_id = 42 AND status = 'NEW'` and the reverse are the same query and produce the same plan. Rearranging your `WHERE` clause to "match the index" achieves nothing; rearranging your `ORDER BY` changes the answer. ## Extra keys past the index `ORDER BY customer_id, status` starts with a matching key but continues with one the index does not hold. The index gives you rows grouped correctly by `customer_id` but arbitrarily ordered inside each group, which is not the requested ordering. Classically the engine falls back to a full sort. Some engines can exploit the partial order with an incremental sort that sorts only within each run of equal `customer_id` values, which is far cheaper and can still be stopped early — PostgreSQL added such an operator in version 13. Do not assume it; check the plan. ## Directions A prefix match is necessary but not sufficient. The index can be read forwards, giving key order, or backwards, giving the exact reverse. So the `ORDER BY` directions must either all agree with the index's declared directions or all be their opposite. `ORDER BY customer_id DESC, created_at DESC` is the free reverse walk; `ORDER BY customer_id DESC, created_at ASC` is neither, and sorts. Where NULLs sort relative to real values is a second, related trap. Engines differ in their default placement, and asking for a placement the index does not physically have (`NULLS FIRST` on an index that stores them last) can reintroduce a sort even though the columns and directions line up. If you write an explicit `NULLS FIRST`/`NULLS LAST`, verify the plan. ## A practical checklist Before assuming an existing index will serve your `ORDER BY`: are all the ordering keys bare columns, in the same sequence as the index keys, starting at the first key not already pinned by an equality predicate, with directions all matching or all inverted, and with nothing between the scan and the ordering that would discard the order? Every "no" is a sort — which matters enormously when a small row limit sits on top, because a sort forces the whole input to be read before a single row is returned.
- Why does WHERE customer_id > 100 ORDER BY created_at not get the same free ordering as WHERE customer_id = 42?Because equality pins the leading key to a single value, so the scanned stretch of the index is internally ordered by `created_at`. A range leaves the leading key varying, so the walk produces `created_at` order only within each `customer_id` value, not across the whole result. That is not the ordering the query asked for, so a sort is added.
- Does rewriting the WHERE clause so its predicates appear in index key order help?No. The `WHERE` clause is a set of conditions, not a sequence; the optimiser normalises it and the textual order carries no meaning. What matters is which columns are constrained and how — equality versus range — and the sequence of the `ORDER BY` list. Reordering predicates is cargo-cult tuning.
- With keys (customer_id, created_at), can ORDER BY customer_id, created_at, id avoid a sort?Not by the prefix rule alone, because `id` is not an index key: within one `customer_id`/`created_at` pair the index imposes no order on `id`. An engine with incremental sort can sort just those tiny groups cheaply; otherwise expect a full sort. If the pair is already unique, drop the redundant tie-breaker instead.
saying these in an interview costs you the question
- Says any index on a mentioned column removes the sort
- Thinks reordering WHERE predicates makes an index usable
- Believes ORDER BY created_at works because created_at is in the index
- Ignores that a range predicate on the leading key breaks the ordering
- Confuses the order of ORDER BY keys with the order of WHERE conditions