Ottimizzazione delle sottoquery correlate in Aurora PostgreSQL

Una sottoquery correlata fa riferimento alle colonne della tabella dalla query esterna. Viene valutata una volta per ogni riga restituita dalla query esterna. Nell’esempio seguente, la sottoquery fa riferimento a una colonna della tabella ot. Questa tabella non è inclusa nella clausola FROM della sottoquery, ma vi viene fatto riferimento nella clausola FROM della query esterna. Se la tabella ot ha 1 milione di righe, la sottoquery deve essere valutata 1 milione di volte.

SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);

Miglioramento delle prestazioni delle query di Aurora PostgreSQL con la trasformazione delle sottoquery

Abilitazione della trasformazione nella sottoquery

Per abilitare la trasformazione di sottoquery correlate in outer join equivalenti, impostare il parametro apg_enable_correlated_scalar_transform su ON. Il valore predefinito di questo parametro è OFF.

È possibile modificare il gruppo di parametri del cluster o dell’istanza per impostare i parametri. Per ulteriori informazioni, consulta Gruppi di parametri per Amazon Aurora.

In alternativa, è possibile configurare l’impostazione solo per la sessione corrente con il seguente comando:

SET apg_enable_correlated_scalar_transform TO ON;

Verifica della trasformazione

Utilizzare il comando EXPLAIN per verificare se la sottoquery correlata è stata trasformata in un outer join nel piano di query.

Quando la trasformazione è abilitata, la parte di sottoquery correlata applicabile verrà trasformata in outer join. Ad esempio:

postgres=> CREATE TABLE ot (a INT, b INT);
CREATE TABLE
postgres=> CREATE TABLE it (a INT, b INT);
CREATE TABLE

postgres=> SET apg_enable_correlated_scalar_transform TO ON;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);

                         QUERY PLAN
--------------------------------------------------------------
 Hash Join
   Hash Cond: (ot.a = apg_scalar_subquery.scalar_output)
   Join Filter: ((ot.b)::numeric < apg_scalar_subquery.avg)
   ->  Seq Scan on ot
   ->  Hash
         ->  Subquery Scan on apg_scalar_subquery
               ->  HashAggregate
                     Group Key: it.a
                     ->  Seq Scan on it                                        

La stessa query non viene trasformata quando il parametro GUC viene impostato su OFF. Il piano non avrà un outer join ma un sottopiano.

postgres=> SET apg_enable_correlated_scalar_transform TO OFF;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);
                QUERY PLAN
----------------------------------------
 Seq Scan on ot
   Filter: ((b)::numeric < (SubPlan 1))
   SubPlan 1
     ->  Aggregate
           ->  Seq Scan on it
                 Filter: (a = ot.a)  

Limitazioni

Utilizzo della cache delle sottoquery per migliorare le prestazioni delle query di Aurora PostgreSQL

Aurora PostgreSQL supporta la cache delle sottoquery per archiviare i risultati delle sottoquery correlate. Questa funzionalità salta le esecuzioni ripetute delle sottoquery correlate quando i risultati delle sottoquery sono già presenti nella cache.

Informazioni sulla cache delle sottoquery

Il nodo Memoize di PostgreSQL è la parte fondamentale della cache delle sottoquery. Il nodo Memoize mantiene una tabella hash nella cache locale per mappare i valori dei parametri di input alle righe dei risultati delle query. Il limite di memoria per la tabella hash è il prodotto di work_mem e hash_mem_multiplier. Per ulteriori informazioni, consulta Consumo di risorse.

Durante l’esecuzione delle query, la cache delle sottoquery utilizza la percentuale di riscontri nella cache (CHR, Cache Hit Rate) per stimare se la cache sta migliorando le prestazioni delle query e per decidere in fase di esecuzione della query se continuare a utilizzare la cache. CHR è il rapporto tra il numero di riscontri nella cache e il numero totale di richieste. Ad esempio, se una sottoquery correlata deve essere eseguita 100 volte e 70 di tali risultati di esecuzione possono essere recuperati dalla cache, il CHR è 0,7.

Per ogni numero di mancati riscontri nella cache di apg_subquery_cache_check_interval, il vantaggio della cache delle sottoquery viene valutato controllando se il CHR è maggiore di apg_subquery_cache_hit_rate_threshold. In caso contrario, la cache verrà eliminata dalla memoria e l’esecuzione della query tornerà alla riesecuzione della sottoquery originale, non memorizzata nella cache.

Parametri che controllano il comportamento della cache delle sottoquery

La tabella seguente elenca i parametri che controllano il comportamento della cache delle sottoquery.

È possibile modificare il gruppo di parametri del cluster o dell’istanza per impostare i parametri. Per ulteriori informazioni, consulta Gruppi di parametri per Amazon Aurora.

In alternativa, è possibile configurare l’impostazione solo per la sessione corrente con il seguente comando:

SET apg_enable_subquery_cache TO ON;

Attivazione della cache delle sottoquery in Aurora PostgreSQL

Quando la cache delle sottoquery è abilitata, Aurora PostgreSQL applica la cache per salvare i risultati delle sottoquery. Il piano di query avrà quindi un nodo Memoize sotto. SubPlan

Ad esempio, la sequenza di comandi seguente mostra il piano di esecuzione delle query stimato di una sottoquery correlata semplice senza cache delle sottoquery.

postgres=> SET apg_enable_subquery_cache TO OFF;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);

             QUERY PLAN
------------------------------------
 Seq Scan on ot
   Filter: (b < (SubPlan 1))
   SubPlan 1
     ->  Seq Scan on it
           Filter: (a = ot.a)

Dopo l'attivazioneapg_enable_subquery_cache, il piano di query conterrà un nodo Memoize sotto il SubPlan nodo, a indicare che la sottoquery prevede di utilizzare la cache.

postgres=> SET apg_enable_subquery_cache TO ON;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);

             QUERY PLAN
------------------------------------
 Seq Scan on ot
   Filter: (b < (SubPlan 1))
   SubPlan 1
     ->  Memoize
           Cache Key: ot.a
           Cache Mode: binary
           ->  Seq Scan on it
                 Filter: (a = ot.a)

Il piano di esecuzione delle query effettivo contiene maggiori dettagli sulla cache delle sottoquery, inclusi i riscontri nella cache e i mancati riscontri nella cache. L’output seguente mostra il piano di esecuzione delle query effettivo della query di esempio precedente dopo l’inserimento di alcuni valori nelle tabelle.

postgres=> EXPLAIN (COSTS FALSE, TIMING FALSE, ANALYZE TRUE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);
            QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on ot (actual rows=2 loops=1)
   Filter: (b < (SubPlan 1))
   Rows Removed by Filter: 8
   SubPlan 1
     ->  Memoize (actual rows=0 loops=10)
           Cache Key: ot.a
           Cache Mode: binary
           Hits: 4  Misses: 6  Evictions: 0  Overflows: 0  Memory Usage: 1kB
           ->  Seq Scan on it (actual rows=0 loops=6)
                 Filter: (a = ot.a)
                 Rows Removed by Filter: 4   

Il numero totale di riscontri nella cache è 4 e il numero totale di mancati riscontri nella cache è 6. Se il numero totale di riscontri e mancati riscontri è inferiore al numero di loop nel nodo Memoize, significa che la valutazione CHR non ha avuto esito positivo e che la cache è stata ripulita e abbandonata a un certo punto. L’esecuzione della sottoquery è quindi tornata alla riesecuzione originale non memorizzata nella cache.

Limitazioni

La cache delle sottoquery non supporta determinati modelli di sottoquery correlate. Questi tipi di query verranno eseguiti senza cache, anche se la cache delle sottoquery è attivata:

Miglioramento delle prestazioni delle query PostgreSQL di Aurora utilizzando la trasformazione della finestra delle subquery

Aurora PostgreSQL può accelerare le sottoquery aggregate correlate trasformandole in funzioni finestra equivalenti. Questa ottimizzazione si applica alle sottoquery scalari correlate in una clausola WHERE che confrontano un'espressione esterna con un aggregato, come il seguente modello di query:

SELECT o.id, o.amount
FROM orders o, products p
WHERE p.id = o.product_id
  AND o.amount < (
    SELECT 0.2 * avg(o2.amount)
    FROM orders o2
    WHERE o2.product_id = p.id
  );

La trasformazione è una riscrittura a livello di piano che modifica solo il piano di esecuzione, non i risultati della query.

Quando utilizzare la trasformazione della finestra di subquery

Valuta la possibilità di abilitare questa trasformazione se hai query che corrispondono al modello di autocorrelazione: una riga esterna confrontata con un aggregato calcolato sulla stessa tabella con una condizione di correlazione.

Questa trasformazione è complementare alla trasformazione delle sottoquery correlate esistente, che riscrive le sottoquery correlate come join esterni. La trasformazione della finestra di sottoquery le riscrive invece come funzioni di finestra. Le due trasformazioni coprono modelli diversi:

È possibile abilitare entrambe le trasformazioni contemporaneamente. Quando una query è idonea per entrambe, viene applicata la trasformazione della finestra della sottoquery; quando è idonea per una sola, viene applicata quella trasformazione.

Attivazione della trasformazione della finestra di sottoquery

Per abilitare la trasformazione di sottoquery aggregate correlate in funzioni di finestra, impostate il parametro su. apg_enable_subquery_to_window_transform ON Il valore predefinito di questo parametro è OFF.

È possibile modificare il gruppo di parametri del cluster o dell’istanza per impostare i parametri. Per ulteriori informazioni, consulta Gruppi di parametri per Amazon Aurora.

In alternativa, è possibile configurare l’impostazione solo per la sessione corrente con il seguente comando:

SET apg_enable_subquery_to_window_transform TO ON;

Per disabilitare la trasformazione, impostate il parametro in modo che OFF utilizzi gli stessi metodi. Le modifiche hanno effetto senza il riavvio dell'istanza; il SET a livello di sessione è immediato e le modifiche ai gruppi di parametri si applicano alle nuove connessioni.

Verifica della trasformazione

Utilizzate il comando EXPLAIN per verificare se la sottoquery correlata è stata trasformata in una funzione finestra nel piano di query.

Quando la trasformazione è abilitata, il piano di query contiene un WindowAgg nodo anziché un SubPlan nodo. Ad esempio:

postgres=> SET apg_enable_subquery_to_window_transform TO ON;
SET
postgres=> EXPLAIN (COSTS FALSE)
SELECT o.id, o.amount
FROM orders o, products p
WHERE p.id = o.product_id
  AND o.amount < (
    SELECT 0.2 * avg(o2.amount)
    FROM orders o2
    WHERE o2.product_id = p.id
  );

                      QUERY PLAN
------------------------------------------------------
 Subquery Scan on _sw
   Filter: (_sw.amount < (0.2 * _sw._w_agg_result))
   ->  WindowAgg
         Window: w1 AS (PARTITION BY o.product_id)
         ->  Sort
               Sort Key: o.product_id
               ->  Hash Join
                     Hash Cond: (o.product_id = p.id)
                     ->  Seq Scan on orders o
                     ->  Hash
                           ->  Seq Scan on products p

La stessa query non viene trasformata quando il parametro GUC viene impostato su OFF. Il piano non avrà un WindowAgg nodo SubPlan ma un.

postgres=> SET apg_enable_subquery_to_window_transform TO OFF;
SET
postgres=> EXPLAIN (COSTS FALSE)
SELECT o.id, o.amount
FROM orders o, products p
WHERE p.id = o.product_id
  AND o.amount < (
    SELECT 0.2 * avg(o2.amount)
    FROM orders o2
    WHERE o2.product_id = p.id
  );

                 QUERY PLAN
---------------------------------------------
 Hash Join
   Hash Cond: (o.product_id = p.id)
   Join Filter: (o.amount < (SubPlan 1))
   ->  Seq Scan on orders o
   ->  Hash
         ->  Seq Scan on products p
   SubPlan 1
     ->  Aggregate
           ->  Seq Scan on orders o2
                 Filter: (product_id = p.id)

SELECT set_config('babelfishpg_tsql.apg_enable_subquery_to_window_transform', 'true', false);
SELECT set_config('babelfishpg_tsql.apg_enable_subquery_to_window_transform', 'false', false);

SET BABELFISH_SHOWPLAN_ALL ON
-- run your query
SET BABELFISH_SHOWPLAN_ALL OFF

Limitazioni

Se una query non soddisfa queste condizioni, viene eseguita con il piano originale; non viene generato alcun errore.