Ubuntu安装pg_hint_plan插件

最新推荐文章于 2024-08-20 10:15:40 发布

Jackson_rw

最新推荐文章于 2024-08-20 10:15:40 发布

阅读量450

点赞数

分类专栏： Postgrelsql系列文章标签： ubuntu postgresql 数据库

本文链接：https://blog.csdn.net/cuirise/article/details/113478060

版权

Postgrelsql系列专栏收录该内容

3 篇文章 0 订阅

订阅专栏

安装pg_hint_plan插件

准备
安装
测试
实验

准备

pg_hint_plan-REL10_1_3_6.tar
（https://download.csdn.net/download/cuirise/25414018）

安装

依次执行以下命令

tar -xzvf pg_hint_plan-REL10_1_3_6.tar.gz

cd pg_hint_plan-REL10_1_3_6

make

make install

注意：请提前安装make和gcc

apt install make
apt install gcc

测试

依次输入命令

ls -l /usr/lib/postgresql/10/lib |grep -i hint

应输出如下内容，即成功

-rwxr-xr-x 1 root root 308472 Sep 27 08:57 pg_hint_plan.so

输入命令

ls -l /usr/share/postgresql/10/extension |grep -i hint

应输出如下内容，即成功

-rw-r--r-- 1 root root   233 Sep 27 08:57 pg_hint_plan--1.3.0--1.3.1.sql
-rw-r--r-- 1 root root   233 Sep 27 08:57 pg_hint_plan--1.3.1--1.3.2.sql
-rw-r--r-- 1 root root   233 Sep 27 08:57 pg_hint_plan--1.3.2--1.3.3.sql
-rw-r--r-- 1 root root   233 Sep 27 08:57 pg_hint_plan--1.3.3--1.3.5.sql
-rw-r--r-- 1 root root   333 Sep 27 08:57 pg_hint_plan--1.3.5--1.3.6.sql
-rw-r--r-- 1 root root   685 Sep 27 08:57 pg_hint_plan--1.3.6.sql
-rw-r--r-- 1 root root   104 Sep 27 08:57 pg_hint_plan.control

注意输入的文件路径可能有所不同，请参考如下make install后显示的内容，如：

/bin/mkdir -p '/usr/share/postgresql/10/extension'
/bin/mkdir -p '/usr/share/postgresql/10/extension'
/bin/mkdir -p '/usr/lib/postgresql/10/lib'
/usr/bin/install -c -m 644 .//pg_hint_plan.control '/usr/share/postgresql/10/extension/'
/usr/bin/install -c -m 644 .//pg_hint_plan--*.sql  '/usr/share/postgresql/10/extension/'
/usr/bin/install -c -m 755  pg_hint_plan.so '/usr/lib/postgresql/10/lib/'

实验

创建实验表

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

不使用插件来建立查询

explain analyze select * from emp a,dept b where a.deptno=b.deptno;

显示如下执行计划，其中优化器给出的连接算法为hash join

Hash Join  (cost=19.23..35.67 rows=510 width=300) (actual time=0.016..0.021 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.005..0.006 rows=14 loops=1)
  ->  Hash  (cost=14.10..14.10 rows=410 width=170) (actual time=0.005..0.005 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.002..0.003 rows=4 loops=1)
Planning time: 0.099 ms
Execution time: 0.039 ms

使用插件建立查询

explain analyze select /*+ NestLoop(a b)*/* from emp a,dept b where a.deptno=b.deptno;

显示如下执行计划，其中指定使用NestLoop

Nested Loop  (cost=0.15..147.14 rows=510 width=300) (actual time=0.016..0.030 rows=14 loops=1)
  ->  Seq Scan on emp a  (cost=0.00..15.10 rows=510 width=130) (actual time=0.007..0.008 rows=14 loops=1)
  ->  Index Scan using dept_pkey on dept b  (cost=0.15..0.26 rows=1 width=170) (actual time=0.001..0.001 rows=1 loops=14)
        Index Cond: (deptno = a.deptno)
Planning time: 0.112 ms
Execution time: 0.076 ms