L'ottimizzazione delle query SQL e' molto importante in tutti i database
relazionali.
Spesso si leggono query generate da codice in cui sono presenti
lunghissime liste di valori confrontate con l'operatore IN (...).
Non sempre questo e' il modo piu' efficiente di trattare una
serie di confronti in Postgres.
In questa paginetta vedremo una serie di esempi pratici con
costrutti alternativi
che possono essere piu' efficienti.
Con una serie di esempi pratici, partendo dai costrutti SQL meno performanti ed arrivando a quelli piu' efficiente, utilizzando quasi tutti passi disponibili all'ottimizzatore Postgres vederemo come rendere efficienti le query con l'operatore IN.
Per brevita' non sono descritti in questa paginetta gli accessi ai dati, gli algoritmi di join, gli indici, l'EXPLAIN, ... la cui conoscenza e' data per scontata ma che potete rivedere nella pagina Ottimizzazione SQL in Postgres.
Iniziamo a selezionare tutti i dati dalla nostra tabella:
Per comprendere cosa fa Postgres possiamo utilizzare l' EXPLAIN [NdA basta aggiungere il comando prima della query]:
Seq Scan on pgbench_accounts (cost=0.00..2824.00 rows=100000 width=97)
Se non vi sono condizioni di ricerca Postgres non puo' far altro che eseguire un Seq Scan ovvero un sequential scan dell'intera tabella leggendo tutti i dati.
Ma se abbiamo una condizione su un indice il comportamento e' molto diverso:
Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.29..8.31 rows=1 width=97) Index Cond: (aid = 17)
Naturalmente il costo e quindi la durata della ricerca e' molto differente
ed e' molto piu' basso quando si accede per chiave ad un unico valore.
Infatti viene utilizzato un Index Scan che ricerca i dati sull'indice
e quindi sulla heap.
Ma cosa succede se vogliamo accedere per piu' valori ma non a tutti?
E' quello che vedremo con gli esempi dei prossimi capitoli!
Vediamo cosa sceglie l'ottimizzatore per svolgere una codizione con l'operatore IN():
Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.29..29.81 rows=5 width=97)
Index Cond: (aid = ANY ('{17,97,113,199,271}'::integer[]))
L'ottimizzatore utilizza uno Index Scan confrontando tutta la lista di valori.
Naturalmente deve essere presente l'indice sulla colonna ricercata, altrimenti la ricerca deve avvenire con un Seq Scan ed un semplice Filter:
Seq Scan on pgbench_accounts (cost=0.00..3449.00 rows=5 width=97)
Filter: (aid = ANY ('{17,97,113,199,271}'::integer[]))
Fino a che il numero di elementi e' limitato l'operatore IN e gli altri costrutti alternativi presentano piccole differenze nei tempi di esecuzione; ma quando l'elenco dei valori e' grande le differenze del tempo di esecuzione possono essere molto elevate.
In questa pagina cerchiamo di migliorare le prestazioni...
ma naturalmente si possono anche peggiorare!
Ero indeciso se riportare questi esempi ma la frequenza in cui si trovano
nella pratica mi ha convinto che era opportuno aggiungerli.
Meglio fare 100 transazioni separate o una sola transazione con 100 SQL?
E' sicuramente migliore la seconda scelta!
Meglio fare 100 query che restituiscono un record o una query che restituisce 100 record?
E' sicuramente migliore la seconda scelta!
Ma vi sono casi in cui non e' sempre semplice scegliere l'alternativa migliore...
Come alternativa alla IN possiamo utilizzare una UNION:
HashAggregate (cost=41.67..41.72 rows=5 width=352)
Group Key: pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler
-> Append (cost=0.29..41.62 rows=5 width=352)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.29..8.31 rows=1 width=97)
Index Cond: (aid = 17)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts pgbench_accounts_1 (cost=0.29..8.31 rows=1 width=97)
Index Cond: (aid = 97)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts pgbench_accounts_2 (cost=0.29..8.31 rows=1 width=97)
Index Cond: (aid = 113)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts pgbench_accounts_3 (cost=0.29..8.31 rows=1 width=97)
Index Cond: (aid = 199)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts pgbench_accounts_4 (cost=0.29..8.31 rows=1 width=97)
Index Cond: (aid = 271)
L'ottimizzatore esegue per 5 volte un'accesso per chiave Index Scan e quindi aggrega in Append il risultato.
Certo che l'utilizzo della UNION genera uno statement molto piu' lungo della query originale...
[NdA come giustamente segnalato... e' piu' efficiente utilizzare una UNION ALL al posto della UNION;
il piano per gestire la condizione della IN() non cambia ma si risparmia l'aggregazione finale]
Oppure utilizzare la OR:
Bitmap Heap Scan on pgbench_accounts (cost=21.51..40.83 rows=5 width=97)
Recheck Cond: ((aid = 17) OR (aid = 97) OR (aid = 113) OR (aid = 199) OR (aid = 271))
-> BitmapOr (cost=21.51..21.51 rows=5 width=0)
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..4.30 rows=1 width=0)
Index Cond: (aid = 17)
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..4.30 rows=1 width=0)
Index Cond: (aid = 97)
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..4.30 rows=1 width=0)
Index Cond: (aid = 113)
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..4.30 rows=1 width=0)
Index Cond: (aid = 199)
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..4.30 rows=1 width=0)
Index Cond: (aid = 271)
In questo caso l'ottimizzatore sceglie un Bitmap Index Scan, che e' una variazione dell'algoritmo di index scan perche' l'accesso alla tabella viene posticipato dopo aver trovato tutti i blocchi con le righe di interesse.
Anche se vi possono essere eccezioni nella maggioranza dei casi le esecuzioni con UNION o con OR sono piu' lente dell'operatore IN e quindi fino ad ora siamo riusciti a solo peggiorare [NdA Pero' abbiamo visto i diversi metodi di accesso che l'ottimizzatore puo' utilizzare nelle query]!
Come tutti i database relazionali Postgres esegue in modo molto efficiente i join. Quando il numero di valori della clausola IN e' molto elevato si puo' utilizzare un diverso statement SQL piu' efficiente che sfrutta un join. La query di esempio con la IN puo' essere trasformata in:
Nested Loop (cost=0.29..41.61 rows=5 width=97)
-> Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=4)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.29..8.31 rows=1 width=97)
Index Cond: (aid = "*VALUES*".column1)
Con un numero elevato di valori l'utilizzo di JOIN VALUES generalmente
fornisce prestazioni nettamente migliori della clausola IN.
In questo caso esegue un Nested Loop utilizzando come outer table
la lista di valori ed una ricerca per indice sulla tabella pgbench_accounts.
Le differenze possono diventare molto evidenti se non sono presenti tutti gli indici
e se il numero di valori e' dell'ordine delle centinaia.
Poiche' viene utilizzato un join con VALUES abbiamo piu' strumenti per modificare l'execution plan come impostando un work_mem o la scelta dell'algoritmo:
Hash Join (cost=0.12..3199.18 rows=5 width=97)
Hash Cond: (pgbench_accounts.aid = "*VALUES*".column1)
-> Seq Scan on pgbench_accounts (cost=0.00..2824.00 rows=100000 width=97)
-> Hash (cost=0.06..0.06 rows=5 width=4)
-> Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=4)
Il costo in questo caso e' piu' alto perche' viene eseguito un Hash Join molto efficiente... ma anche una scansione sequenziale della tabella pgbench_accounts. Anche in questo caso e' opportuno valutare le diverse alternative con l'EXPLAIN o, meglio, con l'EXPLAIN ANALYZE.
Le temporary tables di Postgres hanno diverse caratteristiche che le rendono molto performanti.
Le temporary tables sono visibili solo per la durata della sessione e sono private ovvero ciascuna connessione vede la propria copia di dati. Le temporary tables vengono cancellate automaticamente, sono UNLOGGED (ovvero non richiedono scritture sui WAL e non vengono replicate), non vengono trattate dall'VACUUM e dall'ANALYZE automatici. Per default le temporary tables vengono create sul temp_tablespace ed e' possibile indicare come trattare i dati al termine della transazione: ON COMMIT [PRESERVE ROWS | DELETE ROWS | DROP]. A parte questo le temporary tables sono pero' tabelle a tutti gli effetti e possono essere create, utilizzate con statement DML successivi, possono essere creati indici, eseguito l'ANALYZE, ...
Questo permette di ottimizzare le query con tutti gli strumenti a disposizione del programmatore, anche se a costo di una gestione piu' complessa di quella del semplice operatore di IN:
Merge Join (cost=180.08..6356.44 rows=2550 width=97)
Merge Cond: (pgbench_accounts.aid = list.v)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.29..5888.41 rows=100000 width=97)
-> Sort (cost=179.78..186.16 rows=2550 width=4)
Sort Key: list.v
-> Seq Scan on list (cost=0.00..35.50 rows=2550 width=4)
Nell'esempio e' stato utilizzato il COPY perche' e' il comando piu' efficiente
in Postgres, ma e' molto veloce anche una semplice insert multivalue:
INSERT INTO list VALUES (17), (97), (113), (199), (271);
La creazione di indici, l'analyze sono passi che dipendono dalle condizioni:
con un Hash Join la creazione dell'indice non e' necessaria.
In questo caso poiche' e' presente un indice sulla temporary table Postgres puo' eseguire un Merge Join che e' molto efficiente quando le due tabelle sono di grandi dimensioni.
Non e' tutto qui!
I costrutti alternativi presentati in questa pagina sono validi se e solo se sono soddisfatti alcuni requisiti di base. Gli indici debbono essere stati creati, le tabelle debbono essere state analizzate, il VACUUM deve essere stato eseguito correttamente e le percentuali di dead tuples e di bloat debbono essere ragionevoli, gli indici non debbono essere invalidi, la base dati ed il sistema ospite debbono essere stati correttamente dimensionati, installati e configurati, ...
Ci sono molteplici altri costrutti SQL che e' possibile utilizzare. Ad esempio le CTE (clausola WITH), le subquery (WHERE EXISTS) per ricordare i principali. Nel caso la condizione sia NOT IN le possibili variazioni dell'SQL sono quelle gia' descritte ma... la differenza delle prestazioni spesso e' ancora piu' rilevante. Le variazioni sul tema poi sono innumerevoli se si aggiunge il partizionamento, il parallelismo, le versioni di Postgres, il teorema di De Morgan, il teorema di Pareto, ... e la legge di Murphy!
Ogni caso puo' essere diverso ed e' quindi importante analizzare le diverse alternative. Gli strumenti sono sempre gli stessi: misurare i tempi di risposta ed analizzare i piani di esecuzione con EXPLAIN o EXPLAIN ANALYZE.
In questa paginetta sono stati riportati elementi utili per l'ottimizzazione SQL dell'operatore IN in PostgreSQL. Altri documenti utili sull'argomento sono: Ottimizzazione SQL in PostgreSQL Tuning in PostgreSQL e Statistiche prestazionali in PostgreSQL (in particolare l'estensione pg_stat_statements) perche' trattano in modo molto piu' ampio gli argomenti relativi all'ottimizzazione SQL non descritti in questa pagina che si occupa di un caso specifico.
In questa paginetta abbiamo utilizzato la tabella pgbench_account del tool pgbench. E' una tabella con 10.000 righe (con la scala di default) con una chiave primaria sul'account id (aid). Si tratta di una tabella molto semplice pero' i concetti descritti hanno validita' generale e sono replicabili mutatis mutandis a situazioni reali con oggetti piu' complessi, di maggiori dimensioni, in query piu' articolate... Auguri!
Titolo: Ottimizzazione IN()... in PostgreSQL
Livello: Avanzato
Data:
14 Febbraio 2021 ❤️
Versione: 1.0.1 - 1 Aprile 2023
Autore: mail [AT] meo.bogliolo.name