02 - EXPLAIN ANALYZE: Leggere e Ottimizzare i Query Plan

EXPLAIN ANALYZE: Leggere e Ottimizzare i Query Plan PostgreSQL

Una query che impiega 8 secondi in produzione e spesso ottimizzabile a 50ms con il giusto indice o una piccola riscrittura. EXPLAIN ANALYZE e lo strumento che rivela esattamente perche PostgreSQL esegue una query in un certo modo: quante righe scansiona, quali indici usa, dove spende il 90% del tempo. Imparare a leggere i query plan e la skill piu impattante per un developer PostgreSQL.

La Struttura di un Query Plan

Un query plan e un albero di operazioni: ogni nodo rappresenta un'operazione (scansione, join, sort) e i nodi figli forniscono i dati al nodo padre. PostgreSQL esegue l'albero dal basso verso l'alto.

-- EXPLAIN ANALYZE: aggiunge il tempo reale e le righe effettive
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
  o.id AS ordine_id,
  u.email,
  SUM(ol.quantita * ol.prezzo_unitario) AS totale
FROM ordini o
JOIN utenti u ON u.id = o.utente_id
JOIN ordini_linee ol ON ol.ordine_id = o.id
WHERE o.stato = 'completato'
  AND o.creato_il >= '2026-01-01'
GROUP BY o.id, u.email
ORDER BY totale DESC
LIMIT 100;

Output tipico (annotato):

Limit  (cost=45823.12..45823.37 rows=100 width=48)
       (actual time=892.341..892.363 rows=100 loops=1)
  ->  Sort  (cost=45823.12..46073.12 rows=100000 width=48)
            (actual time=892.340..892.350 rows=100 loops=1)
        Sort Key: (sum((ol.quantita * ol.prezzo_unitario))) DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  HashAggregate  (cost=40000.00..42500.00 rows=100000 ...)
                           (actual time=820.123..880.234 rows=98432 loops=1)
              Group Key: o.id, u.email
              Batches: 5  Memory Usage: 4096kB  Disk Usage: 1024kB  <- PROBLEMA!
              ->  Hash Join  (cost=1200.00..35000.00 rows=500000 ...)
                             (actual time=45.123..650.234 rows=512000 loops=1)
                    Hash Cond: (ol.ordine_id = o.id)
                    ->  Seq Scan on ordini_linee ol           <- Sequential Scan!
                             (actual time=0.012..280.123 rows=2100000 loops=1)
                    ->  Hash  (cost=800.00..800.00 rows=32000 ...)
                              (actual time=44.123..44.123 rows=31845 loops=1)
                          Buckets: 32768  Batches: 1  Memory: 2048kB
                          ->  Hash Join  (cost=100.00..800.00 ...)
                                         (actual time=2.123..38.123 ...)
                                Hash Cond: (o.utente_id = u.id)
                                ->  Index Scan using idx_ordini_stato_data on ordini o
                                         (actual time=0.234..15.123 rows=31845 loops=1)
                                    Index Cond: ((stato = 'completato') AND ...)
                                ->  Hash  (cost=50.00..50.00 rows=4000 ...)
                                          ...
Planning Time: 3.456 ms
Execution Time: 892.987 ms

Leggere le Metriche Chiave

Cost: Stima vs Realta

Ogni nodo mostra due coppie di valori: (cost=startup..total rows=stima width=N) sono le stime del planner, mentre (actual time=startup..total rows=reali loops=N) sono i valori misurati realmente. Quando c'e grande discrepanza tra stima e reale, il planner ha informazioni sbagliate sulle statistiche.

-- Discrepanza grave: stima 1000 righe, realta 500000
-- Questo causa scelta errata del tipo di join e degli indici
--
-- (cost=... rows=1000 ...)    <- planner si aspettava 1000 righe
-- (actual ... rows=500000 ...) <- ma ne ha trovate 500000
--
-- Soluzione: aggiornare le statistiche
ANALYZE VERBOSE ordini_linee;

-- Per controllare le statistiche attualmente in uso
SELECT
  tablename,
  attname,
  n_distinct,
  correlation,
  most_common_vals,
  most_common_freqs
FROM pg_stats
WHERE tablename = 'ordini_linee'
  AND attname IN ('ordine_id', 'stato');

Seq Scan vs Index Scan vs Index-Only Scan

Il tipo di scansione impatta enormemente le performance. PostgreSQL sceglie il tipo in base alla selettivita stimata e alla dimensione della tabella.

-- Quando PostgreSQL sceglie Sequential Scan nonostante un indice esista:
-- 1. Troppe righe restituite (>5-10% della tabella): seq scan e piu veloce
-- 2. Statistiche obsolete: il planner crede ci siano poche righe
-- 3. work_mem insufficiente per materializzare il result set
-- 4. L'indice non copre le colonne del WHERE

-- Forzare l'Index Scan per debugging (NON usare in produzione)
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_seqscan = on;

-- Index-Only Scan: il piu efficiente (nessun accesso alla tabella heap)
-- Richiede che tutte le colonne nel SELECT siano nell'indice
-- E che le pagine siano nel visibility map (dopo autovacuum)

-- Esempio: creare un covering index per abilitare index-only scan
CREATE INDEX idx_ordini_covering
  ON ordini (stato, creato_il)
  INCLUDE (utente_id, totale);  -- colonne extra non usate nel filtro

-- Verificare se il visibility map e aggiornato
VACUUM ordini;  -- o aspettare autovacuum

Identificare i Nodi Costosi

Il modo piu veloce per trovare il collo di bottiglia e usare FORMAT JSON con strumenti visuali, oppure cercare manualmente il nodo con actual time piu alto.

-- Usare EXPLAIN con FORMAT JSON e pgMustard / explain.dalibo.com
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;

-- Pattern da cercare nell'output:
-- 1. "Disk Usage" > 0 in HashAggregate o Sort: work_mem troppo basso
-- 2. "Rows Removed by Filter" molto alto: indice mancante o inefficiente
-- 3. "loops" > 1 su Seq Scan: Nested Loop con tabella grande
-- 4. Stima vs reale molto diversa: statistiche obsolete

-- Calcolo del "costo effettivo" di un nodo:
-- actual_total_time * loops = tempo totale del nodo
-- Se un nodo con loops=50000 ha actual_time=0.05ms,
-- il costo totale e 2500ms!

I Tipi di Join e Quando Usarli

Nested Loop Join

Itera su ogni riga della tabella "outer" e per ognuna cerca nella tabella "inner". Ottimo quando l'inner e piccolo e indicizzato. Disastroso quando entrambe le tabelle sono grandi.

-- Nested Loop: ideale per join su PK con pochi record
-- outer: 100 ordini recenti -> inner: users con indice su id
-- Costo: 100 * O(log N) = molto efficiente

-- Quando diventa un problema:
-- outer: 50.000 righe -> inner: 50.000 righe -> 2.5 miliardi di confronti!
-- Il planner dovrebbe preferire Hash Join in questo caso

-- Se vedi Nested Loop su tabelle grandi, verifica:
-- 1. Statistiche: il planner credeva di trovare poche righe
-- 2. join_collapse_limit: aumentare se il planner non esplora tutte le alternative
-- 3. enable_nestloop = off per testare se Hash/Merge Join sarebbe meglio
SET enable_nestloop = off;
EXPLAIN ANALYZE ...;
SET enable_nestloop = on;

Hash Join

Costruisce una hash table dalla tabella piu piccola, poi scansiona la piu grande cercando le corrispondenze. Efficiente per join su tabelle medie/grandi senza indici utilizzabili.

-- Hash Join con spill to disk (Batches > 1): aumentare work_mem
-- "Batches: 5  Memory Usage: 4096kB  Disk Usage: 1024kB"
-- significa che la hash table non entra in memoria

-- Calcolare la work_mem necessaria
-- La hash table occupa circa: righe * larghezza_media * 2
-- Aumentare per sessione specifica (non globalmente)
SET work_mem = '256MB';
EXPLAIN ANALYZE ...;
SET work_mem = DEFAULT;

-- Oppure aumentare solo per le query analitiche
ALTER ROLE analyst_user SET work_mem = '256MB';

Merge Join

Richiede che entrambi gli input siano ordinati sulla chiave di join. PostgreSQL lo sceglie quando entrambe le tabelle hanno indici che garantiscono l'ordine.

Correggere le Stime del Planner

Le stime errate sono la causa piu comune di query plan subottimali. Ecco le tecniche per correggerle.

-- 1. Aumentare il target di statistiche per colonne ad alta cardinalita
ALTER TABLE ordini ALTER COLUMN stato SET STATISTICS 500;  -- default: 100
ANALYZE ordini;

-- 2. Creare statistiche estese per colonne correlate
-- (PostgreSQL non sa che stato='completato' implica certi range di date)
CREATE STATISTICS stat_ordini_stato_data (dependencies, ndistinct)
  ON stato, creato_il FROM ordini;
ANALYZE ordini;

-- 3. Verificare le statistiche con pg_statistic_ext
SELECT
  stxname,
  stxkeys::int[] AS colonne,
  stxdependencies,
  stxndistinct
FROM pg_statistic_ext
JOIN pg_statistic_ext_data ON pg_statistic_ext_data.stxoid = pg_statistic_ext.oid
WHERE stxrelid = 'ordini'::regclass;

-- 4. Forzare il re-planning disabilitando la plan cache
-- (utile per query parametriche con distribuzione skewed)
SET plan_cache_mode = force_generic_plan;

auto_explain: Catturare le Query Lente Automaticamente

In produzione non puoi eseguire EXPLAIN manualmente su ogni query. auto_explain logga automaticamente il piano delle query che superano una soglia di tempo.

-- Abilitare auto_explain in postgresql.conf
# shared_preload_libraries = 'auto_explain'  # richiede riavvio

# auto_explain parametri
auto_explain.log_min_duration = '500ms'  # logga query > 500ms
auto_explain.log_analyze = true           # mostra i tempi reali
auto_explain.log_buffers = true           # mostra I/O
auto_explain.log_timing = true            # timing dettagliato
auto_explain.log_triggers = false         # non loggare trigger
auto_explain.log_verbose = false          # output compatto
auto_explain.log_format = 'text'          # json per tools

-- In alternativa: abilitare per sessione (senza riavvio)
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '200ms';
SET auto_explain.log_analyze = true;

-- Ora ogni query > 200ms nel log di PostgreSQL includera il piano
-- Cercare in /var/log/postgresql/postgresql-17-main.log:
-- duration: 892.987 ms  plan:
-- ...Seq Scan on ordini_linee  (actual time=0.012..280.123)...

Workflow Pratico di Ottimizzazione

Ecco il processo sistematico per ottimizzare una query lenta identificata in produzione.

-- STEP 1: Identificare la query con pg_stat_statements
SELECT
  LEFT(query, 100) AS q,
  calls,
  ROUND(mean_exec_time::numeric, 1) AS mean_ms,
  ROUND(max_exec_time::numeric, 1) AS max_ms,
  ROUND(total_exec_time::numeric / 1000, 1) AS total_s
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 10;

-- STEP 2: Ottenere il piano con EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ;

-- STEP 3: Identificare il nodo piu costoso
-- Cercare: actual time alto, Seq Scan su tabelle grandi, Batches > 1

-- STEP 4: Verificare gli indici esistenti
SELECT
  indexname,
  indexdef,
  pg_relation_size(indexrelid) AS size_bytes
FROM pg_indexes
WHERE tablename = 'ordini_linee';

-- STEP 5: Creare l'indice mancante
-- CONCURRENTLY: non blocca le scritture durante la creazione
CREATE INDEX CONCURRENTLY idx_ordini_linee_ordine_id
  ON ordini_linee (ordine_id)
  INCLUDE (quantita, prezzo_unitario);

-- STEP 6: Verificare che il nuovo piano usi l'indice
EXPLAIN (ANALYZE, BUFFERS)
SELECT ol.ordine_id, SUM(ol.quantita * ol.prezzo_unitario)
FROM ordini_linee ol
WHERE ol.ordine_id IN (SELECT id FROM ordini WHERE stato = 'completato')
GROUP BY ol.ordine_id;

-- STEP 7: Misurare il miglioramento con pg_stat_statements
-- Attendere almeno 100 esecuzioni, poi confrontare mean_exec_time
SELECT pg_stat_statements_reset();
-- ... attendere ...
SELECT calls, mean_exec_time, max_exec_time FROM pg_stat_statements WHERE ...;

Strumenti Visivi per i Query Plan

Per query plan complessi, gli strumenti visivi accelerano l'analisi. I migliori sono gratuiti.

• explain.dalibo.com: incolla l'output JSON di EXPLAIN, ottieni un grafico interattivo. Il nodo piu lento e evidenziato in rosso.
• pgMustard: analisi guidata con suggerimenti specifici ("considera un indice su colonna X").
• pspg: pager per psql che colora l'output EXPLAIN in modo leggibile.

-- Copiare l'output JSON per explain.dalibo.com
\pset format unaligned
\pset tuples_only on
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;

-- Output e un JSON singolo che puoi incollare direttamente

Conclusioni

EXPLAIN ANALYZE e la lente che rivela il funzionamento interno di PostgreSQL. Imparare a leggere Sequential Scan vs Index Scan, capire quando Hash Join e preferibile a Nested Loop, e identificare le discrepanze tra stime e realta trasforma un developer da "scrive SQL" a "ottimizza database".

Nel prossimo articolo approfondiamo l'indexing avanzato: partial index, covering index, BRIN per time-series e GIN per full-text search. Questi sono gli strumenti che, combinati con la comprensione dei query plan, permettono ottimizzazioni drammatiche.