管理 Aurora PostgreSQL 的查询执行计划 - Amazon Aurora
启用查询计划管理升级查询计划管理基础知识
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅中国的 AWS 服务入门。

管理 Aurora PostgreSQL 的查询执行计划

使用 与 PostgreSQL 兼容的 Amazon Aurora 的查询计划管理功能，您可以控制更改查询执行计划的方式和时间。查询计划管理有两个主要目标：

  • 防止在数据库系统更改时计划性能下降

  • 控制查询优化程序什么时候使用新计划

对所有关系数据库管理系统 (RDBMS) 而言，查询优化的质量和一致性对其性能和稳定性都有巨大的影响。查询优化程序可为 SQL 语句在特定时间点创建查询执行计划。随着条件的变化，优化程序可能会选择其他使得性能下降的计划。很多更改可能都会致使查询优化程序选择不同计划，并导致性能下降。这些更改包括统计数据、约束、环境设置、查询参数绑定和软件升级的更改。对于高性能应用程序而言，性能下降是一个重点关注的事项。

通过查询计划管理，您可以控制所要管理的一组语句的执行计划。您可执行以下操作：

  • 通过强制优化程序从少量的已知良好计划中选择，提高计划的稳定性。

  • 集中优化计划，然后在全局范围内分发最佳计划。

  • 确定未使用的索引，并评估创建或删除索引的影响。

  • 自动检测优化程序发现的最低成本的新计划。

  • 尝试使用风险较小的新优化程序功能，因为您可以选择仅批准将提高性能的计划更改。

主题

启用 Aurora PostgreSQL 的查询计划管理

查询计划管理在 Amazon Aurora PostgreSQL 版本 2.1.0（与 PostgreSQL 10.5 兼容）及更高版本中提供。

启用查询计划管理

  1. 通过以下网址打开 Amazon RDS 控制台：https://console.amazonaws.cn/rds/。

  2. 创建新的实例级参数组，供查询计划管理参数使用。有关更多信息，请参阅 创建数据库参数组。将新参数组与要在其中使用查询计划管理的数据库实例关联。有关更多信息，请参阅 修改数据库集群中的数据库实例。

  3. 创建新的集群级参数组，供查询计划管理参数使用。有关更多信息，请参阅 创建数据库集群参数组。将新的集群级别参数组与要在其中使用查询计划管理的数据库集群相关联。有关更多信息，请参阅 使用控制台、CLI 和 API 修改数据库集群。

  4. 打开您的集群级参数组，并将 rds.enable_plan_management 参数设置为 1。有关更多信息，请参阅修改数据库集群参数组中的参数。

  5. 重启数据库实例以启用此新设置。

  6. 使用 SQL 客户端（例如 psql）连接到数据库实例。

  7. 为数据库实例创建 apg_plan_mgmt 扩展。下面是一个示例。 

    psql my-database
my-database=> CREATE EXTENSION apg_plan_mgmt;

    如果您在 template1 默认数据库中创建 apg_plan_mgmt 扩展，则查询计划管理扩展在您创建的各个新数据库中可用。

    注意

    要创建 apg_plan_mgmt 扩展，您需要 rds_superuser 角色。创建 apg_plan_mgmt 扩展时，将创建 apg_plan_mgmt 角色。用户必须已授予了 apg_plan_mgmt 角色才能管理 apg_plan_mgmt 扩展。

升级查询计划管理

查询计划管理的最新版本是 2.0。如果您安装了以前版本的查询计划管理，我们强烈建议您升级到版本 2.0。有关版本的详细信息，请参阅 Amazon Aurora PostgreSQL 的扩展和模块。

要升级，请在集群或数据库实例级运行以下命令。

ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.0';
SELECT apg_plan_mgmt.validate_plans('update_plan_hash');
SELECT apg_plan_mgmt.reload();

查询计划管理基础知识

您可以使用查询计划管理来管理任何 SELECT、INSERT、UPDATE 或 DELETE 语句，不论语句有多复杂。支持预编译、动态、嵌入和立即模式的 SQL 语句。可以使用所有 PostgreSQL 语句功能，包括分区表、继承、行级安全性和递归公用表表达式 (CTE)。

执行手动计划捕获

要捕获特定语句的计划，请使用手动捕获模式，如下例中所示。

/* Turn on manual capture */
SET apg_plan_mgmt.capture_plan_baselines = manual; 
EXPLAIN SELECT COUNT(*) from pg_class;       -- capture the plan baseline
SET apg_plan_mgmt.capture_plan_baselines = off;    -- turn off capture
SET apg_plan_mgmt.use_plan_baselines =     true;   -- turn on plan usage

您可以执行 SELECT、INSERT、UPDATE 或 DELETE 语句，也可以包括 EXPLAIN 语句，如上所示。使用 EXPLAIN 捕获计划，同时避免了执行语句带来的开销或潜在的副作用。有关手动捕获的更多信息，请参阅手动捕获特定 SQL 语句的计划。

查看捕获的计划

当 EXPLAIN SELECT 在前一个示例中运行时，优化程序将保存计划。为此，它会将行插入 apg_plan_mgmt.dba_plans 视图并在自治事务中提交计划。如果您已授予了 apg_plan_mgmt 角色，则可以查看 apg_plan_mgmt.dba_plans 视图的内容。以下查询显示 dba_plans 视图的一些重要列。 

SELECT sql_hash, plan_hash, status, enabled, plan_outline, sql_text::varchar(40)
FROM apg_plan_mgmt.dba_plans
ORDER BY sql_text, plan_created;

显示的每一行表示一个托管计划。上一个示例显示以下信息。

  • sql_hash – 计划所针对的托管语句的 ID。

  • plan_hash – 托管计划的 ID。

  • status – 计划的状态。优化程序可以运行已批准的计划。

  • enabled – 该值指示计划已启用可供使用，还是已禁用并且不可供使用。

  • plan_outline – 托管计划的详细信息。

有关 apg_plan_mgmt.dba_plans 视图的更多信息，请参阅在 apg_plan_mgmt.dba_plans 视图中检查计划。

使用托管语句和 SQL 哈希

托管语句 是优化程序在查询计划管理下捕获的 SQL 语句。您可以指定要使用手动或自动捕获，将哪些 SQL 语句作为托管语句进行捕获：

  • 对于手动捕获，您向优化程序提供特定语句，如上例中所示。

  • 对于自动捕获，优化程序捕获多次运行的语句的计划。自动捕获在下面的示例中显示。

在 apg_plan_mgmt.dba_plans 视图中，您可以通过 SQL 哈希值来确定托管语句。SQL 哈希在 SQL 语句的标准化表示形式上计算，消除了一些差异（例如文本值）。使用标准化意味着，当多个 SQL 语句只有文本值或参数值不同时，它们在 apg_plan_mgmt.dba_plans 视图中使用相同的 SQL 哈希来表示。因此，同一个 SQL 哈希可以有多个计划，每个计划在不同的条件下是最优的。

优化程序处理任何 SQL 语句时，它使用以下规则来创建标准化 SQL 语句：

  • 删除任何前导块注释

  • 删除任何 EXPLAIN 关键字和 EXPLAIN 选项（如果有）

  • 删除尾随空格

  • 删除所有文本

  • 保留空格和大小写以便阅读

例如，使用以下语句。

/*Leading comment*/ EXPLAIN SELECT /* Query 1 */ * FROM t WHERE x > 7 AND y = 1;

优化程序按以下所示标准化此语句。

SELECT /* Query 1 */ * FROM t WHERE x > CONST AND y = CONST;

使用自动计划捕获

如果您希望捕获应用程序中所有 SQL 语句的计划，或者如果您无法使用手动捕获，则可以使用自动计划捕获。通过自动计划捕获，默认情况下优化程序捕获至少运行两次的语句的计划。执行以下操作来使用自动计划捕获。

  1. 通过在参数组中为数据库实例将 apg_plan_mgmt.capture_plan_baselines 设置为 automatic 来启用自动计划捕获。有关更多信息，请参阅修改数据库参数组中的参数。

  2. 重新启动您的数据库实例。

在您的应用程序运行时，优化程序捕获多次运行的任何语句的计划。优化程序始终将托管语句的首次捕获计划的状态设置为 approved。托管语句的一组已批准计划称为计划基线。

随着应用程序持续运行，优化程序可能会查找托管语句的其他计划。优化程序将其他捕获的计划的状态设置为 Unapproved。

某个托管语句的所有已捕获计划集称为计划历史记录。稍后，您可以确定 Unapproved 计划的执行是否良好，并使用 apg_plan_mgmt.evolve_plan_baselines 函数或 apg_plan_mgmt.set_plan_status 函数将其更改为 Approved、Rejected 或 Preferred。

要启用自动计划捕获，在数据库实例的参数组中将 apg_plan_mgmt.capture_plan_baselines 设置为 off。然后重启数据库让设置生效。

有关计划捕获的更多信息，请参阅捕获执行计划。

验证计划

在删除了所依赖的对象（例如索引）时，托管计划会变得无效（“过时”）。要查找和删除所有已过时的计划，请使用 apg_plan_mgmt.validate_plans 函数。 

SELECT apg_plan_mgmt.validate_plans('delete');

有关更多信息，请参阅验证计划。

批准可以改进性能的新计划

在使用托管计划时，您可以验证优化程序发现的较新、较低成本的计划，相比计划基线中已有的最低成本计划是否速度更快。要执行性能比较并（可选）批准更快的计划，请调用 apg_plan_mgmt.evolve_plan_baselines 函数。

在以下示例中，对于任何已启用的未批准计划，如果其速度比计划基准中的最低成本计划至少快 10%，则自动批准该计划。

SELECT apg_plan_mgmt.evolve_plan_baselines(
   sql_hash,
   plan_hash, 
   1.1,
   'approve'
)
FROM apg_plan_mgmt.dba_plans 
WHERE status = 'Unapproved' AND enabled = true;

当 apg_plan_mgmt.evolve_plan_baselines 函数运行时，它会收集性能统计数据并将其保存到 apg_plan_mgmt.dba_plans 视图的 planning_time_ms、execution_time_ms、cardinality_error、total_time_benefit_ms 和 execution_time_benefit_ms 列中。apg_plan_mgmt.evolve_plan_baselines 函数还会更新 last_verified 或 last_validated timestamps 列，在其中您可以看到收集性能统计数据的最近时间。 

SELECT sql_hash, plan_hash, status, last_verified, sql_text::varchar(40)
FROM apg_plan_mgmt.dba_plans
ORDER BY last_verified DESC;  -- value updated by evolve_plan_baselines()

有关验证计划的更多信息，请参阅评估计划性能。

删除计划

如果在计划保留期内，计划未执行或者未选择作为最低成本计划，则优化程序会删除这些计划。默认情况下，计划保留期为 32 天。要更改计划保留期，请设置 apg_plan_mgmt.plan_retention_period 参数。

您还可以查看 apg_plan_mgmt.dba_plans 视图的内容，并使用 apg_plan_mgmt.delete_plan 函数删除任何不再需要的计划。有关更多信息，请参阅删除计划。