使用pg_hint_plan定义Postgresql执行计划

pg_hint_plan版本1.5：https://github.com/ossc-db/pg_hint_plan/blob/PG15/docs/hint_details.md
postgresql版本16

前言

pg_hint_plan的使用教程很多，本篇主要通过实例介绍一些使用时经常遇到的问题，例如：

1. 表有schema怎么办？
2. sql有换行匹配不上怎么办？
3. sql模版太长拼不对怎么办？

2 安装

wget https://github.com/ossc-db/pg_hint_plan/archive/refs/tags/REL15_1_5_0.tar.gz
1

下载后解压到任意目录

tar xzvf REL15_1_5_0.tar.gz

cd pg_hint_plan-REL15_1_5_0/

## 事先配置环境变量，确认pg_config可以执行
pg_config

make
make install
1
2
3
4
5
6
7
8
9

• 安装后最好修改配置参数shared_preload_libraries = 'pg_hint_plan'然后重启
• 使用create extension pg_hint_plan;创建插件，以后就可以一直使用了。

可以查询到插件配置表示安装成功：

select name, setting from pg_settings where name ~ 'pg_hint_plan';
              name              | setting 
--------------------------------+---------
 pg_hint_plan.debug_print       | off
 pg_hint_plan.enable_hint       | on
 pg_hint_plan.enable_hint_table | off
 pg_hint_plan.hints_anywhere    | off
 pg_hint_plan.message_level     | log
 pg_hint_plan.parse_messages    | info
1
2
3
4
5
6
7
8
9

创建测试表

drop table student;
create table student(sno int primary key, sname varchar(10), ssex int);
insert into student select t.a, substring(md5(random()::text), 1, 5), t.a % 2 from generate_series(1, 100000) t(a);
create index idx1 on student(sname);
create index idx2 on student(sname, ssex);
vacuum analyze student;

create schema sch1;
drop table sch1.student;
create table sch1.student(sno int primary key, sname varchar(10), ssex int);
insert into sch1.student select t.a, substring(md5(random()::text), 1, 5), t.a % 2 from generate_series(1, 100000) t(a);
create index idx10 on sch1.student(sname);
create index idx20 on sch1.student(sname, ssex);
vacuum analyze sch1.student;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3 注释hint（可以改SQL的场景）

• 必选参数：pg_hint_plan.enable_hint = on
• 可选参数：打印调试信息，是否走hint了？
  • set pg_hint_plan.debug_print = ‘detailed’;
  • set client_min_messages to log;

场景一：【无schema】【控制索引】

explain select * from student where sname = 'be12b';
                             QUERY PLAN                              
---------------------------------------------------------------------
 Index Scan using idx1 on student  (cost=0.29..8.31 rows=1 width=14)
   Index Cond: ((sname)::text = 'be12b'::text



explain select /*+ IndexScan(student idx2) */ * from student where sname = 'be12b';
                             QUERY PLAN                              
---------------------------------------------------------------------
 Index Scan using idx2 on student  (cost=0.42..8.44 rows=1 width=14)
   Index Cond: ((sname)::text = 'be12b'::text)
1
2
3
4
5
6
7
8
9
10
11
12
13

场景二：【有schema】【控制索引】

注意：插件无法识别schema，只做表名的字符串匹配，所以有schema的SQL请直接忽略schma即可：

• 有schema正确用法：/*+ IndexScan(student idx20) */
• 有schema错误用法：/*+ IndexScan(sch1.student idx20) */

explain select * from sch1.student where sname = 'be12b';
                              QUERY PLAN                              
----------------------------------------------------------------------
 Index Scan using idx10 on student  (cost=0.29..8.31 rows=1 width=14)
   Index Cond: ((sname)::text = 'be12b'::text)



explain select /*+ IndexScan(student idx20) */ * from sch1.student where sname = 'be12b';
                              QUERY PLAN                              
----------------------------------------------------------------------
 Index Scan using idx20 on student  (cost=0.42..8.44 rows=1 width=14)
   Index Cond: ((sname)::text = 'be12b'::text)
1
2
3
4
5
6
7
8
9
10
11
12
13

4 hint种类

5 使用hint table（不能改SQL的场景）

必选参数：set pg_hint_plan.enable_hint_table to on;

如果不允许改SQL，pg_hint_plan插件也提供了另一种字符串匹配的方式来固定执行计划：将SQL模版和hint插入pg_hint_plan提供的表中，如果执行SQL与任意模版匹配，则执行相应的hint。

上述SQL模版拼写是不太容易处理的问题，因为插件要求SQL模版与执行的SQL需要严格匹配（空格、换行都要完全相同）。

下面用一个实例介绍如何快速构造一个可用的SQL模版。

实例

例如生产SQL（存在换行、空格）使用索引idx10，需要调整他的索引为idx20。

explain /* 123     */
select
 sno      ,
   sname  , 
      ssex
from sch1.student where sname = 'be12b';

                              QUERY PLAN                              
----------------------------------------------------------------------
 Index Scan using idx10 on student  (cost=0.29..8.31 rows=1 width=14)
   Index Cond: ((sname)::text = 'be12b'::text)
1
2
3
4
5
6
7
8
9
10
11

现在希望这条SQL走idx10索引，但是原SQL中有一些空格和换行。

select name, setting from pg_settings where name ~ 'pg_hint_plan';
              name              | setting 
--------------------------------+---------
 pg_hint_plan.debug_print       | off
 pg_hint_plan.enable_hint       | on
 pg_hint_plan.enable_hint_table | off
 pg_hint_plan.hints_anywhere    | off
 pg_hint_plan.message_level     | log
 pg_hint_plan.parse_messages    | info
1
2
3
4
5
6
7
8
9

首先打开参数：

set pg_hint_plan.debug_print = 'detailed';
set pg_hint_plan.enable_hint_table = on;
set client_min_messages = log;
1
2
3

执行一次原始SQL

explain /* 123     */
select
 sno      ,
   sname  , 
      ssex
from sch1.student where sname = 'be12b';
1
2
3
4
5
6

这里会看到三条日志

LOG:  pg_hint_plan[qno=0x7]: no match found in table:  application name = "psql", normalized_query="explain /* 123     */
select
 sno      ,
   sname  , 
      ssex
from sch1.student where sname = ?;"

LOG:  hints in comment="(none)", query="explain /* 123     */
select
 sno      ,
   sname  , 
      ssex
from sch1.student where sname = 'be12b';", debug_query_string="explain /* 123     */
select
 sno      ,
   sname  , 
      ssex
from sch1.student where sname = 'be12b';"

LOG:  pg_hint_plan[qno=0x6]: planner: no valid hint
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

这里注意第一条日志中的normalized_query字段，内容就是已经模版化的SQL：

normalized_query="explain /* 123     */
select
 sno      ,
   sname  , 
      ssex
from sch1.student where sname = ?;"
1
2
3
4
5
6

下面将内容原封不动的插入pg_hint_plan表中，并指定hint内容

技巧：使用 $$SQL文本$$ 代替 'SQL文本' 可以避免SQL内部存在标点符号需要转义的问题。PG中$$中的文本是不会转义的。

insert into hint_plan.hints (norm_query_string,application_name,hints) 
values(
$$explain /* 123     */
select
 sno      ,
   sname  , 
      ssex
from sch1.student where sname = ?;$$,
'psql',
'IndexScan(student idx20)');   
1
2
3
4
5
6
7
8
9
10

这里在执行一次原始SQL，发现执行计划变化表示成功。

explain /* 123     */
select
 sno      ,
   sname  , 
      ssex
from sch1.student where sname = 'be12b';
                              QUERY PLAN                              
----------------------------------------------------------------------
 Index Scan using idx20 on student  (cost=0.42..8.44 rows=1 width=14)
   Index Cond: ((sname)::text = 'be12b'::text)
1
2
3
4
5
6
7
8
9
10