Menu

PostgreSQL EXPLAIN ANALYZE: leggere il piano di una query

EXPLAIN mostra il piano che PostgreSQL sceglie per una query, ed EXPLAIN ANALYZE esegue la query e aggiunge il numero reale di righe e i tempi di ogni passaggio. Impara a leggere l'albero del piano e i nomi dei nodi più comuni.

Questa pagina include editor eseguibili: modifica, esegui e vedi subito l'output.

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)
ParteSignificato
cost=STARTUP..TOTALlavoro 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
widthdimensione media stimata di una riga in byte
actual timemillisecondi fino alla prima riga e fino all'ultima, per ciclo
rows (reale)righe restituite, per ciclo
loopsquante 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:

NodoCosa fa
Seq Scanlegge tutta la tabella
Index Scantrova le righe tramite un indice, poi legge ogni riga dalla tabella
Index Only Scanrisponde dall'indice senza visitare la tabella
Bitmap Index Scan + Bitmap Heap Scanraccoglie da un indice le posizioni delle righe che corrispondono, poi le legge nell'ordine della tabella
Nested Loopper ogni riga esterna, cerca le corrispondenze nell'input interno; va bene quando il lato esterno è piccolo
Hash Join + Hashcostruisce una tabella hash da un input e la interroga con l'altro
Merge Joinunisce due input già ordinati sulla chiave di join
Sort, Incremental Sortordina le righe per ORDER BY, DISTINCT o un merge join
HashAggregate, GroupAggregateGROUP BY con una tabella hash, oppure su input ordinato
Limitsi ferma dopo N righe
Gatherraccoglie 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.

Illustrazione dei linguaggi di programmazione di Coddy

Impara a programmare con Coddy

Inizia