Expression-Index ignoriert: Warum COALESCE im Index nicht zum ORDER BY passte — 29 500× Speedup

Der Ausgangspunkt: Eine Customer-Portal-Suche über 828 000 Unternehmen, sortiert nach quality_score absteigend, NULL-Werte am Ende. Initial-Laufzeit: 7,5 Sekunden für die erste Seite. Nicht akzeptabel für eine UI.

Die Diagnose schien trivial. quality_score ist nullable. Ein normaler B-Tree-Index auf quality_score ist da, wird vom Planner aber verworfen, weil NULLS LAST eine getrennte Scan-Richtung erfordert. Lösung laut Lehrbuch: funktionaler Index auf dem Ausdruck, der auch im ORDER BY steht.

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

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

Erneut ausgeführt: 7,5 Sekunden. Keine Verbesserung. EXPLAIN ANALYZE zeigte Bitmap Heap Scan → Top-N heapsort. Der neue Index? Ignoriert.

Der subtile Fehler

Das ORDER BY lautet:

ORDER BY quality_score DESC NULLS LAST

Der Index lautet:

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

Für den PostgreSQL-Planner sind das zwei verschiedene Dinge. Der Index sortiert nach COALESCE(quality_score, 0). Das ORDER BY sortiert nach quality_score mit der Nebenbedingung NULLS LAST. Der Planner kann nicht beweisen, dass die Sortierreihenfolge in allen Randfällen gleich ist (Negativ-Werte, Gleichstände mit NULL etc.), also verwirft er den Index.

Dass ein Mensch die Äquivalenz „sieht", ist irrelevant. Der Planner sieht Syntax-Expressions, keine semantischen Invarianten.

Der Fix

Zwei Zeilen identisch machen — die im Index und die im ORDER BY:

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

EXPLAIN ANALYZE nach dem Fix: Index Scan Backward using idx_firm_quality_desc → 0,25 ms. Von 7,5 s auf 0,25 ms. 29 500× Speedup.

Keine zusätzlichen Indexe. Keine geänderten Daten. Keine neuen Maschinen. Nur die Expression in beiden Ausdrücken angleichen.

Warum das ein wiederkehrendes Muster ist

Der Planner erkennt nur exakte Expression-Matches als Index-Kandidaten (plus eine kleine Menge syntaktisch äquivalenter Formen — aber NULLS LAST fällt nicht darunter). Sobald die Anwendung eine minimal andere Expression formuliert, geht der Index wieder verloren.

Weitere Klassiker aus derselben Familie:

• Index auf (LOWER(email)) — das ORDER BY oder WHERE muss LOWER(email) nutzen, nicht ILIKE.
• Index auf (date_trunc('day', created_at)) — das WHERE muss exakt dieselbe date_trunc-Form nutzen, nicht created_at::date.
• Teilindex WHERE status = 'active' — die Query muss status = 'active' (nicht status IN ('active') oder UPPER(status) = 'ACTIVE') enthalten.

Operative Konsequenz

Beim Anlegen eines funktionalen Index immer beide Statements nebeneinander dokumentieren — im Index-Kommentar oder im Migrations-Script:

-- Intended ORDER BY:
--   SELECT ... FROM firm ORDER BY COALESCE(quality_score, 0) DESC LIMIT N;
-- Any deviation in the ORDER BY (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);

Und ein EXPLAIN-Guard im CI, falls die Tabelle groß genug ist:

def test_firm_search_uses_index():
    plan = db.execute("EXPLAIN (FORMAT JSON) "
                      "SELECT ... ORDER BY COALESCE(quality_score, 0) DESC LIMIT 50")
    assert "idx_firm_quality_desc" in json.dumps(plan[0][0])

Die Lektion

Expression-Identität ist keine Feinheit. Sie ist die binäre Bedingung, unter der ein Index überhaupt greifen darf. Bei Nullable-Spalten, bei abgeleiteten Feldern, bei Partial-Indexen — der Ausdruck in der Query muss zeichengleich dem Ausdruck im Index sein.

Wenn der Speedup um mehrere Größenordnungen ausbleibt, ist die erste Frage nie „Ist der Index falsch?", sondern „Ist die Expression im Query dieselbe wie im Index?".