Amélioration des performances des requêtes Aurora PostgreSQL à l'aide de la transformation des sous-requêtes Utilisation du cache de sous-requêtes pour améliorer les performances des requêtes Aurora PostgreSQL

Optimisation des sous-requêtes corrélées dans Aurora PostgreSQL

Une sous-requête corrélée référence les colonnes de la table à partir de la requête externe. Il est évalué une fois pour chaque ligne renvoyée par la requête externe. Dans l'exemple suivant, la sous-requête fait référence à une colonne de la table ot. Cette table n'est pas incluse dans la clause FROM de la sous-requête, mais elle est référencée dans la clause FROM de la requête externe. Si le tableau contient 1 million de lignes, la sous-requête doit être évaluée 1 million de fois.


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

Note

La transformation des sous-requêtes et le cache de sous-requêtes sont disponibles dans Aurora PostgreSQL à partir de la version 16.8, tandis que Babelfish pour Aurora PostgreSQL prend en charge ces fonctionnalités à partir de la version 4.2.0.

Amélioration des performances des requêtes Aurora PostgreSQL à l'aide de la transformation des sous-requêtes

Aurora PostgreSQL peut accélérer les sous-requêtes corrélées en les transformant en jointures externes équivalentes. Cette optimisation s'applique aux deux types de sous-requêtes corrélées suivants :

Sous-requêtes renvoyant une valeur agrégée unique et apparaissant dans la liste SELECT.
```
SELECT ot.a, ot.b, (SELECT AVG(it.b) FROM it WHERE it.a = ot.a) FROM ot;
```
Sous-requêtes renvoyant une valeur agrégée unique et apparaissant dans une clause WHERE.
```
SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);
```

Activation de la transformation dans la sous-requête

Pour activer la transformation de sous-requêtes corrélées en jointures externes équivalentes, définissez le apg_enable_correlated_scalar_transform paramètre sur. ON La valeur par défaut de ce paramètre est OFF.

Vous pouvez modifier le groupe de paramètres du cluster ou de l'instance pour définir les paramètres. Pour en savoir plus, veuillez consulter la section Groupes de paramètres pour Amazon Aurora ().

Vous pouvez également configurer le paramètre uniquement pour la session en cours à l'aide de la commande suivante :


SET apg_enable_correlated_scalar_transform TO ON;

Vérification de la transformation

Utilisez la commande EXPLAIN pour vérifier si la sous-requête corrélée a été transformée en jointure externe dans le plan de requête.

Lorsque la transformation est activée, la partie de sous-requête corrélée applicable est transformée en jointure externe. Par exemple :


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 même requête n'est pas transformée lorsque le paramètre GUC est activéOFF. Le plan n'aura pas de jointure externe mais un sous-plan à la place.


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)

Limites

La sous-requête doit figurer dans la liste SELECT ou dans l'une des conditions de la clause where. Sinon, il ne sera pas transformé.
La sous-requête doit renvoyer une fonction d'agrégation. Les fonctions d'agrégation définies par l'utilisateur ne sont pas prises en charge pour la transformation.
Une sous-requête dont l'expression de retour n'est pas une simple fonction d'agrégation ne sera pas transformée.
La condition corrélée dans les clauses WHERE de la sous-requête doit être une simple référence de colonne. Sinon, il ne sera pas transformé.
La condition corrélée dans la sous-requête où les clauses doivent être un simple prédicat d'égalité.
La sous-requête ne peut pas contenir de clause HAVING ou de clause GROUP BY.
La clause where de la sous-requête peut contenir un ou plusieurs prédicats combinés à AND.

Note

L'impact de la transformation sur les performances varie en fonction de votre schéma, de vos données et de votre charge de travail. L'exécution de sous-requêtes corrélées avec la transformation peut améliorer considérablement les performances à mesure que le nombre de lignes produites par la requête externe augmente. Nous vous recommandons vivement de tester cette fonctionnalité dans un environnement hors production avec votre schéma, vos données et votre charge de travail réels avant de l'activer dans un environnement de production.

Utilisation du cache de sous-requêtes pour améliorer les performances des requêtes Aurora PostgreSQL

Aurora PostgreSQL prend en charge le cache de sous-requêtes pour stocker les résultats des sous-requêtes corrélées. Cette fonctionnalité ignore les exécutions répétées de sous-requêtes corrélées lorsque les résultats des sous-requêtes se trouvent déjà dans le cache.

Comprendre le cache des sous-requêtes

Le nœud Memoize de PostgreSQL est l'élément clé du cache de sous-requêtes. Le nœud Memoize gère une table de hachage dans le cache local pour mapper les valeurs des paramètres d'entrée aux lignes de résultats des requêtes. La limite de mémoire pour la table de hachage est le produit de work_mem et hash_mem_multiplier. Pour en savoir plus, consultez la section Consommation de ressources.

Lors de l'exécution des requêtes, le cache de sous-requêtes utilise le taux de réussite du cache (CHR) pour estimer si le cache améliore les performances des requêtes et pour décider, au moment de l'exécution de la requête, s'il convient de continuer à utiliser le cache. CHR est le rapport entre le nombre d'accès au cache et le nombre total de demandes. Par exemple, si une sous-requête corrélée doit être exécutée 100 fois et que 70 de ces résultats d'exécution peuvent être extraits du cache, le CHR est de 0,7.

Pour chaque nombre de mises en cache manquantes dans apg_subquery_cache_interval, l'avantage du cache de sous-requêtes est évalué en vérifiant si le CHR est supérieur à apg_subquery_cache_hit_rate_threshold. Dans le cas contraire, le cache sera supprimé de la mémoire et l'exécution de la requête reviendra à la réexécution initiale de la sous-requête non mise en cache.

Paramètres qui contrôlent le comportement du cache des sous-requêtes

Le tableau suivant répertorie les paramètres qui contrôlent le comportement du cache de sous-requêtes.

Paramètre	Description	Par défaut	Autorisé
apg_enable_sous-query_cache	Permet d'utiliser le cache pour les sous-requêtes scalaires corrélées.	OFF	ON, OFF
apg_subquery_cache_check_interval	Définit la fréquence, en nombre d'erreurs de cache, pour évaluer le taux de réussite du cache des sous-requêtes.	500	0–2147483647
apg_subquery_cache_hit_rate_threshold	Définit le seuil du taux de réussite du cache des sous-requêtes.	0.3	0,0—1,0

Note

Des valeurs plus élevées de apg_subquery_cache_check_interval peuvent améliorer la précision de l'estimation des avantages du cache basée sur le CHR, mais cela augmentera la surcharge du cache, car le CHR ne sera pas évalué tant que la table de cache ne contiendra pas de apg_subquery_cache_check_interval lignes.
Des valeurs plus élevées indiquent un apg_subquery_cache_hit_rate_threshold biais en faveur de l'abandon du cache de sous-requêtes et du retour à la réexécution des sous-requêtes d'origine, non mise en cache.

Vous pouvez également configurer le paramètre uniquement pour la session en cours à l'aide de la commande suivante :


SET apg_enable_subquery_cache TO ON;

Activation du cache de sous-requêtes dans Aurora PostgreSQL

Lorsque le cache de sous-requêtes est activé, Aurora PostgreSQL applique le cache pour enregistrer les résultats des sous-requêtes. Le plan de requête comportera alors un nœud Memoize en dessous. SubPlan

Par exemple, la séquence de commandes suivante montre le plan d'exécution estimé d'une sous-requête corrélée simple sans cache de sous-requêtes.


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)

Une fois activéapg_enable_subquery_cache, le plan de requête contiendra un nœud Memoize sous le SubPlan nœud, indiquant que la sous-requête prévoit d'utiliser le 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)

Le plan d'exécution des requêtes proprement dit contient plus de détails sur le cache des sous-requêtes, y compris les accès au cache et les erreurs de cache. La sortie suivante montre le plan d'exécution réel de la requête d'exemple ci-dessus après l'insertion de certaines valeurs dans les tables.


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

Le nombre total d'accès au cache est de 4, et le nombre total d'échecs du cache est de 6. Si le nombre total de succès et d'échecs est inférieur au nombre de boucles dans le nœud Memoize, cela signifie que l'évaluation CHR n'a pas été réussie et que le cache a été nettoyé et abandonné à un moment donné. L'exécution de la sous-requête est ensuite revenue à la réexécution initiale non mise en cache.

Limites

Le cache de sous-requêtes ne prend pas en charge certains modèles de sous-requêtes corrélées. Ces types de requêtes seront exécutés sans cache, même si le cache de sous-requêtes est activé :

IN/EXISTS/ANY/ALLsous-requêtes corrélées
Sous-requêtes corrélées contenant des fonctions non déterministes.
Sous-requêtes corrélées qui font référence à des colonnes de table externes avec des types de données qui ne prennent pas en charge les opérations de hachage ou d'égalité.

Avertissement JavaScript est désactivé ou n'est pas disponible dans votre navigateur.

Pour que vous puissiez utiliser la documentation AWS, Javascript doit être activé. Vous trouverez des instructions sur les pages d'aide de votre navigateur.

Conventions de rédaction

Amélioration des performances des requêtes avec Aurora Optimized Reads

Améliorer les performances des requêtes grâce à la jointure adaptative