支持的 SQL 语句限制术语可用版本开启查询计划管理升级查询计划管理关闭 Aurora PostgreSQL 查询计划管理

Aurora PostgreSQL 查询计划管理概览

Aurora PostgreSQL 查询计划管理旨在确保计划的稳定性，而无论对数据库的更改是否可能导致查询计划回归。当优化程序在系统或数据库更改后为给定的 SQL 语句选择次优计划时，就会发生查询计划回归。更改统计数据、约束、环境设置、查询参数绑定以及升级 PostgreSQL 数据库引擎都可能导致计划回归。

使用 Aurora PostgreSQL 查询计划管理，您可以控制更改查询执行计划的方式和时间。Aurora PostgreSQL 查询计划管理的优势包括以下几点。

通过强制优化程序从少量的已知良好计划中选择，提高计划的稳定性。
集中优化计划，然后在全局范围内分发最佳计划。
确定未使用的索引，并评估创建或删除索引的影响。
自动检测优化程序发现的最低成本的新计划。
尝试使用风险较小的新优化程序功能，因为您可以选择仅批准将提高性能的计划更改。

您可以主动使用查询计划管理提供的工具，为某些查询指定最佳计划。或者，您可以使用查询计划管理来应对不断变化的情况，并避免计划回归。有关更多信息，请参阅 Aurora PostgreSQL 查询计划管理的最佳实践。

支持的 SQL 语句

查询计划管理支持以下类型的 SQL 语句。

任何 SELECT、INSERT、UPDATE 或 DELETE 语句，而不考虑复杂性。
已准备好语句。有关更多信息，请参阅 PostgreSQL 文档中的 PREPARE。
动态语句，包括那些在即时模式下运行的语句。有关更多信息，请参阅 PostgreSQL 文档中的动态 SQL 和 EXECUTE IMMEDIATE。
嵌入式 SQL 命令和语句。有关更多信息，请参阅 PostgreSQL 文档中的嵌入式 SQL 命令。
命名函数内的语句。有关更多信息，请参阅 PostgreSQL 文档中的 CREATE FUNCTION。
包含临时表的语句。
过程和 DO 块内的语句。

您可以在手动模式下将查询计划管理与 EXPLAIN 结合使用来捕获计划，而无需实际运行它。有关更多信息，请参阅分析优化程序的已选择计划。要了解有关查询计划管理的模式（手动、自动）的更多信息，请参阅捕获 Aurora PostgreSQL 执行计划。

Aurora PostgreSQL 查询计划管理支持所有 PostgreSQL 语言功能，包括分区表、继承、行级安全性和递归公用表表达式（CTE）。要了解有关这些 PostgreSQL 语言功能的更多信息，请参阅 PostgreSQL 文档中的表分区、行安全策略和 WITH 查询（公用表表达式）以及其他主题。

有关 Aurora PostgreSQL 查询计划管理功能的不同版本的信息，请参阅《Aurora PostgreSQL 版本注释》中的 Aurora PostgreSQL apg_plan_mgmt 扩展版本。

查询计划管理限制

Aurora PostgreSQL 查询计划管理的当前版本存在以下限制。

对于引用系统关系的语句不捕获计划 – 不捕获引用系统关系的语句，如 pg_class。这是特意设计的，目的是防止捕获大量由系统生成的供内部使用的计划。这也适用于视图内的系统表。
您的 Aurora PostgreSQL 数据库集群可能需要更大的数据库实例类 - 根据工作负载不同，查询计划管理可能需要具有超过 2 个 vCPU 的数据库实例类。max_worker_processes 的数量受数据库实例类大小的限制。2-vCPU 数据库实例类（例如 db.t3.medium）提供的 max_worker_processes 的数量可能不足应对给定的工作负载。如果您使用查询计划管理，我们建议您为 Aurora PostgreSQL 数据库集群选择一个具有超过 2 个 vCPU 的数据库实例类。

如果数据库实例类无法支持工作负载，则查询计划管理会引发一条错误消息，如下所示。
```
WARNING: could not register plan insert background process
HINT: You may need to increase max_worker_processes.
```
在这种情况下，您应该将 Aurora PostgreSQL 数据库集群纵向扩展到具有更多内存的数据库实例类大小。有关更多信息，请参阅数据库实例类支持的数据库引擎。
已存储在会话中的计划不受影响 - 查询计划管理提供了一种在不更改应用程序代码的情况下影响查询计划的方法。但是，如果通用计划已存储在现有会话中，并且要更改其查询计划，则必须先在数据库集群参数组中将 plan_cache_mode 设置为 force_custom_plan。
在以下情况下，apg_plan_mgmt.dba_plans 和 pg_stat_statements 中的 queryid 可能分叉：
- 对象存储在 apg_plan_mgmt.dba_plans 中后会被删除并重新创建。
- apg_plan_mgmt.plans 表是从另一个集群导入的。

有关 Aurora PostgreSQL 查询计划管理功能的不同版本的信息，请参阅《Aurora PostgreSQL 版本注释》中的 Aurora PostgreSQL apg_plan_mgmt 扩展版本。

查询计划管理术语

本主题中使用了以下术语。

托管式语句: 优化程序在查询计划管理下捕获的 SQL 语句。托管式语句在 apg_plan_mgmt.dba_plans 视图中存储了一个或多个查询执行计划。
计划基准: 给定托管式语句的一组已批准计划。也就是说，托管式语句在 status 视图的 dba_plan 列中具有“已批准”状态的所有计划。
计划历史记录: 给定托管式语句的所有已捕获计划的集合。计划历史记录包含为该语句捕获的所有计划，无论其状态如何。
查询计划回归: 当优化程序选择的计划比在对数据库环境进行给定更改（例如，新的 PostgreSQL 版本或更改统计数据）之前选择的计划更不理想时，就会出现这种情况。

Aurora PostgreSQL 查询计划管理版本

所有当前可用的 Aurora PostgreSQL 版本均支持查询计划管理。有关更多信息，请参阅《Aurora PostgreSQL 版本注释》中的 Amazon Aurora PostgreSQL 更新的列表。

安装 apg_plan_mgmt 扩展时，查询计划管理功能会添加到您的 Aurora PostgreSQL 数据库集群中。不同版本的 Aurora PostgreSQL 支持不同版本的 apg_plan_mgmt 扩展。我们建议您将查询计划管理扩展升级到 Aurora PostgreSQL 版本的最新发行版。

注意

有关每个 apg_plan_mgmt 扩展版本的版本注释，请参阅《Aurora PostgreSQL 版本注释》中的 Aurora PostgreSQL apg_plan_mgmt 扩展版本。

通过使用 psql 连接到实例并使用元命令 \dx 列出扩展，可以识别集群上运行的版本，如下所示。


labdb=> \dx
                       List of installed extensions
     Name      | Version |    Schema     |                            Description
---------------+---------+---------------+-------------------------------------------------------------------
 apg_plan_mgmt | 1.0     | apg_plan_mgmt | Amazon Aurora with PostgreSQL compatibility Query Plan Management
 plpgsql       | 1.0     | pg_catalog    | PL/pgSQL procedural language
(2 rows)

输出显示该集群正在使用 1.0 版本的扩展。只有某些 apg_plan_mgmt 版本适用于给定的 Aurora PostgreSQL 版本。在某些情况下，您可能需要将 Aurora PostgreSQL 数据库集群升级到新的次要版本或应用补丁，以便可以升级到最新版本的查询计划管理。输出中显示的 apg_plan_mgmt 版本 1.0 来自 Aurora PostgreSQL 版本 10.17 数据库集群，该集群没有可用的更高 apg_plan_mgmt 版本。在这种情况下，Aurora PostgreSQL 数据库集群应升级到最新版本的 PostgreSQL。

有关将 Aurora PostgreSQL 数据库集群升级到新版本 PostgreSQL 的更多信息，请参阅 Amazon Aurora PostgreSQL 更新。

要了解如何升级 apg_plan_mgmt 扩展，请参阅升级 Aurora PostgreSQL 查询计划管理。

开启 Aurora PostgreSQL 查询计划管理

为 Aurora PostgreSQL 数据库集群设置查询计划管理涉及安装扩展和更改多个数据库集群参数设置。您需要 rds_superuser 权限才能安装 apg_plan_mgmt 扩展和为 Aurora PostgreSQL 数据库集群开启此功能。

安装该扩展会创建一个新角色 apg_plan_mgmt。此角色可让数据库用户查看、管理和维护查询计划。作为具有 rds_superuser 权限的管理员，请务必根据需要将 apg_plan_mgmt 角色授予数据库用户。

只有具有 rds_superuser 角色的用户才能完成以下过程。rds_superuser 对于创建 apg_plan_mgmt 扩展及其 apg_plan_mgmt 角色是必需的。用户必须已授予了 apg_plan_mgmt 角色才能管理 apg_plan_mgmt 扩展。

为 Aurora PostgreSQL 数据库集群开启查询计划管理

以下步骤为提交到 Aurora PostgreSQL 数据库集群的所有 SQL 语句开启查询计划管理。这称为自动模式。要了解有关模式之间的差异的更多信息，请参阅捕获 Aurora PostgreSQL 执行计划。

通过以下网址打开 Amazon RDS 控制台：https://console.aws.amazon.com/rds/。
为 Aurora PostgreSQL 数据库集群创建自定义数据库集群参数组。您需要更改某些参数，才能激活查询计划管理并设置其行为。有关更多信息，请参阅在 Amazon Aurora 中创建数据库参数组。
打开自定义数据库集群参数组，并将 rds.enable_plan_management 参数设置为 1，如下图所示。

有关更多信息，请参阅在 Amazon Aurora 中修改数据库集群参数组中的参数。
创建自定义数据库参数组，用于在实例级别设置查询计划参数。有关更多信息，请参阅在 Amazon Aurora 中创建数据库集群参数组。
修改 Aurora PostgreSQL 数据库集群的写入器实例，以使用自定义数据库参数组。有关更多信息，请参阅修改数据库集群中的数据库实例。
修改 Aurora PostgreSQL 数据库集群，以使用自定义数据库集群参数组。有关更多信息，请参阅使用控制台、CLI 和 API 修改数据库集群。
重启数据库实例以启用自定义参数组设置。
使用 psql 或 pgAdmin 连接到 Aurora PostgreSQL 数据库集群的数据库实例端点。以下示例对于 rds_superuser 角色使用原定设置 postgres 账户。
```
psql --host=cluster-instance-1.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=my-db
```
为数据库实例创建 apg_plan_mgmt 扩展，如下所示。
```
labdb=> CREATE EXTENSION apg_plan_mgmt;
CREATE EXTENSION
```
提示
在应用程序的模板数据库中安装 apg_plan_mgmt 扩展。缺省设置模板数据库名为 template1。要了解更多信息，请参阅 PostgreSQL 文档中的模板数据库。
将 apg_plan_mgmt.capture_plan_baselines 参数更改为 automatic。此设置使优化程序为已计划或执行两次或更多次的每个 SQL 语句生成计划。

注意
查询计划管理还具有手动模式，可用于特定的 SQL 语句。要了解更多信息，请参阅捕获 Aurora PostgreSQL 执行计划。
将 apg_plan_mgmt.use_plan_baselines 参数的值更改为“on”。该参数使优化程序可以从其计划基准中为该语句选择计划。要了解更多信息，请参阅使用 Aurora PostgreSQL 托管式计划。

注意
您可以修改会话的任一动态参数的值，且无需重启实例。

完成查询计划管理的设置后，请务必将 apg_plan_mgmt 角色授予需要查看、管理或维护查询计划的任何数据库用户。

升级 Aurora PostgreSQL 查询计划管理

我们建议您将查询计划管理扩展升级到 Aurora PostgreSQL 版本的最新发行版。

以具有 rds_superuser 权限的用户身份连接到 Aurora PostgreSQL 数据库集群的写入器实例。如果您在设置实例时保留原定设置名称，则以 postgres 身份进行连接。此示例说明如何使用 psql，但如果您愿意，也可以使用 pgAdmin。
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```

运行以下查询以升级扩展。


ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.1';

使用 apg_plan_mgmt.validate_plans 函数更新所有计划的哈希。优化程序会验证所有已批准、未批准和已拒绝的计划，以确保它们仍然是新版本扩展的可行计划。
```
SELECT apg_plan_mgmt.validate_plans('update_plan_hash');
```
要了解有关使用此函数的更多信息，请参阅验证计划。
使用 apg_plan_mgmt.reload 函数，通过 dba_plans 视图中经过验证的计划刷新共享内存中的任何计划。
```
SELECT apg_plan_mgmt.reload();
```

要了解有关可用于查询计划管理的所有函数的更多信息，请参阅 Aurora PostgreSQL 查询计划管理的函数参考。

关闭 Aurora PostgreSQL 查询计划管理

您可以随时通过关闭 apg_plan_mgmt.use_plan_baselines 和 apg_plan_mgmt.capture_plan_baselines 来禁用查询计划管理。


labdb=> SET apg_plan_mgmt.use_plan_baselines = off;

labdb=> SET apg_plan_mgmt.capture_plan_baselines = off;

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

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

文档惯例

管理 Aurora PostgreSQL 的查询执行计划

Aurora PostgreSQL 查询计划管理的最佳实践