PostgreSQL常用優(yōu)化技巧示例介紹
1、標(biāo)量子查詢與filter
當(dāng)一個查詢在select和from之間，那么這種子查詢就是標(biāo)量子查詢。實際應(yīng)用中，很多人在寫SQL時為了方便會寫一堆標(biāo)量子查詢的SQL，在表數(shù)據(jù)不大時，一般并不會有什么影響，但當(dāng)數(shù)據(jù)量較大時，往往會對性能造成巨大影響。
因為標(biāo)量子查詢類似于一個天然的嵌套循環(huán)，而且驅(qū)動表固定為主表。如下所示：
bill=# explain select empno,ename,sal,deptno,
bill-# (select d.dname from dept d where d.deptno = e.deptno) as dname
bill-# from emp e;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on emp e (cost=0.00..15.84 rows=14 width=64)
SubPlan 1
-> Seq Scan on dept d (cost=0.00..1.05 rows=1 width=9)
Filter: (deptno = e.deptno)
(4 rows)
對于上面的SQL，emp表每輸出一行數(shù)據(jù)，都要去dept表中全表掃描一遍。
而我們都知道，嵌套循環(huán)的被驅(qū)動表的連接列必須包含在索引中，同理，標(biāo)量子查詢的表的連接列也必須包含在索引中。但是我們在實際寫SQL時還是要避免使用標(biāo)量子查詢，否則主表返回大量數(shù)據(jù)時，子表得被多次遍歷，從而對SQL性能產(chǎn)生巨大影響。
那么對于標(biāo)量子查詢的SQL我們該怎么優(yōu)化呢？最常用的就是改寫成外連接，這樣對于PostgreSQL的優(yōu)化器而言可以根據(jù)實際情況去選擇表的連接方式。這里需要注意的是，不能將標(biāo)量子查詢改成內(nèi)連接，我們前面的例子中也可以看到，標(biāo)量子查詢實際是一個傳值的過程，當(dāng)主表傳值給子表時，如果沒有相應(yīng)的值則會顯示NULL，而如果使用內(nèi)連接的話這部分?jǐn)?shù)據(jù)就丟失了。
因此，上面的標(biāo)量子查詢可以改寫成：
可以看到，優(yōu)化器根據(jù)實際情況選擇了更合適的hash join。
bill=# explain select e.empno,e.ename,e.sal,e.deptno,d.dname
bill-# from emp e
bill-# left join dept d on (d.deptno = e.deptno);
QUERY PLAN
-------------------------------------------------------------------
Hash Left Join (cost=1.09..2.31 rows=14 width=27)
Hash Cond: (e.deptno = d.deptno)
-> Seq Scan on emp e (cost=0.00..1.14 rows=14 width=18)
-> Hash (cost=1.04..1.04 rows=4 width=13)
-> Seq Scan on dept d (cost=0.00..1.04 rows=4 width=13)
(5 rows)
當(dāng)主表連接列是外鍵，而子表的連接列是主鍵時，使用內(nèi)連接也可以，因為外鍵自然不會存在NULL值。
bill=# explain select e.empno,e.ename,e.sal,e.deptno,d.dname
bill-# from emp e
bill-# inner join dept d on (d.deptno = e.deptno);
QUERY PLAN
-------------------------------------------------------------------
Hash Join (cost=1.09..2.31 rows=14 width=27)
Hash Cond: (e.deptno = d.deptno)
-> Seq Scan on emp e (cost=0.00..1.14 rows=14 width=18)
-> Hash (cost=1.04..1.04 rows=4 width=13)
-> Seq Scan on dept d (cost=0.00..1.04 rows=4 width=13)
(5 rows)
除了標(biāo)量子查詢外，往往filter也會產(chǎn)生類似的情況，因為在filter中驅(qū)動表也會被固定住，那么優(yōu)化器可能會選擇低效的執(zhí)行計劃。而對于PostgreSQL而言本身也不支持hint功能，如果錯誤的執(zhí)行計劃被固定，那么往往只能去改寫SQL。
這里說明下下filter，在PostgreSQL中filter主要有2種情況，一種是我們常見的where后面過濾數(shù)據(jù)的，這種一般不會產(chǎn)生什么性能問題，例如：
bill=# explain select * from t where id < 10;
QUERY PLAN
-------------------------------------------------------
Seq Scan on t (cost=0.00..16925.00 rows=100 width=4)
Filter: (id < 10)
(2 rows)
而另一種就是filter中是一些表的連接條件，這種呢便是我們前面說的情況，往往需要去關(guān)注的，例如：
bill=# explain select exists (select 1 from t where t.id=n.id) from n;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on n (cost=0.00..169250145.00 rows=10000 width=1)
SubPlan 1
-> Seq Scan on t (cost=0.00..16925.00 rows=1 width=0)
Filter: (id = n.id)
(4 rows)
那么哪些寫法會容易產(chǎn)生filter呢？在PostgreSQL中當(dāng)使用exists或者not exists時，或者子查詢中有固話子查詢的關(guān)鍵詞，如union、union all、cube、rollup、limit等，那么執(zhí)行計劃往往容易產(chǎn)生filter。
因此上面的SQL我們用in去替換exists進(jìn)行改寫：
bill=# explain select id in (select id from t) from n;
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on n (cost=0.00..129160170.00 rows=10000 width=1)
SubPlan 1
-> Materialize (cost=0.00..23332.00 rows=1000000 width=4)
-> Seq Scan on t (cost=0.00..14425.00 rows=1000000 width=4)
(4 rows)
除此之外，在PostgreSQL中我們更推薦使用= any的方式去改寫該類SQL：
bill=# explain select id = any(array(select id from t)) from n;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on n (cost=14425.00..14695.00 rows=10000 width=1)
InitPlan 1 (returns $0)
-> Seq Scan on t (cost=0.00..14425.00 rows=1000000 width=4)
(3 rows)
當(dāng)然這并不是說in的寫法就一定比exists要好，只是相較于exists更不容易產(chǎn)生filter。這是為什么呢？因為如果子查詢中包含我們上面提到的固化關(guān)鍵字時，子查詢會被固化為一個整體，當(dāng)采用exists寫法時，如果子查詢中有主表的連接列，那么便只能是主表通過連接列給子查詢中的表傳值，因此會選擇filter。而使用in的寫法，即使子查詢被固化，但如果沒有主表連接列的字段，那么便不會選擇filter。
2、視圖合并
不知道大家有沒有遇到過類似下面的情況：
select xxx from () t1, () t2 where t1.id = t2.id;
明明t1和t2兩個子查詢單獨執(zhí)行都很快，但是放到一起速度卻變得特別慢，這種情況往往就是視圖合并所導(dǎo)致的。
例如下面的SQL：
我們按照SQL中的順序來看應(yīng)該是emp和dept兩表先進(jìn)行關(guān)聯(lián)，然后再去和salgrade表關(guān)聯(lián)。但執(zhí)行計劃中的順序卻變成了emp和salgrade表先關(guān)聯(lián)，最后才去關(guān)聯(lián)dept表。
這說明發(fā)生了視圖合并，即視圖/子查詢中的內(nèi)容被拆開了。
bill=# explain select a.*,c.grade
bill-# from (select ename,sal,a.deptno,b.dname
bill(# from emp a,dept b
bill(# where a.deptno = b.deptno) a,
bill-# salgrade c
bill-# where a.sal between c.losal and c.hisal;
QUERY PLAN
-----------------------------------------------------------------------------
Hash Join (cost=1.09..4.56 rows=8 width=27)
Hash Cond: (a.deptno = b.deptno)
-> Nested Loop (cost=0.00..3.43 rows=8 width=18)
Join Filter: ((a.sal >= c.losal) AND (a.sal <= c.hisal))
-> Seq Scan on emp a (cost=0.00..1.14 rows=14 width=14)
-> Materialize (cost=0.00..1.07 rows=5 width=12)
-> Seq Scan on salgrade c (cost=0.00..1.05 rows=5 width=12)
-> Hash (cost=1.04..1.04 rows=4 width=13)
-> Seq Scan on dept b (cost=0.00..1.04 rows=4 width=13)
(9 rows)
從上面的例子可以看出，視圖合并一般產(chǎn)生性能問題都是因為發(fā)生視圖合并后表的連接順序變化導(dǎo)致的。不過一般優(yōu)化器這么做是為了幫我們選擇更合適的表連接順序，而當(dāng)優(yōu)化器選擇了錯誤的連接順序時，我們就有必要對SQL進(jìn)行改寫了。
由于PostgreSQL中我們無法使用hint來讓優(yōu)化器禁止視圖合并，所以我們便需要了解一些SQL改寫的技巧。
和前面的filter一樣，當(dāng)我們將視圖/子查詢固化后，那么便不能進(jìn)行視圖合并。因此上面的SQL我們可以改寫為：
加上group by后，子查詢被固化，視圖沒有發(fā)生合并，emp和dept表先進(jìn)行關(guān)聯(lián)了。
bill=# explain select a.*,c.grade
bill-# from (select ename,sal,a.deptno,b.dname
bill(# from emp a,dept b
bill(# where a.deptno = b.deptno group by ename,sal,a.deptno,b.dname) a,
bill-# salgrade c
bill-# where a.sal between c.losal and c.hisal;
QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop (cost=2.45..5.02 rows=8 width=27)
Join Filter: ((a.sal >= c.losal) AND (a.sal <= c.hisal))
-> HashAggregate (cost=2.45..2.59 rows=14 width=23)
Group Key: a.ename, a.sal, a.deptno, b.dname
-> Hash Join (cost=1.09..2.31 rows=14 width=23)
Hash Cond: (a.deptno = b.deptno)
-> Seq Scan on emp a (cost=0.00..1.14 rows=14 width=14)
-> Hash (cost=1.04..1.04 rows=4 width=13)
-> Seq Scan on dept b (cost=0.00..1.04 rows=4 width=13)
-> Materialize (cost=0.00..1.07 rows=5 width=12)
-> Seq Scan on salgrade c (cost=0.00..1.05 rows=5 width=12)
(11 rows)
3、謂詞推入
說完視圖合并，我們再來看看你視圖不能合并時會出現(xiàn)的一種情況——謂詞推入。即對于那些不能合并的視圖，并且有謂詞進(jìn)行過濾，CBO會將謂詞過濾條件推入到視圖內(nèi)，為了盡早的過濾掉無用的數(shù)據(jù)，從而提升性能。
從CBO的角度來看，進(jìn)行謂詞推入自然是好的，因為可以提前過濾掉不需要的數(shù)據(jù)。但是如果推入的謂詞是連接列的，那么可能導(dǎo)致表的join產(chǎn)生變化，SQL性能變得更差。
如下SQL所示：
外層的謂詞d.deptno between c.losal and c.hisal推入到了視圖里面。
bill=# create or replace view v1 as select ename,sal,a.deptno,b.dname
bill-# from emp a,dept b
bill-# where a.deptno = b.deptno;
CREATE VIEW
bill=# explain select d.*,c.grade from v1 d,salgrade c
bill-# where d.deptno between c.losal and c.hisal;
QUERY PLAN
-----------------------------------------------------------------------------
Hash Join (cost=1.09..4.56 rows=8 width=27)
Hash Cond: (a.deptno = b.deptno)
-> Nested Loop (cost=0.00..3.43 rows=8 width=18)
Join Filter: ((a.deptno >= c.losal) AND (a.deptno <= c.hisal))
-> Seq Scan on emp a (cost=0.00..1.14 rows=14 width=14)
-> Materialize (cost=0.00..1.07 rows=5 width=12)
-> Seq Scan on salgrade c (cost=0.00..1.05 rows=5 width=12)
-> Hash (cost=1.04..1.04 rows=4 width=13)
-> Seq Scan on dept b (cost=0.00..1.04 rows=4 width=13)
(9 rows)
那我們該如何防止謂詞內(nèi)推呢？在Oracle中可以通過關(guān)閉連接列的謂詞推入的隱藏參數(shù)_push_join_predicate來實現(xiàn)，那么在PostgreSQL中又該如何實現(xiàn)呢？
和上面類似，我們可以將視圖固化來避免這種情況，但一般來說不建議將視圖固化，因為大部分情況謂詞推入大多數(shù)對性能是有好處的。例如當(dāng)我們在視圖中使用limit時會導(dǎo)致謂詞無法推入，因此一般也不建議在視圖中使用limit，為什么呢？因為如果謂詞進(jìn)行推入的話，limit取到的值可能就不同了，會對結(jié)果集產(chǎn)生影響，所以自然不能推入了，因為優(yōu)化器的任何等價轉(zhuǎn)換都是在不改變SQL結(jié)果的情況下才能進(jìn)行的。
到此這篇關(guān)于PostgreSQL常用優(yōu)化技巧示例介紹的文章就介紹到這了,更多相關(guān)PostgreSQL優(yōu)化技巧內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了postgresql 性能參數(shù)配置方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgresql 中position函數(shù)的性能詳解
這篇文章主要介紹了postgresql 中position函數(shù)的性能詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
關(guān)于PostgreSQL JSONB的匹配和交集問題
這篇文章主要介紹了PostgreSQL JSONB的匹配和交集問題，本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-09-09
隨著使用時間的增加，數(shù)據(jù)庫中的數(shù)據(jù)量也不斷增加，因此數(shù)據(jù)庫查詢越來越慢。加速數(shù)據(jù)庫的方法很多，如添加特定的索引，將日志目錄換到單獨的磁盤分區(qū)，調(diào)整數(shù)據(jù)庫引擎的參數(shù)等。本文即將介紹的數(shù)據(jù)庫表分區(qū)即能達(dá)到此效果2021-06-06
PostgreSQL設(shè)置時區(qū)、時間/日期函數(shù)匯總大全
PostgreSQL是一款簡介而又性能強(qiáng)大的數(shù)據(jù)庫應(yīng)用程序,其在日期時間數(shù)據(jù)方面所支持的功能也都非常給力,這篇文章主要給大家介紹了關(guān)于PostgreSQL設(shè)置時區(qū)、時間/日期函數(shù)的相關(guān)資料,需要的朋友可以參考下2023-09-09
如何獲取PostgreSQL數(shù)據(jù)庫中的JSON值
這篇文章主要介紹了如何獲取PostgreSQL數(shù)據(jù)庫中的JSON值操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
在Docker環(huán)境中,對已運行的PostgreSQL數(shù)據(jù)庫實例進(jìn)行密碼更改是一項常見的維護(hù)操作,下面將詳述如何通過一系列命令行操作來實現(xiàn)這一目標(biāo),需要的朋友可以參考下2024-07-07
這篇文章主要給大家介紹了關(guān)于PostgreSQL操作json/jsonb的相關(guān)資料,PostgreSQL提供了兩種存儲JSON數(shù)據(jù)的類型:json和jsonb; jsonb是json的二進(jìn)制形式,文中介紹的非常詳細(xì),需要的朋友可以參考下2023-09-09
postgresql pg_hba.conf 簡介及配置詳解
配置文件之pg_hba.conf該文件用于控制訪問安全性,管理客戶端對于PostgreSQL服務(wù)器的訪問權(quán)限,本文給大家介紹postgresql pg_hba.conf 簡介及配置,感興趣的朋友跟隨小編一起看看吧2024-03-03
postgresql 存儲函數(shù)調(diào)用變量的3種方法小結(jié)
這篇文章主要介紹了postgresql 存儲函數(shù)調(diào)用變量的3種方法小結(jié)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01