Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Suggerimenti per migliorare le prestazioni
PrecedenteLocking e indiciStatistiche usate dal pianificatoreSuccessivo

14. Suggerimenti per migliorare le prestazioni

Le prestazioni della query possono essere influenzate da molte cose. Alcune di queste possono essere controllate dall'utente, mentre altre sono fondamentali per il disegno del sistema. Questo capitolo presenta alcuni consigli per capire e mettere a punto le prestazioni di PostgreSQL™.

14.1. Usare EXPLAIN

PostgreSQL™ crea un piano di query per ogni query che riceve. Scegliere il giusto piano che corrisponda alla struttura della query e alle proprietà dei dati è fondamentale per avere buone prestazioni, per questo il sistema include un complesso pianificatore che prova a scegliere piani buoni. È possibile usare il comando EXPLAIN(7) per vedere che piano di query viene generato dal pianificatore per ogni query. La lettura dei piani è un'arte che necessita una vasta spiegazione, questo libro non lo è; ma ecco alcune informazioni.

La struttura di un piano di query è un albero di nodi di piano. I nodi al livello più basso dell'albero sono nodi di scanzione di tabella: essi restituiscono righe grezze da una tabella. Ci sono diversi tipi di nodi di scansione per diversi metodi di accesso alla tabella: scansioni sequenziali, scansioni con indice, scansioni con indice bitmap. Se la query richiede il join, l'aggregazione, l'ordinamento o altre operazioni sulle righe grezze, allora ci saranno nodi aggiuntivi superiori ai nodi di scansione per eseguire queste operazioni. Di nuovo, di solito c'è più di un modo possibile per fare queste operazioni, così anche qui possono essere usati diversi tipi di nodo. L'output di EXPLAIN ha una linea per ogni nodo nell'albero del piano, che mostra il tipo di base del nodo più il costo stimato che il pianificatore ha calcolato per l'esecuzione di quel nodo. La prima linea (il nodo superiore a tutti gli altri) possiede il costo totale stimato per l'esecuzione del piano; il pianificatore cerca di minimizzare questo numero.

Ecco un banale esempio, solo per mostrare come appare l'output: [9]

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

I numeri presentati da EXPLAIN sono (da sinistra a destra):

  • Il costo stimato per iniziare (tempo speso prima che la scanzione di output possa partire, per es., il tempo per fare l'ordinamento in un nodo di ordinamento)

  • Costo totale stimato (se vengono recuperate tutte le righe, sebbene potrebbero non esserlo; per es., una query con una clausola LIMIT si fermerà prima di pagare il costo totale del nodo Limit di input del piano)

  • Numero stimato di righe in output da questo nodo del piano (di nuovo, solo se eseguito fino al completamento)

  • Dimensione media stimata (in byte) delle righe in output da questo nodo del piano

I costi sono misurati in unità arbitrarie determinate dai parametri di costo del pianificatore (si veda Sezione 18.6.2, «Costanti di costo del planner»). La prassi tradizionale è di misurare i costi in unità di pagina disco; così, seq_page_cost è impostato convenzionalmente a 1.0 e i parametri degli altri costi sono impostati relavitamente a quello. (Gli esempi in questa sezione sono eseguiti con i parametri di costo predefiniti).

È importatnte notare che il costo di un nodo di livello superiore include il costo di tutti i suoi nodi figli. È importante anche capire che il costo tiene conto solo di cose di cui il pianificatore tiene di conto. In particolare, il costo non considera il tempo speso trasmettendo le righe risultanti al client, che potrebbe essere un fattore importante nel calcolo del reale tempo di esecuzione; ma il pianificatore lo ignora dato che non può cambiarlo modificando il piano. (Ogni piano corretto fornirà in output lo stesso insieme di righe).

Il valore delle righe è un po' complicato dato che non è il numero di righe processate o scansionate dal nodo del piano. Di solito è meno, in quanto riflette la selettività stimata di qualsiasi condizione WHERE applicata al nodo. Idealmente le righe stimate si avvicineranno al numero di righe effettivamente restituite, aggiornate, o cancellate dalla query.

Ritornando ai nostri esempi:

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

Questo è semplice come appare. Se si fa:

SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';

si scoprirà che tenk1 ha 358 pagine di disco e 10000 righe. Il costo stimato è calcolato come (pagine disco lette * seq_page_cost) + (righe scansionate * cpu_tuple_cost). In maniera predefinita, seq_page_cost è 1.0 e cpu_tuple_cost è 0.01, così il costo stimato è (358 * 1.0) + (10000 * 0.01) = 458.

Adesso si modifichi la query originale per aggiungere una condizione WHERE:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..483.00 rows=7033 width=244)
   Filter: (unique1 < 7000)

Notare che l'output di EXPLAIN mostra che la clausola WHERE è stata applicata come una condizione «filtro»; questo significa che il nodo del piano controlla la condizione per ogni riga che scansiona, e mostra in output solo quelle che soddisfano la condizione. La stima delle righe di output è stata ridotta a causa della clausola WHERE. Comunque, la scansione dovrà comunque visitare tutte le 10000 righe, così il costo non è diminuito; infatti si è alzato un po' (di 10000 * cpu_operator_cost, per essere esatti) per riflettere il tempo in più speso dalla CPU per controllare la condizione WHERE.

Il numero effettivo di righe che questa query selezionerà sono 7000, ma le righe stimate sono solo approssimate. Se si prova a duplicare questo esperimento, probabilmente si otterranno stime leggermente diverse; in più, cambierà dopo ogni comando ANALYZE, dato che le statistiche prodotte da ANALYZE sono prese da un campione casuale della tabella.

Adesso, rendiamo la condizione più restrittiva:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=2.37..232.35 rows=106 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
         Index Cond: (unique1 < 100)

Qui il pianificatore ha deciso di usare un piano a due passaggi: il nodo del piano inferiore visita un indice per trovare le posizioni delle righe che corrispondono alla condizione dell'indice, e quindi il nodo di piano superiore effettivamente ottiene le righe dalla tabella stessa. Prendere le righe separatamente è molto più costoso che leggerle sequenzialmente, ma dato che tutte le pagine della tabella devono essere visitate, è comunque più conveniente di una scansione sequenziale. (La ragione per usare due livelli di piano è che il nodo del piano superiore ordina le posizioni della riga identificate dall'indice nell'ordine fisico prima di leggerle, per minimizzare il costo di fetch separate. Il «bitmap» menzionato nei nomi di nodo è il meccanismo che fa l'ordinamento).

Se la condizione WHERE è abbastanza selettiva, il pianificatore potrebbe usare un «simple» piano di scansione di indice:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..10.00 rows=2 width=244)
   Index Cond: (unique1 < 3)

In questo caso le righe della tabella sono ottenute in ordine di indice, che le rende ancora più costose da leggere, ma ce ne sono alcune per cui il costo aggiuntivo dell'ordinamento delle posizioni della riga non ha importanza. La maggior parte delle volte si vedrà questo tipo di piano per query che ottengono solo una singola riga, e per query che hanno una condizione ORDER BY che corrisponde all'ordine dell'indice.

Aggiungere un'altra condizione alla clausola WHERE:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3 AND stringu1 = 'xxx';

                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..10.01 rows=1 width=244)
   Index Cond: (unique1 < 3)
   Filter: (stringu1 = 'xxx'::name)

La condizione stringu1 = 'xxx' aggiunta riduce la stima delle righe in output, ma non il costo, dato che si dovrà comunque visitare lo stesso insieme di righe. Notare che la clausola stringu1 non può essere applicata come condizione di indice (dato che questo indice è solo sulla colonna unique1). Invece viene applicato come filtro sulle righe ricavate dall'indice. Per questo il costo è aumentato sensibilmente per rispecchiare questo controllo ulteriore.

Se ci sono indici su diverse colonne referenziate nella WHERE, il pianificatore potrebbe scegliere di usare una combinazione di AND o OR degli indici:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=11.27..49.11 rows=11 width=244)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   ->  BitmapAnd  (cost=11.27..11.27 rows=11 width=0)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
               Index Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..8.65 rows=1042 width=0)
               Index Cond: (unique2 > 9000)

Ma questo richiede la visita di entrambi gli indici, che non è necessariamente un vantaggio rispetto ad usare solo un indice e trattare l'altra condizione come un filtro. Se si cambiano gli intervalli coinvolti si noterà che il piano cambia conseguentemente.

Si provi a fare il join di due tabelle, usando le colonne di cui abbiamo discusso:

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Nested Loop  (cost=2.37..553.11 rows=106 width=488)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244)
         Recheck Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
               Index Cond: (unique1 < 100)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..3.01 rows=1 width=244)
         Index Cond: (unique2 = t1.unique2)

In this nested-loop join, the outer (upper) scan is the same bitmap index scan we saw earlier, and so its cost and row count are the same because we are applying the WHERE clause unique1 < 100 at that node. The t1.unique2 = t2.unique2 clause is not relevant yet, so it doesn't affect the row count of the outer scan. For the inner (lower) scan, the unique2 value of the current outer-scan row is plugged into the inner index scan to produce an index condition like unique2 = constant. So we get the same inner-scan plan and costs that we'd get from, say, EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42. The costs of the loop node are then set on the basis of the cost of the outer scan, plus one repetition of the inner scan for each outer row (106 * 3.01, here), plus a little CPU time for join processing.

In questo esempio il conteggio della riga di output è lo stesso del prodotto dei conteggi delle due scansioni, ma questo non è vero in tutti i casi dato che ci potrebbero essere clausole WHERE che fanno riferimento a entrambe le tabelle e quindi possono essere applicate solo al punto join, non della scansione dell'input. Per esempio, se avessimo aggiunto WHERE ... AND t1.hundred < t2.hundred, diminuirebbe il conteggio delle righe di output del nodo join, ma non cambierebbe la scansione dell'input.

Un modo di guardare ai diversi piani è di forzare il pianificatore a ignorare qualsiasi strategia pensi sia la più economica, usando i flag abilita/disabilita descritti in Sezione 18.6.1, «Planner Method Configuration». (Queso è uno strumento rudimentale, ma utile. Si veda anche Sezione 14.3, «Controllare il pianificatore con clausole JOIN esplicite»).

SET enable_nestloop = off;
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Hash Join  (cost=232.61..741.67 rows=106 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..458.00 rows=10000 width=244)
   ->  Hash  (cost=232.35..232.35 rows=106 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
                     Index Cond: (unique1 < 100)

Questo piano propone di estrarre le 100 righe di interesse di tenk1 usando la stessa vecchia scansione di indice, mettendoli via in una tabella hash in memoria, e quindi facendo una scansione sequenziale di tenk2, per possibili corrispondenze di t1.unique2 = t2.unique2 per ogni riga di tenk2. Il costo di leggere tenk1 e impostare la tabella hash è un costo di inizio per la join hash, dato che non ci sarà output finchè non è possibile cominciare a leggere tenk2. Il tempo totale stimato per la join include anche un costo massiccio per la CPU per sondare la tabella hash 10000 volte. Notare, comunque, che non si conterà 10000 volte 232.35; la messa a punto della tabella hash viene fatta solo una volta in questo tipo di piano.

È possibile controllare l'accuratezza dei costi stimati del pianificatore usando EXPLAIN ANALYZE. Questo comando esegue effettivamente la query, e quindi mostra il tempo di esecuzione reale accumulato all'interno di ogni nodo del piano così come i costi stimati mostrati da una normale EXPLAIN. Per esempio, si potrebbe ottenere un risultato simile a questo:

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)
         Recheck Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)
               Index Cond: (unique1 < 100)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1 loops=100)
         Index Cond: (unique2 = t1.unique2)
 Total runtime: 14.452 ms

Notare che i valori di «actual time» sono in millisecondi, mentre i costi stimati sono espressi in unità arbitrarie; così probabilmente non corrisponderanno. La cosa da tener conto è se il rapporto tra il tempo effettivo e i costi stimati sono molto diversi.

In alcuni piani di query, è possibile che un nodo di sottopiano venga eseguito più di una volta. Per esempio, la scansione di indice interna viene eseguita una volta per ogni riga esterna nel piano con ciclo annidato visto sopra. In questi casi, il valore loops riporta il numero totale di esecuzioni del nodo, e il tempo effettivo e i valori delle righe mostrate sono medie per esecuzione. Viene fatto questo per rendere i numeri confrontabili con il modo in cui sono mostrati i costi stimati. Moltiplicare per il valore loops per avere il tempo totale speso effettivamente in quel nodo.

Il tempo di esecuzione totale mostrato in EXPLAIN ANALYZE include l'avvio dell'esecutore e il tempo per la chiusura, così come il tempo speso processando le righe risultanti. Non include il tempo per il parsing, la riscrittura, o la pianificazione. Per una query SELECT, il tempo di esecuzione totale normalmente sarà solo un po' più grande rispetto al tempo totale riportato per il nodo del piano più alto. Per i comandi INSERT, UPDATE, e DELETE, il tempo totale di esecuzione potrebbe essere considerevolmente grande, dato che include il tempo speso processando le righe risultanti. Per questi comandi, il tempo per il nodo di piano superiore è essenzialmente il tempo speso localizzando le vecchie righe e/o calcolando le nuove, ma non include il tempo speso applicando i cambiamenti. Anche il tempo speso attivando i trigger, se presenti, sta fuori del nodo di piano superiore, e viene mostrato separatamente per ogni trigger.

Vale la pena notare che i risultati di EXPLAIN non devono essere estrapolati in situazioni diverse da quella che si sta attualmente testando; per esempio, non si possono prendere per buoni i risultati di una tabella di piccole dimensioni se applicati ad una di grandi dimensioni. Le stime di costo del pianificatore non sono lineari e quindi si potrebbe essere scelto un piano diverso per una tabella di dimensioni diverse. Un esempio estremo è che su una tabella che occupa solo una pagina disco, quasi sempre si otterrà un piano scan sequenziale sia che gli indici siano disponibili che non. Il pianificatore si rende conto che dovrà fare una lettura di pagina disco in ogni caso per processare la tabella, e quindi non c'è vantaggio nello spendere letture di pagina aggiuntive per cercare un indice.



[9] Gli esempi in questa sezione sono presi dal database di test di regressione dopo aver fatto un VACUUM ANALYZE, usando i sorgenti di sviluppo della versione 8.2. Si dovrebbe essere capaci di ottenere risultati simili se si provano gli esempi, ma i costi stimati e i conteggi delle righe potrebbero variare sensibilmente dato che le statistiche di ANALYZE sono campioni casuali piuttosto che esatte.

Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Suggerimenti per migliorare le prestazioni
PrecedenteLocking e indiciStatistiche usate dal pianificatoreSuccessivo