维护 Aurora PostgreSQL 执行计划 - Amazon Aurora
评估计划性能验证计划使用 pg_hint_plan 修复计划删除计划导出和导入计划
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅 中国的 Amazon Web Services 服务入门 (PDF)。

维护 Aurora PostgreSQL 执行计划

查询计划管理提供技术和函数，用于添加、维护和改进执行计划。

评估计划性能

在优化程序将计划作为未批准计划捕获之后，使用 apg_plan_mgmt.evolve_plan_baselines 函数根据其实际性能来比较计划。根据性能试验的结果，您可以将计划的状态从未批准更改为已批准或已拒绝。如果计划不满足您的要求，您可以改为考虑使用 apg_plan_mgmt.evolve_plan_baselines 函数来临时禁用计划。

批准更好的计划

以下示例演示如何使用 apg_plan_mgmt.evolve_plan_baselines 函数将托管计划的状态更改为已批准。

SELECT apg_plan_mgmt.evolve_plan_baselines (
   sql_hash, 
   plan_hash, 
   min_speedup_factor := 1.0, 
   action := 'approve'
) 
FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved';
NOTICE:     rangequery (1,10000)
NOTICE:     Baseline   [ Planning time 0.761 ms, Execution time 13.261 ms]
NOTICE:     Baseline+1 [ Planning time 0.204 ms, Execution time 8.956 ms]
NOTICE:     Total time benefit: 4.862 ms, Execution time benefit: 4.305 ms
NOTICE:     Unapproved -> Approved
evolve_plan_baselines 
-----------------------
0
(1 row)

输出显示 rangequery 语句的性能报告，参数绑定 1 和 10,000。新的未批准计划 (Baseline+1) 比以前的已批准最佳计划 (Baseline) 要好。要确认新计划现在已 Approved，请查看 apg_plan_mgmt.dba_plans 视图。

SELECT sql_hash, plan_hash, status, enabled, stmt_name 
FROM apg_plan_mgmt.dba_plans;
sql_hash  | plan_hash |  status  | enabled | stmt_name  
------------+-----------+----------+---------+------------
1984047223 | 512153379 | Approved | t       | rangequery
1984047223 | 512284451 | Approved | t       | rangequery
(2 rows)

托管计划现在包含两个已批准计划，这些计划是语句的计划基线。您还可以直接调用 apg_plan_mgmt.set_plan_status 函数，以直接将计划的状态字段设置为 'Approved'、'Rejected'、'Unapproved' 或 'Preferred'。

拒绝或禁用速度较慢的计划

要拒绝或禁用计划，请将 'reject' 或 'disable' 作为操作参数传递给 apg_plan_mgmt.evolve_plan_baselines 函数。此示例禁用任何已捕获并且其速度比语句的最佳 Unapproved 计划慢至少 10% 的 Approved 计划。

SELECT apg_plan_mgmt.evolve_plan_baselines(
sql_hash,  -- The managed statement ID
plan_hash, -- The plan ID
1.1,       -- number of times faster the plan must be 
'disable'  -- The action to take. This sets the enabled field to false.
)
FROM apg_plan_mgmt.dba_plans
WHERE status = 'Unapproved' AND   -- plan is Unapproved
origin = 'Automatic';       -- plan was auto-captured

您也可以直接将计划设置为已拒绝或已禁用。要直接将计划的已启用字段设置为 true 或 false，请调用 apg_plan_mgmt.set_plan_enabled 函数。要直接将计划的状态字段设置为 'Approved'、'Rejected'、'Unapproved' 或 'Preferred'，请调用 apg_plan_mgmt.set_plan_status 函数。

验证计划

使用 apg_plan_mgmt.validate_plans 函数来删除或禁用无效的计划。

在删除了所依赖的对象（例如索引或表）时，托管计划会成为无效或过时。但是，如果重新创建了删除的对象，计划可能仅临时无效。如果某个无效计划以后会变成有效，建议您禁用无效的计划或者不执行任何操作，而不是删除它。

要查找并删除所有无效且在过去一周中未使用的计划，请使用 apg_plan_mgmt.validate_plans 函数，如下所示。

SELECT apg_plan_mgmt.validate_plans(sql_hash, plan_hash, 'delete') 
FROM apg_plan_mgmt.dba_plans
WHERE last_used < (current_date - interval '7 days');

要直接启用或禁用计划，请使用 apg_plan_mgmt.set_plan_enabled 函数。

使用 pg_hint_plan 修复计划

查询优化程序经过精心设计，用于查找所有语句的最优计划，大多数情况下优化程序可以找到较好的计划。但是，有时候您可能已知存在比优化程序所生成计划好得多的计划。使优化程序生成所需计划的两种建议方法包括使用 pg_hint_plan 扩展，或者在 PostgreSQL 中设置 Grand Unified Configuration (GUC) 变量：

  • pg_hint_plan 扩展 – 使用 PostgreSQL 的 pg_hint_plan 扩展指定“提示”以修改计划程序的工作方式。要安装 pg_hint_plan 扩展并了解有关如何使用该扩展的更多信息，请参阅 pg_hint_plan 文档。

  • GUC 变量 – 覆盖一个或多个成本模型参数或其他优化程序参数，例如 from_collapse_limit 或 GEQO_threshold。

在您使用这些技术之一来强制查询优化程序使用计划时，您还可以使用查询计划管理来捕获和强制使用新计划。

您可以使用 pg_hint_plan 扩展来更改联接顺序、联接方法或者 SQL 语句的访问路径。您使用具有特殊 pg_hint_plan 语法的 SQL 注释来修改优化程序如何创建计划。例如，假设问题 SQL 语句具有双向联接。

SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;

然后，假设优化程序选择联接顺序（t1、t2），而您知道联接顺序（t2、t1）速度更快。以下提示强制优化程序使用速度更快的联接顺序 (t2, t1)。包括 EXPLAIN，以使优化程序为 SQL 语句生成计划，但不运行语句。（未显示输出。）

/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;

以下步骤显示如何使用 pg_hint_plan。

修改优化程序生成的计划并使用 pg_hint_plan 捕获计划

  1. 启用手动捕获模式。

    SET apg_plan_mgmt.capture_plan_baselines = manual;

  2. 指定感兴趣 SQL 语句的提示。

    /*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;

    在此语句运行后，优化程序在 apg_plan_mgmt.dba_plans 视图中捕获计划。捕获的计划不包括特殊 pg_hint_plan 注释语法，因为查询计划管理通过删除前导注释来标准化语句。

  3. 使用 apg_plan_mgmt.dba_plans 视图查看托管计划。

    SELECT sql_hash, plan_hash, status, sql_text, plan_outline 
FROM apg_plan_mgmt.dba_plans;

  4. 将计划的状态设置为 Preferred。这样做可以确保优化程序将选择运行它，而不是在最低成本计划未处于 Approved 或 Preferred 状态时从一组已批准计划中选择。

    SELECT apg_plan_mgmt.set_plan_status(sql-hash, plan-hash, 'preferred' );

  5. 禁用手动计划捕获并强制托管计划的使用。

    SET apg_plan_mgmt.capture_plan_baselines = false;
SET apg_plan_mgmt.use_plan_baselines = true;

    现在，当原始 SQL 语句运行时，优化程序将选择 Approved 或 Preferred 计划。如果最低成本计划不是 Approved 或 Preferred，则优化程序将选择 Preferred 计划。

删除计划

如果计划已超过一个月（具体来说，为 32天）未使用，则会自动删除。这是 apg_plan_mgmt.plan_retention_period 参数的原定设置。您可以将计划保留期更改为较长的时间段，也可以更改为从值 1 开始的较短时间段。通过从当前日期中减去 last_used 日期来计算自上次使用计划以来的天数。last_used 日期是优化程序选择计划作为最低成本计划或运行该计划的最近日期。计划的这一日期存储在 apg_plan_mgmt.dba_plans 视图中。

建议您删除长时间未使用或者无用的计划。每个计划都具有 last_used 日期，优化程序在每次执行某个计划或选择计划作为语句的最低成本计划时，都会更新该日期。检查上次 last_used 日期以确定可以安全删除的计划。

以下查询返回一个具有三列的表，其中包含有关计划总数、无法删除的计划数和成功删除的计划数的计数。此查询有一个嵌套查询示例，旨在说明如何使用 apg_plan_mgmt.delete_plan 函数来删除过去 31 天内未选为最低成本计划且其状态不为 Rejected 的所有计划。

SELECT (SELECT COUNT(*) from apg_plan_mgmt.dba_plans) total_plans,
       COUNT(*) FILTER (WHERE result = -1) failed_to_delete,
       COUNT(*) FILTER (WHERE result = 0) successfully_deleted
       FROM (
            SELECT apg_plan_mgmt.delete_plan(sql_hash, plan_hash) as result
            FROM apg_plan_mgmt.dba_plans
            WHERE last_used < (current_date - interval '31 days')
            AND status <> 'Rejected'
            ) as dba_plans ;   


 total_plans | failed_to_delete | successfully_deleted
-------------+------------------+----------------------
           3 |                0 |                    2

有关更多信息，请参阅apg_plan_mgmt.delete_plan。

要删除无效和您认为会保持无效的计划，请使用 apg_plan_mgmt.validate_plans 函数。此函数可让您删除或禁用无效计划。有关更多信息，请参阅验证计划。

重要

如果您未删除多余的计划，则最终可能会耗尽为查询计划管理留出的共享内存。要控制可供托管计划使用的内存量，请使用 apg_plan_mgmt.max_plans 参数。在自定义数据库参数组中设置此参数并重启数据库实例，让更改生效。有关更多信息，请参阅 apg_plan_mgmt.max_plans 参数。

导出和导入计划

您可以导出托管计划并将其导入到其他数据库实例。

导出托管计划

授权用户可以将 apg_plan_mgmt.plans 表的任意子集复制到其他表，然后使用 pg_dump 命令来保存。以下是示例。

CREATE TABLE plans_copy AS SELECT * 
FROM apg_plan_mgmt.plans [ WHERE predicates ] ;
% pg_dump --table apg_plan_mgmt.plans_copy -Ft mysourcedatabase > plans_copy.tar
DROP TABLE apg_plan_mgmt.plans_copy;
导入托管计划

  1. 将所导出的托管计划的 .tar 文件，复制到将要还原计划的系统中。

  2. 使用 pg_restore 命令将 tar 文件复制到新表。

    % pg_restore --dbname mytargetdatabase -Ft plans_copy.tar

  3. 将 plans_copy 表与 apg_plan_mgmt.plans 表合并，如以下示例所示。

    注意

    有些情况下，您可能从 apg_plan_mgmt 扩展的一个版本转储并还原到其他版本。在这些情况下，计划表中的列可能不同。如果是这样，明确命名列而不是使用 SELECT *。

    INSERT INTO apg_plan_mgmt.plans SELECT * FROM plans_copy
 ON CONFLICT ON CONSTRAINT plans_pkey
 DO UPDATE SET
 status = EXCLUDED.status,
 enabled = EXCLUDED.enabled,
 -- Save the most recent last_used date 
 --
 last_used = CASE WHEN EXCLUDED.last_used > plans.last_used 
 THEN EXCLUDED.last_used ELSE plans.last_used END, 
 -- Save statistics gathered by evolve_plan_baselines, if it ran:
 --
 estimated_startup_cost = EXCLUDED.estimated_startup_cost,
 estimated_total_cost = EXCLUDED.estimated_total_cost,
 planning_time_ms = EXCLUDED.planning_time_ms,
 execution_time_ms = EXCLUDED.execution_time_ms,
 total_time_benefit_ms = EXCLUDED.total_time_benefit_ms, 
 execution_time_benefit_ms = EXCLUDED.execution_time_benefit_ms;

  4. 将托管计划重新加载到共享内存中，删除临时计划表。

    SELECT apg_plan_mgmt.reload(); -- refresh shared memory
DROP TABLE plans_copy;