Leggere un piano
Un piano è un albero di operazioni; la radice restituisce il risultato e ogni nodo preleva righe dai suoi figli. EXPLAIN da solo mostra le stime del pianificatore (unità di costo e righe attese); EXPLAIN ANALYZE esegue davvero la query e aggiunge tempi e conteggi di righe reali, che è ciò che rende un piano leggibile. BUFFERS aggiunge quante pagine da 8 kB sono venute dalla cache (hit) o dal disco (read). Il percorso abituale in un piano: trova il nodo con il tempo esclusivo maggiore, poi guarda perché è lì: una stima sbagliata delle righe sopra di esso, un indice mancante, o un’impostazione di memoria.
I numeri su un nodo
| Campo | Significato |
|---|---|
| cost=0.29..8.31 | Stima del pianificatore in unità arbitrarie: costo per restituire la prima riga .. costo per restituire tutte le righe. Confrontabile solo all’interno di un piano. |
| rows= | Righe stimate per loop. Anche il valore reale è per loop; moltiplica per loops per il totale. |
| actual time=0.05..12.3 | Millisecondi fino alla prima riga .. all’ultima riga, per loop, figli compresi. Lo strumento sottrae i figli per mostrare il tempo proprio di ogni nodo. |
| loops= | Quante volte è girato il nodo. Il lato interno di un nested loop gira una volta per ogni riga esterna; sotto un Gather equivale al numero di worker. |
| Buffers: shared hit=.. read=.. | Pagine trovate in shared_buffers contro lette dal SO/disco. Le letture sono dove va il tempo di I/O; track_io_timing lo mostra come I/O Timings. |
| Rows Removed by Filter | Righe che il nodo ha letto e poi scartato: il segno più chiaro che manca un indice. |
I cinque problemi abituali
- Stime sbagliate. Se un nodo si aspetta 10 righe e ne riceve 100.000, il pianificatore ha scelto un nested loop o un sort che aveva senso solo per 10. Esegui
ANALYZE; per predicati su colonne correlate crea statistiche estese; per CTE e funzioni il pianificatore non ha idea e usa valori di default. - Sequential scan con un filtro selettivo. Va bene su tabelle piccole. Su quelle grandi la soluzione è un indice sulle colonne filtrate, idealmente che corrisponda a WHERE e ORDER BY.
- Spill. Sort external merge, Hash Batches > 1, HashAggregate su disco, bitmap lossy: l’operazione non è entrata in
work_mem. Alzarlo per la sessione o la query costa poco; alzarlo globalmente si moltiplica per le connessioni. - Nested loop sopra uno scan. Il lato interno gira una volta per ogni riga esterna; se è un Seq Scan o un Materialize di un insieme grande, il tempo esplode. Il lato interno ha bisogno di un indice, o al pianificatore va permesso un hash join.
- Index-only scan che non lo sono. Heap Fetches vicino al numero di righe significa che la visibility map è vecchia: fai VACUUM della tabella.
Ottenere i piani
-- PostgreSQL: la forma più utile
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT JSON) SELECT …;
-- per le scritture, dentro una transazione da annullare
BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE …; ROLLBACK;
-- registrare automaticamente i piani lenti: auto_explain
LOAD 'auto_explain'; SET auto_explain.log_min_duration = '200ms'; SET auto_explain.log_analyze = on;
-- MySQL 8.0.18+
EXPLAIN ANALYZE SELECT …; -- albero con tempi reali
EXPLAIN FORMAT=JSON SELECT …; -- stime con dettagli di costoDomande
Perché i tempi dei nodi non sommano al totale?
Il tempo reale di ogni nodo include i suoi figli, quindi lo strumento mostra il tempo esclusivo (nodo meno figli). I worker paralleli girano contemporaneamente, quindi un nodo sotto Gather con loops=3 viene contato una volta, non tre; i lati interni dei nested loop vengono moltiplicati per loops. Tempo di pianificazione e dei trigger sono elencati a parte.
Com’è un buon piano?
Uno in cui le stime sono entro un fattore dieci dai valori reali, nessun nodo va su disco, il nodo più costoso è quello che ti aspetti (la grande tabella che devi davvero leggere) e i buffer letti da disco sono pochi rispetto agli hit. Per un piano così l’elenco dei rilievi è vuoto.
Supporta tabelle partizionate e piani paralleli?
Sì: Append / Merge Append sulle partizioni, Gather e Gather Merge con Workers Planned/Launched, aggregati Partial e Finalize. Le scomposizioni per worker (VERBOSE) vengono analizzate ma mostrate come parte del nodo.
Posso incollare un piano da pgAdmin, DataGrip o psql con i bordi della tabella?
Sì. Le righe vengono ripulite dai caratteri di cornice di psql, dall’intestazione QUERY PLAN e dal piè di pagina «(n rows)». Il JSON copiato da una GUI come un’unica stringa tra virgolette va prima liberato dalle virgolette.
Come si confronta il supporto per MySQL?
L’albero EXPLAIN ANALYZE di MySQL 8 ha la stessa forma di un piano testuale PostgreSQL ed è pienamente supportato; EXPLAIN FORMAT=JSON (solo stime) viene mappato sullo stesso albero: i tipi di accesso diventano tipi di scan, nested_loop diventa un nodo di join, e i flag filesort / tabella temporanea diventano rilievi. ANALYZE FORMAT=JSON di MariaDB non è ancora supportato.