Expression index ignoriran: zašto COALESCE u indeksu nije odgovarao ORDER BY-u — 29 500× speedup

Polazna točka: customer portal pretraga preko 828 000 tvrtki, sortirano po quality_score silazno s NULL vrijednostima na kraju. Početno trajanje: 7,5 sekundi za prvu stranicu. Neprihvatljivo za UI.

Dijagnoza je izgledala trivijalno. quality_score je nullable. Postoji običan B-tree indeks, ali ga planner odbacuje jer NULLS LAST traži odvojen scan smjer. Rješenje po udžbeniku: funkcionalni indeks nad istom ekspresijom koju koristi 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;

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

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 dvije različite stvari. Indeks sortira po COALESCE(quality_score, 0). ORDER BY sortira po quality_score s uvjetom NULLS LAST. Planner ne može dokazati da je redoslijed u svakom graničnom slučaju isti (negativne vrijednosti, izjednačenja s NULL itd.), pa odbacuje indeks.

Što čovjek „vidi" kao ekvivalentno nije bitno. Planner vidi sintaktičke ekspresije, ne semantičke invarijante.

Fix

Učini dvije linije identičnima — onu u indeksu i onu u ORDER BY:

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

EXPLAIN ANALYZE nakon: 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 izmjene podataka. Bez novih strojeva. Samo izjednačena ekspresija na oba mjesta.

Zašto je ovo ponavljajući obrazac

Planner prepoznaje samo egzaktna podudaranja ekspresije kao kandidate za indeks (plus mali skup sintaktički ekvivalentnih oblika — ali NULLS LAST nije među njima). Čim aplikacija formulira malo drugačiju ekspresiju, indeks je izgubljen.

Klasici iz iste obitelji:

• Indeks na (LOWER(email)) — ORDER BY ili WHERE mora koristiti LOWER(email), ne ILIKE.
• Indeks na (date_trunc('day', created_at)) — WHERE mora koristiti točno isti date_trunc oblik, ne created_at::date.
• Partial indeks WHERE status = 'active' — query mora sadržavati status = 'active' (ne status IN ('active') ili UPPER(status) = 'ACTIVE').

Operativna posljedica

Pri kreiranju funkcionalnog indeksa uvijek dokumentiraj oba statementa zajedno — u komentaru indeksa ili u migracijskoj 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 u CI-ju ako je tablica dovoljno 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

Identitet ekspresije nije detalj. To je binarni uvjet pod kojim indeks uopće smije zaživjeti. Za nullable stupce, izvedena polja, partial indekse — ekspresija u queryju mora biti znakovno identična ekspresiji u indeksu.

Kad izostane speedup za više redova veličine, prvo pitanje nikad nije „Je li indeks krivi?", nego „Je li ekspresija u queryju ista kao u indeksu?".