EXPLAIN query mostra il piano che PostgreSQL sceglie per una query: un albero di passaggi (nodi) con costi e numeri di righe stimati. EXPLAIN ANALYZE query esegue anche la query e aggiunge il tempo, le righe e i cicli reali di ogni nodo. Leggi l'albero partendo dal nodo più rientrato e andando verso l'esterno: i nodi interni passano le loro righe al nodo sopra di loro.
Non c'è un indice su email, quindi il piano è un Seq Scan: PostgreSQL legge ogni riga e tiene quelle che superano il Filter. Rows Removed by Filter mostra il lavoro sprecato. I tempi cambiano a ogni esecuzione; conta più la forma del piano che i numeri.
Leggere una riga del piano
Ogni nodo stampa una riga con questa forma:
Seq Scan on users (cost=STARTUP..TOTAL rows=N width=W) (actual time=FIRST..LAST rows=N loops=L)
| Parte | Significato |
|---|---|
cost=STARTUP..TOTAL | lavoro stimato prima della prima riga, e per tutte le righe, in unità del planner (non millisecondi) |
rows (il primo) | numero stimato di righe che il nodo restituisce |
width | dimensione media stimata di una riga in byte |
actual time | millisecondi fino alla prima riga e fino all'ultima, per ciclo |
rows (reale) | righe restituite, per ciclo |
loops | quante volte è stato eseguito il nodo; moltiplica tempo e righe per questo valore |
I costi di un nodo padre includono quelli dei suoi figli. Planning Time ed Execution Time in fondo sono i totali dell'intera istruzione.
Riconoscere i nodi più comuni
EXPLAIN (COSTS OFF) nasconde i numeri, così la forma del piano si vede meglio:
Leggi dall'interno. Entrambe le tabelle vengono scansionate e i clienti filtrati finiscono in un Hash. L'Hash Join lo interroga con ogni ordine, e il GroupAggregate in cima conta il risultato. I nodi che incontrerai più spesso:
| Nodo | Cosa fa |
|---|---|
Seq Scan | legge tutta la tabella |
Index Scan | trova le righe tramite un indice, poi legge ogni riga dalla tabella |
Index Only Scan | risponde dall'indice senza visitare la tabella |
Bitmap Index Scan + Bitmap Heap Scan | raccoglie da un indice le posizioni delle righe che corrispondono, poi le legge nell'ordine della tabella |
Nested Loop | per ogni riga esterna, cerca le corrispondenze nell'input interno; va bene quando il lato esterno è piccolo |
Hash Join + Hash | costruisce una tabella hash da un input e la interroga con l'altro |
Merge Join | unisce due input già ordinati sulla chiave di join |
Sort, Incremental Sort | ordina le righe per ORDER BY, DISTINCT o un merge join |
HashAggregate, GroupAggregate | GROUP BY con una tabella hash, oppure su input ordinato |
Limit | si ferma dopo N righe |
Gather | raccoglie le righe dai worker paralleli |
Trovare le stime di righe sbagliate
Il controllo più utile in EXPLAIN ANALYZE è confrontare le rows stimate con le rows reali sullo stesso nodo. Quando sono molto distanti, il planner ha scelto il piano partendo da numeri sbagliati, spesso perché le statistiche mancano o non sono aggiornate:
La scansione restituisce 10 righe reali, ma la stima è molto più alta perché la tabella non è ancora stata analizzata. Aggiungi ANALYZE tasks; prima di EXPLAIN e la stima si avvicina a 10. L'autovacuum analizza le tabelle in background, ma esegui tu ANALYZE dopo un caricamento massivo (vedi VACUUM e autovacuum).
Analizzare una modifica ai dati senza tenerla
EXPLAIN ANALYZE esegue l'istruzione, quindi un UPDATE o un DELETE modifica davvero le righe. Mettilo dentro una transazione e fai rollback:
Dopo il rollback ci sono ancora tutte le 1000 righe.
Mostrare l'I/O con BUFFERS e altre opzioni
EXPLAIN (ANALYZE, BUFFERS) SELECT ...; -- pages read from cache (hit) and disk (read)
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT ...; -- also list changed planner settings
EXPLAIN (FORMAT JSON) SELECT ...; -- machine-readable, for plan visualizers
EXPLAIN (ANALYZE, TIMING OFF) SELECT ...; -- row counts without per-node timing overhead
BUFFERS mostra l'I/O: un nodo con molte pagine read è andato su disco. PostgreSQL 17 ha aggiunto SERIALIZE (il costo di convertire in testo le righe di output) e MEMORY (la memoria usata dal planner). La guida ufficiale a EXPLAIN analizza altri piani.
Provare un piano con le impostazioni del planner
Per vedere il piano che PostgreSQL userebbe senza un certo metodo, disattiva quel metodo nella tua sessione. Fallo solo negli esperimenti, mai nel codice di produzione:
Con 100 righe il planner preferisce un Seq Scan; con enable_seqscan = off il piano mostra Index Scan using items_pkey. Confronta i tempi dei due EXPLAIN ANALYZE per capire se l'indice aiuta. RESET enable_seqscan; ripristina il valore predefinito.
Domande frequenti
Che differenza c'è tra EXPLAIN ed EXPLAIN ANALYZE?
EXPLAIN pianifica soltanto la query e mostra delle stime. EXPLAIN ANALYZE la esegue anche e aggiunge il tempo reale, il numero di righe e il numero di cicli di ogni nodo, quindi impiega quanto la query stessa.
Cosa significa cost in un piano di PostgreSQL?
Il cost è la stima del planner in unità arbitrarie, scalate in modo che leggere una pagina in sequenza costi 1 (seq_page_cost). Serve per confrontare piani diversi della stessa query, non come un tempo in millisecondi.
EXPLAIN ANALYZE modifica i dati?
Sì, per INSERT, UPDATE, DELETE e MERGE esegue la modifica. Lancialo tra BEGIN e ROLLBACK per misurare senza tenere il risultato.
Perché viene scelto un Seq Scan quando esiste un indice?
Perché il planner stima che costi meno: la tabella è piccola, la query restituisce una grossa parte delle righe, oppure la condizione non può usare l'indice. Anche statistiche non aggiornate possono ingannarlo, e ANALYZE lo risolve.