PostgreSQL常用優(yōu)化技巧示例介紹
1、標量子查詢與filter
當一個查詢在select和from之間，那么這種子查詢就是標量子查詢。實際應用中，很多人在寫SQL時為了方便會寫一堆標量子查詢的SQL，在表數據不大時，一般并不會有什么影響，但當數據量較大時，往往會對性能造成巨大影響。
因為標量子查詢類似于一個天然的嵌套循環(huán)，而且驅動表固定為主表。如下所示：
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表每輸出一行數據，都要去dept表中全表掃描一遍。
而我們都知道，嵌套循環(huán)的被驅動表的連接列必須包含在索引中，同理，標量子查詢的表的連接列也必須包含在索引中。但是我們在實際寫SQL時還是要避免使用標量子查詢，否則主表返回大量數據時，子表得被多次遍歷，從而對SQL性能產生巨大影響。
那么對于標量子查詢的SQL我們該怎么優(yōu)化呢？最常用的就是改寫成外連接，這樣對于PostgreSQL的優(yōu)化器而言可以根據實際情況去選擇表的連接方式。這里需要注意的是，不能將標量子查詢改成內連接，我們前面的例子中也可以看到，標量子查詢實際是一個傳值的過程，當主表傳值給子表時，如果沒有相應的值則會顯示NULL，而如果使用內連接的話這部分數據就丟失了。
因此，上面的標量子查詢可以改寫成：
可以看到，優(yōu)化器根據實際情況選擇了更合適的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)
當主表連接列是外鍵，而子表的連接列是主鍵時，使用內連接也可以，因為外鍵自然不會存在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)
除了標量子查詢外，往往filter也會產生類似的情況，因為在filter中驅動表也會被固定住，那么優(yōu)化器可能會選擇低效的執(zhí)行計劃。而對于PostgreSQL而言本身也不支持hint功能，如果錯誤的執(zhí)行計劃被固定，那么往往只能去改寫SQL。
這里說明下下filter，在PostgreSQL中filter主要有2種情況，一種是我們常見的where后面過濾數據的，這種一般不會產生什么性能問題，例如：
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中是一些表的連接條件，這種呢便是我們前面說的情況，往往需要去關注的，例如：
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)
那么哪些寫法會容易產生filter呢？在PostgreSQL中當使用exists或者not exists時，或者子查詢中有固話子查詢的關鍵詞，如union、union all、cube、rollup、limit等，那么執(zhí)行計劃往往容易產生filter。
因此上面的SQL我們用in去替換exists進行改寫：
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)
當然這并不是說in的寫法就一定比exists要好，只是相較于exists更不容易產生filter。這是為什么呢？因為如果子查詢中包含我們上面提到的固化關鍵字時，子查詢會被固化為一個整體，當采用exists寫法時，如果子查詢中有主表的連接列，那么便只能是主表通過連接列給子查詢中的表傳值，因此會選擇filter。而使用in的寫法，即使子查詢被固化，但如果沒有主表連接列的字段，那么便不會選擇filter。
2、視圖合并
不知道大家有沒有遇到過類似下面的情況：
select xxx from () t1, () t2 where t1.id = t2.id;
明明t1和t2兩個子查詢單獨執(zhí)行都很快，但是放到一起速度卻變得特別慢，這種情況往往就是視圖合并所導致的。
例如下面的SQL：
我們按照SQL中的順序來看應該是emp和dept兩表先進行關聯，然后再去和salgrade表關聯。但執(zhí)行計劃中的順序卻變成了emp和salgrade表先關聯，最后才去關聯dept表。
這說明發(fā)生了視圖合并，即視圖/子查詢中的內容被拆開了。
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)
從上面的例子可以看出，視圖合并一般產生性能問題都是因為發(fā)生視圖合并后表的連接順序變化導致的。不過一般優(yōu)化器這么做是為了幫我們選擇更合適的表連接順序，而當優(yōu)化器選擇了錯誤的連接順序時，我們就有必要對SQL進行改寫了。
由于PostgreSQL中我們無法使用hint來讓優(yōu)化器禁止視圖合并，所以我們便需要了解一些SQL改寫的技巧。
和前面的filter一樣，當我們將視圖/子查詢固化后，那么便不能進行視圖合并。因此上面的SQL我們可以改寫為：
加上group by后，子查詢被固化，視圖沒有發(fā)生合并，emp和dept表先進行關聯了。
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、謂詞推入
說完視圖合并，我們再來看看你視圖不能合并時會出現的一種情況——謂詞推入。即對于那些不能合并的視圖，并且有謂詞進行過濾，CBO會將謂詞過濾條件推入到視圖內，為了盡早的過濾掉無用的數據，從而提升性能。
從CBO的角度來看，進行謂詞推入自然是好的，因為可以提前過濾掉不需要的數據。但是如果推入的謂詞是連接列的，那么可能導致表的join產生變化，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)
那我們該如何防止謂詞內推呢？在Oracle中可以通過關閉連接列的謂詞推入的隱藏參數_push_join_predicate來實現，那么在PostgreSQL中又該如何實現呢？
和上面類似，我們可以將視圖固化來避免這種情況，但一般來說不建議將視圖固化，因為大部分情況謂詞推入大多數對性能是有好處的。例如當我們在視圖中使用limit時會導致謂詞無法推入，因此一般也不建議在視圖中使用limit，為什么呢？因為如果謂詞進行推入的話，limit取到的值可能就不同了，會對結果集產生影響，所以自然不能推入了，因為優(yōu)化器的任何等價轉換都是在不改變SQL結果的情況下才能進行的。
到此這篇關于PostgreSQL常用優(yōu)化技巧示例介紹的文章就介紹到這了,更多相關PostgreSQL優(yōu)化技巧內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了postgresql 性能參數配置方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了postgresql 中position函數的性能詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
這篇文章主要介紹了PostgreSQL JSONB的匹配和交集問題，本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-09-09
隨著使用時間的增加，數據庫中的數據量也不斷增加，因此數據庫查詢越來越慢。加速數據庫的方法很多，如添加特定的索引，將日志目錄換到單獨的磁盤分區(qū)，調整數據庫引擎的參數等。本文即將介紹的數據庫表分區(qū)即能達到此效果2021-06-06
PostgreSQL設置時區(qū)、時間/日期函數匯總大全
PostgreSQL是一款簡介而又性能強大的數據庫應用程序,其在日期時間數據方面所支持的功能也都非常給力,這篇文章主要給大家介紹了關于PostgreSQL設置時區(qū)、時間/日期函數的相關資料,需要的朋友可以參考下2023-09-09
這篇文章主要介紹了如何獲取PostgreSQL數據庫中的JSON值操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
在Docker環(huán)境中,對已運行的PostgreSQL數據庫實例進行密碼更改是一項常見的維護操作,下面將詳述如何通過一系列命令行操作來實現這一目標,需要的朋友可以參考下2024-07-07
這篇文章主要給大家介紹了關于PostgreSQL操作json/jsonb的相關資料,PostgreSQL提供了兩種存儲JSON數據的類型:json和jsonb; jsonb是json的二進制形式,文中介紹的非常詳細,需要的朋友可以參考下2023-09-09
postgresql pg_hba.conf 簡介及配置詳解
配置文件之pg_hba.conf該文件用于控制訪問安全性,管理客戶端對于PostgreSQL服務器的訪問權限,本文給大家介紹postgresql pg_hba.conf 簡介及配置,感興趣的朋友跟隨小編一起看看吧2024-03-03
這篇文章主要介紹了postgresql 存儲函數調用變量的3種方法小結，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01