使用pg_hint_plan固定Postgresql执行计划

2023-10-13 10:24:38 浏览数 (1)

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 安装

代码语言：javascript复制
wget https://github.com/ossc-db/pg_hint_plan/archive/refs/tags/REL15_1_5_0.tar.gz

下载后解压到任意目录

代码语言：javascript复制
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;创建插件，以后就可以一直使用了。

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

代码语言：javascript复制
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

创建测试表

代码语言：javascript复制
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】【控制索引】
代码语言：javascript复制
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) */
代码语言：javascript复制
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种类

Group

Format

Description

Scan method

SeqScan(table)

Forces sequential scan on the table

TidScan(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 pattern

NoSeqScan(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 method

NestLoop(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 order

Leading(table table[ table...])

Forces join order as specified.

Leading(<join pair>)

Forces join order and directions as specified. A join pair is a pair of tables and/or other join pairs enclosed by parentheses, which can make a nested structure.

Behavior control on Join

Memoize(table table[ table...])

Allow the topmost join of a join among the specified tables to memoize the inner result. (Note that this doesn’t enforce.)

NoMemoize(table table[ table...])

Inhibit the topmost join of a join among the specified tables from memoizing the inner result.

Row number correction

Rows(table table[ table...] correction)

Corrects row number of a result of the joins consist of the specfied tables. The available correction methods are absolute (#), addition ( ), subtract (-) and multiplication (*). should be a string that strtod() can read.

Parallel query configuration

`Parallel(table <# of workers> [soft

hard])`

GUC

Set(GUC-param value)

Set the GUC parameter to the value while planner is running.

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。

代码语言：javascript复制
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中有一些空格和换行。

代码语言：javascript复制
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

首先打开参数：

代码语言：javascript复制
set pg_hint_plan.debug_print = 'detailed';
set pg_hint_plan.enable_hint_table = on;
set client_min_messages = log;

执行一次原始SQL

代码语言：javascript复制
explain /* 123     */
select
 sno      ,
   sname  , 
      ssex
from sch1.student where sname = 'be12b';

这里会看到三条日志

代码语言：javascript复制
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：

代码语言：javascript复制
normalized_query="explain /* 123     */
select
 sno      ,
   sname  , 
      ssex
from sch1.student where sname = ?;"

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

技巧：使用

SQL文本

代替 'SQL文本' 可以避免SQL内部存在标点符号需要转义的问题。PG中

代码语言：javascript复制
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，发现执行计划变化表示成功。

代码语言：javascript复制
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)
postgresql table 插件 配置 索引

0 人点赞