pg_plan_advice
In questa paginetta parliamo di due nuove estensioni di
PostgreSQL...
tanto nuove che non sono ancora disponibili!
Vogliamo infatti introdurre le estensioni
pg_plan_advice e pg_stash_advice
che consentono di suggerire all'ottimizzatore
un piano d'esecuzione o di associarlo ad una query.
Si tratta di nuove funzionalita'
che saranno disponibili nella prossima versione di PostgreSQL PG19.
Dopo una breve introduzione vedremo qualche elemento utile sull'ottimizzatore e
sul comando di EXPLAIN di PostgreSQL...
ma se gia' li conoscete potete saltarli!
Quindi vedremo in dettaglio
pg_plan_advice
e pg_stash_advice che sono sicuramente un'interessante novita' per PostgreSQL.
Introduzione
La posizione
della Community PostgreSQL e'
che se l'ottimizzatore sbaglia nella scelta del piano
il problema e' altrove ed e' necessario correggerlo alla fonte
(disegno logico/fisico errato, ANALYZE non aggiornate, query da riscrivere, ...)
e non indicando un piano alternativo.
Mantenendo questa logica, in modo integrato con il Planner ed
il comando di EXPLAIN i due nuovi moduli pg_plan_advice e pg_stash_advice
forniscono una possibilita' in piu' a DBA e programmatori esperti
per indirizzare la scelta del Planner.
Con i due nuovi moduli e' possibile agire in modo tattico impostando con un comando di SET
il suggerimento e verificarne l'applicazione con EXPLAIN.
Oppure, con l'estensione pg_stash_advice, associare il suggerimento al queryid
in modo che venga applicato ogni volta che una query e' richiamata.
Non sono l'unico strumento a disposizione dei DBA e dei programmatori
per migliorare le prestazioni di una query ma sono comunque moduli importanti
per comprendere meglio il funzionamento del Planner e un ulteriore mezzo
per guidarlo
Ottimizzatore
Il processo di analisi ed esecuzione di uno statement SQL richiede diversi passi
e viene svolta in modo indipendente dal processo associato alla connessione.
La prima fase e' quella di parsing che ha un analizzatore sintattico
che riconosce gli identificatori (scan.l) ed ha una serie di regole (gram.y) di trattamento.
A questo punto i passi possono essere molto diversi a seconda che si tratti di semplici comandi
o istruzioni SQL complesse che richiedono ulteriori analisi e riscritture.
Inoltre una query identica puo' essere gia' stata eseguita di recente
ed in questo caso non e' piu' necessario analizzare tutti i dettagli
per ottimizzare la query (soft parse).
PostgreSQL utilizza un ottimizzatore cost-based.
Quando viene sottomesso un nuovo statement SQL l'ottimizzatore determina
il query tree da utilizzare basandosi sulle statistiche raccolte
dalle tabelle e dalle colonne utilizzate nella query.
Per ogni statement vengono analizzati tutti i percorsi possibili per
ottenere il risultato finale calcolando la complessita' di ciascuno
basandosi sui parametri che definiscono il costo di ogni metodo
di accesso e sulle statistiche raccolte dall'ANALYZE.
Un algoritmo genetico e' utilizzato per ridurre il numero delle combinazioni
dei possibili percorsi di ricerca quando il numero di join sarebbe troppo
elevato da esplorare ogni combinazione con l'algoritmo deterministico esaustivo;
e' infatti possibile utilizzare qualsiasi combinazione di join per ottenere
il risultato finale con una crescita esponenziale del numero di plan
al crescere del numero di tabelle.
Per gli statement gia' in memoria la fase di ottimizzazione non viene ripetuta (soft parse)
mentre e' necessaria per i nuovi statement (hard parse).
Il primo e piu' importante aspetto per ottenere buone prestazioni
e' il corretto disegno logico (normalizzazione, datatype, ...) e fisico
(indici, partizionamento, ...)
della base dati.
E' naturalmente molto importante che le statistiche su cui si basa l'ottimizzatore
siano sempre aggiornate. Questo avviene in automatico da parte del processo di autovacuum
ma, in caso di modifiche significative spesso e' opportuno il lancio del comando di ANALYZE.
Se le statistiche non sono aggiornate l'ottimizzatore non ha elementi per effettuare le scelte corrette.
Il livello di dettaglio dell'analyze e' determinato dal parametro default_statistics_target
(default: 100) ed e' anche possibile configurare statistiche estese per raccogliere ulteriori
dettagli sui dati.
L'ottimizzatore puo' essere controllato con un'ampia serie di
parametri
utilizzando il comando di SET.
Per maggiori dettagli potete leggere il documento Ottimizzazione SQL.
EXPLAIN
Un execution plan e' un grafo composto da tutti i passi necessari
per ottenere il risultato della query.
Naturalmente i nodi del grafo sono gli algoritmi necessari per
verificare le condizioni, ordinare ed aggregare i dati,
limitare il numero di righe
e, soprattutto, eseguire i join tra le tabelle presenti nella query.
Per ottenere i dettagli su come l'ottimizzatore ha pianificato l'esecuzione
di una query si utilizza la clausola EXPLAIN:
EXPLAIN SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;
QUERY PLAN
----------------------------------------------------------------------------
Hash Join (cost=143.50..74780.95 rows=2000000 width=83)
Hash Cond: (f.dim_id = d.id)
-> Seq Scan on join_fact f (cost=0.00..69383.00 rows=2000000 width=67)
-> Hash (cost=81.00..81.00 rows=5000 width=16)
-> Seq Scan on join_dim d (cost=0.00..81.00 rows=5000 width=16)
Con l'explain vengono visualizzati gli algormitmi di accesso ai dati scelti dall'ottimizzatore
per eseguire la query. Oltre alle tabelle interessate sono riportati gli eventuali
indici: fondamentali per un accesso efficiente.
Le opzioni dell'EXPLAIN sono parecchie tra cui la possibilita' di ottenere
l'execution plan in formato JSON ad esempio con
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
e va infine ricordata la possibilita' di attivare l'autoexplain.
Gli execution plan su query reali possono diventare parecchio complessi e
sono di grande aiuto strumenti come
PEV2
disponibile anche su
web.
pg_plan_advice
pg_plan_advice e' il modulo, integrato con il Planner PostgreSQL
che consente di descrivere, riprodurre ed alterare le decisioni del Planner.
Poiche' generalmente il Planner effettua le scelte migliori e' importante alterare
i piani solo nei pochi casi in cui i vantaggi sono certi ed evidenti.
Utilizzo
Come prima cosa e' necessario caricare il modulo pg_plan_advice
(pg_plan_advice tecnicamente non e' un'extension ma e' un loadable module,
la differenza e' sottile e quindi mi sono permesso di chiamarla extension ;-).
Per caricare il modulo pg_plan_advice
si puo' impostare il parametro shared_preload_libraries (che richiede un riavvio)
o il parametro session_preload_libraries (che richiede la rilettura dei parametri e l'avvio di una sessione)
oppure utilizzando il comando LOAD.
Una volta caricato il modulo, l'EXPLAIN potra' utilizzare l'opzione PLAN_ADVICE:
EXPLAIN (COSTS OFF, PLAN_ADVICE, VERBOSE)
SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;
QUERY PLAN
--------------------------------------------------------------------------
Hash Join
Output: f.id, f.dim_id, f.amount, f.payload, d.id, d.category, d.label
Inner Unique: true
Hash Cond: (f.dim_id = d.id)
-> Seq Scan on public.join_fact f
Output: f.id, f.dim_id, f.amount, f.payload
-> Hash
Output: d.id, d.category, d.label
-> Seq Scan on public.join_dim d
Output: d.id, d.category, d.label
Query Identifier: 4635755508291557612
Generated Plan Advice:
JOIN_ORDER(f d)
HASH_JOIN(d)
SEQ_SCAN(f d)
NO_GATHER(f d)
Con la stessa sintassi in cui il Plan Advice viene generato dall'EXPLAIN
e' possibile impostare un advice:
SET pg_plan_advice.advice = 'JOIN_ORDER(d f)';
EXPLAIN (COSTS OFF, PLAN_ADVICE, VERBOSE)
SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;
QUERY PLAN
--------------------------------------------------------------------------
Hash Join
Output: f.id, f.dim_id, f.amount, f.payload, d.id, d.category, d.label
Hash Cond: (d.id = f.dim_id)
-> Seq Scan on public.join_dim d
Output: d.id, d.category, d.label
-> Hash
Output: f.id, f.dim_id, f.amount, f.payload
-> Seq Scan on public.join_fact f
Output: f.id, f.dim_id, f.amount, f.payload
Query Identifier: 4635755508291557612
Supplied Plan Advice:
JOIN_ORDER(d f) /* matched */
Generated Plan Advice:
JOIN_ORDER(d f)
HASH_JOIN(f)
SEQ_SCAN(d f)
NO_GATHER(f d)
SET pg_plan_advice.advice = ''; -- Do not forget to clean up the advice...
Dall'esempio dovrebbe essere chiaro che gli advice possono riguardare anche
un singolo elemento del piano, l'EXPLAIN riporta in modo preciso gli Advice applicati
ed il piano finale risultante.
Non sempre un Advice puo' essere applicato. I casi possibili sono:
- matched
- partially matched
- not matched
- inapplicable
- conflicting
- failed
Un Advice puo' essere accettato dal Planner
se e solo se e' contenuto in uno degli execution tree ritenuti validi
anche se poi scartati per il maggior costo.
Ecco l'elenco completo degli Advice utilizzabili:
SEQ_SCAN(target [ ... ])
TID_SCAN(target [ ... ])
INDEX_SCAN(target index_name [ ... ])
INDEX_ONLY_SCAN(target index_name [ ... ])
FOREIGN_JOIN((target [ ... ]) [ ... ])
BITMAP_HEAP_SCAN(target [ ... ])
DO_NOT_SCAN(target [ ... ])
JOIN_ORDER(join_order_item [ ... ])
MERGE_JOIN_MATERIALIZE(join_method_item [ ... ])
MERGE_JOIN_PLAIN(join_method_item [ ... ])
NESTED_LOOP_MATERIALIZE(join_method_item [ ... ])
NESTED_LOOP_MEMOIZE(join_method_item [ ... ])
NESTED_LOOP_PLAIN(join_method_item [ ... ])
HASH_JOIN(join_method_item [ ... ])
PARTITIONWISE(partitionwise_item [ ... ])
SEMIJOIN_UNIQUE(sj_unique_item [ ... ])
SEMIJOIN_NON_UNIQUE(sj_unique_item [ ... ])
GATHER(gather_item [ ... ])
GATHER_MERGE(gather_item [ ... ])
NO_GATHER(advice_target [ ... ])
In molti casi il target e' semplicemente l'alias della tabella, ma e' possibile indirizzare
partizioni, subquery, ...
Il modo piu' semplice per ottenere gli Advice ed i target, come abbiamo visto,
e' quello di utilizzare l'EXPLAIN!
Parametri
Vediamo in dettaglio i parametri del modulo pg_plan_advice:
name | context | setting | unit | min_val | max_val | source | category
-----------------------------------------------+------------+---------+------+---------+---------+---------+--------------------
pg_plan_advice.advice | user | | | | | default | Customized Options
pg_plan_advice.always_explain_supplied_advice | user | on | | | | default | Customized Options
pg_plan_advice.always_store_advice_details | user | off | | | | default | Customized Options
pg_plan_advice.feedback_warnings | user | off | | | | default | Customized Options
pg_plan_advice.trace_mask | user | off | | | | default | Customized Options
Tutti i parametri sono con context user, quindi possono essere cambiati in qualsiasi momento con un SET,
il parametro piu' significativo e' pg_plan_advice.advice che contiene l'Advice da passare al Planner.
pg_stash_advice
pg_stash_advice e' l'estensione che consente di associare un advice alle query
utilizzando il queryid.
Configurazione
Come prima cosa e' necessario caricare il modulo pg_stash_advice
e quindi va creata un'estensione.
Il modo piu' semplice e' quello di inserire pg_stash_advice nel parametro shared_preload_libraries
eseguire un riavvio e lanciare il comando
CREATE EXTENSION pg_stash_advice sul database in cui e' verra' utilizzato.
Nella configurazione di default viene attivato il processo pg_stash_advice worker
che si occupa di salvare gli stash periodicamente.
Utilizzo
Una volta creata l'estensione risultano disponibili le seguenti funzioni:
pg_create_advice_stash(stash_name text) returns void
pg_drop_advice_stash(stash_name text) returns void
pg_set_stashed_advice(stash_name text, query_id bigint, advice_string text) returns void
pg_get_advice_stashes() returns setof (stash_name text, num_entries bigint)
pg_get_advice_stash_contents(stash_name text) returns setof (stash_name text, query_id bigint, advice_string text)
pg_start_stash_advice_worker() returns void
Uno stash e' in pratica un contenitore di suggerimenti a cui viene assegnato un nome.
Le funzioni dell'estensione consentono di creare gli stash e di aggiungere i suggerimenti associati ad un queryid.
Per impostare lo stash da utilizzare si utilizza il comando SET pg_stash_advice.stash_name=stash_name;.
Vediamo un semplice esempio di utilizzo:
SELECT pg_create_advice_stash('acorn_hoard');
SELECT pg_set_stashed_advice('acorn_hoard', 4635755508291557612, 'JOIN_ORDER(d f)');
SET pg_stash_advice.stash_name='acorn_hoard';
EXPLAIN (COSTS OFF, PLAN_ADVICE, VERBOSE)
SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;
QUERY PLAN
--------------------------------------------------------------------------
Hash Join
Output: f.id, f.dim_id, f.amount, f.payload, d.id, d.category, d.label
Hash Cond: (d.id = f.dim_id)
-> Seq Scan on public.join_dim d
Output: d.id, d.category, d.label
-> Hash
Output: f.id, f.dim_id, f.amount, f.payload
-> Seq Scan on public.join_fact f
Output: f.id, f.dim_id, f.amount, f.payload
Query Identifier: 4635755508291557612
Supplied Plan Advice:
JOIN_ORDER(d f) /* matched */
Generated Plan Advice:
JOIN_ORDER(d f)
HASH_JOIN(f)
SEQ_SCAN(d f)
NO_GATHER(f d)
Con i primi due passi abbiamo creato il contenitore di suggerimenti ed inserito un suggerimento,
quindi con SET abbiamo attivato lo stash e l'explain della query finale mostra chiaramente che l'advice
e' stato riconosciuto, associato ed applicato.
Parametri
Vediamo in dettaglio i parametri di pg_stash_advice:
name | context | setting | unit | min_val | max_val | source | category
-----------------------------------------------+------------+---------+------+---------+---------+---------+--------------------
pg_stash_advice.persist | postmaster | on | | | | default | Customized Options
pg_stash_advice.persist_interval | sighup | 30 | s | 0 | 3600 | default | Customized Options
pg_stash_advice.stash_name | user | | | | | default | Customized Options
Per motivi di efficienza gli stash sono mantenuti in memoria ma
con i parametri di default gli stash vengono salvati su disco e quindi persistono al riavvio dell'istanza.
Il parametro piu' importante e' pg_stash_advice.stash_name che permette di specificare
lo stash da passare al Planner.
Tuning SQL: La gerarchia degli interventi
pg_plan_advice introduce un ulteriore strumento a disposizione del DBA e del programmatore
per migliorare le prestazioni delle query. Tuttavia, non dobbiamo dimenticare che il Planner e' solo
un componente di una complessa architettura in cui la correttezza di ogni elemento e' fondamentale.
I piani di esecuzione alterati artificialmente non devono sostituire una buona progettazione.
In ordine approssimativo di importanza e priorita' di intervento:
- Disegno logico: Struttura delle tabelle, scelta dei datatype corretti, normalizzazione o denormalizzazione e overnormalizzazione mirate.
- Disegno fisico: Indici appropriati, partizionamento delle tabelle e clustering dei dati.
- Salute del database: Statistiche aggiornate (ANALYZE), configurazione ottimale di autovacuum e risorse hardware.
- Scrittura delle query: Riscrittura del codice SQL per agevolare il lavoro del Planner
(e.g. evitare funzioni sulle colonne indicizzate, utilizzo di UNION anziche' di OR, utilizzo di CTE, ...).
- Parametri dell'ottimizzatore: Configurazione dei costi di PostgreSQL (es.
random_page_cost, effective_cache_size).
- Advice e Hint: Modifica manuale o guidata del piano di esecuzione sia con impostazioni di parametri del planner
(e.g.
SET enable_nestloop=off) che con l'uso di moduli o estensioni (e.g. pg_plan_advice, pg_hint_plan).
Da usare come ultima risorsa quando i punti precedenti non bastano!
Problemi? Parliamone!
postgres=# explain (plan_advice) ...;
ERROR: unrecognized EXPLAIN option "plan_advice"
LINE 1: explain (plan_advice) ...;
Non e' stato caricato il modulo pg_plan_advice!
pg_plan_advice e pg_stash_advice possono solo suggerire passi di un piano,
se il Planner non trova un piano in cui i passi sono applicabili non
c'e' modo di costringerlo ad applicare il suggerimento inviato.
Il piano deve essere tra quelli elaborati dal Planner anche se poi
non scelti perche' con un costo maggiore di altri.
Con il modulo pg_stash_advice e' possibile far scegliere al planner
a scegliere un determinato piano di esecuzione
indicando un advice assegnato ad un queryid.
In realta' pero' un piano d'esecuzione non dipende solo dalla query
ma anche dai parametri passati che possono rendere piu' o meno selettive le condizioni.
Quindi passare un advice in generale "vincola" il Planner ad una scelta che potrebbe non essere ottimale.
Si tratta di un problema comune a tutti gli strumenti di HINT...
E' il primo rilascio di questa funzionalita'...
vi sono diversi limiti noti e sicuramente vi saranno evoluzioni.
Varie ed eventuali
Anche se la release PG19 non e' ancora disponibile ufficialmente ecco e' possibile
installare la versione PG19 Beta1 (MacOS).
Nell'idea originale dell'autore Robert Haas le estensioni sono tre ma il commit di
pg_collect_advice e' stato spostato a PG20.
L'estensione pg_collect_advice consente di raccogliere, localmente al processo postgres o in memoria condivisa,
i piani di tutte le query eseguite basandosi ovviamente sul modulo pg_plan_advice.
Pagine web utili?
pg_plan_advice Devel Documentation,
Query Planning Parameters,
Explaining the unexplainable,
pg_hint_plan,
...
In italiano del nostro autore preferito:
ottimizzazione SQL,
l'estensione pg_stat_statements,
Aurora PostgreSQL QPM,
EDB PostgreSQL Advanced Server HINTS,
...