Cos'è EXPLAIN e come usarlo per analizzare le query

Immagina di sviluppare un'applicazione e una delle query improvvisamente diventa la più costosa nel tuo database. Inizi a notare crash e rallentamenti nell'app. Ed è qui che entra in gioco EXPLAIN, che ti aiuta a capire dove tutto è andato storto. Ottimizzare le query analizzando EXPLAIN ti permette di risparmiare risorse, guadagnare tempo e migliorare l'esperienza degli utenti con la tua app.

EXPLAIN è il tuo modo per guardare dentro PostgreSQL e vedere esattamente come il database intende eseguire la query. Ti mostra se verrà usato un indice o se si farà uno scan completo della tabella, quali passi farà l'ottimizzatore, in che ordine e quanto saranno grandi i risultati intermedi.

In altre parole, EXPLAIN ti permette di capire cosa aspettarti dall'esecuzione della query: quanto è "pesante", quante righe si pensa di processare e quali risorse verranno usate. È uno strumento indispensabile quando una query inizia a rallentare e devi scoprire il perché.

EXPLAIN è come una torcia nel buio: con lui vedi cosa succede sotto il cofano e dove esattamente le cose si inceppano.

Sintassi del comando EXPLAIN

Diamo un'occhiata alla sintassi base del comando EXPLAIN:

EXPLAIN la_tua_query_SQL;

Esempio di query:

EXPLAIN SELECT * FROM students WHERE age > 20;

Quando lanci questo comando, PostgreSQL non eseguirà la query. Invece, ti mostrerà il piano di esecuzione. È come uno schizzo prima di costruire qualcosa — utile per vedere cosa succederà prima di rompere tutto.

Ecco un esempio di output:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

Questo output può sembrare spaventoso, ma tranquillo — ora vediamo i componenti principali.

Analisi di un piano di esecuzione base

Vediamo insieme quel risultato:

1. Seq Scan on students — significa che PostgreSQL farà uno scan completo della tabella students (scansione sequenziale). Non è sempre male, ma su tabelle grandi Seq Scan può essere lento.

2. cost=0.00..35.00 — è una stima dei costi dell'operazione:

   • Startup Cost: costo iniziale dell'operazione (qui 0.00).
   • Total Cost: costo totale per completare l'operazione (qui 35.00).

3. rows=7 — PostgreSQL pensa che la condizione age > 20 restituirà 7 righe. Questo si chiama "cardinalità" (cardinality). Se vedi stime strane, potrebbe voler dire che le statistiche della tua tabella sono vecchie.

4. width=37 — è la dimensione media di una riga in byte.

5. Filter: (age > 20) — specifica che PostgreSQL applicherà il filtro controllando ogni riga.

Quindi, l'output di EXPLAIN ti dà un'idea delle strategie e delle ipotesi di PostgreSQL. Puoi usare queste info per ottimizzare.

Opzioni del comando EXPLAIN

Anche se l'output base di EXPLAIN è già utile, puoi modificarlo con queste opzioni:

ANALYZE

Con questa opzione PostgreSQL non solo mostra il piano di esecuzione, ma esegue davvero la query e ti dà i dati reali. Esempio:

EXPLAIN ANALYZE SELECT * FROM students WHERE age > 20;

Così puoi confrontare le ipotesi di PostgreSQL con l'esecuzione reale e vedere quanto ci azzecca.

VERBOSE

Mostra dettagli extra, utili per un'analisi approfondita. Esempio:

EXPLAIN VERBOSE SELECT * FROM students WHERE age > 20;

BUFFERS

Mostra l'uso dei buffer di memoria durante l'esecuzione della query. Si usa insieme a ANALYZE:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM students WHERE age > 20;

COSTS

Se vuoi nascondere o mostrare le info sui costi (cost), usa questa opzione:

EXPLAIN (COSTS OFF) SELECT * FROM students WHERE age > 20;

FORMAT

L'output del piano può essere presentato in altri formati, tipo JSON o XML. Esempio:

EXPLAIN (FORMAT JSON) SELECT * FROM students WHERE age > 20;

Esempio di utilizzo di EXPLAIN

Immagina un database university con la tabella students. Supponiamo che vuoi trovare tutti gli studenti con più di 20 anni:

EXPLAIN SELECT * FROM students WHERE age > 20;

L'output potrebbe essere questo:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

Come già detto, è una scansione sequenziale Seq Scan, che può essere inefficiente su tabelle grandi.

Ora creiamo un indice sulla colonna age e vediamo se il piano cambia:

CREATE INDEX age_index ON students(age);

EXPLAIN SELECT * FROM students WHERE age > 20;

Output:

Index Scan using age_index on students  (cost=0.15..4.23 rows=7 width=37)
  Index Cond: (age > 20)

Ora PostgreSQL usa una scansione tramite indice (Index Scan), che di solito è più veloce, soprattutto su tabelle grandi.

Domande e errori tipici

Perché la mia query è lenta anche se c'è un indice?

Forse la query restituisce troppe righe, quindi usare l'indice non conviene. L'indice potrebbe essere fatto male o non aggiornato.

E se l'output di EXPLAIN è difficile da capire?

Parti da query semplici e studia i nodi del piano di esecuzione uno per uno.

Come faccio a sapere se le statistiche della tabella sono vecchie?

Esegui il comando ANALYZE students

Quando usare EXPLAIN senza ANALYZE?

Se vuoi vedere il piano senza eseguire davvero la query (ad esempio per query che modificano i dati).