Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.
Optimisation des sous-requêtes corrélées dans Babelfish
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 t1. Cette table n'est pas incluse dans la FROM clause de la sous-requête, mais elle est référencée dans la FROM clause de la requête externe. Si la table t1 comporte 1 million de lignes, la sous-requête doit être évaluée 1 million de fois.
SELECT col1, col2 FROM t1 WHERE col1 < (SELECT sum(col1) FROM t2 WHERE t1.col2 = t2.col2);
Améliorer les performances des requêtes Babelfish à l'aide de la transformation des sous-requêtes
Babelfish 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 SELECT liste. Pour plus d'informations, consultez la SELECTclause contenue
dans la SQL documentation de Microsoft Transact. SELECT ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) FROM outer_sb_t1; -
Sous-requêtes renvoyant une valeur agrégée unique et apparaissant dans une WHERE clause.
SELECT * FROM outer_sb_t1 WHERE ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) > 1.0;
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 Ce paramètre est disponible dans Babelfish 4.2.0 et versions ultérieures. 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, consultez Groupes de paramètres pour Amazon Aurora.
Vous pouvez également configurer le paramètre uniquement pour la session en cours en appelant la fonctionset_config. Par exemple, exécutez la commande suivante pour activer le cache de sous-requêtes dans Babelfish. Pour en savoir plus, consultez la section Fonctions des paramètres de configuration
1> SELECT set_config('apg_enable_subquery_cache', 'on', false) 2> GO
Vérification de la transformation
Utilisez la EXPLAIN commande pour vérifier si la sous-requête corrélée a été transformée en jointure externe dans le plan de requête. Pour de plus amples informations, veuillez consulter Utilisation du plan d'explication pour améliorer les performances des requêtes Babelfish.
Lorsque la transformation est activée, la partie de sous-requête corrélée applicable est transformée en jointure externe. Par exemple :
1>select set_config('apg_enable_correlated_scalar_transform', 'true', false);2>GO1>set BABELFISH_STATISTICS PROFILE on2>GO1>select customer_name, ( select max(o.cost) from correlated_orders o2>where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount3>from correlated_customers c order by customer_name ;4>GOQUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Text: select customer_name, ( select max(o.cost) from correlated_orders o where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10 ) AS max_order_amount from correlated_customers c order by customer_name Sort (cost=88.23..90.18 rows=780 width=40) Sort Key: c.customer_name NULLS FIRST -> Hash Left Join (cost=30.90..50.76 rows=780 width=40) Hash Cond: (c.customer_id = o.customer_id) -> Seq Scan on correlated_customers c (cost=0.00..17.80 rows=780 width=36) -> Hash (cost=28.73..28.73 rows=174 width=12) -> HashAggregate (cost=26.99..28.73 rows=174 width=12) Group Key: o.customer_id -> Seq Scan on correlated_orders o (cost=0.00..25.30 rows=338 width=12) Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10)) ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
La même requête n'est pas transformée lorsque le GUC paramètre est activéOFF. Le plan n'aura pas de jointure externe mais un sous-plan à la place.
1>select set_config('apg_enable_correlated_scalar_transform', 'false', false);2>GO1>select customer_name, ( select max(o.cost)2>from correlated_orders o3>where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount4>from correlated_customers c order by customer_name ;5>GOQUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Sort (cost=21789.97..21791.92 rows=780 width=40) Sort Key: c.customer_name NULLS FIRST -> Seq Scan on correlated_customers c (cost=0.00..21752.50 rows=780 width=40) SubPlan 1 -> Aggregate (cost=27.86..27.87 rows=1 width=8) -> Seq Scan on correlated_orders o (cost=0.00..27.85 rows=2 width=8) Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10) AND (customer_id = c.customer_id))
Limites
-
La sous-requête doit se trouver dans la select_list ou dans l'une des conditions de la clause where. Sinon, 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 simple fonction d'agrégation ne sera pas transformée.
-
La condition corrélée dans les WHERE clauses de 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é.
Une sous-requête corrélée contenant une TOP clause ne peut pas être transformée.
-
La sous-requête ne peut pas contenir de clause A HAVING ou de clause GROUP BY.
-
La clause where de la sous-requête peut contenir un ou plusieurs prédicats combinés à. AND Si la WHERE clause contient une clause OR, elle ne peut pas être transformée.
Utilisation du cache de sous-requêtes pour améliorer les performances des requêtes Babelfish
À partir de la version 4.2.0, Babelfish 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 SQL de Postgre 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
Pendant l'exécution de la requête, le cache des sous-requêtes utilise le taux de succès 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. CHRest 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, la valeur CHR est 0,7.
Pour chaque nombre de caches manqués par apg_subquery_cache_interval, les avantages du cache de sous-requêtes sont évalués en vérifiant si le cache est supérieur à apg_subquery_cache_hit_rate_threshold. CHR 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 |
ALLUMÉ, 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_intervalpeuvent améliorer la précision de l'estimation des avantages du cache CHR basée sur le cache, mais elles augmenteront la surcharge du cache, car elles CHR ne seront pas évaluées tant que la table de cache ne contiendra pas deapg_subquery_cache_check_intervallignes.Des valeurs plus élevées indiquent un
apg_subquery_cache_hit_rate_thresholdbiais 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 modifier le groupe de paramètres du cluster ou de l'instance pour définir les paramètres. Pour en savoir plus, consultez la section Utilisation des groupes de paramètres.
Vous pouvez également configurer le paramètre uniquement pour la session en cours en appelant la fonctionset_config. Par exemple, exécutez la commande suivante pour activer le cache de sous-requêtes dans Babelfish. Pour en savoir plus, consultez la section Fonctions des paramètres de configuration
1> SELECT set_config('apg_enable_subquery_cache', 'on', false) 2> GO
Activer le cache des sous-requêtes dans Babelfish
Actuellement, le cache des sous-requêtes est défini OFF par défaut. Comme indiqué ci-dessus, vous pouvez l'activer en modifiant votre groupe de paramètres. Dans apg_enable_subquery_cache ce casON, Babelfish applique le cache des sous-requêtes 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. Pour en savoir plus, consultez Utiliser le plan d'explication pour améliorer les performances des requêtes Babelfish.
1>CREATE TABLE outer_table (outer_col1 INT, outer_col2 INT)2>CREATE TABLE inner_table (inner_col1 INT, inner_col2 INT)3>GO1>EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'off'2>GO1>SET BABELFISH_SHOWPLAN_ALL ON2>GO1>SELECT outer_col1, (2>SELECT inner_col13>FROM inner_table4>WHERE inner_col2 = outer_col25>) FROM outer_table6>GOQUERY PLAN ------------------------------------------------------------ Query Text: SELECT outer_col1, ( SELECT inner_col1 FROM inner_table WHERE inner_col2 = outer_col2 ) FROM outer_table Seq Scan on outer_table SubPlan 1 -> Seq Scan on inner_table Filter: (inner_col2 = outer_table.outer_col2)
1>SET BABELFISH_SHOWPLAN_ALL OFF2>GO1>EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'on'2>GO
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.
Seq Scan on outer_table SubPlan 1 -> Memoize Cache Key: outer_table.outer_col2 Cache Mode: logical -> Seq Scan on inner_table Filter: (inner_col2 = outer_table.outer_col2)
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.
Seq Scan on outer_table (actual rows=10 loops=1) SubPlan 1 -> Memoize (actual rows=1 loops=10) Cache Key: outer_table.outer_col2 Cache Mode: logical Hits: 4 Misses: 6 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> Seq Scan on inner_table (actual rows=1 loops=6) Filter: (inner_col2 = outer_table.outer_col2) 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'CHRévaluation n'a pas réussi 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é :
-
Sous-requêtes ALL corrélées EXISTS ANY IN///
-
Sous-requêtes corrélées contenant des fonctions non déterministes.
-
Sous-requêtes corrélées qui font référence à une colonne de table externe VARBINARY ou BINARY à un type de BIT données.
