PostgreSQL: Leggere i Piani di Esecuzione e Capire Quando un Indice Serve Davvero

Aggiungere un indice è la prima cosa che si fa quando una query è lenta. È anche una delle più sbagliate, se fatta senza guardare cosa sta facendo il database. PostgreSQL non decide in base a quanto tu credi che una query sia selettiva: decide in base a stime, costi e statistiche. Se le stime sono sbagliate, l'indice giusto resta lì inutilizzato e quello sbagliato viene scelto per la query sbagliata.

La buona notizia è che tutto questo è visibile. EXPLAIN non è uno strumento da DBA incalliti: è il modo normale di lavorare con un database relazionale.

Un piano di esempio, letto riga per riga

Prendiamo una tabella di ordini con un indice su (customer_id, created_at) e una query tipica: gli ultimi ordini di un cliente.

EXPLAIN ANALYZE
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 20;

L'output, in un caso favorevole, assomiglia a questo (i numeri sono un esempio, non una misura reale):

Limit  (cost=0.43..8.71 rows=20 width=24) (actual time=0.031..0.058 rows=20 loops=1)
  ->  Index Scan Backward using orders_customer_created_idx on orders
        (cost=0.43..412.55 rows=105 width=24) (actual time=0.029..0.052 rows=20 loops=1)
        Index Cond: ((customer_id = 42) AND (created_at >= (now() - '30 days'::interval)))
Planning Time: 0.212 ms
Execution Time: 0.081 ms

Come si legge:

• Il piano si legge dall'interno verso l'esterno. Il nodo più indentato è quello che tocca i dati per primo; quelli esterni consumano il suo output. Qui dentro c'è un Index Scan Backward, fuori un Limit.
• cost=0.43..8.71 sono due valori: costo di avvio e costo totale. Non sono millisecondi, sono unità arbitrarie calibrate su quanto costa leggere una pagina da disco. Servono a confrontare piani tra loro, non a prevedere il tempo.
• rows=20 nel nodo esterno è la stima di quante righe usciranno da quel nodo. width=24 è la stima della larghezza media in byte.
• Con ANALYZE compaiono anche actual time e rows reali, più il numero di loops. Se stima e realtà divergono di ordini di grandezza, il problema non è l'indice: sono le statistiche.
• Index Scan Backward è la parte che fa risparmiare più tempo: l'indice è già ordinato, quindi il database legge 20 righe e si ferma. Con un Seq Scan avrebbe dovuto ordinare tutto il risultato e poi tagliare.

Costo non è tempo

Il primo errore è leggere il costo come se fosse una durata. Non lo è. Il planner confronta alternative usando un modello: quanto costa leggere in sequenza, quanto costa un accesso casuale, quanto costa ordinare. Il costo serve a scegliere, non a misurare.

Per misurare si usa EXPLAIN ANALYZE, che esegue davvero la query. Attenzione: se la query è una INSERT, UPDATE o DELETE, la esegue sul serio. Il modo sicuro è racchiuderla in una transazione annullata:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE created_at < now() - interval '5 years';
ROLLBACK;

Seq Scan contro Index Scan

Il planner sceglie un accesso sequenziale quando deve leggere una frazione alta della tabella: leggere pagine consecutive è più economico che saltare qua e là. La soglia non è fissa, dipende dalla larghezza delle righe e dalla cache.

Un Seq Scan non è un sintomo. È una scelta, e nella maggior parte dei casi è quella giusta. Se lo vedi su una query veloce, non c'è niente da correggere.

Un indice ha un prezzo

Ogni indice occupa spazio e va aggiornato ad ogni INSERT, UPDATE e DELETE sulla tabella. Su una tabella con cinque indici, una singola scrittura diventa sei scritture, più il lavoro di manutenzione. In un carico di sola lettura il costo è quasi nullo; in un carico di scrittura intenso è la prima cosa che si paga.

Quindi la domanda giusta non è "questo indice rende più veloce questa query?" ma "questo indice rende più veloce questa query abbastanza da giustificare il costo su tutte le scritture?". Se la query gira una volta al giorno e non è critica, la risposta è spesso no.

Due accorgimenti che riducono il costo senza rinunciare ai benefici:

• Indici parziali, quando le query toccano sempre un sottoinsieme: CREATE INDEX ... ON orders (created_at) WHERE status = 'pending';
• Indici con INCLUDE, per aggiungere colonne al payload dell'indice ed evitare di tornare alla tabella (index-only scan), senza includerle nella chiave di ordinamento.

Indici composti: l'ordine delle colonne conta

Un indice su (customer_id, created_at) è un indice ordinato prima per cliente e poi, dentro ogni cliente, per data. Questo lo rende utile per:

WHERE customer_id = 42
WHERE customer_id = 42 AND created_at >= '2025-01-01'
WHERE customer_id = 42 ORDER BY created_at DESC

Ma non per WHERE created_at >= '2025-01-01' da sola, perché i valori di created_at non sono contigui nell'indice: sono sparsi tra i vari clienti.

La regola pratica è: uguaglianze prima, intervalli dopo. Le colonne usate con = vanno in testa, quelle usate con range o ordinamento subito dopo. Un indice su (created_at, customer_id) sarebbe molto meno utile nelle query di sopra, anche se contiene le stesse colonne.

Perché l'indice non viene usato

Ci sono poche cause ricorrenti, e riconoscerle evita di creare indici inutili:

• Una funzione applicata alla colonna. WHERE date_trunc('day', created_at) = '2025-03-01' non può usare un indice su created_at, perché il planner non sa invertire la funzione. Soluzioni: riscrivere come intervallo (>= '2025-03-01' AND < '2025-03-02') oppure creare un indice sull'espressione.
• Tipi non allineati. Confrontare una colonna bigint con un parametro testuale può costringere a un cast che disattiva l'indice, soprattutto nei join tra colonne di tipo diverso.
• LIKE '%testo%'. Un prefisso variabile non è utilizzabile da un indice B-tree. Se serve davvero la ricerca full-text sul mezzo delle stringhe, la strada è pg_trgm con un indice GIN.
• Statistiche vecchie. Se la tabella è cambiata molto dall'ultimo ANALYZE, le stime sono fuori scala e il planner decide male. Su tabelle scritte in modo intenso, controlla che l'autovacuum non sia in ritardo.
• Colonne correlate. Se city e cap sono quasi sempre coerenti tra loro, PostgreSQL assume indipendenza e stima numeri irrealistici. Le extended statistics risolvono questo caso specifico.

Il metodo, in pratica

Quando una query è lenta, l'ordine di lavoro è sempre lo stesso:

1. Trova la query che pesa. pg_stat_statements aggrega per tempo totale, non per singola esecuzione: una query da 5 ms chiamata un milione di volte pesa più di una da 2 secondi chiamata una volta.
2. Guarda il piano con EXPLAIN (ANALYZE, BUFFERS). I buffer letti e quelli trovati in cache dicono se il problema è la CPU o il disco.
3. Confronta stime e realtà. Se rows stimato è 100 e quello reale è 400.000, non aggiungere indici: aggiorna le statistiche o aggiungi quelle estese.
4. Identifica il nodo che costa. Spesso è un Nested Loop con troppe iterazioni, o un Sort su un risultato grande.
5. Valuta l'indice minimo che risolve quel nodo, e chiediti se il carico di scrittura lo tollera.
6. Misura di nuovo. Se il piano non cambia, rimuovi l'indice: non serve, e lo stai pagando.

Un test rapido per capire se un indice candidato verrebbe davvero scelto: disattiva temporaneamente gli scan sequenziali nella sessione corrente con SET enable_seqscan = off; e rileggi il piano. Serve solo come diagnosi, mai come configurazione permanente: obbligare il planner a usare un indice è quasi sempre un modo per peggiorare le cose.

Conclusione

• Il piano di esecuzione è l'unico modo per sapere cosa fa davvero il database: si legge dall'interno verso l'esterno e va letto insieme ai numeri reali di EXPLAIN ANALYZE.
• Il costo non è tempo: serve a confrontare piani. Il tempo si misura, non si stima.
• Un Seq Scan è spesso la scelta corretta. Un indice in più è un costo su ogni scrittura, non un miglioramento gratuito.
• Negli indici composti l'ordine delle colonne decide se l'indice verrà usato: uguaglianze in testa, intervalli e ordinamenti dopo.
• Prima di creare un indice, controlla le statistiche: molte query lente sono in realtà query con stime sbagliate.