云原生数据仓库AnalyticDB PostgreSQL版的pg_hint_plan插件提供了Hint功能(注释语句提示)。Hint功能可以干预和调优执行计划,从而提升SQL的执行能力。
版本限制
功能介绍
AnalyticDB PostgreSQL版优化器,基于统计数据而非固定的规则,评估SQL语句各种可行的执行算子的代价并选择代价最低的组合执行。虽然优化器会尽可能选择最好的执行计划,但是由于数据潜在的内连关系,最终给出的执行计划未必适用于当前场景。
pg_hint_plan插件可以用Hint来强制干预和调优SQL语句的执行计划,并注册调优后的SQL语句模板和Hint规则,后续遇到相同SQL语句模板(常数参数数值不同,其它值都相同)的SQL语句将自动生成Hint干预调优后的执行计划,从而提高执行效率。
启用Hint功能
执行以下命令安装插件以启用Hint功能:
CREATE EXTENSION pg_hint_plan;
仅安装插件的库可以使用Hint功能。
支持的Hint
类别 | 格式 | 说明 |
设置语句级GUC参数 |
| 设置优化器阶段的GUC参数。 目前GUC参数仅在优化器阶段生效,暂时在其它阶段(例如Rewrite和Execute等阶段)不生效。
|
扫描方法提示 |
| 强制序列扫描。 |
| 强制TID扫描。 | |
| 强制索引扫描,且允许指定一个索引。 | |
| 强制使用仅索引扫描,且允许指定一个索引。 | |
| 强制使用位图索引(Bitmap)扫描。 | |
| 禁用序列扫描。 | |
| 禁用TID扫描。 | |
| 禁用索引扫描。 | |
| 禁用仅索引扫描。 | |
| 禁用位图索引扫描。 | |
联接方法提示 说明 需要配合联接顺序提示共同使用。 |
| 强制使用嵌套循环联接。 |
| 强制使用散列联接。 | |
| 强制使用合并联接。 | |
| 禁用嵌套循环联接。 | |
| 禁用散列联接。 | |
| 禁用合并联接。 | |
联接顺序提示 |
| 强制定义联接的顺序。 |
| 强制定义联接的顺序和方向。 | |
行号纠正提示 |
| 纠正由指定表组成的联接结果的行号。 可用的校正方法为绝对值
说明 ROWS修改的是总行数,返回的查询计划中显示的是每个节点平均行数(总行数/节点数量)。 |
当前GUC参数之外的其它Hint仅对查询优化器(Postgres query optimizer)生效,对ORCA优化器不生效。
当前暂时不支持并行程度相关的干预能力。
示例如下:
设置语句级GUC参数
优化器阶段的GUC参数配置,对ORCA优化器和查询优化器均生效。
关闭ORCA优化器:
/*+ SET(optimizer off) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
关闭后将不会使用ORCA优化器。
启用ORCA优化器:
/*+ SET(optimizer on) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
启用后将默认使用ORCA优化器。数据库在大多数情况都会使用ORCA优化器,只有部分情况(例如单表查询、过多分区表等)不会使用ORCA优化器。
强制启用ORCA优化器:
/*+ SET(optimizer on) SET(rds_optimizer_options 0) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
强制启用后所有情况均使用ORCA优化器。数据库只有在ORCA优化器无法创建计划时不使用ORCA优化器。
强制启用ORCA优化器并关闭ORCA优化器的HashJoin能力:
/*+ SET(optimizer on) SET(rds_optimizer_options 0) SET(optimizer_enable_hashjoin off) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
扫描方法提示
以下内容仅适用查询优化器,使用前请执行以下命令关闭ORCA优化器:
SET optimizer to off;
强制t1表进行索引扫描:
/*+ Indexscan(t1) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
禁止对t1表进行索引扫描:
/*+ NoIndexscan(t1) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
强制t1表使用t1_val进行位图索引扫描:
/*+ Bitmapscan(t1 t1_val) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
强制t1表进行仅索引扫描(IndexOnlyScan):
/*+ Indexonlyscan(t1) */EXPLAIN SELECT t2.*, t1.val FROM t1 JOIN t2 ON t1.val = t2.val;
说明仅索引扫描只有仅扫描索引列时才能使用。
强制t1表进行TID扫描:
/*+ Tidscan(t1) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val where t1.ctid = '(1,2)';
说明TID扫描只有在表中有TID条件时才能使用。
联接方法提示和联接顺序提示
以下内容仅适用查询优化器,使用前请执行以下命令关闭ORCA优化器:
SET optimizer to off;
联接时t1为左表,且联接类型为MergeJoin:
/*+ Leading((t1 t2)) MergeJoin(t1 t2) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
联接时t1为左表, 且联接类型为NestLoopJoin:
/*+ Leading((t1 t2)) NestLoop(t1 t2) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
联接时t1为左表, 且联接时禁止HashJoin:
/*+ Leading((t1 t2)) NoHashJoin(t1 t2) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
指定t2与t3先进行HashJoin,随后与t1进行NestLoopJoin:
/*+ Leading(((t2 t3) t1)) HashJoin(t2 t3) NestLoop(t2 t3 t1) */EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.val = t2.val and t2.val = t3.val;
行号纠正提示
以下内容仅适用查询优化器,使用前请执行以下命令关闭ORCA优化器:
SET optimizer to off;
将t1与t2联接后的总行数扩大100倍:
/*+ Rows(t1 t2 *100) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
将t1与t2联接后的总行数缩小100倍:
/*+ Rows(t1 t2 *0.01) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
将t1与t2联接后的总行数增加100行:
/*+ Rows(t1 t2 +100) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
将t1与t2联接后的总行数减少100行:
/*+ Rows(t1 t2 -100) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
将t1与t2联接后的总行数修正为100:
/*+ Rows(t1 t2 #100) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
GUC参数
参数名称 | 默认值 | 说明 |
pg_hint_plan.enable_hint | on | 是否使用Hint干预计划。取值说明:
|
pg_hint_plan.enable_hint_table | off | 是否使用Hint注册功能。取值说明:
|
pg_hint_plan.jumble_mode | off | 定义是否使用OID来标识SQL参数化语句中的对象,例如表、函数、操作符等。取值说明:
说明 该参数不建议经常切换,切换后将无法识别切换前注册的规则。 |
pg_hint_plan.parse_messages | info | 控制Parse Hint阶段的错误信息的日志等级。取值如下: error、warning、notice、info、log、debug[1-5]。 |
pg_hint_plan.message_level | log | 控制Hint其它阶段的错误信息的日志等级。取值如下: error、warning、notice、info、log、debug[1-5]。 |
注册Hint
当希望相同SQL模板的SQL语句自动应用Hint或者当出现SQL语句不方便添加Hint时,您可以将Hint注册信息添加至系统表hint_plan.hints中。注册后,后续执行相同SQL语句模板的SQL语句时,会自动生成Hint调优的执行计划。
hint_plan.hints表结构如下:
列名 | 类型 | 内容 |
id | integer | 注册Hint规则的标号,默认递增。 |
norm_query_string | text | SQL语句模板,即去除参数(Param)和常数(Const)的SQL语句。 |
application_name | text | 注册该Hint规则的应用标识字符串,用于多个应用间隔离规则,默认为 application_name列拥有唯一键约束。 |
hints | text | 为SQL语句模板注册的Hint。 hints列拥有唯一键约束。 |
query_hash | bigint | SQL语句模板参数化后的Hash值,为标准化SQL的唯一标识。 query_hash列拥有唯一键约束。 |
enable | boolean | 控制Hint规则是否可用。同一SQL语句模板只能使用一个Hint规则。 |
prepare_param_strings | text | 注册的查询SQL语句为Prepare语句时,记录其参数。 |
您可以直接查询hint_plan.hints表,但不建议直接修改该表,如需修改建议使用对应函数进行修改。
以下内容将介绍Hint注册函数:
SQL语句参数化函数
hint_plan.gp_hint_query_parameterize(<query>, <application_name>)
参数
说明
query
包含Hint的SQL语句。
application_name
注册该Hint规则的应用标识字符串,此处默认留空(
''
)。该函数用于获取带有Hint的SQL语句的各种参数信息,返回信息如下:
参数
说明
query_hash
SQL语句模板参数化后的Hash值,为标准化SQL的唯一标识。
norm_query_string
SQL语句模板。
comment_hints
语句的注释。
first_matched_hint_in_table
在hint_plan.hints表中与SQL语句模板匹配的注释。
prepare_param_strings
SQL语句中提取出的参数。
示例如下:
SELECT * FROM hint_plan.gp_hint_query_parameterize('/*+ MergeJoin(t1 t2) Leading((t1 t2)) */SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 20;');
返回示例如下:
-[ RECORD 1 ]---------------+-------------------------------------------------------------------------- query_hash | -4733464863014584191 norm_query_string | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; comment_hints | MergeJoin(t1 t2) Leading((t1 t2)) first_matched_hint_in_table | HashJoin(t1 t2) Leading((t1 t2)) prepare_param_strings | {}
Hint注册函数
hint_plan.insert_hint_table(<query>, <application_name>)
参数
说明
query
包含Hint的SQL语句。
application_name
注册该Hint规则的应用标识字符串,此处默认留空(
''
)。使用该函数可以为同一个SQL语句模板注册不同的Hint规则。当您重复插入SQL语句模板、Hint、应用标识字符串相同的Hint规则时,hint_plan.hints表中不会存在多组相同的Hint规则,只会将对应的Hint规则变为true,其它Hint规则变为false。
示例如下:
SELECT hint_plan.insert_hint_table('/*+ MergeJoin(t1 t2) Leading((t1 t2)) */SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;');
返回示例如下:
insert_hint_table --------------------------------------------------------------------------------------------------------------------------------------------------- (1,"SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2;","","MergeJoin(t1 t2) Leading((t1 t2)) ",-4733464863014584191,t,{}) (1 row)
Hint修改函数
hint_plan.upsert_hint_table(<query>, <application_name>)
参数
说明
query
包含Hint的SQL语句。
application_name
注册该Hint规则的应用标识字符串,此处默认留空(
''
)。如果SQL语句对应的参数模板有可用的Hint,则将hint_plan.hints表中的原Hint替换为
query
自带的Hint;如果没有可用的Hint,则新注册的Hint规则。示例如下:
查询hint_plan.hints表中现有的Hint规则:
SELECT * FROM hint_plan.hints;
返回信息如下:
id | norm_query_string | application_name | hints | query_hash | enable | prepare_param_strings ----+---------------------------------------------------------------------------+------------------+------------------------------------+----------------------+--------+----------------------- 1 | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; | | MergeJoin(t1 t2) Leading((t1 t2)) | -4733464863014584191 | f | {} 2 | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; | | Nestloop(t1 t2) Leading((t1 t2)) | -4733464863014584191 | t | {} (2 rows)
执行Hint修改函数:
SELECT hint_plan.upsert_hint_table('/*+ HashJoin(t1 t2) Leading((t1 t2)) */SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;');
返回信息如下:
upsert_hint_table -------------------------------------------------------------------------------------------------------------------------------------------------- (2,"SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2;","","HashJoin(t1 t2) Leading((t1 t2)) ",-4733464863014584191,t,{}) (1 row)
查询修改Hint规则后的hint_plan.hints表:
SELECT * FROM hint_plan.hints;
可以看到同SQL语句模板的Hint从
Nestloop(t1 t2) Leading((t1 t2))
变成了HashJoin(t1 t2) Leading((t1 t2))
,返回信息如下:id | norm_query_string | application_name | hints | query_hash | enable | prepare_param_strings ----+---------------------------------------------------------------------------+------------------+------------------------------------+----------------------+--------+----------------------- 1 | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; | | MergeJoin(t1 t2) Leading((t1 t2)) | -4733464863014584191 | f | {} 2 | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; | | HashJoin(t1 t2) Leading((t1 t2)) | -4733464863014584191 | t | {} (2 rows)
Hint删除函数
删除对应ID的Hint规则:
hint_plan.delete_hint_table(<id>)
删除对应SQL语句,Hint,应用标识字符串的Hint规则:
hint_plan.delete_hint_table(<query>, <hint>, <application_name>)
删除所有对应SQL语句,应用标识字符串的Hint规则:
hint_plan.delete_all_hint_table(<query>, <application_name>)
参数
说明
id
hint_plan.hints表中的标号(ID)。
query
SQL语句,可以不包含Hint。
hint
Hint。
application_name
注册该Hint规则的应用标识字符串,此处默认留空(
''
)。示例如下:
查询原hint_plan.hints表信息:
SELECT * FROM hint_plan.hints;
返回信息如下:
id | norm_query_string | application_name | hints | query_hash | enable | prepare_param_strings ----+---------------------------------------------------------------------------+------------------+-------------------------------------------------+----------------------+--------+----------------------- 1 | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; | | MergeJoin(t1 t2) Leading((t1 t2)) | -4733464863014584191 | f | {} 2 | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; | | HashJoin(t1 t2) Leading((t1 t2)) | -4733464863014584191 | t | {} 3 | select * from t1 join t2 on t1.val = t2.val; | | set(optimizer on) set(rds_optimizer_options 0) | -2169095602568752481 | f | {} 4 | select * from t1 join t2 on t1.val = t2.val; | | set(optimizer off) | -2169095602568752481 | t | {} (4 rows)
根据ID删除对应的Hint规则:
SELECT hint_plan.delete_hint_table(1);
返回信息如下:
WARNING: "max_appendonly_tables": setting is deprecated, and may be removed in a future release. delete_hint_table --------------------------------------------------------------------------------------------------------------------------------------------------- (1,"SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2;","","MergeJoin(t1 t2) Leading((t1 t2)) ",-4733464863014584191,f,{}) (1 row)
查询删除后的hint_plan.hints表:
SELECT * FROM hint_plan.hints;
返回信息如下:
id | norm_query_string | application_name | hints | query_hash | enable | prepare_param_strings ----+---------------------------------------------------------------------------+------------------+-------------------------------------------------+----------------------+--------+----------------------- 2 | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; | | HashJoin(t1 t2) Leading((t1 t2)) | -4733464863014584191 | t | {} 3 | select * from t1 join t2 on t1.val = t2.val; | | set(optimizer on) set(rds_optimizer_options 0) | -2169095602568752481 | f | {} 4 | select * from t1 join t2 on t1.val = t2.val; | | set(optimizer off) | -2169095602568752481 | t | {} (3 rows)
根据SQL语句、Hint和应用标识字符串删除对应的Hint规则:
SELECT hint_plan.delete_hint_table('SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 5 and t2.val > 1;', 'HashJoin(t1 t2) Leading((t1 t2))');
返回信息如下:
delete_hint_table -------------------------------------------------------------------------------------------------------------------------------------------------- (2,"SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2;","","HashJoin(t1 t2) Leading((t1 t2)) ",-4733464863014584191,t,{}) (1 row)
查询删除后的hint_plan.hints表:
SELECT * FROM hint_plan.hints;
返回信息如下:
id | norm_query_string | application_name | hints | query_hash | enable | prepare_param_strings ----+----------------------------------------------+------------------+-------------------------------------------------+----------------------+--------+----------------------- 3 | select * from t1 join t2 on t1.val = t2.val; | | set(optimizer on) set(rds_optimizer_options 0) | -2169095602568752481 | f | {} 4 | select * from t1 join t2 on t1.val = t2.val; | | set(optimizer off) | -2169095602568752481 | t | {} (2 rows)
根据SQL语句、应用标识字符串删除对应的Hint规则:
SELECT hint_plan.delete_all_hint_table('select * from t1 join t2 on t1.val = t2.val;');
返回信息如下:
delete_all_hint_table ----------------------------------------------------------------------------------------------------------------------------------- (3,"select * from t1 join t2 on t1.val = t2.val;","","set(optimizer on) set(rds_optimizer_options 0) ",-2169095602568752481,f,{}) (4,"select * from t1 join t2 on t1.val = t2.val;","","set(optimizer off) ",-2169095602568752481,t,{}) (2 rows)
查询删除后的hint_plan.hints表:
SELECT * FROM hint_plan.hints;
返回信息如下:
id | norm_query_string | application_name | hints | query_hash | enable | prepare_param_strings ----+-------------------+------------------+-------+------------+--------+----------------------- (0 rows)
Hint使能函数
启用对应ID的Hint规则,启用后同SQL语句模板的其它Hint规则将不可用:
hint_plan.enable_hint_table(<id>)
启用对应SQL语句,Hint,应用标识字符串的Hint规则,启用后同SQL语句模板的其它Hint规则将不可用:
hint_plan.enable_hint_table(<query>, <hint>, <application_name>)
禁止对应ID的Hint规则:
hint_plan.disable_hint_table(<id>)
禁止对应SQL语句,Hint,应用标识字符串的Hint规则:
hint_plan.disable_hint_table(<query>, <hint>, <application_name>)
禁止对应SQL语句,应用标识字符串的Hint规则:
hint_plan.disable_all_hint_table(<query>, <application_name>)
参数
说明
id
hint_plan.hints表中的标号(ID)。
query
SQL语句,可以不包含Hint。
hint
Hint规则。
application_name
注册该Hint规则的应用标识字符串,此处默认留空(
''
)。示例如下:
查询原hint_plan.hints表信息:
SELECT * FROM hint_plan.hints;
返回信息如下:
id | norm_query_string | application_name | hints | query_hash | enable | prepare_param_strings ----+----------------------------------------------+------------------+-------------------------------------------------+----------------------+--------+----------------------- 5 | select * from t1 join t2 on t1.val = t2.val; | | set(optimizer off) | -2169095602568752481 | f | {} 6 | select * from t1 join t2 on t1.val = t2.val; | | set(optimizer on) set(rds_optimizer_options 0) | -2169095602568752481 | t | {} (2 rows)
禁止对应ID的Hint规则:
SELECT hint_plan.disable_hint_table(6);
返回信息如下:
disable_hint_table ----------------------------------------------------------------------------------------------------------------------------------- (6,"select * from t1 join t2 on t1.val = t2.val;","","set(optimizer on) set(rds_optimizer_options 0) ",-2169095602568752481,f,{}) (1 row)
查询变更状态后的hint_plan.hints表:
SELECT * FROM hint_plan.hints;
返回信息如下:
id | norm_query_string | application_name | hints | query_hash | enable | prepare_param_strings ----+----------------------------------------------+------------------+-------------------------------------------------+----------------------+--------+----------------------- 5 | select * from t1 join t2 on t1.val = t2.val; | | set(optimizer off) | -2169095602568752481 | f | {} 6 | select * from t1 join t2 on t1.val = t2.val; | | set(optimizer on) set(rds_optimizer_options 0) | -2169095602568752481 | f | {} (2 rows)
启用对应ID的Hint规则:
SELECT hint_plan.enable_hint_table(5);
返回信息如下:
enable_hint_table ------------------------------------------------------------------------------------------------------- (5,"select * from t1 join t2 on t1.val = t2.val;","","set(optimizer off) ",-2169095602568752481,t,{}) (1 row)
查询变更状态后的hint_plan.hints表:
SELECT * FROM hint_plan.hints;
返回信息如下:
id | norm_query_string | application_name | hints | query_hash | enable | prepare_param_strings ----+----------------------------------------------+------------------+-------------------------------------------------+----------------------+--------+----------------------- 6 | select * from t1 join t2 on t1.val = t2.val; | | set(optimizer on) set(rds_optimizer_options 0) | -2169095602568752481 | f | {} 5 | select * from t1 join t2 on t1.val = t2.val; | | set(optimizer off) | -2169095602568752481 | t | {} (2 rows)
根据SQL语句、应用标识字符串启用对应的Hint规则:
SELECT hint_plan.enable_hint_table('select * from t1 join t2 on t1.val = t2.val;', 'set(optimizer off)');
返回信息如下:
enable_hint_table ------------------------------------------------------------------------------------------------------- (5,"select * from t1 join t2 on t1.val = t2.val;","","set(optimizer off) ",-2169095602568752481,t,{}) (1 row)
查询变更状态后的hint_plan.hints表:
SELECT * FROM hint_plan.hints;
返回信息如下:
id | norm_query_string | application_name | hints | query_hash | enable | prepare_param_strings ----+----------------------------------------------+------------------+-------------------------------------------------+----------------------+--------+----------------------- 6 | select * from t1 join t2 on t1.val = t2.val; | | set(optimizer on) set(rds_optimizer_options 0) | -2169095602568752481 | f | {} 5 | select * from t1 join t2 on t1.val = t2.val; | | set(optimizer off) | -2169095602568752481 | t | {} (2 rows)
卸载pg_hint_plan插件
如果您不需要使用Hint功能,可以通过以下语句卸载插件:
DROP EXTENSION pg_hint_plan;