pg_hint_plan 使用hint固定SQL执行计划

Plan Hint是PG社区官方版”永远”不考虑引入的功能之一（参见PG TODO，查找”Oracle-style”），社区开发者的理念是，引入Hint功能，会掩盖优化器本身的问题，导致缺陷不被暴露出来。但对于使用者来讲，遇到某些SQL的查询计划不好，性能出了问题，其他方法又不奏效的情况下，首先的目标还是想尽快解决问题，而Hint就可以在这种时候帮助到我们。

民间的 pg_hint_plan插件 支持类似oracle hint的功能，另外阿里云RDS for PG、PPAS也已经支持了Hint功能。

一、 插件安装及设置

test=> create extension pg_hint_plan;
CREATE EXTENSION

修改所有用户的session_preload_libraries

由于pg_hint_plan是用到了hook的，使用前一定要调用so中的_PG_init(void)初始化一下hook，因此用到了 alter role all set session_preload_libraries='pg_hint_plan';

postgres=> alter role all set session_preload_libraries = 'pg_hint_plan';
ALTER ROLE

只为本用户设置（会话需要重新连接，以加载hook）

alter role 自己 set session_preload_libraries='pg_hint_plan';

如果只想在当前会话有效，可以用LOAD命令启用：

postgres=# LOAD 'pg_hint_plan';
LOAD

二、 pg_hint_plan用法

整个query的第一个comment (/*+ */) 被pg_hint_plan用来固定执行计划。如果有多个comment，也只认第一个，请务必注意。

1. 简单案例

其中 /*+ seqscan(test) */ 和 /*+ bitmapscan(test) */ 就是指定的hint。

test=> create table test(id int primary key, info text);
CREATE TABLE
test=> insert into test select generate_series(1,100000);
INSERT 0 100000

test=> explain select * from test where id=1;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Index Scan using test_pkey on test  (cost=0.29..8.31 rows=1 width=36)
   Index Cond: (id = 1)
(2 rows)

test=> /*+ seqscan(test) */ explain select * from test where id=1;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on test  (cost=0.00..1124.11 rows=272 width=36)
   Filter: (id = 1)
(2 rows)

test=> /*+ bitmapscan(test) */ explain select * from test where id=1;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=4.30..8.31 rows=1 width=36)
   Recheck Cond: (id = 1)
   ->  Bitmap Index Scan on test_pkey  (cost=0.00..4.30 rows=1 width=0)
         Index Cond: (id = 1)
(4 rows)

2. 较复杂案例

来看个复杂点的例子

 Schema |      Name      | Type  | Owner  |  Size   | Description                    
--------+----------------+-------+--------+---------+---------
 public | test01 | table | digoal | 7273 MB | 
 public | test02 | table | digoal | 560 MB  | 
 public | test03 | table | digoal | 368 MB  | 

SELECT xxxx
    FROM
  "test01" AS rd
    INNER JOIN "test02" AS d ON (rd.test02_uuid = d.uuid)
    INNER JOIN "test03" AS ir ON (d.test03_uuid = ir.uuid)
    WHERE
  d.status = 'normal'
    AND ir.u_uuid = 'ttttttttt' and (d.test02_status in ('test02ed','checked')) 
      and d.is_sub = false and d.is_filter = false 
ORDER BY d.test02_time desc limit 10 offset 0

需要固定为以下执行计划

Limit  (cost=1204.30..1204.33 rows=10 width=276) (actual time=11.131..11.132 rows=10 loops=1)
   ->  Sort  (cost=1204.30..1204.49 rows=75 width=276) (actual time=11.131..11.131 rows=10 loops=1)
         Sort Key: d.test02_time
         Sort Method: quicksort  Memory: 33kB
         ->  Nested Loop  (cost=1.28..1202.68 rows=75 width=276) (actual time=0.085..11.095 rows=17 loops=1)
               ->  Nested Loop  (cost=0.85..1117.07 rows=62 width=101) (actual time=0.066..10.879 rows=13 loops=1)
                     ->  Index Scan using "abc" on test03 ir  (cost=0.42..14.89 rows=10 width=38) (actual time=0.026..0.166 rows=96 loops=1)
                           Index Cond: ((u_uuid)::text = 'ttttttttt'::text)
                     ->  Index Scan using "bcd" on test02 d  (cost=0.43..109.86 rows=36 width=80) (actual time=0.109..0.111 rows=0 loops=96)
                           Index Cond: ((test03_uuid)::text = (ir.uuid)::text)
                           Filter: ((NOT is_sub) AND (NOT is_filter) AND ((test02_status)::text = ANY ('{test02ed,checked}'::text[])) AND ((status)::text = 'normal'::text))
                           Rows Removed by Filter: 67
               ->  Index Scan using "def" on test01 rd  (cost=0.43..1.32 rows=6 width=192) (actual time=0.014..0.014 rows=1 loops=13)
                     Index Cond: ((test02_uuid)::text = (d.uuid)::text)

分析以上执行计划，需要固定的包括 索引，嵌套循环，以及JOIN的顺序，驱动顺序。

SQL语句改成

/*+ 
  NestLoop(ir d) 
  NestLoop(ir d rd) 
  Leading(((ir d) rd)) 
  IndexScan(rd "def") 
  IndexScan(d "bcd") 
  IndexScan(ir "abc") 
*/ 
SELECT xxxx
    FROM
  "test01" AS rd
    INNER JOIN "test02" AS d ON (rd.test02_uuid = d.uuid)
    INNER JOIN "test03" AS ir ON (d.test03_uuid = ir.uuid)
    WHERE
  d.status = 'normal'
    AND ir.u_uuid = 'ttttttttt' and (d.test02_status in ('test02ed','checked')) 
      and d.is_sub = false and d.is_filter = false 
ORDER BY d.test02_time desc limit 10 offset 0;

3. pg_hint_plan语法解释

/*+ 
  NestLoop(ir d)  # 表示ir d两个表使用嵌套循环JOIN，如果用了别名，请使用别名。 

  NestLoop(ir d rd)  # 表示ir与d join完后再与rd JOIN。所以如果要固定JOIN顺序，可以分多个JOIN hint来写，就像上面这样。  

  Leading(((ir d) rd))    # 表示JOIN顺序和驱动顺序，每一对JOIN对象都需要用括号表示，hash 和 nestloop JOIN 请务必注意括号内的别名或表名顺序)。

  IndexScan(rd "def")   #  指定索引，如果表名使用了别名，请使用别名。    
  IndexScan(d "bcd") 
  IndexScan(ir "abc") 

  务必注意，如果对象名用了 小写和下划线 以外的字符。必须使用""引用起来。  
*/ 

三、 pg_hint_plan支持的Hint类型

支持的Hint有很多种，最新版参考pg_hint_plan文档

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.
	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.
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 (#<n>), addition (+<n>), subtract (-<n>) and multiplication (*<n>). <n> should be a string that strtod() can read.
GUC	Set(GUC-param value)	Set the GUC parameter to the value while planner is running.

例如

postgres=# /*+
postgres*#     Set(random_page_cost 2.0)
postgres*#  */
postgres-# SELECT * FROM table1 t1 WHERE key = 'value';

postgres=# /*+ Rows(a b #10) */ SELECT... ; Sets rows of join result to 10
postgres=# /*+ Rows(a b +10) */ SELECT... ; Increments row number by 10
postgres=# /*+ Rows(a b -10) */ SELECT... ; Subtracts 10 from the row number.
postgres=# /*+ Rows(a b *10) */ SELECT... ; Makes the number 10 times larger.

四、 内核实现

看完了形形色色的Hint，我们会想，这些Hint是怎么改变复杂的优化器逻辑，使其生成我们需要的查询计划的呢？我们从其源码看起（源码可以从这里下载）。

插件主要的代码集中在pg_hint_plan.c里面。从其中PG_init函数的代码可以看出，它利用了planner_hook（优化器的函数钩子，实际上是全局变量，存放函数地址，可以被插件更改，换成插件自定义函数的地址），用pg_hint_plan_planner取代了原来的优化器逻辑。这样PG在处理一个SQL时，将调用pg_hint_plan_planner来做优化。而pg_hint_plan_planner会调用get_hints_from_comment，来读取Hint，并调用create_hintstate进行语法分析。这里要说明的是，create_hintstate遇到一张表上的多个同类型Hint（包括重复的Hint），只保留最后一个，前面的会忽略。

另外，还有两个函数钩子被利用：get_relation_info_hook 和 join_search_hook。这两个钩子分别被修改指向了pg_hint_plan_get_relation_info和pg_hint_plan_join_search。前者是在优化器处理基本表（非视图、非函数的表）获取表信息时被调用，调用栈如下：

query_planner -> add_base_rels_to_query -> build_simple_rel -> get_relation_info -> get_relation_info_hook(即pg_hint_plan_get_relation_info)

这个pg_hint_plan_get_relation_info做了什么呢？仔细看会惊讶的发现，它是用来删除索引的！对，它在优化器获取表的基本信息后被调用，然后其从基本信息删除了那些在Hint中未使用的索引。例如，t1上有两个索引t1_i_a和t1_i_b，如果指定了IndexScan(t1 t_i_b)这个Hint，那么t1_i_a的索引信息在这里被删除，这样在后续的优化中，就永远不会考虑t1_i_a这个索引了！

再看pg_hint_plan_join_search，其被调用的位置如下：

query_planner -> make_one_rel -> make_rel_from_joinlist ->join_search_hook(即pg_hint_plan_join_search)

可见，它是在为一个SQL语句生成连接结果时被调用，其输入为待连接的表，输出为连接后生成的表及其最优的查询计划。它主要做了两件事：

1. 调用rebuild_scan_path重新生成基本表的访问路径。为什么要重新生成呢？因为在基本表的访问计划生成阶段，扫描类的Hint并未实际起作用（只是对索引做过删除处理）。例如，即使指定了IndexScan(t1 t1_i_a)，但外部的GUC变量enable_indexscan被设置为了off，在这里也只会看到一个表扫描（SeqScan）的查询计划。因此这里需要重新设置好GUC变量（例如如果遇到IndexScan Hint，需要把GUC变量enable_indexscan重置为on），再做一遍访问计划。由于基本表一般数量较少，访问计划也只需再生成一次，所以此步开销是可接受的；

2. 调用pg_hint_plan_standard_join_search生成连接的计划。这里是应用连接方法和连接顺序Hint的地方。要想改变连接方法或顺序，需要进一步修改优化器的整个逻辑，但优化器没那么多的预定义钩子可用了，采用函数钩子的方法不可行。于是，插件便“自备”了优化器的主流程代码（其实是从同版本的PG里面拷贝出来的），见插件代码中的core.c和make_join_rel.c两个文件。里面很多地方是被插件修改过的。其中核心的是修改对add_paths_to_joinrel的调用，使优化器实际调用add_paths_to_joinrel_wrapper。这个函数是用于为输入的两张表（可能是连接生成的中间表），生成一个连接计划。可以看到add_paths_to_joinrel_wrapper会先去查找有没有对应的Hint，如果有就直接利用，并舍弃掉不符合Hint的连接方法和顺序（这是连接顺序Hint其作用的地方）。

可以看到，此插件的实现并不复杂，它巧妙利用了优化器优化流程中的关键点，来应用Hint，达到固定查询计划的目的。

参考

AliCloudDB for PostgreSQL pg_hint_plan插件的用法-阿里云开发者社区

http://pghintplan.osdn.jp/pg_hint_plan.html
http://pghintplan.osdn.jp/hint_list.html
https://yq.aliyun.com/articles/17212

PgSQL · 特性分析 · Plan Hint