Interpretazione del piano di esecuzione: lettura e analisi dei nodi (Seq Scan, Index Scan, Hash Join)

Oggi vediamo insieme cosa sono i nodi del piano di esecuzione di PostgreSQL, come leggerli e, soprattutto, come capire quando qualcosa non va. Capirai perché a volte il tuo database preferisce usare il costoso Seq Scan, anche se pensi che l’indice ci sia già, magari anche più di uno.

Quando PostgreSQL costruisce il piano di esecuzione di una query, lo divide in step chiamati nodi. Ogni nodo è una specie di "passo" che il server del database esegue per processare la tua query. I tipi principali di nodi sono:

Sequential Scan (Seq Scan)

Seq Scan, cioè scansione sequenziale, è il modo più semplice per estrarre dati da una tabella. PostgreSQL prende letteralmente la tabella, legge le righe una dopo l’altra e controlla se rispettano le condizioni della tua query.

Quando viene usato Seq Scan?
Seq Scan si usa se:

• Nella tabella non c’è un indice adatto per velocizzare la query.
• La condizione di filtro è troppo generica perché l’indice sia utile (tipo, estrarre più del 50% dei dati).
• PostgreSQL pensa che leggere la tabella tutta di fila sia più veloce che usare l’indice (a volte succede con tabelle molto piccole).

EXPLAIN SELECT * FROM studenti WHERE eta > 18;

Esempio di risultato:

Seq Scan on studenti  (cost=0.00..35.50 rows=10 width=50)
  Filter: (eta > 18)

Nota Seq Scan on studenti — qui PostgreSQL ti dice che leggerà tutta la tabella "studenti".

Problemi con Seq Scan: Se la tabella è enorme, la scansione sequenziale può richiedere un sacco di tempo.

Index Scan

Index Scan è la scansione dei dati usando un indice. Quando crei un indice in PostgreSQL, è un po’ come fare un indice analitico per la tua tabella. Se la query può usare l’indice, PostgreSQL non scorre tutta la tabella, ma solo le parti che servono.

Quando viene usato Index Scan?

• Nella query ci sono condizioni di filtro su una colonna indicizzata (tipo WHERE).
• Si usano operatori di confronto come =, <, >, BETWEEN ecc.

CREATE INDEX idx_studenti_eta ON studenti(eta);

EXPLAIN SELECT * FROM studenti WHERE eta = 18;

Esempio di risultato:

Index Scan using idx_studenti_eta on studenti  (cost=0.15..8.27 rows=1 width=50)
  Index Cond: (eta = 18)

Qui Index Scan using idx_studenti_eta mostra che PostgreSQL sta usando l’indice idx_studenti_eta. La lettura riga per riga della tabella viene sostituita da un accesso molto più veloce tramite l’indice.

Vantaggi di Index Scan:

• Query molto più veloci su tabelle grandi.
• Meno dati letti dal disco.

Problemi di Index Scan:
Se la tua query restituisce troppi dati (tipo più della metà della tabella), usare l’indice può essere addirittura più lento di Seq Scan.

Hash Join

Hash Join si usa per unire due tabelle in base a una condizione di join (tipo ON studenti.corso_id = corsi.id). PostgreSQL crea una hash table per una delle due tabelle (quella più piccola) e la usa per trovare i match nell’altra tabella.

Quando viene usato Hash Join?

• Quando fai join tra tabelle con INNER JOIN, LEFT JOIN ecc.
• Quando PostgreSQL pensa che Hash Join sia più efficiente di altri metodi di join.

EXPLAIN
SELECT * 
FROM studenti 
JOIN corsi ON studenti.corso_id = corsi.id;

Esempio di risultato:

Hash Join  (cost=25.00..50.00 rows=10 width=100)
  Hash Cond: (studenti.corso_id = corsi.id)
  -> Seq Scan on studenti  (cost=0.00..20.00 rows=10 width=50)
  -> Hash  (cost=15.00..15.00 rows=10 width=50)
       -> Seq Scan on corsi  (cost=0.00..15.00 rows=10 width=50)

Qui Hash Join unisce due tabelle. Nota che PostgreSQL prima fa un Seq Scan su entrambe le tabelle e poi costruisce la hash table (Hash).

Vantaggi di Hash Join:

• Veloce su tabelle di dimensioni medie.
• Efficiente per join tra tabelle con tante righe.

Problemi di Hash Join:
Se la hash table è più grande della memoria disponibile, PostgreSQL userà il disco per salvarla, e questo rallenta parecchio il join.

Esempio di analisi di un piano di esecuzione

Vediamo un esempio reale.

Query:

EXPLAIN ANALYZE
SELECT *
FROM studenti
JOIN corsi ON studenti.corso_id = corsi.id
WHERE studenti.eta > 18;

Risultato:

Hash Join  (cost=35.00..75.00 rows=5 width=100) (actual time=1.00..2.50 rows=5 loops=1)
  Hash Cond: (studenti.corso_id = corsi.id)
  -> Seq Scan on studenti  (cost=0.00..40.00 rows=10 width=50) (actual time=0.50..1.00 rows=7 loops=1)
        Filter: (eta > 18)
        Rows Removed by Filter: 3
  -> Hash  (cost=25.00..25.00 rows=5 width=50) (actual time=0.30..0.30 rows=5 loops=1)
       -> Seq Scan on corsi  (cost=0.00..20.00 rows=5 width=50) (actual time=0.20..0.25 rows=5 loops=1)
Planning Time: 0.50 ms
Execution Time: 3.00 ms

Interpretazione:

1. Hash Join: Nodo principale. PostgreSQL unisce le tabelle studenti e corsi.
   • actual time: da 1.00 a 2.50 ms.
   • rows=5: la query ha restituito 5 righe.
2. Nodi annidati:
   • Seq Scan on studenti: legge in sequenza la tabella studenti e applica il filtro (eta > 18).
   • Rows Removed by Filter = 3: 3 righe non rispettavano la condizione.
   • Hash: PostgreSQL crea una hash table per la tabella corsi.

Confronto e scelta dei nodi

Quando analizzi il piano di esecuzione, la chiave è capire perché PostgreSQL ha scelto un certo metodo per processare i dati. A volte devi intervenire tu per sistemare le cose, tipo aggiungere un indice o riscrivere la query. Ecco qualche dritta:

• Se vedi Seq Scan su una tabella grande, pensa agli indici.
• Se Hash Join è troppo lento, controlla la memoria disponibile per PostgreSQL.
• Usa EXPLAIN ANALYZE per confrontare i valori stimati e quelli reali delle metriche (rows, time).

A questo punto hai già una base per leggere i piani di esecuzione delle query e interpretare i nodi. Nelle prossime lezioni parleremo dei problemi di ottimizzazione più comuni e di come risolverli.