Il comando di EXPLAIN consente di ottenere il piano di esecuzione
degli statement SQL in
PostgreSQL.
Postgres non mantiene la storia dei piani di esecuzione
pero' esiste un modo, semplice e che non richiede il riavvio
dell'istanza o la modifica delle applicazioni,
per raccogliere i piani di esecuzione nel file di log
utilizzando l'estensione auto_explain.
In questa pagina vediamo come.
Il comando di EXPLAIN consente di ottenere il piano di esecuzione di uno statement SQL e va richiamato in modo esplicito davanti al comando che si vuole analizzare:
EXPLAIN SELECT * FROM test WHERE t LIKE 'L''amor%';
QUERY PLAN
---------------------------------------------------------------------------------
Index Only Scan using test_btree_idx on test (cost=0.28..4.30 rows=1 width=33)
Index Cond: ((t >= 'L''amor'::text) AND (t < 'L''amos'::text))
Filter: (t ~~ 'L''amor%'::text)
(3 rows)
Postgres non raccoglie la storia degli statement SQL eseguiti:
nelle diverse viste di sistema
sono disponibili solo i valori correnti dei contatori.
L'estensione pg_stat_statements mantiene le statistiche sugli statement eseguiti, sui tempi medi di esecuzione ed altre informazioni utili per statement ma non raccoglie il piano di esecuzione.
Come fare con Postgres ad individuare il piano di esecuzione delle query piu' pesanti
di un'applicazione di terze parti di cui non abbiamo accesso al codice?
Con l'estensione auto_explain !
Si tratta di un'estensione core della versione Community e quindi e' sempre disponibile sia on-premises che in cloud, basta configurarla.
Attivando l'estensione auto_explain si ottiene nel file di log il piano di esecuzione di ogni statement che supera la durata minima impostata.
Vi sono diverse modalita' di attivazione per l'auto_explain, in questa pagina vediamo quella piu' utilizzata perche' consente di estrarre gli execution plan senza modificare le applicazioni e senza riavviare la base dati. I passi sono i seguenti:
Gia' fatto!
Da questo momento per tutti gli statement che supereranno la soglia impostata
sara' riportato il piano d'esecuzione nel file di log.
Vediamo i principali parametri utilizzabili con auto_explain:
| Parametro | Datatype | Descrizione |
| auto_explain.log_min_duration | integer | Durata minima in millisecondi degli statement su cui eseguire l'EXPLAIN. Il default e' -1 (disabilitato), con 0 viene stampato il plan di ogni statement. Un valore troppo basso puo' essere pesante e far crescere notevolmente la dimensione dei file di log. |
| auto_explain.log_analyze | boolean | Utilizza un EXPLAIN ANALYZE anziche' un semplice EXPLAIN. E' particolarmente pesante quindi va attivato con cautela. Il default e' off. |
| auto_explain.log_timing | boolean | Riporta i tempi di esecuzione per ogni nodo del piano d'esecuzione quando l'ANALYZE e' attivo. Il default e' on. |
| auto_explain.log_nested_statements | boolean | Se attivato effettua l'explain anche degli statement annnidati (eg. richiamati all'interno di una funzione). Il default e' off. |
| auto_explain.log_format | enum | I valori possibili sono: text, xml, json e yaml. |
| auto_explain.log_level | enum | I valori possibili sono: DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING e LOG. |
Come tutte le extension anche l'auto_explain ha un costo dal punto di vista delle performance
e dell'occupazione di spazio nei LOG.
In particolare e' opportuno limitare il numero di statement raccolti
impostando auto_explain.log_min_duration ad un valore ragionevole.
L'auto_explain.log_analyze deve eseguire l'ANALYZE per tutti gli statement,
quindi e' particolarmente pesante e va attivato solo quando strettamente necessario e per un breve periodo,
...
Se non viene attivato l'analyze (per default non e' attivo)
e la durata degli statement tracciati e' ragionevole (eg. 10000 millisecondi ovvero 10 secondi),
l'attivazione dell'estensione non ha impatti significativi sulle prestazioni del database e consente
l'analisi a posteriori dei piani d'esecuzione degli statement di maggior durata.
Non e' consigliabile eseguire far l'analyze e non limitare la durata degli statement
tracciati perche' gli impatti sulle prestazioni e sulla dimensione dei file di log
possono essere significativi: sono possibilita' da utilizzare solo per debug e per periodi limitati.
L'impostazione della libreria puo' anche essere effettuata agendo il file di configurazione postgresql.conf ed inserendo la riga:
# - Shared Library Preloading - session_preload_libraries = 'auto_explain'
In effetti i comandi di ALTER modificano il file postgresql.auto.conf.
Il modulo auto_explain potrebbe essere caricato anche con il comando SQL LOAD 'auto_explain';, l'estensione potrebbe essere configurata anche nel parametro shared_preload_libraries. Ma poiche' il comando di LOAD e' riservato ai superuser e l'impostazione della shared_preload_libraries richiede un riavvio, l'auto_explain e' tipicamente configurato come descritto in precedenza.
Tra i molti strumenti per visualizzare il risultato di un EXPLAIN il mio preferito e' PEV2 disponibile anche online.
Un altro documento utile sull'argomento e' Ottimizzazione SQL in PostgreSQL. Si tratta di un documento molto piu' completo rispetto a questa pagina poiche' tratta di tutte le possibili ottimizzazioni dell'SQL, la cui analisi con l'EXPLAIN e' il punto di partenza.
Come sempre la
documentazione ufficiale PostgreSQL
riporta tutti i dettagli
sia sull'estensione
auto_explain
che sul comando SQL
EXPLAIN.
Infine, tra le migliaia di pagine disponibili nel web sull'argomento, riporto la mia preferita:
Explaining the unexplainable.
Titolo: EXPLAIN Online
Livello: Intermedio
Data:
14 Febbraio 2023 ❤️
Versione: 1.0.3 - 1 Aprile 2023
Autore: mail [AT] meo.bogliolo.name