Leggere un piano di esecuzione con EXPLAIN senza paura

SQL è un linguaggio dichiarativo: si descrive quale risultato si vuole, non come ottenerlo. A decidere come leggere le tabelle, in che ordine unirle e quali indici usare è un componente del database chiamato ottimizzatore. Il piano di esecuzione è la sua decisione messa per iscritto, e il comando EXPLAIN permette di leggerla. È lo strumento più utile per capire perché una query è lenta, invece di tirare a indovinare.

Il comando di base

Basta anteporre EXPLAIN alla query:

EXPLAIN
SELECT * FROM ordini WHERE id_cliente = 42;

Il database non esegue la query, ma mostra il piano previsto. In PostgreSQL esiste anche EXPLAIN ANALYZE, che invece la esegue davvero e affianca alle stime i tempi e il numero di righe reali. Attenzione: con un UPDATE o un DELETE l'esecuzione modifica davvero i dati, quindi conviene racchiuderla in una transazione da annullare con ROLLBACK.

Gli esempi che seguono usano la sintassi di PostgreSQL; MySQL, SQL Server e SQLite hanno comandi equivalenti con un formato di output diverso, ma i concetti sono gli stessi.

Come si legge l'output

Un piano è un albero di operazioni. Si legge dall'interno verso l'esterno: le righe più indentate vengono eseguite per prime e passano il loro risultato al nodo che le contiene.

Hash Join  (cost=35.50..410.20 rows=1200 width=64)
  Hash Cond: (o.id_cliente = c.id)
  ->  Seq Scan on ordini o  (cost=0.00..340.00 rows=20000 width=40)
  ->  Hash  (cost=25.00..25.00 rows=840 width=24)
        ->  Seq Scan on clienti c  (cost=0.00..25.00 rows=840 width=24)
              Filter: (citta = 'Torino')

Qui il database legge i clienti di Torino, costruisce con essi una tabella hash in memoria, poi scorre tutti gli ordini e per ciascuno controlla se il cliente è nella tabella hash.

I numeri tra parentesi

• cost: una stima del lavoro in unità arbitrarie. Il primo valore è il costo prima di restituire la prima riga, il secondo il costo totale. Serve a confrontare piani alternativi, non a prevedere i secondi.
• rows: quante righe l'ottimizzatore si aspetta che quel nodo produca.
• width: la dimensione media stimata di una riga, in byte.

Con EXPLAIN ANALYZE compaiono anche actual time, le righe effettive e il numero di ripetizioni (loops) del nodo.

Le operazioni da riconoscere

Modi di leggere una tabella

Un Seq Scan non è un errore di per sé. Lo diventa quando avviene su una tabella grande per restituire poche righe.

Modi di unire due tabelle

• Nested Loop: per ogni riga della prima tabella cerca le corrispondenze nella seconda. Ottimo quando la prima produce poche righe e la seconda ha un indice sulla colonna di unione.
• Hash Join: costruisce una tabella hash con la più piccola e la confronta con l'altra. Adatto a grandi quantità senza indici utili.
• Merge Join: unisce due flussi già ordinati sulla colonna di unione, scorrendoli in parallelo.

Un esempio di diagnosi

Una query che cerca gli ordini di un cliente impiega troppo:

Seq Scan on ordini  (actual time=0.02..180.40 rows=12 loops=1)
  Filter: (id_cliente = 42)
  Rows Removed by Filter: 1999988

Il messaggio è chiaro: il database ha letto due milioni di righe per tenerne 12. Manca un indice su id_cliente. Dopo averlo creato:

Index Scan using idx_ordini_cliente on ordini
  (actual time=0.03..0.09 rows=12 loops=1)
  Index Cond: (id_cliente = 42)

Il tempo passa da circa 180 millisecondi a meno di uno, sulla stessa macchina.

Stime sbagliate: il problema più subdolo

L'ottimizzatore sceglie il piano in base a statistiche sui dati: quante righe ci sono, quanti valori distinti, come sono distribuiti. Se le statistiche sono vecchie o i dati hanno distribuzioni particolari, le stime sbagliano. Il sintomo, in EXPLAIN ANALYZE, è una grande differenza tra rows stimate ed effettive, per esempio 10 previste e 500.000 reali.

Con una stima così sbagliata, l'ottimizzatore può scegliere un Nested Loop pensando di ripeterlo dieci volte e finire per ripeterlo mezzo milione di volte. Le soluzioni più comuni sono:

1. aggiornare le statistiche (in PostgreSQL con ANALYZE, in MySQL con ANALYZE TABLE);
2. evitare di applicare funzioni alle colonne filtrate, come WHERE anno(data) = 2025, che rendono difficile la stima e impediscono l'uso dell'indice: meglio un intervallo di date;
3. riscrivere condizioni complesse in forme che l'ottimizzatore comprende meglio.

Un metodo di lavoro

1. Eseguire EXPLAIN ANALYZE su una copia dei dati realistica, non su un database di test quasi vuoto.
2. Cercare il nodo che occupa la maggior parte del tempo.
3. Controllare se legge molte più righe di quante ne restituisce.
4. Confrontare righe stimate e reali.
5. Intervenire su una cosa alla volta e rileggere il piano.

In sintesi

• EXPLAIN mostra come il database intende eseguire una query; EXPLAIN ANALYZE la esegue e misura.
• Il piano si legge dai nodi più interni verso l'esterno.
• Un Seq Scan su una tabella grande che restituisce poche righe indica spesso un indice mancante.
• Grandi differenze tra righe stimate e reali indicano statistiche da aggiornare o condizioni da riscrivere.
• Si ottimizza un intervento alla volta, verificando ogni volta il nuovo piano.