Expression index ignorisan: Zašto COALESCE u indeksu nije išao uz ORDER BY — 29 500× speedup

Polazna tačka: customer portal pretraga preko 828 000 firmi, sortirano po quality_score opadajuće, NULL vrednosti na kraju. Početno vreme: 7,5 sekundi za prvu stranicu. Neprihvatljivo za UI.

Dijagnoza je izgledala trivijalno. quality_score je nullable. Normalni B-tree indeks postoji, ali ga planner odbacuje jer NULLS LAST traži odvojen scan pravac. Rešenje po udžbeniku: funkcionalni indeks nad istom ekspresijom koja se koristi u 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;

Ponovo pokrenuto: 7,5 sekundi. Bez poboljšanja. EXPLAIN ANALYZE pokazuje Bitmap Heap Scan → Top-N heapsort. Novi indeks? Ignorisan.

Suptilna greška

ORDER BY glasi:

ORDER BY quality_score DESC NULLS LAST

Indeks glasi:

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

Za PostgreSQL planner to su dve različite stvari. Indeks sortira po COALESCE(quality_score, 0). ORDER BY sortira po quality_score sa uslovom NULLS LAST. Planner ne može dokazati da je redosled u svim graničnim slučajevima isti, pa odbacuje indeks.

Ono što čovek „vidi" kao ekvivalentno nije bitno. Planner vidi syntax expressions, ne semantičke invarijante.

Popravka

Dve linije identične — u indeksu i u ORDER BY:

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

EXPLAIN ANALYZE posle: Index Scan Backward using idx_firm_quality_desc → 0,25 ms. Od 7,5 s do 0,25 ms. 29 500× speedup.

Bez dodatnih indeksa. Bez izmene podataka. Bez novih mašina. Samo izjednačena ekspresija.

Zašto je ovo ponovljen obrazac

Planner prepoznaje samo egzaktna expression-podudaranja (plus mali broj syntactički ekvivalentnih formi — ali NULLS LAST nije tu). Čim aplikacija formuliše malo drugačiju ekspresiju, indeks je izgubljen.

Klasici iz iste porodice:

• Indeks na (LOWER(email)) — ORDER BY ili WHERE mora koristiti LOWER(email), ne ILIKE.
• Indeks na (date_trunc('day', created_at)) — WHERE mora koristiti tačno istu date_trunc formu.
• Partial indeks WHERE status = 'active' — query mora sadržati status = 'active', ne status IN ('active').

Operativna posledica

Kod svakog funkcionalnog indeksa dokumentuj oba statement-a zajedno — u komentaru indeksa ili u migraciji:

-- Intended ORDER BY:
--   SELECT ... 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.

Plus EXPLAIN guard u CI-ju ako je tabela velika.

Lekcija

Identitet ekspresije nije detalj. To je binarni uslov pod kojim indeks uopšte može da se koristi. Kod nullable kolona, kod izvedenih polja, kod partial indeksa — ekspresija u query-ju mora biti znakovno ista kao u indeksu.

Ako nema speedup-a više redova veličine, prvo pitanje nije „Da li je indeks pogrešan?", nego „Da li je ekspresija u query-ju ista kao u indeksu?".