有的时候PG给出的执行计划由于很多原因并不是最优的，需要手动指定执行路径时我们可以加载pg_hint_plan这个插件。

1 安装插件

预先安装Postgresql10.7

cd postgresql-10.7/contrib/
wget https://github.com/ossc-db/pg_hint_plan/archive/REL10_1_3_3.tar.gz
tar xzvf pg_hint_plan-REL10_1_3_3.tar.gz
cd pg_hint_plan-REL10_1_3_3
make
make install

检查文件

cd $PGHOME
ls lib/pg_hint_plan.so
lib/pg_hint_plan.so
ls share/extension/
pg_hint_plan--1.3.0--1.3.1.sql pg_hint_plan--1.3.2--1.3.3.sql pg_hint_plan.control plpgsql.control
pg_hint_plan--1.3.1--1.3.2.sql pg_hint_plan--1.3.3.sql   plpgsql--1.0.sql  plpgsql--unpackaged--1.0.sql

 

2 加载插件

2.1 当前会话加载

1LOAD 'pg_hint_plan';

注意这样加载只在当前回话生效。

2.2 用户、库级自动加载

alter user postgres set session_preload_libraries='pg_hint_plan';
alter database postgres set session_preload_libraries='pg_hint_plan';

配置错了的话就连不上数据库了！

如果配置错了，连接template1库执行

alter database postgres reset session_preload_libraries;
alter user postgres reset session_preload_libraries;

2.3 cluster级自动加载

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

重启数据库

3 检查是否已经加载

pg_hint_plan加载后在extension里面是看不到的，所以需要确认插件是否已经加载

show session_preload_libraries;
 session_preload_libraries
---------------------------
 pg_hint_plan

 

或者

1show shared_preload_libraries;

如果使用load方式加载不需要检查。

4 使用插件定制执行计划

4.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