本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

pg15安装pg_hint_plan扩展包

pg当前是支持HINT固定执行计划，需要通过扩展包pg_hint_plan来实现

一、扩展包下载：

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

二、选择v15版本

pg_hint_plan15 1.5.1 is released
pg_hint_plan15 1.5.1 is released. This version only supports PostgreSQL 15.
Some changes are made in this release:
    Fix hint stack corruption on ERROR when setting GUCs from Set hints (Michael Paquier: 61a3a55)
    Fix handling of unavailable indexes in Scan hints (Sami Imseih: 33adb40)
    Reset more aggressively hints for queries executed via extended query protocol (tanujnay112: de709e6)
    Add EXPLAIN (COSTS false) to some tests (Masahiro Ikeda: ffd7f62)
    Bootstrap a new documentation (Julien Rouhaud: 287e9b5, and more). This removes the HTML documentation, switching to a set of markdown files with support for multiple languages possible.
    Fix and improve documentation (Michael Paquier: ea8616b)

下载源码包（任选其一）

三、上传postgres用户下

[postgres@ora19c02 ~]$ ll -d pg_hint_plan-REL15_1_5_1.zip 
-rw-r--r-- 1 postgres postgres 227951 Nov  1 15:21 pg_hint_plan-REL15_1_5_1.zip

四、解压包

unzip pg_hint_plan-REL15_1_5_1.zip

五、进入解压目录，并编绎

[postgres@ora19c02 pg_hint_plan-REL15_1_5_1]$ make 
[postgres@ora19c02 pg_hint_plan-REL15_1_5_1]$ make install

编绎可能会碰到的问题

1）make时提示命令找不到

/bin/sh: rpmbuild: command not found

解决
  yum install rpm-build

2）权限不足

[postgres@ora19c02 pg_hint_plan-REL15_1_5_1]$ make install
/usr/bin/mkdir -p '/usr/local/postgres/share/extension'
/usr/bin/mkdir -p '/usr/local/postgres/share/extension'
/usr/bin/mkdir -p '/usr/local/postgres/lib'
/usr/bin/install -c -m 644 .//pg_hint_plan.control '/usr/local/postgres/share/extension/'
/usr/bin/install: cannot create regular file ‘/usr/local/postgres/share/extension/pg_hint_plan.control’: Permission denied
make: *** [install] Error 1

授权解决

[root@ora19c02 ]# chmod 777 /usr/local/postgres -R

六、验证安装

[postgres@ora19c02 pg_hint_plan-REL15_1_5_1]$ ls -l /usr/local/postgres/lib/ | grep hint
-rwxr-xr-x 1 postgres postgres  357016 Nov  1 15:31 pg_hint_plan.so

[postgres@ora19c02 pg_hint_plan-REL15_1_5_1]$ ls -l /usr/local/postgres/share/extension/ | grep hint
-rw-r--r-- 1 postgres postgres   437 Nov  1 15:31 pg_hint_plan--1.3.0--1.3.1.sql
-rw-r--r-- 1 postgres postgres   684 Nov  1 15:31 pg_hint_plan--1.3.0.sql
-rw-r--r-- 1 postgres postgres   437 Nov  1 15:31 pg_hint_plan--1.3.1--1.3.2.sql
-rw-r--r-- 1 postgres postgres   437 Nov  1 15:31 pg_hint_plan--1.3.2--1.3.3.sql
-rw-r--r-- 1 postgres postgres   437 Nov  1 15:31 pg_hint_plan--1.3.3--1.3.4.sql
-rw-r--r-- 1 postgres postgres   437 Nov  1 15:31 pg_hint_plan--1.3.4--1.3.5.sql
-rw-r--r-- 1 postgres postgres   437 Nov  1 15:31 pg_hint_plan--1.3.5--1.3.6.sql
-rw-r--r-- 1 postgres postgres   437 Nov  1 15:31 pg_hint_plan--1.3.6--1.3.7.sql
-rw-r--r-- 1 postgres postgres   437 Nov  1 15:31 pg_hint_plan--1.3.7--1.3.8.sql
-rw-r--r-- 1 postgres postgres   437 Nov  1 15:31 pg_hint_plan--1.3.8--1.3.9.sql
-rw-r--r-- 1 postgres postgres   433 Nov  1 15:31 pg_hint_plan--1.3.9--1.4.sql
-rw-r--r-- 1 postgres postgres   437 Nov  1 15:31 pg_hint_plan--1.4.1--1.4.2.sql
-rw-r--r-- 1 postgres postgres   435 Nov  1 15:31 pg_hint_plan--1.4--1.4.1.sql
-rw-r--r-- 1 postgres postgres   434 Nov  1 15:31 pg_hint_plan--1.4.2--1.5.sql
-rw-r--r-- 1 postgres postgres   436 Nov  1 15:31 pg_hint_plan--1.5--1.5.1.sql
-rw-r--r-- 1 postgres postgres   104 Nov  1 15:31 pg_hint_plan.control

七、设置插件

7.1 会话级别设置

postgres=# LOAD 'pg_hint_plan';
LOAD

如果报错，在template1数据库下执行以下SQL

testdb=# \c template1
template1=# create extension pg_hint_plan;
CREATE EXTENSION

7.2 用户级别设置

postgres=# alter user postgres set session_preload_libraries='pg_hint_plan';
ALTER ROLE

7.3 数据库级别设置

postgres=# alter database postgres set session_preload_libraries='pg_hint_plan';
ALTER DATABASE

7.4 集群级别设置

修改参数文件

[postgres@ora19c02 data]$ vi postgresql.conf 
shared_preload_libraries = 'pg_hint_plan'       # (change requires restart)

重启数据库

pg_ctl restart

7.5 重置配置

配置错了的话就连不上数据库，如果配置错了，连接template1库执行

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

八、测试HINT功能

8.1 查看参数

testdb=# show session_preload_libraries;
 session_preload_libraries 
---------------------------
 pg_hint_plan

8.2 、模拟数据

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);

8.3 执行SQL，查看默认执行计划

testdb=# 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.088..0.098 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.012..0.015 rows=14
 loops=1)
   ->  Hash  (cost=14.10..14.10 rows=410 width=170) (actual time=0.016..0.017 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.009..0.009 
rows=4 loops=1)
 Planning Time: 1.149 ms
 Execution Time: 0.182 ms
(8 rows)

以上输出可以看到，默认的执行计划走HJ

8.4 模拟NL

testdb=# explain analyze select /*+ nestloop(a b)*/* from emp a,dept b where a.deptno=b.deptno;
                                                        QUERY PLAN                               
                          
-------------------------------------------------------------------------------------------------
--------------------------
 Nested Loop  (cost=0.15..147.14 rows=510 width=300) (actual time=0.040..0.056 rows=14 loops=1)
   ->  Seq Scan on emp a  (cost=0.00..15.10 rows=510 width=130) (actual time=0.009..0.011 rows=14
 loops=1)
   ->  Index Scan using dept_pkey on dept b  (cost=0.15..0.26 rows=1 width=170) (actual time=0.00
2..0.002 rows=1 loops=14)
         Index Cond: (deptno = a.deptno)
 Planning Time: 0.166 ms
 Execution Time: 0.113 ms
(6 rows)

8.5 模拟MJ

testdb=# 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.168..0.178 rows=14 loops=1)
   Merge Cond: (a.deptno = b.deptno)
   ->  Sort  (cost=38.04..39.31 rows=510 width=130) (actual time=0.142..0.146 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.014..0.019 r
ows=14 loops=1)
   ->  Sort  (cost=31.89..32.92 rows=410 width=170) (actual time=0.020..0.020 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.010..0.011 
rows=4 loops=1)
 Planning Time: 1.826 ms
 Execution Time: 0.417 ms
(12 rows)