淺談pg_hint_plan定制執(zhí)行計(jì)劃
更新時(shí)間：2021年01月26日 16:29:42 作者：mingjie.gmj
這篇文章主要介紹了淺談pg_hint_plan定制執(zhí)行計(jì)劃操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧
有的時(shí)候PG給出的執(zhí)行計(jì)劃由于很多原因并不是最優(yōu)的，需要手動(dòng)指定執(zhí)行路徑時(shí)我們可以加載pg_hint_plan這個(gè)插件。
1 安裝插件
預(yù)先安裝Postgresql10.7
cd postgresql-10.7/contrib/ wget https://github.com/ossc-db/pg_hint_plan/archive/REL10_1_3_3.tar.gz tar xzvf pg_hint_plan-REL10_1_3_3.tar.gz cd pg_hint_plan-REL10_1_3_3 make make install
檢查文件
cd $PGHOME ls lib/pg_hint_plan.so lib/pg_hint_plan.so ls share/extension/ pg_hint_plan--1.3.0--1.3.1.sql pg_hint_plan--1.3.2--1.3.3.sql pg_hint_plan.control plpgsql.control pg_hint_plan--1.3.1--1.3.2.sql pg_hint_plan--1.3.3.sql plpgsql--1.0.sql plpgsql--unpackaged--1.0.sql
2 加載插件
2.1 當(dāng)前會(huì)話加載
LOAD 'pg_hint_plan';
注意這樣加載只在當(dāng)前回話生效。
2.2 用戶、庫(kù)級(jí)自動(dòng)加載
alter user postgres set session_preload_libraries='pg_hint_plan'; alter database postgres set session_preload_libraries='pg_hint_plan';
配置錯(cuò)了的話就連不上數(shù)據(jù)庫(kù)了！
如果配置錯(cuò)了，連接template1庫(kù)執(zhí)行
alter database postgres reset session_preload_libraries; alter user postgres reset session_preload_libraries;
2.3 cluster級(jí)自動(dòng)加載
在postgresql.conf中修改shared_preload_libraries=‘pg_hint_plan'
重啟數(shù)據(jù)庫(kù)
3 檢查是否已經(jīng)加載
pg_hint_plan加載后在extension里面是看不到的，所以需要確認(rèn)插件是否已經(jīng)加載
show session_preload_libraries; session_preload_libraries --------------------------- pg_hint_plan
或者
show shared_preload_libraries;
如果使用load方式加載不需要檢查。
4 使用插件定制執(zhí)行計(jì)劃
4.1 初始化測(cè)試數(shù)據(jù)
create table t1 (id int, t int, name varchar(255));
create table t2 (id int , salary int);
create table t3 (id int , age int);
insert into t1 values (1,200,'jack');
insert into t1 values (2,300,'tom');
insert into t1 values (3,400,'john');
insert into t2 values (1,40000);
insert into t2 values (2,38000);
insert into t2 values (3,18000);
insert into t3 values (3,38);
insert into t3 values (2,55);
insert into t3 values (1,12);
explain analyze select * from t1 left join t2 on t1.id=t2.id left join t3 on t1.id=t3.id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=89.82..337.92 rows=17877 width=540) (actual time=0.053..0.059 rows=3 loops=1)
Hash Cond: (t3.id = t1.id)
-> Seq Scan on t3 (cost=0.00..32.60 rows=2260 width=8) (actual time=0.002..0.002 rows=3 loops=1)
-> Hash (cost=70.05..70.05 rows=1582 width=532) (actual time=0.042..0.043 rows=3 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 17kB
-> Hash Right Join (cost=13.15..70.05 rows=1582 width=532) (actual time=0.034..0.039 rows=3 loops=1)
Hash Cond: (t2.id = t1.id)
-> Seq Scan on t2 (cost=0.00..32.60 rows=2260 width=8) (actual time=0.002..0.002 rows=3 loops=1)
-> Hash (cost=11.40..11.40 rows=140 width=524) (actual time=0.017..0.017 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on t1 (cost=0.00..11.40 rows=140 width=524) (actual time=0.010..0.011 rows=3 loops=1)
Planning time: 0.154 ms
Execution time: 0.133 ms
創(chuàng)建索引
create index idx_t1_id on t1(id);
create index idx_t2_id on t2(id);
create index idx_t3_id on t3(id);
explain analyze select * from t1 left join t2 on t1.id=t2.id left join t3 on t1.id=t3.id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=2.14..3.25 rows=3 width=540) (actual time=0.045..0.047 rows=3 loops=1)
Hash Cond: (t1.id = t3.id)
-> Hash Left Join (cost=1.07..2.14 rows=3 width=532) (actual time=0.030..0.032 rows=3 loops=1)
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1 (cost=0.00..1.03 rows=3 width=524) (actual time=0.005..0.006 rows=3 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.007..0.007 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on t2 (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.005..0.005 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on t3 (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.002 rows=3 loops=1)
Planning time: 0.305 ms
Execution time: 0.128 ms
4.2 強(qiáng)制走index scan
/*+ indexscan(t1 idx_d)
/*+ indexscan(t1 idx_t1_id)
explain (analyze,buffers) select * from t1 where id=2;
QUERY PLAN
----------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..1.04 rows=1 width=524) (actual time=0.011..0.013 rows=1 loops=1)
Filter: (id = 2)
Rows Removed by Filter: 2
Buffers: shared hit=1
Planning time: 0.058 ms
Execution time: 0.028 ms
explain (analyze,buffers) /*+ indexscan(t1) */select * from t1 where id=2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Scan using idx_t1_id on t1 (cost=0.13..8.15 rows=1 width=524) (actual time=0.044..0.046 rows=1 loops=1)
Index Cond: (id = 2)
Buffers: shared hit=1 read=1
Planning time: 0.145 ms
Execution time: 0.072 ms
explain (analyze,buffers) /*+ indexscan(t1 idx_t1_id) */select * from t1 where id=2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Scan using idx_t1_id on t1 (cost=0.13..8.15 rows=1 width=524) (actual time=0.016..0.017 rows=1 loops=1)
Index Cond: (id = 2)
Buffers: shared hit=2
Planning time: 0.079 ms
Execution time: 0.035 ms
4.3 強(qiáng)制多條件組合
/*+ indexscan(t2) indexscan(t1 idx_t1_id) */
/*+ seqscan(t2) indexscan(t1 idx_t1_id) */
explain analyze SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Hash Join (cost=1.07..2.14 rows=3 width=532) (actual time=0.018..0.020 rows=3 loops=1)
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1 (cost=0.00..1.03 rows=3 width=524) (actual time=0.006..0.007 rows=3 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.005..0.005 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on t2 (cost=0.00..1.03 rows=3 width=8) (actual time=0.001..0.003 rows=3 loops=1)
Planning time: 0.114 ms
Execution time: 0.055 ms
(8 rows)
組合兩個(gè)條件走indexscan
/*+ indexscan(t2) indexscan(t1 idx_t1_id) */explain analyze SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Merge Join (cost=0.26..24.40 rows=3 width=532) (actual time=0.047..0.053 rows=3 loops=1)
Merge Cond: (t1.id = t2.id)
-> Index Scan using idx_t1_id on t1 (cost=0.13..12.18 rows=3 width=524) (actual time=0.014..0.015 rows=3 loops=1)
-> Index Scan using idx_t2_id on t2 (cost=0.13..12.18 rows=3 width=8) (actual time=0.026..0.028 rows=3 loops=1)
組合兩個(gè)條件走indexscan+seqscan
/*+ seqscan(t2) indexscan(t1 idx_t1_id) */explain analyze SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.13..13.35 rows=3 width=532) (actual time=0.025..0.032 rows=3 loops=1)
Join Filter: (t1.id = t2.id)
Rows Removed by Join Filter: 6
-> Index Scan using idx_t1_id on t1 (cost=0.13..12.18 rows=3 width=524) (actual time=0.016..0.018 rows=3 loops=1)
-> Materialize (cost=0.00..1.04 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=3)
-> Seq Scan on t2 (cost=0.00..1.03 rows=3 width=8) (actual time=0.004..0.005 rows=3 loops=1)
4.4 強(qiáng)制指定join method
/*+ NestLoop(t1 t2) MergeJoin(t1 t2 t3) Leading(t1 t2 t3) */
/*+ NestLoop(t1 t2 t3) MergeJoin(t2 t3) Leading(t1 (t2 t3)) */
explain analyze select * from t1 left join t2 on t1.id=t2.id left join t3 on t1.id=t3.id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=2.14..3.25 rows=3 width=540) (actual time=0.053..0.056 rows=3 loops=1)
Hash Cond: (t1.id = t3.id)
-> Hash Left Join (cost=1.07..2.14 rows=3 width=532) (actual time=0.036..0.038 rows=3 loops=1)
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1 (cost=0.00..1.03 rows=3 width=524) (actual time=0.007..0.007 rows=3 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.009..0.009 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on t2 (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.006..0.006 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on t3 (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=1)
強(qiáng)制走循環(huán)嵌套連接
/*+ NestLoop(t1 t2) MergeJoin(t1 t2 t3) Leading(t1 t2 t3) */
explain analyze select * from t1 left join t2 on t1.id=t2.id left join t3 on t1.id=t3.id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=3.28..3.34 rows=3 width=540) (actual time=0.093..0.096 rows=3 loops=1)
Merge Cond: (t1.id = t3.id)
-> Sort (cost=2.23..2.23 rows=3 width=532) (actual time=0.077..0.078 rows=3 loops=1)
Sort Key: t1.id
Sort Method: quicksort Memory: 25kB
-> Nested Loop Left Join (cost=0.00..2.20 rows=3 width=532) (actual time=0.015..0.020 rows=3 loops=1)
Join Filter: (t1.id = t2.id)
Rows Removed by Join Filter: 6
-> Seq Scan on t1 (cost=0.00..1.03 rows=3 width=524) (actual time=0.005..0.005 rows=3 loops=1)
-> Materialize (cost=0.00..1.04 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=3)
-> Seq Scan on t2 (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=1)
-> Sort (cost=1.05..1.06 rows=3 width=8) (actual time=0.012..0.013 rows=3 loops=1)
Sort Key: t3.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on t3 (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=1)
控制連接順序
/*+ NestLoop(t1 t2 t3) MergeJoin(t2 t3) Leading(t1 (t2 t3)) */
explain analyze select * from t1 left join t2 on t1.id=t2.id left join t3 on t1.id=t3.id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=1.07..3.31 rows=3 width=540) (actual time=0.036..0.041 rows=3 loops=1)
Join Filter: (t1.id = t3.id)
Rows Removed by Join Filter: 6
-> Hash Left Join (cost=1.07..2.14 rows=3 width=532) (actual time=0.030..0.032 rows=3 loops=1)
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1 (cost=0.00..1.03 rows=3 width=524) (actual time=0.008..0.009 rows=3 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.007..0.007 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on t2 (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.004 rows=3 loops=1)
-> Materialize (cost=0.00..1.04 rows=3 width=8) (actual time=0.001..0.002 rows=3 loops=3)
-> Seq Scan on t3 (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=1)
4.5 控制單條SQL的cost
/*+ set(seq_page_cost 20.0) seqscan(t1) */
/*+ set(seq_page_cost 20.0) seqscan(t1) */explain analyze select * from t1 where id > 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..20.04 rows=1 width=524) (actual time=0.011..0.013 rows=2 loops=1)
Filter: (id > 1)
Rows Removed by Filter: 1
set seq_page_cost 200，注意下面的cost已經(jīng)變成了200.04
/*+ set(seq_page_cost 200.0) seqscan(t1) */explain analyze select * from t1 where id > 1;
QUERY PLAN
------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..200.04 rows=1 width=524) (actual time=0.010..0.011 rows=2 loops=1)
Filter: (id > 1)
Rows Removed by Filter: 1
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:
相關(guān)文章
PostgreSQL數(shù)據(jù)庫(kù)命令行執(zhí)行SQL腳本的三種方式
生成環(huán)境中,出于安全性等原因,往往不提供數(shù)據(jù)庫(kù)連接工具,所以對(duì)數(shù)據(jù)庫(kù)的更新和升級(jí)就得通過(guò)命令行來(lái)實(shí)現(xiàn),本文總結(jié)了三種命令行執(zhí)行sql腳本的方式,需要的朋友可以參考下2024-02-02
Mac系統(tǒng)重置PostgreSQL密碼的方法示例代碼
PostgreSQL 是一個(gè)免費(fèi)的對(duì)象-關(guān)系數(shù)據(jù)庫(kù)服務(wù)器(ORDBMS)，在靈活的BSD許可證下發(fā)行。這篇文章主要介紹了Mac系統(tǒng)重置PostgreSQL密碼的方法示例代碼,需要的朋友可以參考下2020-02-02
這篇文章主要介紹了postgresql減少wal日志生成量的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
這篇文章主要介紹了postgresql 中的to_char()常用操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-02-02
PostgreSQL 恢復(fù)誤刪數(shù)據(jù)的操作
這篇文章主要介紹了PostgreSQL 恢復(fù)誤刪數(shù)據(jù)的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
postgresql 實(shí)現(xiàn)將字段為空的值替換為指定值
這篇文章主要介紹了postgresql 實(shí)現(xiàn)將字段為空的值替換為指定值，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
Ubuntu中卸載Postgresql出錯(cuò)的解決方法
這篇文章主要給大家介紹了關(guān)于在Ubuntu中卸載Postgresql出錯(cuò)的解決方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧。2017-09-09
PostgreSQL教程(二十)：PL/pgSQL過(guò)程語(yǔ)言
這篇文章主要介紹了PostgreSQL教程(二十)：PL/pgSQL過(guò)程語(yǔ)言,本文講解了、PL/pgSQL概述、PL/pgSQL的結(jié)構(gòu)、聲明、基本語(yǔ)句、控制結(jié)構(gòu)等內(nèi)容,需要的朋友可以參考下2015-05-05
這篇文章主要介紹了PostgreSQL 字符串拆分與合并案例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
PostgreSQL忘記postgres賬號(hào)密碼的解決方法
這篇文章主要介紹了PostgreSQL忘記postgres賬號(hào)的密碼的解決方法,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-01-01