Expression index ignorato: perché COALESCE nell'indice non corrispondeva all'ORDER BY — 29 500× di speedup

Punto di partenza: una ricerca nel customer portal su 828 000 aziende, ordinata per quality_score decrescente con i NULL alla fine. Runtime iniziale: 7,5 secondi per la prima pagina. Inaccettabile per una UI.

La diagnosi sembrava banale. quality_score è nullable. Un normale B-tree index c'è, ma il planner lo scarta perché NULLS LAST richiede una direzione di scan separata. Soluzione da manuale: un indice funzionale sulla stessa espressione usata nell'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;

Eseguito di nuovo: 7,5 secondi. Nessun miglioramento. EXPLAIN ANALYZE mostrava Bitmap Heap Scan → Top-N heapsort. Il nuovo indice? Ignorato.

L'errore sottile

L'ORDER BY recita:

ORDER BY quality_score DESC NULLS LAST

L'indice recita:

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

Per il planner di PostgreSQL sono due cose diverse. L'indice ordina per COALESCE(quality_score, 0). L'ORDER BY ordina per quality_score con la condizione NULLS LAST. Il planner non può dimostrare che l'ordinamento sia identico in ogni caso limite (valori negativi, parità con NULL, ecc.), quindi scarta l'indice.

Che un umano «veda» l'equivalenza è irrilevante. Il planner vede espressioni sintattiche, non invarianti semantici.

Il fix

Rendere identiche le due righe — quella nell'indice e quella nell'ORDER BY:

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

EXPLAIN ANALYZE dopo il fix: Index Scan Backward using idx_firm_quality_desc → 0,25 ms. Da 7,5 s a 0,25 ms. 29 500× speedup.

Nessun indice aggiuntivo. Nessun dato modificato. Nessuna nuova macchina. Solo allineare l'espressione in entrambi i punti.

Perché è un pattern ricorrente

Il planner riconosce solo match esatti di espressione come candidati index (più un piccolo insieme di forme sintatticamente equivalenti — ma NULLS LAST non è tra queste). Non appena l'applicazione formula un'espressione leggermente diversa, l'indice è perso di nuovo.

Altri classici della stessa famiglia:

• Indice su (LOWER(email)) — l'ORDER BY o WHERE deve usare LOWER(email), non ILIKE.
• Indice su (date_trunc('day', created_at)) — il WHERE deve usare esattamente la stessa forma date_trunc, non created_at::date.
• Partial index WHERE status = 'active' — la query deve contenere status = 'active' (non status IN ('active') o UPPER(status) = 'ACTIVE').

Conseguenza operativa

Quando crei un indice funzionale, documenta sempre entrambe le istruzioni insieme — nel commento dell'indice o nello script di migrazione:

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

Più un EXPLAIN-guard nella CI se la tabella è abbastanza grande:

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

La lezione

L'identità dell'espressione non è un dettaglio. È la condizione binaria sotto cui un indice può essere usato. Per colonne nullable, campi derivati, indici parziali — l'espressione nella query deve essere carattere per carattere identica a quella nell'indice.

Quando lo speedup di più ordini di grandezza non si materializza, la prima domanda non è mai «L'indice è sbagliato?», ma «L'espressione nella query è la stessa di quella nell'indice?».