如何使用pg_hint_plan插件_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

pg_hint_plan插件通过特殊的注释语句提示来调整既定的执行计划。

背景信息

PostgreSQL使用基于代价的优化器，优化路线使用统计数据而非固定的规则。对于一条SQL语句，优化器会去评估所有可能的代价并最终选择代价最低的去执行。优化器会尽力选择最好的执行计划，但由于其并不了解数据中可能存在的一些内在连接关系，导致这些执行计划可能并不完美。

通过指定GUC变量，可以调整执行计划，但会影响整个会话。而pg_hint_plan只调整单个执行计划，不影响整个会话，可以优化执行计划。

注意事项

DMS暂时不支持提示注释，请使用其他连接方式连接数据库。
pg_hint_plan插件只识别第一个注释块中的内容。
当进行扫描时，如果遇到了除字母、数字、空格、下划线、逗号、圆括号以外的字符时会立即停止扫描。
pg_hint_plan插件对于对象的处理与PostgreSQL并不一致，只会按照对象的对象名进行比较。例如，一个名为TBL的对象在提示语句中只会匹配TBL，而不会匹配tbl或Tbl。

使用限制

在PL/pgSQL存储过程中使用pg_hint_plan插件的限制如下：

提示只对以下类型的语句生效：
- 返回一行的查询（SELECT、INSERT、UPDATE、DELETE）。
- 返回多行的查询（RETURN QUERY）。
- 执行SQL语句（EXECUTE QUERY）。
- 打开游标（OPEN）。
- 对查询结果的遍历（FOR）。
一个提示语句必须被放置于每个查询的第一个单词之后，因为过早的提示语句无法被认为是该查询的一部分。

创建插件

创建插件。
```
CREATE EXTENSION pg_hint_plan;
```
加载插件。
- 单个用户自动加载。
  - 执行以下命令，加载插件。
```
ALTER USER xxx set session_preload_libraries='pg_hint_plan';
```
    说明其中，xxx需要更改为实际登录的用户名。
  - 执行以下命令，对单独数据库自动加载。
```
ALTER DATABASE xxx set session_preload_libraries='pg_hint_plan';
```
  说明如果配置错误导致无法登录数据库，则需要以其它用户/数据库登录到PolarDB中，进行重置：
```
ALTER USER xxx reset session_preload_libraries;
ALTER DATABASE xxx reset session_preload_libraries;
```
- 数据库集群自动加载。
  请前往配额中心，在配额名称PolarDB PG pg_hint_plan使用的操作列，单击申请，申请添加pg_hint_plan插件。
- 查看是否已加载插件。
  1. 执行以下命令，将调试信息输出到客户端。
```
SET pg_hint_plan.debug_print TO on;
SET pg_hint_plan.message_level TO notice;
```
  2. 执行以下命令，查看是否加载成功。
```
/*+Set(enable_seqscan 1)*/select 1;
```
    显示结果如下，表示加载成功。
```
NOTICE:  pg_hint_plan: used hint: Set(enable_seqscan 1)
```
  3. 执行以下命令，关闭调试信息输出。
```
RESET pg_hint_plan.debug_print;
RESET pg_hint_plan.message_level;
```

使用说明

注释提示

pg_hint_plan的注释以/*+开头，以*/结束。提示语句包括提示名和参数（参数使用括号包裹，参数之间使用空格分隔）。为了增加可读性，每一个提示语句都可以重新换行。

示例

HashJoin作为连接方法，并且使用序列扫描SeqScan来扫描表pgbench_accounts：

/*+
   HashJoin(a b)
   SeqScan(a)
 */
EXPLAIN SELECT *
   FROM pgbench_branches b
   JOIN pgbench_accounts a ON b.bid = a.bid
   ORDER BY a.aid;

返回结果如下：

                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Sort  (cost=31465.84..31715.84 rows=100000 width=197)
   Sort Key: a.aid
   ->  Hash Join  (cost=1.02..4016.02 rows=100000 width=197)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..2640.00 rows=100000 width=97)
         ->  Hash  (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

提示表

虽然可以使用注释提示的方式对SQL语句进行提示，但是当SQL语句无法编辑时，该提示方式便不可用。对于这种情况，可以将这些提示放在一张特殊的表hint_plan.hints中。表结构如下所示：


字段	说明
id	提示ID号，唯一且自动填充。
norm_query_string	与要提示的查询匹配的模式。所有的常量可以被替换为？，并且空格在这个模式中是有意义的。
application_name	应用会话的名称，置空表示任何应用。
hints	提示语句，不需要注释标记。

以下是提示表的示例，用户在创建插件时，默认拥有提示表的权限。当提示表和注释中的单语句提示同时存在时，提示表的优先级高于注释中的单语句提示：

INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
    VALUES (
        'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
        '',
        'SeqScan(t1)'
    );
INSERT 0 1
 UPDATE hint_plan.hints
    SET hints = 'IndexScan(t1)'
  WHERE id = 1;
UPDATE 1
 DELETE FROM hint_plan.hints
  WHERE id = 1;
DELETE 1

提示类型

提示类型
根据提示短语影响执行计划的方式，支持的提示类型包括扫描方法提示、连接方法提示、连接顺序提示、行数校正提示、并行执行提示和GUC参数设置提示。
- 扫描方法提示
  扫描方法提示对目标表强制执行特定的扫描方法，pg_hint_plan通过表的别名（如果存在的话）来识别目标表。扫描方法可能是序列扫描、索引扫描等。
  扫描提示对普通表、继承表、无日志表、临时表和系统表有效。对外部表、表函数、常量值语句、通用表达式、视图和子查询无效。
  示例命令如下：
```
/*+
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
 */
 SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
```
- 连接方法提示
  连接方法提示强制指定相关表聚合在一起的方法。对普通表、继承表、无日志表、临时表、外部表、系统表、表函数、常量值命令和通用表达式有效。对视图和子查询无效。
- 连接顺序提示
  连接顺序提示指定两张或多张表的连接顺序。包括两种强制指定方法：
  - 强制执行特定的连接顺序，但不限制每个连接级别的方向。
  - 强制连接方向。
  示例命令如下：
```
 /*+
    NestLoop(t1 t2)
    MergeJoin(t1 t2 t3)
    Leading(t1 t2 t3)
  */
 SELECT * FROM table1 t1
     JOIN table table2 t2 ON (t1.key = t2.key)
     JOIN table table3 t3 ON (t2.key = t3.key);
```
  说明其中
  NestLoop(t1 t2)：指定表t1和t2的连接方法。
  MergeJoin(t1 t2 t3)：指定表t1、t2和t3之间的连接方法。
  Leading(t1 t2 t3)：指定三张表的连接顺序。
- 行数校正提示
  行数校正提示会校正由于查询优化器限制而导致的行数错误。
```
 /*+ Rows(a b #10) */ SELECT... ; # 设置连接结果的行数为10
 /*+ Rows(a b +10) */ SELECT... ; # 行数增加10
 /*+ Rows(a b -10) */ SELECT... ; # 行数减去10
 /*+ Rows(a b *10) */ SELECT... ; # 行数增大10倍
```
- 并行执行提示
  并行执行提示会指定并行的执行计划。
  并行级别提示对普通的表、继承表、无日志表和系统表有效。对外部表、常量从句、通用表达式、视图和子查询无效。视图的内部表可以通过其真实名称或别名指定目标对象。
  下面两个示例说明在每张表上执行查询的方式不同：
  - 方式一：指定表c1的并行度为3，表c2的并行度为5。
```
EXPLAIN /*+ Parallel(c1 3 hard) Parallel(c2 5 hard) */
       SELECT c2.a FROM c1 JOIN c2 ON (c1.a = c2.a);
```
    返回结果如下：
```
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Hash Join  (cost=2.86..11406.38 rows=101 width=4)
   Hash Cond: (c1.a = c2.a)
   ->  Gather  (cost=0.00..7652.13 rows=1000101 width=4)
         Workers Planned: 3
         ->  Parallel Seq Scan on c1  (cost=0.00..7652.13 rows=322613 width=4)
   ->  Hash  (cost=1.59..1.59 rows=101 width=4)
         ->  Gather  (cost=0.00..1.59 rows=101 width=4)
               Workers Planned: 5
               ->  Parallel Seq Scan on c2  (cost=0.00..1.59 rows=59 width=4)
                                        
```
  - 方式二：指定表t1的并行度为5。
```
EXPLAIN /*+ Parallel(tl 5 hard) */ SELECT sum(a) FROM tl;
```
    返回结果如下：
```
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Finalize Aggregate  (cost=693.02..693.03 rows=1 width=8)
   ->  Gather  (cost=693.00..693.01 rows=5 width=8)
         Workers Planned: 5
         ->  Partial Aggregate  (cost=693.00..693.01 rows=1 width=8)
               ->  Parallel Seq Scan on tl  (cost=0.00..643.00 rows=20000 width=4)
```
- GUC参数设置提示
  在执行查询的过程中改变GUC参数的值。此值仅在执行器生成查询计划期间有效，并且不会对其它语句产生影响。如果对同一个GUC参数进行多次设置，则以最后一个为准。
  示例命令如下：
```
 /*+ Set(random_page_cost 2.0) */
 SELECT * FROM table1 t1 WHERE key = 'value';
```

提示格式列表

所有提示支持的格式如下所示。在注释中增加以下格式的字段，可以使用相应的功能。其中[]表示可选参数。


类型	格式	说明
扫描方法提示（Scan method）	SeqScan(table)	强制对名为table的表使用Sequence Scan。
	TidScan(table)	强制对名为table的表使用TID Scan。
	IndexScan(table[ index...])	强制对名为table的表使用Index Scan，如果在后面增加index名称，则可以指定需要使用的索引。
	IndexOnlyScan(table[ index...])	强制对名为table的表使用Index Only Scan，如果在后面增加index名称，则可以指定需要使用的索引。
	BitmapScan(table[ index...])	强制对名为table的表使用Bitmap Index Scan，如果在后面增加index名称，则可以指定需要使用的索引。
	NoSeqScan(table)	禁止对名为table的表使用Seqence Scan。
	NoTidScan(table)	禁止对名为table的表使用Tid Scan。
	NoIndexScan(table)	禁止对名为table的表使用Index Scan。
	NoIndexOnlyScan(table)	禁止对名为table的表使用Index Only Scan。
	NoBitmapScan(table)	禁止对名为table的表强制使用Bitmap Index Scan。
连接方法提示（Join method）	NestLoop(table table[ table...])	对包含指定表名的表之间的Join连接操作，强制使用Nest Loop Join进行连接操作。
	HashJoin(table table[ table...])	对包含指定表名的表之间的Join连接操作，强制使用Hash Join进行连接操作。
	MergeJoin(table table[ table...])	对包含指定表名的表之间的Join连接操作，强制使用Merge Join进行连接操作。
	NoNestLoop(table table[ table...])	对包含指定表名的表之间的Join连接操作，禁止使用Nest Loop Join进行连接操作。
	NoHashJoin(table table[ table...])	对包含指定表名的表之间的Join连接操作，禁止使用Hash Join进行连接操作。
	NoMergeJoin(table table[ table...])	对包含指定表名的表之间的Join连接操作，禁止使用Merge Join进行连接操作。
连接顺序提示（Join order）	Leading(table table[ table...])	指定表之间进行Join连接的顺序。
连接顺序提示（Join order）	Leading(<join pair>)	指定两个表之间进行Join的先后顺序。
行数校正提示（Row number correction）	Rows(table table[ table...] correction)	校正由指定表组成的联结结果的行数。可用的校正方法包括绝对值（＃<n>）、加法（+ <n>）、减法（-<n>）和乘法（* <n>），其中<n>表示需要指定的行的数量。
并行执行提示（Parallel query configuration）	Parallel(table <# of workers> [soft\|hard])	强制或禁止并行执行针对指定表的扫描。说明 <# of workers>是所需的并行度（并行执行的程序数量），其中0表示禁止并行执行。如果第三个参数是soft（默认），表示仅修改max_parallel_workers_per_gather参数的值，由优化器决定实际的并行度。 hard表示强制使用其指定的并行度。
	PX(<# of workers>)	表示跨机并行查询时进行并行查询。说明 <# of workers>表示并行度。
	NoPX()	表示针对此查询强制不使用跨机并行查询功能。
GUC参数配置提示	Set(GUC-param value)	优化器运行时，将GUC参数设置为该值。

说明 pg_hint_plan也可以指定跨机并行查询生成的查询计划。目前在跨机并行查询场景下，不支持行数校正提示，连接方法提示仅能作用于两表之间的连接，连接顺序提示仅能指定全部表之间的先后顺序。