Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

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 dalla tabella ot. Questa tabella non è inclusa nella clausola FROM della subquery, ma è referenziata 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 PostgreSQL di Aurora utilizzando la trasformazione delle subquery

Abilitazione della trasformazione nella sottoquery

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

È possibile modificare il cluster o il gruppo di parametri dell'istanza per impostare i parametri. Per ulteriori informazioni, consulta .

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

Utilizzate 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 subquery 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 viene ruotato il parametro GUC. 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 subquery per migliorare le prestazioni delle query PostgreSQL di Aurora

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

Comprendere la cache delle subquery

Il nodo Memoize di PostgreSQL è la parte fondamentale della cache delle subquery. 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 subquery utilizza il Cache Hit Rate (CHR) 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 accessi alla 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 errori nella cache di apg_subquery_cache_check_interval, il vantaggio della cache delle subquery 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 subquery

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

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

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 subquery in Aurora PostgreSQL

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

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

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 subquery 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 gli accessi alla cache e gli errori nella cache. L'output seguente mostra l'effettivo piano di esecuzione delle query 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 accessi alla cache è 4 e il numero totale di accessi mancati alla cache è 6. Se il numero totale di accessi e mancati è 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 subquery non supporta determinati modelli di sottoquery correlate. Questi tipi di query verranno eseguiti senza cache, anche se la cache delle subquery è attivata: