PostgreSQL 并行計算解說之22 - parallel append

标簽

PostgreSQL , cpu 并行 , smp 并行 , 并行計算 , gpu 并行 , 并行過程支援

https://github.com/digoal/blog/blob/master/201903/20190317_14.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        
      
lead parallel

接下來進行一一介紹。

關鍵知識請先自行了解：

1、優化器自動并行度算法 CBO

《PostgreSQL 9.6 并行計算優化器算法淺析》《PostgreSQL 11 并行計算算法，參數，強制并行度設定》

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#parallel-append parallel append

多段并行執行

例如分區表的操作，當一個QUERY涉及多個分區時，每個分區的執行部分為一個獨立段，多個分區可以并行執行，優化器支援結果并行 append。

資料量：10億

場景	資料量	關閉并行	開啟并行	并行度	開啟并行性能提升倍數
	10億	70.5 秒	3.16 秒	24	22.3 倍

postgres=# show max_worker_processes ;    
 max_worker_processes     
----------------------    
 128    
(1 row)    
postgres=# set min_parallel_table_scan_size =0;    
postgres=# set min_parallel_index_scan_size =0;    
postgres=# set parallel_tuple_cost =0;    
postgres=# set parallel_setup_cost =0;    
postgres=# set max_parallel_workers=128;    
postgres=# set max_parallel_workers_per_gather =24;    
postgres=# set enable_parallel_hash =on;    
postgres=# set enable_parallel_append =on;    
postgres=# set enable_partitionwise_aggregate =off;    
postgres=# set work_mem ='128MB';

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#1%E5%85%B3%E9%97%AD%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-705-%E7%A7%92 1、關閉并行，耗時： 70.5 秒。

postgres=# set max_parallel_workers_per_gather =0;    
postgres=# set enable_parallel_append =off;    
    
    
postgres=# explain select count(*) from ccc where order_id=1;    
                              QUERY PLAN                                
----------------------------------------------------------------------  
 Aggregate  (cost=17905421.61..17905421.62 rows=1 width=8)  
   ->  Append  (cost=0.00..17905421.55 rows=24 width=0)  
         ->  Seq Scan on ccc0  (cost=0.00..745998.20 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc1  (cost=0.00..727405.10 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc2  (cost=0.00..839291.45 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc3  (cost=0.00..634111.15 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc4  (cost=0.00..764438.90 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc5  (cost=0.00..708800.20 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc6  (cost=0.00..727511.15 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc7  (cost=0.00..783234.00 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc8  (cost=0.00..699378.05 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc9  (cost=0.00..708898.20 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc10  (cost=0.00..783522.20 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc11  (cost=0.00..615479.05 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc12  (cost=0.00..951260.55 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc13  (cost=0.00..783499.00 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc14  (cost=0.00..913779.60 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc15  (cost=0.00..708653.05 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc16  (cost=0.00..736590.70 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc17  (cost=0.00..783320.20 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc18  (cost=0.00..932607.90 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc19  (cost=0.00..615568.50 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc20  (cost=0.00..746233.40 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc21  (cost=0.00..466366.88 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc22  (cost=0.00..783250.55 rows=1 width=0)  
               Filter: (order_id = 1)  
         ->  Seq Scan on ccc23  (cost=0.00..746223.45 rows=1 width=0)  
               Filter: (order_id = 1)  
(50 rows)  
    
postgres=# select count(*) from ccc where order_id=1;    
 count   
-------  
     1  
(1 row)  
  
Time: 70514.708 ms (01:10.515)

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#2%E5%BC%80%E5%90%AF%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-316-%E7%A7%92 2、開啟并行，耗時： 3.16 秒。

postgres=# set max_parallel_workers_per_gather =24;    
postgres=# set enable_parallel_append =on;    
    
postgres=# explain select count(*) from ccc where order_id=1;    
                                     QUERY PLAN                                        
-------------------------------------------------------------------------------------  
 Aggregate  (cost=5926253.57..5926253.58 rows=1 width=8)  
   ->  Gather  (cost=0.00..5926253.51 rows=24 width=0)  
         Workers Planned: 24  
         ->  Parallel Append  (cost=0.00..5926253.51 rows=24 width=0)  
               ->  Parallel Seq Scan on ccc12  (cost=0.00..314843.31 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc18  (cost=0.00..308669.75 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc14  (cost=0.00..302438.07 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc2  (cost=0.00..277784.35 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc10  (cost=0.00..259326.13 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc13  (cost=0.00..259318.46 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc17  (cost=0.00..259263.09 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc22  (cost=0.00..259236.23 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc7  (cost=0.00..259230.75 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc4  (cost=0.00..253010.04 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc20  (cost=0.00..246984.48 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc23  (cost=0.00..246981.19 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc0  (cost=0.00..246906.63 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc16  (cost=0.00..243792.99 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc6  (cost=0.00..240788.84 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc1  (cost=0.00..240752.80 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc9  (cost=0.00..234627.47 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc5  (cost=0.00..234597.51 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc15  (cost=0.00..234546.34 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc8  (cost=0.00..231476.54 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc3  (cost=0.00..209876.96 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc19  (cost=0.00..203737.69 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc11  (cost=0.00..203708.09 rows=1 width=0)  
                     Filter: (order_id = 1)  
               ->  Parallel Seq Scan on ccc21  (cost=0.00..154355.70 rows=1 width=0)  
                     Filter: (order_id = 1)  
(52 rows)  
  
postgres=# select count(*) from ccc where order_id=1;    
 count   
-------  
     1  
(1 row)  
  
Time: 3163.179 ms (00:03.163)

注意，如果所有并行執行的分段的執行結果加起來結果集很大，append的結果會非常大，那麼性能瓶頸可能會在Parallel Append 的節點上。

是以本例在where中加了一個filter，使得每個分段的結果集很小，Parallel Append節點不會成為瓶頸。性能提升非常明顯。

通常，如果分段是分區表的話，結合其他的并行優化，enable_partitionwise_aggregate, enable_partitionwise_join，讓分段結果集盡量小，這樣就可以提高整體性能。

https://github.com/digoal/blog/blob/master/201903/20190317_14.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_14.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_14.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虛拟機

PostgreSQL 并行計算解說之22 - parallel append

标簽

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#%E8%83%8C%E6%99%AF 背景

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#parallel-append parallel append

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#1%E5%85%B3%E9%97%AD%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-705-%E7%A7%92 1、關閉并行，耗時： 70.5 秒。

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#2%E5%BC%80%E5%90%AF%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-316-%E7%A7%92 2、開啟并行，耗時： 3.16 秒。

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#%E5%85%B6%E4%BB%96%E7%9F%A5%E8%AF%86 其他知識

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#%E5%8F%82%E8%80%83 參考

https://github.com/digoal/blog/blob/master/201903/20190317_14.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陷阱與缺陷②(文法陷阱之優先級)一、錯誤案列二、優先級規律

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

mybatis_入門程式Mybatis入門

線性表之順序表的實作

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

sqlServer根據經緯查距離

Effective Java 8:通用程式設計

C++判斷素數、求最大公約數代碼判斷一個數是否為素數求兩個數的最大公約數

SequoiaDB巨杉資料庫C++驅動概述

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method

hdu7108哈希

PostgreSQL 并行計算解說 之22 - parallel append

标簽

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#%E8%83%8C%E6%99%AF 背景

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#parallel-append parallel append

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#1%E5%85%B3%E9%97%AD%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-705-%E7%A7%92 1、關閉并行，耗時： 70.5 秒。

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#2%E5%BC%80%E5%90%AF%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-316-%E7%A7%92 2、開啟并行，耗時： 3.16 秒。

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#%E5%85%B6%E4%BB%96%E7%9F%A5%E8%AF%86 其他知識

https://github.com/digoal/blog/blob/master/201903/20190317_14.md#%E5%8F%82%E8%80%83 參考

繼續閱讀

PostgreSQL 并行計算解說之22 - parallel append