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

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

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

Limitations

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.

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

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 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é :

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

Aurora PostgreSQL peut accélérer les sous-requêtes agrégées corrélées en les transformant en fonctions de fenêtre équivalentes. Cette optimisation s'applique aux sous-requêtes scalaires corrélées dans une clause WHERE qui comparent une expression externe à un agrégat, comme le modèle de requête suivant :

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 transformation est une réécriture au niveau du plan qui modifie uniquement le plan d'exécution, et non les résultats de la requête.

Quand utiliser la transformation de fenêtre de sous-requête

Envisagez d'activer cette transformation si vous avez des requêtes qui correspondent au modèle d'autocorrélation : une ligne extérieure comparée à un agrégat calculé sur la même table avec une condition de corrélation.

Cette transformation est complémentaire de la transformation de sous-requête corrélée existante, qui réécrit les sous-requêtes corrélées sous forme de jointures externes. La transformation de fenêtre de sous-requête les réécrit plutôt en tant que fonctions de fenêtre. Les deux transformations recouvrent des modèles différents :

Vous pouvez activer les deux transformations en même temps. Lorsqu'une requête répond aux deux critères, la transformation de la fenêtre de sous-requête est appliquée ; lorsqu'elle répond aux critères d'une seule requête, cette transformation est appliquée.

Activation de la transformation de la fenêtre de sous-requête

Pour activer la transformation de sous-requêtes agrégées corrélées en fonctions de fenêtre, définissez le apg_enable_subquery_to_window_transform paramètre 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_subquery_to_window_transform TO ON;

Pour désactiver la transformation, définissez le paramètre de manière à OFF utiliser les mêmes méthodes. Les modifications prennent effet sans redémarrage de l'instance ; le paramètre SET au niveau de la session est immédiat et les modifications apportées aux groupes de paramètres s'appliquent aux nouvelles connexions.

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 fonction de fenêtre dans le plan de requête.

Lorsque la transformation est activée, le plan de requête contient un WindowAgg nœud au lieu d'un SubPlan nœud. Par exemple :

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 même requête n’est pas transformée lorsque le paramètre GUC est activé (OFF). Le plan n'aura pas de WindowAgg nœud mais un à la SubPlan place.

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

Limitations

Si une requête ne répond pas à ces conditions, elle s'exécute selon son plan d'origine ; aucune erreur n'est générée.