Miglioramento delle prestazioni delle query di Aurora PostgreSQL con la trasformazione delle sottoquery Utilizzo della cache delle sottoquery per migliorare le prestazioni delle query di Aurora PostgreSQL

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);

Nota

La trasformazione delle sottoquery e la cache delle sottoquery sono disponibili in Aurora PostgreSQL a partire dalla versione 16.8, mentre Babelfish per Aurora PostgreSQL supporta queste funzionalità dalla versione 4.2.0.
A partire dalle versioni 4.6.0 e 5.2.0 di Babelfish per Aurora PostgreSQL, queste funzionalità sono controllate dai seguenti parametri:
- babelfishpg_tsql.apg_enable_correlated_scalar_transform
- babelfishpg_tsql.apg_enable_subquery_cache
Per impostazione predefinita, entrambi i parametri sono attivati.

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

Aurora PostgreSQL può accelerare le sottoquery correlate trasformandole in outer join equivalenti. Questa ottimizzazione si applica ai seguenti due tipi di sottoquery correlate:

Sottoquery che restituiscono un singolo valore aggregato e vengono visualizzate nell’elenco SELECT.
```
SELECT ot.a, ot.b, (SELECT AVG(it.b) FROM it WHERE it.a = ot.a) FROM ot;
```
Sottoquery che restituiscono un singolo valore aggregato e vengono visualizzate in una clausola WHERE.
```
SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);
```

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. Per 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

La sottoquery deve trovarsi nell’elenco SELECT o in una delle condizioni della clausola where. In caso contrario, non verrà trasformata.
La sottoquery deve restituire una funzione di aggregazione. Le funzioni di aggregazione definite dall’utente non sono supportate per la trasformazione.
Una sottoquery la cui espressione restituita non è una semplice funzione di aggregazione non verrà trasformata.
La condizione correlata nelle clausole WHERE della sottoquery deve essere un semplice riferimento di colonna. In caso contrario, non verrà trasformata.
La condizione correlata nella sottoquery in cui le clausole devono essere un semplice predicato di uguaglianza.
La sottoquery non può contenere né una clausola HAVING né una clausola GROUP BY.
La clausola where nella sottoquery può contenere uno o più predicati combinati con AND.

Nota

L’impatto della trasformazione sulle prestazioni varia a seconda dello schema, dei dati e del carico di lavoro. L’esecuzione di sottoquery correlate con la trasformazione può migliorare significativamente le prestazioni all’aumentare del numero di righe prodotte dalla query esterna. Si consiglia vivamente di testare questa funzionalità in un ambiente non di produzione con lo schema, i dati e il carico di lavoro effettivi prima di abilitarla in un ambiente di produzione.

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.

Parametro	Descrizione	Default	Consentito
apg_enable_subquery_cache	Abilita l’uso della cache per sottoquery scalari correlate.	OFF	ON, OFF
apg_subquery_cache_check_interval	Imposta la frequenza, in numero di mancati riscontri nella cache, per valutare la percentuale di riscontri nella cache delle sottoquery.	500	0–2147483647
apg_subquery_cache_hit_rate_threshold	Imposta la soglia per la percentuale di riscontri nella cache delle sottoquery.	0.3	0.0 - 1.0

Nota

Valori maggiori di apg_subquery_cache_check_interval possono migliorare l’accuratezza della stima dei vantaggi della cache basata su CHR, ma aumenteranno il sovraccarico della cache, poiché CHR non verrà valutato finché la tabella della cache non avrà righe apg_subquery_cache_check_interval.
Valori più elevati di apg_subquery_cache_hit_rate_threshold tendono ad abbandonare la cache delle sottoquery e a tornare alla riesecuzione originale delle sottoquery non memorizzate nella cache.

È 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 in 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’attivazione di apg_enable_subquery_cache, il piano di query conterrà un nodo Memoize sotto il nodo SubPlan, 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:

Sottoquery correlate IN/EXISTS/ANY/ALL
Sottoquery correlate contenenti funzioni non deterministiche.
Sottoquery correlate che fanno riferimento a colonne esterne della tabella con tipi di dati che non supportano operazioni di hashing o uguaglianza.

Avvertimento JavaScript è disabilitato o non è disponibile nel tuo browser.

Per usare la documentazione AWS, JavaScript deve essere abilitato. Consulta le pagine della guida del browser per le istruzioni.

Convenzioni dei documenti

Prestazioni delle query migliorate con Letture ottimizzate per Aurora

Miglioramento delle prestazioni delle query utilizzando join adattivo