• 云贝教育 |【PostgreSQL PGCA】pg15安装pg_hint_plan扩展包


    pg15安装pg_hint_plan扩展包

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

    一、扩展包下载:

    Releases · ossc-db/pg_hint_plan · GitHub

    二、选择v15版本
    1. pg_hint_plan15 1.5.1 is released
    2. pg_hint_plan15 1.5.1 is released. This version only supports PostgreSQL 15.
    3. Some changes are made in this release:
    4. Fix hint stack corruption on ERROR when setting GUCs from Set hints (Michael Paquier: 61a3a55)
    5. Fix handling of unavailable indexes in Scan hints (Sami Imseih: 33adb40)
    6. Reset more aggressively hints for queries executed via extended query protocol (tanujnay112: de709e6)
    7. Add EXPLAIN (COSTS false) to some tests (Masahiro Ikeda: ffd7f62)
    8. 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.
    9. Fix and improve documentation (Michael Paquier: ea8616b)

    下载源码包(任选其一)

    图片

    三、上传postgres用户下
    1. [postgres@ora19c02 ~]$ ll -d pg_hint_plan-REL15_1_5_1.zip
    2. -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

    五、进入解压目录,并编绎
    1. [postgres@ora19c02 pg_hint_plan-REL15_1_5_1]$ make
    2. [postgres@ora19c02 pg_hint_plan-REL15_1_5_1]$ make install

    编绎可能会碰到的问题:

    1)make时提示命令找不到

    1. /bin/sh: rpmbuild: command not found
    2. 解决
    3. yum install rpm-build

    2)权限不足

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

    授权解决

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

    六、验证安装
    1. [postgres@ora19c02 pg_hint_plan-REL15_1_5_1]$ ls -l /usr/local/postgres/lib/ | grep hint
    2. -rwxr-xr-x 1 postgres postgres 357016 Nov 1 15:31 pg_hint_plan.so
    3. [postgres@ora19c02 pg_hint_plan-REL15_1_5_1]$ ls -l /usr/local/postgres/share/extension/ | grep hint
    4. -rw-r--r-- 1 postgres postgres 437 Nov 1 15:31 pg_hint_plan--1.3.0--1.3.1.sql
    5. -rw-r--r-- 1 postgres postgres 684 Nov 1 15:31 pg_hint_plan--1.3.0.sql
    6. -rw-r--r-- 1 postgres postgres 437 Nov 1 15:31 pg_hint_plan--1.3.1--1.3.2.sql
    7. -rw-r--r-- 1 postgres postgres 437 Nov 1 15:31 pg_hint_plan--1.3.2--1.3.3.sql
    8. -rw-r--r-- 1 postgres postgres 437 Nov 1 15:31 pg_hint_plan--1.3.3--1.3.4.sql
    9. -rw-r--r-- 1 postgres postgres 437 Nov 1 15:31 pg_hint_plan--1.3.4--1.3.5.sql
    10. -rw-r--r-- 1 postgres postgres 437 Nov 1 15:31 pg_hint_plan--1.3.5--1.3.6.sql
    11. -rw-r--r-- 1 postgres postgres 437 Nov 1 15:31 pg_hint_plan--1.3.6--1.3.7.sql
    12. -rw-r--r-- 1 postgres postgres 437 Nov 1 15:31 pg_hint_plan--1.3.7--1.3.8.sql
    13. -rw-r--r-- 1 postgres postgres 437 Nov 1 15:31 pg_hint_plan--1.3.8--1.3.9.sql
    14. -rw-r--r-- 1 postgres postgres 433 Nov 1 15:31 pg_hint_plan--1.3.9--1.4.sql
    15. -rw-r--r-- 1 postgres postgres 437 Nov 1 15:31 pg_hint_plan--1.4.1--1.4.2.sql
    16. -rw-r--r-- 1 postgres postgres 435 Nov 1 15:31 pg_hint_plan--1.4--1.4.1.sql
    17. -rw-r--r-- 1 postgres postgres 434 Nov 1 15:31 pg_hint_plan--1.4.2--1.5.sql
    18. -rw-r--r-- 1 postgres postgres 436 Nov 1 15:31 pg_hint_plan--1.5--1.5.1.sql
    19. -rw-r--r-- 1 postgres postgres 104 Nov 1 15:31 pg_hint_plan.control

    七、设置插件

    7.1 会话级别设置

    1. postgres=# LOAD 'pg_hint_plan';
    2. LOAD

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

    1. testdb=# \c template1
    2. template1=# create extension pg_hint_plan;
    3. CREATE EXTENSION

    7.2 用户级别设置

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

    7.3 数据库级别设置

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

    7.4 集群级别设置

    修改参数文件

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

    重启数据库

    pg_ctl restart

    7.5 重置配置

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

    1. alter database postgres reset session_preload_libraries;
    2. alter user postgres reset session_preload_libraries;

    八、测试HINT功能

    8.1 查看参数

    1. testdb=# show session_preload_libraries;
    2. session_preload_libraries
    3. ---------------------------
    4. pg_hint_plan

    8.2 、模拟数据

    1. CREATE TABLE
    2. IF NOT EXISTS dept (
    3. -- 部门编号
    4. deptno serial PRIMARY KEY,
    5. -- 部门名称
    6. dname VARCHAR (15),
    7. -- 部门所在位置
    8. loc VARCHAR (50)
    9. );
    10. CREATE TABLE
    11. IF NOT EXISTS emp (
    12. -- 雇员编号
    13. empno serial,
    14. -- 雇员姓名
    15. ename VARCHAR (15),
    16. -- 雇员职位
    17. job VARCHAR (10),
    18. -- 雇员对应的领导的编号
    19. mgr INT,
    20. -- 雇员的雇佣日期
    21. hiredate DATE,
    22. -- 雇员的基本工资
    23. sal DECIMAL (7, 2),
    24. -- 奖金
    25. comm DECIMAL (7, 2),
    26. -- 所在部门
    27. deptno INT,
    28. FOREIGN KEY (deptno) REFERENCES dept (deptno)
    29. );
    30. -- dept表中的数据
    31. INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
    32. INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
    33. INSERT INTO dept VALUES (30,'SALES','CHICAGO');
    34. INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
    35. -- emp表中的数据
    36. INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('1980-12-17','yyyy-mm-dd'),800,NULL,20);
    37. INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('1981-2-20','yyyy-mm-dd'),1600,300,30);
    38. INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('1981-2-22','yyyy-mm-dd'),1250,500,30);
    39. INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('1981-4-2','yyyy-mm-dd'),2975,NULL,20);
    40. INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('1981-9-28','yyyy-mm-dd'),1250,1400,30);
    41. INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1981-5-1','yyyy-mm-dd'),2850,NULL,30);
    42. INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('1981-6-9','yyyy-mm-dd'),2450,NULL,10);
    43. INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('87-7-13','yyyy-mm-dd'),3000,NULL,20);
    44. INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('1981-11-17','yyyy-mm-dd'),5000,NULL,10);
    45. INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('1981-9-8','yyyy-mm-dd'),1500,0,30);
    46. INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('87-7-13','yyyy-mm-dd'),1100,NULL,20);
    47. INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('1981-12-3','yyyy-mm-dd'),950,NULL,30);
    48. INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('1981-12-3','yyyy-mm-dd'),3000,NULL,20);
    49. INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('1982-1-23','yyyy-mm-dd'),1300,NULL,10);

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

    1. testdb=# explain analyze select * from emp a,dept b where a.deptno=b.deptno;
    2. QUERY PLAN
    3. -----------------------------------------------------------------------------------------------------------------
    4. Hash Join (cost=19.23..35.67 rows=510 width=300) (actual time=0.088..0.098 rows=14 loops=1)
    5. Hash Cond: (a.deptno = b.deptno)
    6. -> Seq Scan on emp a (cost=0.00..15.10 rows=510 width=130) (actual time=0.012..0.015 rows=14
    7. loops=1)
    8. -> Hash (cost=14.10..14.10 rows=410 width=170) (actual time=0.016..0.017 rows=4 loops=1)
    9. Buckets: 1024 Batches: 1 Memory Usage: 9kB
    10. -> Seq Scan on dept b (cost=0.00..14.10 rows=410 width=170) (actual time=0.009..0.009
    11. rows=4 loops=1)
    12. Planning Time: 1.149 ms
    13. Execution Time: 0.182 ms
    14. (8 rows)

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

    8.4 模拟NL

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

    8.5 模拟MJ

    1. testdb=# explain analyze select /*+ mergejoin(a b)*/* from emp a,dept b where a.deptno=b.deptno;
    2. QUERY PLAN
    3. -----------------------------------------------------------------------------------------------------------------
    4. Merge Join (cost=69.93..79.63 rows=510 width=300) (actual time=0.168..0.178 rows=14 loops=1)
    5. Merge Cond: (a.deptno = b.deptno)
    6. -> Sort (cost=38.04..39.31 rows=510 width=130) (actual time=0.142..0.146 rows=14 loops=1)
    7. Sort Key: a.deptno
    8. Sort Method: quicksort Memory: 26kB
    9. -> Seq Scan on emp a (cost=0.00..15.10 rows=510 width=130) (actual time=0.014..0.019 r
    10. ows=14 loops=1)
    11. -> Sort (cost=31.89..32.92 rows=410 width=170) (actual time=0.020..0.020 rows=3 loops=1)
    12. Sort Key: b.deptno
    13. Sort Method: quicksort Memory: 25kB
    14. -> Seq Scan on dept b (cost=0.00..14.10 rows=410 width=170) (actual time=0.010..0.011
    15. rows=4 loops=1)
    16. Planning Time: 1.826 ms
    17. Execution Time: 0.417 ms
    18. (12 rows)

    图片

  • 相关阅读:
    机关事务管理局数字化平台,让数据纵向直报,业务横向打通
    带你了解ASO 与 SEO的区别
    11.13 训练周记
    基于jsp+mysql+ssm峰值预警停车场管理系统-计算机毕业设计
    前端教程-vite
    MySQL MHA
    Linux用户管理
    Linux入门学习指南
    Qt 5.15.2 Windows 子目录项目: undefined reference to `vtable for xxx‘
    Lazada双11大促玩法规则解读,2022备战清单来袭!
  • 原文地址:https://blog.csdn.net/yunbee666/article/details/134269923