Postgresql 定制执行计划pg_hint_plan

一、概述

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

二、配置

在postgresql.conf中修改shared_preload_libraries=‘pg_hint_plan'

三、示例

1、初始化测试数据

create table t1 (id int, t int, name varchar(255));

create table t2 (id int , salary int);

create table t3 (id int , age int);

insert into t1 values (1,200,'jack');

insert into t1 values (2,300,'tom');

insert into t1 values (3,400,'john');

insert into t2 values (1,40000);

insert into t2 values (2,38000);

insert into t2 values (3,18000);

insert into t3 values (3,38);

insert into t3 values (2,55);

insert into t3 values (1,12);

explain analyze select * from t1 left join t2 on t1.id=t2.id left join t3 on t1.id=t3.id;

              QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------

 Hash Right Join (cost=89.82..337.92 rows=17877 width=540) (actual time=0.053..0.059 rows=3 loops=1)

 Hash Cond: (t3.id = t1.id)

 -> Seq Scan on t3 (cost=0.00..32.60 rows=2260 width=8) (actual time=0.002..0.002 rows=3 loops=1)

 -> Hash (cost=70.05..70.05 rows=1582 width=532) (actual time=0.042..0.043 rows=3 loops=1)

   Buckets: 2048 Batches: 1 Memory Usage: 17kB

   -> Hash Right Join (cost=13.15..70.05 rows=1582 width=532) (actual time=0.034..0.039 rows=3 loops=1)

    Hash Cond: (t2.id = t1.id)

    -> Seq Scan on t2 (cost=0.00..32.60 rows=2260 width=8) (actual time=0.002..0.002 rows=3 loops=1)

    -> Hash (cost=11.40..11.40 rows=140 width=524) (actual time=0.017..0.017 rows=3 loops=1)

      Buckets: 1024 Batches: 1 Memory Usage: 9kB

      -> Seq Scan on t1 (cost=0.00..11.40 rows=140 width=524) (actual time=0.010..0.011 rows=3 loops=1)

 Planning time: 0.154 ms

 Execution time: 0.133 ms

 

create index idx_t1_id on t1(id);

create index idx_t2_id on t2(id);

create index idx_t3_id on t3(id);

explain analyze select * from t1 left join t2 on t1.id=t2.id left join t3 on t1.id=t3.id;

             QUERY PLAN

--------------------------------------------------------------------------------------------------------------

 Hash Left Join (cost=2.14..3.25 rows=3 width=540) (actual time=0.045..0.047 rows=3 loops=1)

 Hash Cond: (t1.id = t3.id)

 -> Hash Left Join (cost=1.07..2.14 rows=3 width=532) (actual time=0.030..0.032 rows=3 loops=1)

   Hash Cond: (t1.id = t2.id)

   -> Seq Scan on t1 (cost=0.00..1.03 rows=3 width=524) (actual time=0.005..0.006 rows=3 loops=1)

   -> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.007..0.007 rows=3 loops=1)

    Buckets: 1024 Batches: 1 Memory Usage: 9kB

    -> Seq Scan on t2 (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=1)

 -> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.005..0.005 rows=3 loops=1)

   Buckets: 1024 Batches: 1 Memory Usage: 9kB

   -> Seq Scan on t3 (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.002 rows=3 loops=1)

 Planning time: 0.305 ms

 Execution time: 0.128 ms

2、强制走Index Scan

explain (analyze,buffers) /*+ indexscan(t1) */select * from t1 where id=2;

             QUERY PLAN

----------------------------------------------------------------------------------------------------------------

 Index Scan using idx_t1_id on t1 (cost=0.13..8.15 rows=1 width=524) (actual time=0.044..0.046 rows=1 loops=1)

 Index Cond: (id = 2)

 Buffers: shared hit=1 read=1

 Planning time: 0.145 ms

 Execution time: 0.072 ms

 

explain (analyze,buffers) /*+ indexscan(t1 idx_t1_id) */select * from t1 where id=2;

             QUERY PLAN

----------------------------------------------------------------------------------------------------------------

 Index Scan using idx_t1_id on t1 (cost=0.13..8.15 rows=1 width=524) (actual time=0.016..0.017 rows=1 loops=1)

 Index Cond: (id = 2)

 Buffers: shared hit=2

 Planning time: 0.079 ms

 Execution time: 0.035 ms

3、强制多条件组合

/*+ indexscan(t2) indexscan(t1 idx_t1_id) */

/*+ seqscan(t2) indexscan(t1 idx_t1_id) */

explain analyze SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);

            QUERY PLAN

--------------------------------------------------------------------------------------------------------

 Hash Join (cost=1.07..2.14 rows=3 width=532) (actual time=0.018..0.020 rows=3 loops=1)

 Hash Cond: (t1.id = t2.id)

 -> Seq Scan on t1 (cost=0.00..1.03 rows=3 width=524) (actual time=0.006..0.007 rows=3 loops=1)

 -> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.005..0.005 rows=3 loops=1)

   Buckets: 1024 Batches: 1 Memory Usage: 9kB

   -> Seq Scan on t2 (cost=0.00..1.03 rows=3 width=8) (actual time=0.001..0.003 rows=3 loops=1)

 Planning time: 0.114 ms

 Execution time: 0.055 ms

(8 rows)

 

/*+ indexscan(t2) indexscan(t1 idx_t1_id) */explain analyze SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);

              QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------

 Merge Join (cost=0.26..24.40 rows=3 width=532) (actual time=0.047..0.053 rows=3 loops=1)

 Merge Cond: (t1.id = t2.id)

 -> Index Scan using idx_t1_id on t1 (cost=0.13..12.18 rows=3 width=524) (actual time=0.014..0.015 rows=3 loops=1)

 -> Index Scan using idx_t2_id on t2 (cost=0.13..12.18 rows=3 width=8) (actual time=0.026..0.028 rows=3 loops=1)

 

/*+ seqscan(t2) indexscan(t1 idx_t1_id) */explain analyze SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);

              QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------

 Nested Loop (cost=0.13..13.35 rows=3 width=532) (actual time=0.025..0.032 rows=3 loops=1)

 Join Filter: (t1.id = t2.id)

 Rows Removed by Join Filter: 6

 -> Index Scan using idx_t1_id on t1 (cost=0.13..12.18 rows=3 width=524) (actual time=0.016..0.018 rows=3 loops=1)

 -> Materialize (cost=0.00..1.04 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=3)

   -> Seq Scan on t2 (cost=0.00..1.03 rows=3 width=8) (actual time=0.004..0.005 rows=3 loops=1)

 

4、强制指定join method

/*+ NestLoop(t1 t2) MergeJoin(t1 t2 t3) Leading(t1 t2 t3) */

explain analyze select * from t1 left join t2 on t1.id=t2.id left join t3 on t1.id=t3.id;

              QUERY PLAN

--------------------------------------------------------------------------------------------------------------------

 Merge Left Join (cost=3.28..3.34 rows=3 width=540) (actual time=0.093..0.096 rows=3 loops=1)

 Merge Cond: (t1.id = t3.id)

 -> Sort (cost=2.23..2.23 rows=3 width=532) (actual time=0.077..0.078 rows=3 loops=1)

   Sort Key: t1.id

   Sort Method: quicksort Memory: 25kB

   -> Nested Loop Left Join (cost=0.00..2.20 rows=3 width=532) (actual time=0.015..0.020 rows=3 loops=1)

    Join Filter: (t1.id = t2.id)

    Rows Removed by Join Filter: 6

    -> Seq Scan on t1 (cost=0.00..1.03 rows=3 width=524) (actual time=0.005..0.005 rows=3 loops=1)

    -> Materialize (cost=0.00..1.04 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=3)

      -> Seq Scan on t2 (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=1)

 -> Sort (cost=1.05..1.06 rows=3 width=8) (actual time=0.012..0.013 rows=3 loops=1)

   Sort Key: t3.id

   Sort Method: quicksort Memory: 25kB

   -> Seq Scan on t3 (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=1)

 

/*+ NestLoop(t1 t2 t3) MergeJoin(t2 t3) Leading(t1 (t2 t3)) */

explain analyze select * from t1 left join t2 on t1.id=t2.id left join t3 on t1.id=t3.id;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------

 Nested Loop Left Join (cost=1.07..3.31 rows=3 width=540) (actual time=0.036..0.041 rows=3 loops=1)

 Join Filter: (t1.id = t3.id)

 Rows Removed by Join Filter: 6

 -> Hash Left Join (cost=1.07..2.14 rows=3 width=532) (actual time=0.030..0.032 rows=3 loops=1)

   Hash Cond: (t1.id = t2.id)

   -> Seq Scan on t1 (cost=0.00..1.03 rows=3 width=524) (actual time=0.008..0.009 rows=3 loops=1)

   -> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.007..0.007 rows=3 loops=1)

    Buckets: 1024 Batches: 1 Memory Usage: 9kB

    -> Seq Scan on t2 (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.004 rows=3 loops=1)

 -> Materialize (cost=0.00..1.04 rows=3 width=8) (actual time=0.001..0.002 rows=3 loops=3)

   -> Seq Scan on t3 (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=1)

5、控制单条SQL的cost

/*+ set(seq_page_cost 20.0) seqscan(t1) */

/*+ set(seq_page_cost 20.0) seqscan(t1) */explain analyze select * from t1 where id > 1;

           QUERY PLAN

-----------------------------------------------------------------------------------------------

 Seq Scan on t1 (cost=0.00..20.04 rows=1 width=524) (actual time=0.011..0.013 rows=2 loops=1)

 Filter: (id > 1)

 Rows Removed by Filter: 1