postgresql数据库hint功能扩展包pg_hint_plan安装步骤

在pg数据库中不像oracle和mysql可以直接使用hint来固定SQL的执行计划。如果想要使用pg数据库中的hint功能来实现固定SQL的执行计划
需要安装PG数据库的hint功能扩展包，下载地址：
https://zh.osdn.net/projects/pghintplan/releases/
或者
https://github.com/ossc-db/pg_hint_plan/releases

1 插件安装

从上面的下载地址下载对应自己数据库版本的pg_hint_plan扩展包

上传到pg数据库所在的服务器上然后解压
我安装的数据库版本是12的所以我下载的是pg_hint_plan-REL12_1_3_5.tar.gz这个压缩包
解压

[root@postgres pgsql]#tar -xzvf pg_hint_plan-REL12_1_3_5.tar.gz
[root@postgres pgsql]#cd pg_hint_plan-REL12_1_3_5
[root@postgres pgsql]#make 
[root@postgres pgsql]#make install

安装完之后需要检查几个文件查看是否存在

[root@postgres pgsql]# ls -l /usr/local/pgsql/lib |grep -i hint

-rwxr-xr-x 1 postgres postgres  85409 4月  30 00:24 pg_hint_plan.so


[root@postgres pgsql]# ls -l /usr/local/pgsql/share/extension |grep -i hint

-rw-r--r-- 1 postgres postgres 545 4月  30 01:16 pg_hint_plan--1.3.5.sql
-rw-r--r-- 1 postgres postgres 104 4月  30 01:15 pg_hint_plan.control

2 加载插件

2.1 当前会话加载

LOAD 'pg_hint_plan';

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

如果出现以下错误： ERROR: relation “hint_plan.hints” does not exist

在template1数据库下执行以下SQL
create extension 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级自动加载

在postgresql.conf中修改shared_preload_libraries=‘pg_hint_plan’
用/shared_preload_libraries搜索这个参数，去掉前面的#注释，
修改为shared_preload_libraries=‘pg_hint_plan’

vi /usr/local/pgsql/data/postgresql.conf

#shared_preload_libraries = ''  # (change requires restart)
#local_preload_libraries = ''
#session_preload_libraries = ''
#jit_provider = 'llvmjit'               # JIT library to use

# - Other Defaults -

3 重启数据库检查是否已经加载

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

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

或者

show shared_preload_libraries;
shared_preload_libraries 
--------------------------
 pg_hint_plan

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

4 使用插件定制执行计划

利用如下脚本创建测试表

CREATE TABLE
IF NOT EXISTS dept (
    -- 部门编号
    deptno serial PRIMARY KEY,
    -- 部门名称
    dname VARCHAR (15),
    -- 部门所在位置
    loc VARCHAR (50)
);

CREATE TABLE
IF NOT EXISTS emp (
    -- 雇员编号
    empno serial,
    -- 雇员姓名
    ename VARCHAR (15),
    -- 雇员职位
    job VARCHAR (10),
    -- 雇员对应的领导的编号
    mgr INT,
    -- 雇员的雇佣日期
    hiredate DATE,
    -- 雇员的基本工资
    sal DECIMAL (7, 2),
    -- 奖金
    comm DECIMAL (7, 2),
    -- 所在部门
    deptno INT,
    FOREIGN KEY (deptno) REFERENCES dept (deptno)
);

-- dept表中的数据
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
-- emp表中的数据
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('1980-12-17','yyyy-mm-dd'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('1981-2-20','yyyy-mm-dd'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('1981-2-22','yyyy-mm-dd'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('1981-4-2','yyyy-mm-dd'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('1981-9-28','yyyy-mm-dd'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1981-5-1','yyyy-mm-dd'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('1981-6-9','yyyy-mm-dd'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('87-7-13','yyyy-mm-dd'),3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('1981-11-17','yyyy-mm-dd'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('1981-9-8','yyyy-mm-dd'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('87-7-13','yyyy-mm-dd'),1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('1981-12-3','yyyy-mm-dd'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('1981-12-3','yyyy-mm-dd'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('1982-1-23','yyyy-mm-dd'),1300,NULL,10);

然后查看不使用hint的执行计划

template1=# explain analyze select * from emp a,dept b where a.deptno=b.deptno;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=19.23..35.67 rows=510 width=300) (actual time=0.061..0.069 rows=14 loops=1)
   Hash Cond: (a.deptno = b.deptno)
   ->  Seq Scan on emp a  (cost=0.00..15.10 rows=510 width=130) (actual time=0.006..0.007 rows=14 loops=1)
   ->  Hash  (cost=14.10..14.10 rows=410 width=170) (actual time=0.039..0.039 rows=4 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on dept b  (cost=0.00..14.10 rows=410 width=170) (actual time=0.025..0.026 rows=4 loops=1)
 Planning Time: 0.301 ms
 Execution Time: 0.097 ms
(8 rows)

这时候执行计划中使用的是hash的方式连接
下面加上hint让SQL使用merge的方式连接试试

template1=# explain analyze select /*+ mergejoin(a b)*/* from emp a,dept b where a.deptno=b.deptno;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=69.93..79.63 rows=510 width=300) (actual time=0.076..0.085 rows=14 loops=1)
   Merge Cond: (a.deptno = b.deptno)
   ->  Sort  (cost=38.04..39.31 rows=510 width=130) (actual time=0.032..0.033 rows=14 loops=1)
         Sort Key: a.deptno
         Sort Method: quicksort  Memory: 26kB
         ->  Seq Scan on emp a  (cost=0.00..15.10 rows=510 width=130) (actual time=0.005..0.007 rows=14 loops=1)
   ->  Sort  (cost=31.89..32.92 rows=410 width=170) (actual time=0.007..0.007 rows=3 loops=1)
         Sort Key: b.deptno
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on dept b  (cost=0.00..14.10 rows=410 width=170) (actual time=0.002..0.003 rows=4 loops=1)
 Planning Time: 0.288 ms
 Execution Time: 0.180 ms
(12 rows)

这时候可以看到执行计划已经按照我们想要的走了merge连接。