pg_hint_plan版本1.5：https://github.com/ossc-db/pg_hint_plan/blob/PG15/docs/hint_details.md postgresql版本16
前言
pg_hint_plan的使用教程很多，本篇主要通过实例介绍一些使用时经常遇到的问题，例如：
表有schema怎么办？sql有换行匹配不上怎么办？sql模版太长拼不对怎么办？
2 安装
wget https://github.com/ossc-db/pg_hint_plan/archive/refs/tags/REL15_1_5_0.tar.gz
下载后解压到任意目录
tar xzvf REL15_1_5_0.tar.gz
cd pg_hint_plan-REL15_1_5_0/
## 事先配置环境变量，确认pg_config可以执行
pg_config
make
make install
安装后最好修改配置参数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
创建测试表
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;
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)
场景二：【有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)
4 hint种类
GroupFormatDescriptionScan methodSeqScan(table)Forces sequential scan on the tableTidScan(table)Forces TID scan on the table.IndexScan(table[ index...])Forces index scan on the table. Restricts to specified indexes if any.IndexOnlyScan(table[ index...])Forces index only scan on the table. Rstricts to specfied indexes if any. Index scan may be used if index only scan is not available. Available for PostgreSQL 9.2 and later.BitmapScan(table[ index...])Forces bitmap scan on the table. Restoricts to specfied indexes if any.IndexScanRegexp(table[ POSIX Regexp...]) IndexOnlyScanRegexp(table[ POSIX Regexp...]) BitmapScanRegexp(table[ POSIX Regexp...])Forces index scan or index only scan (For PostgreSQL 9.2 and later) or bitmap scan on the table. Restricts to indexes that matches the specified POSIX regular expression patternNoSeqScan(table)Forces not to do sequential scan on the table.NoTidScan(table)Forces not to do TID scan on the table.NoIndexScan(table)Forces not to do index scan and index only scan (For PostgreSQL 9.2 and later) on the table.NoIndexOnlyScan(table)Forces not to do index only scan on the table. Available for PostgreSQL 9.2 and later.NoBitmapScan(table)Forces not to do bitmap scan on the table.Join methodNestLoop(table table[ table...])Forces nested loop for the joins consist of the specifiled tables.HashJoin(table table[ table...])Forces hash join for the joins consist of the specifiled tables.MergeJoin(table table[ table...])Forces merge join for the joins consist of the specifiled tables.NoNestLoop(table table[ table...])Forces not to do nested loop for the joins consist of the specifiled tables.NoHashJoin(table table[ table...])Forces not to do hash join for the joins consist of the specifiled tables.NoMergeJoin(table table[ table...])Forces not to do merge join for the joins consist of the specifiled tables.Join orderLeading(table table[ table...])Forces join order as specified.Leading(
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)
现在希望这条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
首先打开参数：
set pg_hint_plan.debug_print = 'detailed';
set pg_hint_plan.enable_hint_table = on;
set client_min_messages = log;
执行一次原始SQL
explain /* 123 */
select
sno ,
sname ,
ssex
from sch1.student where sname = 'be12b';
这里会看到三条日志
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
这里注意第一条日志中的normalized_query字段，内容就是已经模版化的SQL：
normalized_query="explain /* 123 */
select
sno ,
sname ,
ssex
from sch1.student where sname = ?;"
下面将内容原封不动的插入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)');
这里在执行一次原始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)
精彩内容
发表评论