PostgreSQL的查询提示(hint)扩展pg_hint_plan的使用

一条SQL语句采用哪个执行计划是由优化器根据代价估算自动选择的，但如果希望使用指定的执行计划，就需要进行干预。Oracle中通过提示(hint)干预特点SQL的执行计划，PostgreSQL本身也可以通过enable_bitmapscan之类的参数进行调节，但这些参数是针对会话的，对优化器的控制没有提示来的直接，也没有提示那么精细。
pg_hint_plan是2012年12月公开的一个开源PostgreSQL扩展，其作用于Oracle的Hint相同，并且使用方法也是一样的。下面演示一下pg_hint_plan的使用。

安装
从pg_hint_plan的站点()下载源码。
目前pg_hint_plan只支持PostgreSQL 9.1和9.2，这里使用的是9.2。

#tar xzf pg_hint_plan.tar.gz
#cd pg_hint_pla
#make USE_PGXS=1 PG_CONFIG=/usr/pgsql-9.2/bin/pg_config
#make USE_PGXS=1 PG_CONFIG=/usr/pgsql-9.2/bin/pg_config install

修改配置加载pg_hint_plan
#pg_ctl -D $PGDATA restart
#vi /pg_hba.conf
shared_preload_libraries = 'pg_hint_plan'

重启PG后就可以使用pg_hint_plan了
#pg_ctl -D $PGDATA restart

使用
postgres=# CREATE TABLE tbl(c1 int);
CREATE TABLE
postgres=# INSERT INTO tbl SELECT * from generate_series(1, 10);
INSERT 0 10
postgres=# CREATE INDEX idx ON tbl (c1);
CREATE INDEX
postgres=# VACUUM ANALYZE tbl;
VACUUM

由于记录少默认使用全表扫描。
postgres=# EXPLAIN SELECT * FROM tbl WHERE c1 = 1;
                    QUERY PLAN                    
---------------------------------------------------
 Seq Scan on tbl  (cost=0.00..1.12 rows=1 width=4)
   Filter: (c1 = 1)
(2 rows)

通过hint强制使用索引扫描
postgres=# /*+ IndexScan(tbl) */  EXPLAIN SELECT * FROM tbl WHERE c1 = 1;
                          QUERY PLAN                          
---------------------------------------------------------------
 Index Scan using idx on tbl  (cost=0.00..8.27 rows=1 width=4)
   Index Cond: (c1 = 1)
(2 rows)

pg_hint_plan支持很多提示语句，比如
扫描方式：
    SeqScan(table)
    TidScan(table)
    IndexScan(table[index])
    IndexOnlyScan(table[index])
    BitmapScan(table[index])

连接方式：                
    NestLoop(table table [table...])
    HashJoin(table table [table...])
    MergeJoin(table table [table...])

连接顺序:    
    Leading(table table [table...])

详细可参考pg_hint_plan源码doc目录中的手册

其他:
hint也不是万灵药，PostgreSQL社区的意见认为使用hint这样的机制干预优化器可能会带来维护麻烦，升级失效等一系列的问题。基于这样的考虑，估计PG社区将来也不准备把hint加入到PG本体中。相关讨论参见:http://wiki.postgresql.org/wiki/OptimizerHintsDiscussion