Expression index ignored: Why COALESCE in the index didn't match the ORDER BY — 29 500× speedup

Starting point: a customer-portal search over 828 000 companies, sorted by quality_score descending with NULLs at the end. Initial runtime: 7.5 seconds for the first page. Unacceptable for a UI.

The diagnosis looked trivial. quality_score is nullable. A plain B-tree index exists, but the planner discards it because NULLS LAST requires a separate scan direction. Textbook fix: a functional index on the same expression used in the ORDER BY.

CREATE INDEX idx_firm_quality_desc
  ON firm ((COALESCE(quality_score, 0)) DESC);

SELECT id, name, quality_score
FROM firm
ORDER BY quality_score DESC NULLS LAST
LIMIT 50;

Rerun: 7.5 seconds. No improvement. EXPLAIN ANALYZE showed Bitmap Heap Scan → Top-N heapsort. The new index? Ignored.

The subtle mistake

The ORDER BY reads:

ORDER BY quality_score DESC NULLS LAST

The index reads:

CREATE INDEX ... ON firm ((COALESCE(quality_score, 0)) DESC);

For the PostgreSQL planner those are two different things. The index sorts by COALESCE(quality_score, 0). The ORDER BY sorts by quality_score with the side condition NULLS LAST. The planner can't prove that the ordering is identical in every edge case (negatives, ties with NULL, etc.), so it discards the index.

Whether a human "sees" the equivalence doesn't matter. The planner sees syntactic expressions, not semantic invariants.

The fix

Make the two lines identical — the one in the index and the one in the ORDER BY:

SELECT id, name, quality_score
FROM firm
ORDER BY COALESCE(quality_score, 0) DESC
LIMIT 50;

EXPLAIN ANALYZE after: Index Scan Backward using idx_firm_quality_desc → 0.25 ms. From 7.5 s to 0.25 ms. 29 500× speedup.

No extra indexes. No changed data. No new hardware. Just aligning the expression.

Why this is a recurring pattern

The planner only recognises exact expression matches as index candidates (plus a small set of syntactically equivalent forms — but NULLS LAST is not among them). As soon as the application writes a slightly different expression, the index is lost again.

Classics from the same family:

• Index on (LOWER(email)) — the ORDER BY / WHERE must use LOWER(email), not ILIKE.
• Index on (date_trunc('day', created_at)) — the WHERE must use the exact date_trunc form, not created_at::date.
• Partial index WHERE status = 'active' — the query must contain status = 'active', not status IN ('active') or UPPER(status) = 'ACTIVE'.

Operational consequence

When you create a functional index, document both statements together — in the index comment or in the migration script:

-- Intended ORDER BY:
--   SELECT ... FROM firm ORDER BY COALESCE(quality_score, 0) DESC LIMIT N;
-- Any deviation (e.g. 'quality_score DESC NULLS LAST') silently skips
-- this index and falls back to Top-N sort.
CREATE INDEX idx_firm_quality_desc
  ON firm ((COALESCE(quality_score, 0)) DESC);

Plus an EXPLAIN guard in CI if the table is large enough.

The lesson

Expression identity is not a detail. It is the binary condition under which an index is allowed to kick in at all. For nullable columns, derived fields, partial indexes — the query expression must be character-identical to the index expression.

When the speedup by orders of magnitude fails to appear, the first question is never "Is the index wrong?" — it's "Is the expression in the query the same as in the index?".