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. Elle est évaluée une fois pour chaque ligne renvoyée par la requête externe. Dans l’exemple suivant, la sous-requête 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 la table ot 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.
À partir des versions 4.6.0 et 5.2.0 de Babelfish pour Aurora PostgreSQL, les paramètres suivants contrôlent ces fonctionnalités :
- babelfishpg_tsql.apg_enable_correlated_scalar_transform
- babelfishpg_tsql.apg_enable_subquery_cache
Par défaut, ces deux paramètres sont activés.

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 des sous-requêtes corrélées en jointures externes équivalentes, définissez le paramètre apg_enable_correlated_scalar_transform sur ON. La valeur par défaut de ce paramètre est OFF.

Vous pouvez modifier les paramètres du cluster ou de l’instance pour définir les paramètres. Pour en savoir plus, consultez 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. Exemples :


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)

Limitations

La sous-requête doit figurer dans la liste SELECT ou dans l’une des conditions de la clause WHERE. Dans le cas contraire, elle ne sera pas transformée.
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 fonction d’agrégation simple ne sera pas transformée.
La condition corrélée dans les clauses WHERE de la sous-requête doit être une référence de colonne simple. Dans le cas contraire, elle ne sera pas transformée.
La condition corrélée dans les clauses WHERE de la sous-requête doit être un simple prédicat d’égalité.
La sous-requête ne peut pas contenir de clause HAVING ou 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 générées 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 un 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 leurs résultats se trouvent déjà dans le cache.

Comprendre le cache de 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 de la table de hachage est le produit de work_mem et hash_mem_multiplier. Pour en savoir plus, consultez Consommation des ressources.

Lors de l’exécution des requêtes, le cache de sous-requêtes utilise le taux d’accès au cache pour estimer si le cache améliore les performances des requêtes et pour décider, au moment de l’exécution d’une requête, s’il convient de continuer à utiliser le cache. Le taux d’accès au cache est le rapport entre le nombre d’accès au cache et le nombre total de requêtes. 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 taux d’accès au cache est de 0,7.

Pour chaque perte de mémoire cache par apg_subquery_cache_interval, l’avantage du cache de sous-requêtes est évalué en vérifiant si le taux d’accès au cache est supérieur à apg_subquery_cache_hit_rate_threshold. Si ce n’est pas le cas, 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 de 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_subquery_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 de pertes de mémoire cache, pour évaluer le taux d’accès au cache des sous-requêtes.	500	0–2147483647
apg_subquery_cache_hit_rate_threshold	Définit le seuil du taux d’accès au cache de sous-requêtes.	0.3	0.0-1.0

Note

Des valeurs plus élevées pour apg_subquery_cache_check_interval peuvent améliorer la précision de l’estimation des avantages du cache basée sur le taux d’accès au cache, mais augmentent la surcharge du cache, car le taux d’accès au cache ne sera pas évalué tant que la table de cache ne contiendra pas le nombre de lignes défini pour apg_subquery_cache_check_interval.
Des valeurs plus élevées pour apg_subquery_cache_hit_rate_threshold indiquent un 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 mises en cache.

Vous pouvez modifier les paramètres du cluster ou de l’instance pour définir les paramètres. Pour en savoir plus, consultez 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_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 l’applique pour enregistrer les résultats des sous-requêtes. Dans ce cas, le plan de requête a un nœud Memoize sous SubPlan.

Par exemple, la séquence de commandes suivante montre le plan d’exécution estimé d’une sous-requête simple corrélée 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)

Après l’activation d’apg_enable_subquery_cache, le plan de requête contient un nœud Memoize sous le nœud SubPlan, 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 de sous-requêtes, y compris les accès au cache et les pertes de mémoire cache. La sortie suivante montre le plan d’exécution réel de l’exemple de requête 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 de pertes de mémoire cache est de 6. Si le nombre total d’accès au cache et de pertes de cache mémoire est inférieur au nombre de boucles dans le nœud Memoize, cela signifie que l’évaluation du taux d’accès au cache 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 renvoyée à la réexécution initiale non mise en cache.

Limitations

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 sont exécutés sans cache, même si le cache de sous-requêtes est activé :

Sous-requêtes corrélées IN/EXISTS/ANY/ALL
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élioration des performances des requêtes à l’aide d’une jointure adaptative