Amazon Aurora
Aurora 用户指南
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

维护执行计划

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

评估计划性能

在优化程序将计划作为未批准计划捕获之后,使用 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) 要好。要确认新计划现在已批准,请查看 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 函数。此示例禁用任何已捕获并且其速度比语句的最佳已批准计划慢至少 10% 的未批准计划。

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

您也可以直接将计划设置为已拒绝或已禁用。要直接将计划的已启用字段设置为 truefalse,请调用 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_limitGEQO_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。这样做可以确保优化程序将选择运行它,而不是在最低成本计划未处于 approvedpreferred 状态时从一组已批准计划中选择。

    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 语句运行时,优化程序将选择 approvedpreferred 计划。如果最低成本计划不是 approvedpreferred,则优化程序将选择 preferred 计划。

删除计划

删除长时间未使用或者不再相关的计划。每个计划具有 last_used 日期,优化程序在每次执行某个计划或选取计划作为语句的最低成本计划时,会更新该日期。使用 last_used 日期确定计划最近是否使用过并且仍旧相关。

例如,您可以使用 apg_plan_mgmt.delete_plan 函数,如下所示。这样做将删除所有未选择作为最低成本计划或者至少 31 天未运行的计划。但是,此实例不删除已经明确拒绝的计划。

SELECT SUM(apg_plan_mgmt.delete_plan(sql_hash, plan_hash)) FROM apg_plan_mgmt.dba_plans WHERE last_used < (current_date - interval '31 days') AND status <> 'rejected';

要删除任何不再有效并且您预期不会再有效的计划,请使用 apg_plan_mgmt.validate_plans 函数。有关更多信息,请参阅 验证计划

您可以为删除计划实施自己的策略。在当前日期 last_used 大于 apg_plan_mgmt.plan_retention_period 参数的值(默认值为 32 天)时,自动删除计划。您可以指定更长的间隔,或通过直接调用 delete_plan 函数实施自己的计划保留策略。

重要

如果您未清除计划,则最终可能会耗尽为查询计划管理设置的共享内存。要控制可供托管计划使用的内存量,请使用 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, estimated_rows = EXCLUDED.estimated_rows, actual_rows = EXCLUDED.actual_rows, 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;