Expression index prezrt: zakaj COALESCE v indeksu ni ustrezal ORDER BY — 29 500× speedup

Izhodišče: iskanje v customer portalu prek 828 000 podjetij, sortirano po quality_score padajoče z NULL vrednostmi na koncu. Začetni čas izvajanja: 7,5 sekund za prvo stran. Nesprejemljivo za UI.

Diagnoza je izgledala trivialna. quality_score je nullable. Navaden B-tree indeks obstaja, vendar ga planner zavrne, ker NULLS LAST zahteva ločeno smer scana. Rešitev iz učbenika: funkcijski indeks nad isto izrazno obliko, ki jo uporablja 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;

Znova pognano: 7,5 sekund. Brez izboljšanja. EXPLAIN ANALYZE kaže Bitmap Heap Scan → Top-N heapsort. Novi indeks? Prezrt.

Subtilna napaka

ORDER BY se glasi:

ORDER BY quality_score DESC NULLS LAST

Indeks se glasi:

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

Za planner PostgreSQL sta to dve različni stvari. Indeks sortira po COALESCE(quality_score, 0). ORDER BY sortira po quality_score s pogojem NULLS LAST. Planner ne more dokazati, da je vrstni red v vseh mejnih primerih enak (negativne vrednosti, izenačenja z NULL ipd.), zato indeks zavrže.

To, da človek »vidi« ekvivalentnost, je nepomembno. Planner vidi sintaktične izrazne oblike, ne semantičnih invariant.

Fix

Naredi obe vrstici identični — tisto v indeksu in tisto v ORDER BY:

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

EXPLAIN ANALYZE po fixu: Index Scan Backward using idx_firm_quality_desc → 0,25 ms. Iz 7,5 s na 0,25 ms. 29 500× speedup.

Brez dodatnih indeksov. Brez sprememb podatkov. Brez novih strojev. Le poravnana izrazna oblika v obeh točkah.

Zakaj je to ponavljajoči vzorec

Planner prepozna samo natančna ujemanja izraznih oblik kot kandidate za indeks (plus majhno število sintaktično ekvivalentnih oblik — vendar NULLS LAST ni med njimi). Brž ko aplikacija formulira nekoliko drugačno izrazno obliko, je indeks spet izgubljen.

Klasiki iz iste družine:

• Indeks na (LOWER(email)) — ORDER BY ali WHERE mora uporabljati LOWER(email), ne ILIKE.
• Indeks na (date_trunc('day', created_at)) — WHERE mora uporabljati točno isto date_trunc obliko, ne created_at::date.
• Partial indeks WHERE status = 'active' — query mora vsebovati status = 'active' (ne status IN ('active') ali UPPER(status) = 'ACTIVE').

Operativna posledica

Pri ustvarjanju funkcijskega indeksa vedno dokumentiraj oba statementa skupaj — v komentarju indeksa ali v migracijski skripti:

-- 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);

Plus EXPLAIN guard v CI, če je tabela dovolj velika:

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])

Lekcija

Identiteta izrazne oblike ni podrobnost. To je binarni pogoj, pod katerim indeks sploh sme zaživeti. Pri nullable stolpcih, izpeljanih poljih, partial indeksih — izrazna oblika v queryju mora biti znakovno identična izrazni obliki v indeksu.

Ko speedup za nekaj velikostnih redov ne pride, prvo vprašanje ni nikoli »Je indeks napačen?«, ampak »Je izrazna oblika v queryju enaka kot v indeksu?«.