EXPLAIN mostra il piano di esecuzione delle query di PostgreSQL — come eseguirà una query (quali scan, join, indici). EXPLAIN ANALYZE esegue effettivamente la query e mostra i tempi reali e i conteggi delle righe. Questi sono gli strumenti principali per diagnosticare le query lente.
EXPLAIN orders customer_id ;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;
-- ACTUALLY runs it → shows REAL time, ACTUAL rows, and where time was spent
EXPLAIN (ANALYZE, BUFFERS) SELECT ...; -- also shows buffer/cache usage
EXPLAIN ANALYZE è più utile perché mostra le prestazioni effettive (timing reale, conteggi di righe reali) — rivelando dove la query impiega davvero tempo, non solo stime.
SCAN TYPES (most important):
Seq Scan → FULL TABLE SCAN (reads every row) — slow on big tables, often the problem
Index Scan → uses an index to find rows — fast
Index Only Scan → reads only the index (doesn't touch the table) — fastest
Bitmap Scan → for combining indexes / many matches
WATCH FOR:
✗ Seq Scan on a large table being filtered → likely a MISSING INDEX
✗ Estimated rows VERY different from actual rows → outdated statistics (run ANALYZE)
✗ Expensive joins, sorts spilling to disk ("external merge")
✗ The node with the highest actual time = the bottleneck
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;
-- "Seq Scan on orders ... actual time=120ms" → a full scan, slow
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;
-- "Index Scan using idx_orders_customer ... actual time=0.05ms" → fixed!
EXPLAIN e EXPLAIN ANALYZE sono strumenti essenziali e primari per diagnosticare e ottimizzare le query lente in PostgreSQL — le prestazioni delle query sono critiche per le applicazioni, e questi strumenti sono il modo per capire perché una query è lenta e verificare le correzioni, quindi padroneggiarli è una conoscenza importante a livello senior per il lavoro di performance del database.
Il valore fondamentale è la visibilità nel piano di esecuzione: invece di indovinare perché una query è lenta, EXPLAIN ANALYZE (che esegue la query e mostra timing e conteggi di righe reali) rivela esattamente come Postgres la esegue e dove è impiegato il tempo.
Le cose più importanti da riconoscere sono i tipi di scan — in particolare identificare uno Seq Scan (full table scan) su una tabella grande filtrata, che di solito indica un indice mancante (il problema di prestazioni delle query più comune e impattante), rispetto a uno Index Scan veloce.
Capire come leggere il piano (tipi di scan, confrontare le righe stimate rispetto a quelle reali per individuare statistiche obsolete, identificare il nodo più costoso come collo di bottiglia, osservare gli ordinamenti che straripano su disco) è l'abilità chiave per l'ottimizzazione delle query.
Il flusso di lavoro diagnostico — eseguire EXPLAIN ANALYZE, identificare il collo di bottiglia (spesso uno Seq Scan), applicare la correzione (solitamente aggiungendo un indice) e rieseguire per verificare — è l'approccio standard, basato sulla misurazione, per l'ottimizzazione delle query.
Poiché le query lente sono una delle principali cause di scarso rendimento del database, e poiché EXPLAIN/EXPLAIN ANALYZE sono gli strumenti per diagnosticarle (rivelando indici mancanti, piani scadenti e colli di bottiglia) e verificare le ottimizzazioni, padroneggiarli è una conoscenza importante e di alto valore per le prestazioni di PostgreSQL — è il modo per trovare e correggere sistematicamente le query lente piuttosto che indovinare, un'abilità critica per database performanti e un argomento comune per il lavoro di ottimizzazione dei database.
Una raccolta di domande di colloquio IT con risposte dettagliate — da Junior a Senior.
Dona