Why can an index on last_name not supply the order for ORDER BY LOWER(last_name)?

## An index records one specific ordering A B-tree index on `last_name` is a sorted list of the stored `last_name` values (in the column's collation) plus pointers to rows. Reading it yields exactly one ordering: by `last_name`. When a query asks for rows ordered by `LOWER(last_name)`, that is a different ordering of the same rows, and the index holds no information about it. The optimiser will not — and cannot safely — assume that applying a function leaves the sequence intact, so it plans a sort. This is not a limitation of a particular engine; it follows from what the structure stores. The consequence for the query author is sharp: an expression around an ordering key silently converts a cheap ordered walk into a blocking sort, which matters most when a row limit sits on top, because that limit then bounds the result without bounding the work. ## Order-preserving transforms you can strip Many expressions people write are strictly increasing over the column's domain. If `f` never moves a value ahead of a smaller one, then ordering by the bare column already satisfies ordering by `f(col)`, and you can simply delete the wrapper: ```sql -- index on invoices(total_cents) SELECT * FROM invoices ORDER BY total_cents / 100.0 DESC FETCH FIRST 20 ROWS ONLY; -- sorts SELECT * FROM invoices ORDER BY total_cents DESC FETCH FIRST 20 ROWS ONLY; -- index walk ``` Dividing by a positive constant, adding a constant, and multiplying by a positive constant are all order-preserving. So is truncating a timestamp to a date: ordering by the full timestamp is a *refinement* of ordering by its date part, and since rows sharing a date may legally come back in any sequence, the refined ordering is still a correct answer to the original request. Ordering by an alias of a computed select-list column has the same trap and the same cure — check what the alias expands to. One related rewrite is worth naming: `ORDER BY -price` expresses descending order through arithmetic. Replace it with `ORDER BY price DESC`, which the index can serve. Be careful if the column is nullable, because engines that place NULLs at opposite ends for `ASC` and `DESC` will move the NULL rows; add an explicit `NULLS FIRST` or `NULLS LAST` if their position matters — and then confirm you have not reintroduced a sort by asking for a placement the index does not physically have. ## Transforms you cannot strip Other expressions genuinely reorder rows, and no rewriting of the `ORDER BY` will make the plain column index apply: - **`LOWER(name)` / `UPPER(name)`** under a case-sensitive collation. Case-sensitive orderings typically group by case, so `'Zeta' < 'alpha'` while `LOWER` makes `'alpha' < 'zeta'`. Different sequence, different ordering. - **`COALESCE(shipped_at, created_at)`** — the value being ordered comes from two columns, so no single-column index describes it. - **`CASE WHEN status = 'URGENT' THEN 0 ELSE 1 END`** — a business-priority ordering that exists nowhere in the stored data. - **String manipulation** such as `SUBSTRING(code FROM 4)`, which reorders on a suffix the index knows nothing about. For these the options are: change the column's collation so the plain ordering *is* the one you want (a schema decision with wide consequences); persist the computed value, either as a generated column or by having the schema owner create an index on the expression; or accept the sort because the result set is small enough for it not to matter. The middle option is index-design work — as the query author your contribution is to state precisely which expression must be ordered. ## Watch for expressions you did not write An expression can arrive without being typed by you. Ordering by a select-list alias that names a computed column is one route. A comparison across mismatched types is another: if the engine has to convert a value to compare or order it, it is ordering something other than the stored column. Sorting on a column of a derived table or view whose definition wraps the column in a function is a third, and the most easily missed, because the query text you are reading looks perfectly bare. ## The habit to build When a query has a row limit and feels slower than the number of returned rows justifies, read the `ORDER BY` list and ask of each key: *is this the literal name of a stored column?* If not, decide whether the expression is order-preserving — if it is, delete it; if it is not, you are choosing between a schema change and a sort.