优化 Aurora PostgreSQL 中的关联子查询 - Amazon Aurora
使用子查询转换提高 Aurora PostgreSQL 查询性能使用子查询缓存提高 Aurora PostgreSQL 查询性能
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅 中国的 Amazon Web Services 服务入门 (PDF)。

优化 Aurora PostgreSQL 中的关联子查询

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

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

从版本 16.8 开始，Aurora PostgreSQL 中提供了子查询转换和子查询缓存，而适用于 Aurora PostgreSQL 的 Babelfish 从 4.2.0 开始支持这些功能。

使用子查询转换提高 Aurora PostgreSQL 查询性能

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

  • 返回单个聚合值并出现在 SELECT 列表中的子查询。

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

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

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

在子查询中启用转换

要支持将关联子查询转换为等效的外部联接，请将 apg_enable_correlated_scalar_transform 参数设置为 ON。此参数的默认值为 OFF。

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

或者，可以通过以下命令来仅为当前会话配置此设置：

SET apg_enable_correlated_scalar_transform TO ON;

验证转换

使用 EXPLAIN 命令来验证关联子查询是否已在查询计划中转换为外部联接。

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

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

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

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)

限制

  • 子查询必须位于 SELECT 列表中或 where 子句的其中一个条件中。否则，不会转换此子查询。

  • 子查询必须返回一个聚合函数。不支持用户定义的聚合函数进行转换。

  • 返回表达式不是简单聚合函数的子查询不会被转换。

  • 子查询 WHERE 子句中的关联条件应该是简单的列引用。否则，不会转换此子查询。

  • 子查询中的关联条件，其中子句必须是普通的相等谓词。

  • 子查询不能包含 HAVING 或 GROUP BY 子句。

  • 子查询中的 where 子句可能包含一个或多个使用 AND 组合的谓词。

注意

转换对性能的影响因您的架构、数据和工作负载而异。随着外部查询生成的行数的增加，将子查询执行与转换关联可以显著提高性能。我们强烈建议您先在非生产环境中使用实际架构、数据和工作负载测试此功能，然后才在生产环境中启用。

使用子查询缓存提高 Aurora PostgreSQL 查询性能

Aurora PostgreSQL 支持子查询缓存来存储关联子查询的结果。当子查询结果已经在缓存中时，此功能会跳过重复的关联子查询执行。

了解子查询缓存

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 值偏向于放弃子查询缓存，而返回到原始、未缓存子查询重新执行状态。

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

或者，可以通过以下命令来仅为当前会话配置此设置：

SET apg_enable_subquery_cache TO ON;

在 Aurora PostgreSQL 中开启子查询缓存

启用子查询缓存后，Aurora PostgreSQL 会应用缓存来保存子查询结果。然后，查询计划将在 SubPlan 下有一个 Memoize 节点。

例如，以下命令序列显示了没有子查询缓存的简单关联子查询的估计查询执行计划。

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)

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

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)

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

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

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

限制

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

  • IN/EXISTS/ANY/ALL 关联子查询

  • 包含非确定性函数的关联子查询。

  • 关联子查询，它们引用其数据类型不支持哈希或相等操作的外部表列。