标簽
PostgreSQL , cpu 并行 , smp 并行 , 并行計算 , gpu 并行 , 并行過程支援
https://github.com/digoal/blog/blob/master/201903/20190317_11.md#%E8%83%8C%E6%99%AF 背景
PostgreSQL 11 優化器已經支援了非常多場合的并行。簡單估計，已支援27餘種場景的并行計算。
parallel seq scan
parallel index scan
parallel index only scan
parallel bitmap scan
parallel filter
parallel hash agg
parallel group agg
parallel cte
parallel subquery
parallel create table
parallel create index
parallel select into
parallel CREATE MATERIALIZED VIEW
parallel 排序 : gather merge
parallel nestloop join
parallel hash join
parallel merge join
parallel 自定義并行聚合
parallel 自定義并行UDF
parallel append
parallel union
parallel fdw table scan
parallel partition join
parallel partition agg
parallel gather
parallel gather merge
parallel rc 并行
parallel rr 并行
parallel GPU 并行
parallel unlogged table
接下來進行一一介紹。
關鍵知識請先自行了解：
1、優化器自動并行度算法 CBO
《PostgreSQL 9.6 并行計算 優化器算法淺析》 《PostgreSQL 11 并行計算算法，參數，強制并行度設定》https://github.com/digoal/blog/blob/master/201903/20190317_11.md#parallel-hash-join parallel hash join
并行hash JOIN
資料量：10億 join 10億 on (i=i)。
| 場景 | 資料量 | 關閉并行 | 開啟并行 | 并行度 | 開啟并行性能提升倍數 |
|---|---|---|---|---|---|
| 10億 join 10億 using (i) where t1.i<10000000 and t2.i<10000000 | 8.1 秒 | 1 秒 | 20 | 8.1 倍 | |
| 10億 join 10億 using (i) | 1071 秒 | 92.3 秒 | 11.6 倍 |
https://github.com/digoal/blog/blob/master/201903/20190317_11.md#1%E5%85%B3%E9%97%AD%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-1071-%E7%A7%92--81-%E7%A7%92 1、關閉并行，耗時： 1071 秒 , 8.1 秒。
postgres=# explain select count(*) from table5 t1 join table5 t2 using (i) ;
QUERY PLAN
-------------------------------------------------------------------------------------------
Aggregate (cost=1321974645.04..1321974645.05 rows=1 width=8)
-> Hash Join (cost=30831031.44..1319474644.88 rows=1000000064 width=0)
Hash Cond: (t1.i = t2.i)
-> Seq Scan on table5 t1 (cost=0.00..14424779.64 rows=1000000064 width=4)
-> Hash (cost=14424779.64..14424779.64 rows=1000000064 width=4)
-> Seq Scan on table5 t2 (cost=0.00..14424779.64 rows=1000000064 width=4)
(6 rows)
postgres=# select count(*) from table5 t1 join table5 t2 using (i) ;
count
------------
1000000000
(1 row)
Time: 1071102.574 ms (17:51.103)
postgres=# explain select count(*) from table5 t1 join table5 t2 using (i) where t1.i<10000000 and t2.i<10000000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Aggregate (cost=557583.02..557583.03 rows=1 width=8)
-> Hash Join (cost=278347.86..557405.33 rows=71076 width=0)
Hash Cond: (t1.i = t2.i)
-> Index Only Scan using idx_table5_2 on table5 t1 (cost=0.57..172964.32 rows=8430637 width=4)
Index Cond: (i < 10000000)
-> Hash (cost=172964.32..172964.32 rows=8430637 width=4)
-> Index Only Scan using idx_table5_2 on table5 t2 (cost=0.57..172964.32 rows=8430637 width=4)
Index Cond: (i < 10000000)
(8 rows)
postgres=# select count(*) from table5 t1 join table5 t2 using (i) where t1.i<10000000 and t2.i<10000000;
count
---------
9999999
(1 row)
Time: 8130.739 ms (00:08.131)
https://github.com/digoal/blog/blob/master/201903/20190317_11.md#2%E5%BC%80%E5%90%AF%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-923-%E7%A7%92--1-%E7%A7%92 2、開啟并行，耗時： 92.3 秒 , 1 秒。
postgres=# explain select count(*) from table5 t1 join table5 t2 using (i) ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=14655810.36..14655810.37 rows=1 width=8)
-> Gather (cost=14655810.30..14655810.31 rows=20 width=8)
Workers Planned: 20
-> Partial Aggregate (cost=14655810.30..14655810.31 rows=1 width=8)
-> Parallel Hash Join (cost=5745092.07..14530810.30 rows=50000003 width=0)
Hash Cond: (t1.i = t2.i)
-> Parallel Seq Scan on table5 t1 (cost=0.00..4924779.03 rows=50000003 width=4)
-> Parallel Hash (cost=4924779.03..4924779.03 rows=50000003 width=4)
-> Parallel Seq Scan on table5 t2 (cost=0.00..4924779.03 rows=50000003 width=4)
(9 rows)
postgres=# select count(*) from table5 t1 join table5 t2 using (i) ;
count
------------
1000000000
(1 row)
Time: 92307.627 ms (01:32.308)
postgres=# explain select count(*) from table5 t1 join table5 t2 using (i) where t1.i<10000000 and t2.i<10000000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=192607.16..192607.17 rows=1 width=8)
-> Gather (cost=192607.10..192607.11 rows=20 width=8)
Workers Planned: 20
-> Partial Aggregate (cost=192607.10..192607.11 rows=1 width=8)
-> Parallel Hash Join (cost=98143.00..192598.22 rows=3554 width=0)
Hash Cond: (t1.i = t2.i)
-> Parallel Index Only Scan using idx_table5_2 on table5 t1 (cost=0.57..92873.27 rows=421532 width=4)
Index Cond: (i < 10000000)
-> Parallel Hash (cost=92873.27..92873.27 rows=421532 width=4)
-> Parallel Index Only Scan using idx_table5_2 on table5 t2 (cost=0.57..92873.27 rows=421532 width=4)
Index Cond: (i < 10000000)
(11 rows)
postgres=# select count(*) from table5 t1 join table5 t2 using (i) where t1.i<10000000 and t2.i<10000000;
count
---------
9999999
(1 row)
Time: 1007.838 ms (00:01.008)
hash join , hash agg ，性能與并行度相關，同時與work_mem的設定非常相關。
hash join , 當外部條件t2.i<10000000未設定時，不會在hash table中過濾，這個query rewrite優化器有改進空間。
postgres=# explain select count(*) from table5 t1 join table5 t2 using (i) where t1.i<10000000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=5211686.12..5211686.13 rows=1 width=8)
-> Gather (cost=5211686.06..5211686.07 rows=20 width=8)
Workers Planned: 20
-> Partial Aggregate (cost=5211686.06..5211686.07 rows=1 width=8)
-> Parallel Hash Join (cost=98142.42..5210632.23 rows=421532 width=0)
Hash Cond: (t2.i = t1.i)
-> Parallel Seq Scan on table5 t2 (cost=0.00..4924779.03 rows=50000003 width=4)
-> Parallel Hash (cost=92873.27..92873.27 rows=421532 width=4)
-> Parallel Index Only Scan using idx_table5_2 on table5 t1 (cost=0.57..92873.27 rows=421532 width=4)
Index Cond: (i < 10000000)
(10 rows)
https://github.com/digoal/blog/blob/master/201903/20190317_11.md#%E5%85%B6%E4%BB%96%E7%9F%A5%E8%AF%86 其他知識
2、function, op 識别是否支援parallel
postgres=# select proparallel,proname from pg_proc;
proparallel | proname
-------------+----------------------------------------------
s | boolin
s | boolout
s | byteain
s | byteaout
3、subquery mapreduce unlogged table
對于一些情況，如果期望簡化優化器對非常非常複雜的SQL并行優化的負擔，可以自己将SQL拆成幾段，中間結果使用unlogged table儲存，類似mapreduce的思想。unlogged table同樣支援parallel 計算。
4、vacuum，垃圾回收并行。
5、dblink 異步調用并行
《PostgreSQL VOPS 向量計算 + DBLINK異步并行 - 單執行個體 10億 聚合計算跑進2秒》 《PostgreSQL 相似搜尋分布式架構設計與實踐 - dblink異步調用與多機并行(遠端 遊标+記錄 UDF執行個體)》 《PostgreSQL dblink異步調用實作 并行hash分片JOIN - 含資料交、并、差 提速案例 - 含dblink VS pg 11 parallel hash join VS pg 11 智能分區JOIN》暫時不允許并行的場景(将來PG會繼續擴大支援範圍)：
1、修改行，鎖行，除了create table as , select into, create mview這幾個可以使用并行。
2、query 會被中斷時，例如cursor , loop in PL/SQL ，因為涉及到中間處理，是以不建議開啟并行。
3、paralle unsafe udf ，這種UDF不會并行
4、嵌套并行(udf (内部query并行))，外部調用這個UDF的SQL不會并行。（主要是防止large parallel workers ）
5、SSI 隔離級别
https://github.com/digoal/blog/blob/master/201903/20190317_11.md#%E5%8F%82%E8%80%83 參考
https://www.postgresql.org/docs/11/parallel-plans.html 《PostgreSQL 11 preview - 并行計算 增強 彙總》 《PostgreSQL 10 自定義并行計算聚合函數的原理與實踐 - (含array_agg合并多個數組為單個一進制數組的例子)》https://github.com/digoal/blog/blob/master/201903/20190317_11.md#%E5%85%8D%E8%B4%B9%E9%A2%86%E5%8F%96%E9%98%BF%E9%87%8C%E4%BA%91rds-postgresql%E5%AE%9E%E4%BE%8Becs%E8%99%9A%E6%8B%9F%E6%9C%BA 免費領取阿裡雲RDS PostgreSQL執行個體、ECS虛拟機
![C經典書籍筆記——C陷阱與缺陷②(文法陷阱之優先級)一、錯誤案列 二、優先級規律[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)