pg_hint_plan

• 安装

https://github.com/ossc-db/pg_hint_plan/releases

 

# export PATH= /home/postgres/pgsql/bin:$PATH

# gmake clean 

# gmake 

# gmake install 

 

vi $PGDATA/postgresql.conf  

shared_preload_libraries = 'pg_hint_plan'  

pg_hint_plan.enable_hint = on  

pg_hint_plan.debug_print = on  

pg_hint_plan.message_level = log 

 

postgres=# create extension pg_hint_plan; 

 

• 使用pg_hint_plan来改变PostgreSQL的执行计划

postgres=# /*+                                   

  HashJoin(a b)  

  SeqScan(b)  

*/ explain select a.*,b.* from a,b where a.id=b.id and a.id<10;  

                                 QUERY PLAN                                    

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

 Hash Join  (cost=8.56..1616.65 rows=9 width=94)  

   Hash Cond: (b.id = a.id)  

   ->  Seq Scan on b  (cost=0.00..1233.00 rows=100000 width=47)  

   ->  Hash  (cost=8.45..8.45 rows=9 width=47)  

         ->  Index Scan using a_pkey on a  (cost=0.29..8.45 rows=9 width=47)  

               Index Cond: (id < 10)  

(6 rows)  

postgres=# /*+ SeqScan(a) */ explain select * from a where id<10;  

                      QUERY PLAN                        

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

 Seq Scan on a  (cost=0.00..1483.00 rows=10 width=47)  

   Filter: (id < 10)  

(2 rows)  

postgres=# /*+ BitmapScan(a) */ explain select * from a where id<10;  

                             QUERY PLAN                                

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

 Bitmap Heap Scan on a  (cost=4.36..35.17 rows=9 width=47)  

   Recheck Cond: (id < 10)  

   ->  Bitmap Index Scan on a_pkey  (cost=0.00..4.36 rows=9 width=0)  

         Index Cond: (id < 10)  

(4 rows)  

 

其他看doc目录下的文档

• 参考资料

https://github.com/digoal/blog/blob/master/201602/20160203_01.md?spm=a2c4e.11153940.blogcont68244.70.29035625nnzqPA&file=20160203_01.md