改进 Aurora PostgreSQL 查询计划 - Amazon Aurora
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

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

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

使用 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 计划。