pg15安装pg_hint_plan扩展包
pg当前是支持HINT固定执行计划,需要通过扩展包pg_hint_plan来实现
一、扩展包下载:
Releases · ossc-db/pg_hint_plan · GitHub
- 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@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;
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)