改进 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
函数。此函数可让您删除或禁用无效计划。有关更多信息,请参阅 验证计划。
使用 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 捕获计划
-
启用手动捕获模式。
SET apg_plan_mgmt.capture_plan_baselines = manual;
-
指定感兴趣 SQL 语句的提示。
/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
在此语句运行后,优化程序在
apg_plan_mgmt.dba_plans
视图中捕获计划。捕获的计划不包括特殊pg_hint_plan
注释语法,因为查询计划管理通过删除前导注释来标准化语句。 -
使用
apg_plan_mgmt.dba_plans
视图查看托管计划。SELECT sql_hash, plan_hash, status, sql_text, plan_outline FROM apg_plan_mgmt.dba_plans;
将计划的状态设置为
Preferred
。这样做可以确保优化程序将选择运行它,而不是在最低成本计划未处于Approved
或Preferred
状态时从一组已批准计划中选择。SELECT apg_plan_mgmt.set_plan_status(
sql-hash
,plan-hash
, 'preferred' );-
禁用手动计划捕获并强制托管计划的使用。
SET apg_plan_mgmt.capture_plan_baselines = false; SET apg_plan_mgmt.use_plan_baselines = true;
现在,当原始 SQL 语句运行时,优化程序将选择
Approved
或Preferred
计划。如果最低成本计划不是Approved
或Preferred
,则优化程序将选择Preferred
计划。