使用子查询转换提高 Babelfish 查询性能使用子查询缓存提高 Babelfish 查询性能

在 Babelfish 中优化关联子查询

关联子查询引用外部查询中的表列。对于外部查询返回的每一行，关联子查询都会被评估一次。在以下示例中，子查询引用表 t1 中的一列。此表未包含在子查询的 FROM 子句中，但在外部查询的 FROM 子句中引用了该表。如果表 t1 有 100 万行，则需要对子查询评估 100 万次。



SELECT col1, col2 FROM t1 WHERE col1 < (SELECT sum(col1) FROM t2 WHERE t1.col2 = t2.col2);

使用子查询转换提高 Babelfish 查询性能

Babelfish 可以通过将关联子查询转换为等效的外部联接来加速这些子查询。此优化适用于以下两种类型的关联子查询：

返回单个聚合值并出现在 SELECT 列表中的子查询。有关更多信息，请参阅 Microsoft Transact-SQL 文档中的 SELECT clause。
```
SELECT ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) FROM outer_sb_t1;
```

返回单个聚合值并出现在 WHERE 子句中的子查询。



SELECT * FROM outer_sb_t1 WHERE ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) > 1.0;

在子查询中启用转换

要支持将关联子查询转换为等效的外部联接，请将 apg_enable_correlated_scalar_transform 参数设置为 ON。此参数在 Babelfish 4.2.0 及更高版本中提供。此参数的默认值为 OFF。

您可以修改集群或实例参数组来设置参数。要了解更多信息，请参阅 Amazon Aurora 的参数组。

或者，可以通过调用函数 set_config 来仅为当前会话配置此设置。例如，运行以下命令来在 Babelfish 中开启子查询缓存。要了解更多信息，请参阅 Configuration Settings Functions。



1> SELECT set_config('apg_enable_subquery_cache', 'on', false)
2> GO

验证转换

使用 EXPLAIN 命令来验证关联子查询是否已在查询计划中转换为外部联接。有关更多信息，请参阅使用解释计划提高 Babelfish 查询性能。

启用转换后，适用的关联子查询部分将转换为外部联接。例如：



1> select set_config('apg_enable_correlated_scalar_transform', 'true', false);
2> GO
1> set BABELFISH_STATISTICS PROFILE on
2> GO
1> select customer_name, ( select max(o.cost) from correlated_orders o 
2> where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount
3> from correlated_customers c order by customer_name ;
4> GO

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

将 GUC 参数设置为 OFF 时，不会转换相同的查询。计划将不会有外部联接，而是有子计划。



1> select set_config('apg_enable_correlated_scalar_transform', 'false', false);
2> GO
1> select customer_name, ( select max(o.cost) 
2> from correlated_orders o 
3> where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount
4> from correlated_customers c order by customer_name ;
5> GO
QUERY 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))

限制

子查询必须位于 select_list 中或 where 子句的其中一个条件中，否则它不会转换。
子查询必须返回一个聚合函数。不支持用户定义的聚合函数进行转换。
返回表达式不是简单聚合函数的子查询不会被转换。
子查询 WHERE 子句中的关联条件应该是简单的列引用。否则，不会转换此子查询。
子查询中的关联条件，其中子句必须是普通的相等谓词。
包含 TOP 子句的关联子查询无法转换。
子查询不能包含 HAVING 或 GROUP BY 子句。
子查询中的 where 子句可能包含一个或多个使用 AND 组合的谓词。如果 WHERE 子句包含 OR 子句，则无法对子查询进行转换。

使用子查询缓存提高 Babelfish 查询性能

从版本 4.2.0 开始，Babelfish 支持子查询缓存来存储关联子查询的结果。当子查询结果已经在缓存中时，此功能会跳过重复的关联子查询执行。

了解子查询缓存

PostgreSQL 的 Memoize 节点是子查询缓存的关键部分。Memoize 节点在本地缓存中维护一个哈希表，用于从输入参数值映射到查询结果行。哈希表的内存限制是 work_mem 和 hash_mem_multiplier 的乘积。要了解更多信息，请参阅 Resource Consumption。

在查询执行期间，子查询缓存使用缓存命中率（CHR）来估计缓存是否提高了查询性能，并在查询运行时决定是否继续使用缓存。CHR 是缓存命中数与请求总数的比率。例如，如果关联子查询需要执行 100 次，并且可以从缓存中检索 70 个执行结果，则 CHR 为 0.7。

对于每个 apg_subquery_cache_check_interval 缓存未命中次数，可通过检查 CHR 是否大于 apg_subquery_cache_hit_rate_threshold 来评估子查询缓存的好处。否则，将从内存中删除缓存，查询执行将返回到原始、未缓存的子查询重新执行状态。

控制子查询缓存行为的参数

下表列出了控制子查询缓存行为的参数。

参数	描述	默认	已允许
apg_enable_subquery_cache	支持对关联标量子查询使用缓存。	关闭	ON、OFF
apg_subquery_cache_check_interval	设置评估子查询缓存命中率的频率（以缓存未命中次数为单位）。	500	0–2147483647
apg_subquery_cache_hit_rate_threshold	设置子查询缓存命中率的阈值。	0.3	0.0–1.0

注意

较大的 apg_subquery_cache_check_interval 值可能会提高基于 CHR 的缓存优势估计的准确性，但会增加缓存开销，因为在缓存表具有 apg_subquery_cache_check_interval 行之前不会对 CHR 进行评估。
较大的 apg_subquery_cache_hit_rate_threshold 值偏向于放弃子查询缓存，而返回到原始、未缓存子查询重新执行状态。

您可以修改集群或实例参数组来设置参数。要了解更多信息，请参阅使用参数组。



1> SELECT set_config('apg_enable_subquery_cache', 'on', false)
2> GO

在 Babelfish 中开启子查询缓存

当前，默认情况下子查询缓存为 OFF。如上所述，可以通过修改参数组将其开启。当 apg_enable_subquery_cache 为 ON 时，Babelfish 会应用子查询缓存来保存子查询结果。然后，查询计划将在 SubPlan 下有一个 Memoize 节点。

例如，以下命令序列显示了没有子查询缓存的简单关联子查询的估计查询执行计划。要了解更多信息，请参阅使用解释计划提高 Babelfish 查询性能。



1> CREATE TABLE outer_table (outer_col1 INT, outer_col2 INT)
2> CREATE TABLE inner_table (inner_col1 INT, inner_col2 INT)
3> GO
1> EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'off'
2> GO
1> SET BABELFISH_SHOWPLAN_ALL ON
2> GO
1> SELECT outer_col1, (
2> SELECT inner_col1
3> FROM inner_table
4> WHERE inner_col2 = outer_col2
5> ) FROM outer_table
6> GO

QUERY 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 OFF
2> GO
1> EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'on'
2> GO

开启 apg_enable_subquery_cache 后，查询计划将在 SubPlan 节点下包含一个 Memoize 节点，表示子查询正计划使用缓存。



            
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)

实际的查询执行计划包含子查询缓存的更多详细信息，包括缓存命中和缓存未命中。以下输出显示了在向表中插入一些值后，上述示例查询的实际查询执行计划。



            
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

缓存命中总数为 4，缓存未命中总数为 6。如果命中和未命中总数小于 Memoize 节点中的循环次数，则意味着 CHR 评估未通过，缓存已在某个时候被清理并放弃。然后，子查询执行返回到原始未缓存的重新执行状态。

限制

子查询缓存不支持关联子查询的某些规律。这些类型的查询将在没有缓存的情况下运行，即使子查询缓存已开启也是如此：

IN/EXISTS/ANY/ALL 关联子查询
包含非确定性函数的关联子查询。
引用 BIT、VARBINARY 或 BINARY 数据类型的外部表列的关联子查询。

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

使用 T-SQL 查询提示提高 Babelfish 查询性能

将 Aurora PostgreSQL 扩展与 Babelfish 搭配使用