PostgreSQL 并行計算解說之24 - parallel CTE (Common Table Express)

标簽

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

https://github.com/digoal/blog/blob/master/201903/20190317_17.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 append merge           
                                                
parallel union all        
                                                
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_17.md#parallel-cte parallel cte

CTE是PG支援的複雜SQL模型。優化器已支援CTE的并行執行

資料量：10億

場景	資料量	關閉并行	開啟并行	并行度	開啟并行性能提升倍數
parallel CTE	10億	65.65 秒	3.33 秒	24	19.7 倍

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 =off;          
postgres=# set enable_partitionwise_aggregate =off;          
postgres=# set work_mem ='128MB';

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

postgres=# set max_parallel_workers_per_gather =0;          
     
explain 	  
with   
a0 as (select count(*) from ccc0) ,   
a1 as (select count(*) from ccc1) ,  
a2 as (select count(*) from ccc2) ,  
a3 as (select count(*) from ccc3) ,  
a4 as (select count(*) from ccc4) ,  
a5 as (select count(*) from ccc5) ,  
a6 as (select count(*) from ccc6) ,  
a7 as (select count(*) from ccc7) ,  
a8 as (select count(*) from ccc8) ,  
a9 as (select count(*) from ccc9) ,  
a10 as (select count(*) from ccc10) ,   
a11 as (select count(*) from ccc11) ,  
a12 as (select count(*) from ccc12) ,  
a13 as (select count(*) from ccc13) ,  
a14 as (select count(*) from ccc14) ,  
a15 as (select count(*) from ccc15) ,  
a16 as (select count(*) from ccc16) ,  
a17 as (select count(*) from ccc17) ,  
a18 as (select count(*) from ccc18) ,  
a19 as (select count(*) from ccc19) ,  
a20 as (select count(*) from ccc20) ,   
a21 as (select count(*) from ccc21) ,  
a22 as (select count(*) from ccc22) ,  
a23 as (select count(*) from ccc23)   
select * from a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23;  
                                                                                      QUERY PLAN                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Nested Loop  (cost=17159423.46..17159424.14 rows=1 width=184)  
   CTE a1  
     ->  Aggregate  (cost=727405.10..727405.11 rows=1 width=8)  
           ->  Seq Scan on ccc1  (cost=0.00..625842.88 rows=40624888 width=0)  
   CTE a2  
     ->  Aggregate  (cost=839291.45..839291.46 rows=1 width=8)  
           ->  Seq Scan on ccc2  (cost=0.00..722107.36 rows=46873636 width=0)  
   CTE a3  
     ->  Aggregate  (cost=634111.15..634111.16 rows=1 width=8)  
           ->  Seq Scan on ccc3  (cost=0.00..545575.32 rows=35414332 width=0)  
   CTE a4  
     ->  Aggregate  (cost=764438.90..764438.91 rows=1 width=8)  
           ->  Seq Scan on ccc4  (cost=0.00..657705.92 rows=42693192 width=0)  
   CTE a5  
     ->  Aggregate  (cost=708800.20..708800.21 rows=1 width=8)  
           ->  Seq Scan on ccc5  (cost=0.00..609836.16 rows=39585616 width=0)  
   CTE a6  
     ->  Aggregate  (cost=727511.15..727511.16 rows=1 width=8)  
           ->  Seq Scan on ccc6  (cost=0.00..625934.32 rows=40630732 width=0)  
   CTE a7  
     ->  Aggregate  (cost=783234.00..783234.01 rows=1 width=8)  
           ->  Seq Scan on ccc7  (cost=0.00..673876.80 rows=43742880 width=0)  
   CTE a8  
     ->  Aggregate  (cost=699378.05..699378.06 rows=1 width=8)  
           ->  Seq Scan on ccc8  (cost=0.00..601729.04 rows=39059604 width=0)  
   CTE a9  
     ->  Aggregate  (cost=708898.20..708898.21 rows=1 width=8)  
           ->  Seq Scan on ccc9  (cost=0.00..609919.96 rows=39591296 width=0)  
   CTE a10  
     ->  Aggregate  (cost=783522.20..783522.21 rows=1 width=8)  
           ->  Seq Scan on ccc10  (cost=0.00..674124.76 rows=43758976 width=0)  
   CTE a11  
     ->  Aggregate  (cost=615479.05..615479.06 rows=1 width=8)  
           ->  Seq Scan on ccc11  (cost=0.00..529544.24 rows=34373924 width=0)  
   CTE a12  
     ->  Aggregate  (cost=951260.55..951260.56 rows=1 width=8)  
           ->  Seq Scan on ccc12  (cost=0.00..818443.04 rows=53127004 width=0)  
   CTE a13  
     ->  Aggregate  (cost=783499.00..783499.01 rows=1 width=8)  
           ->  Seq Scan on ccc13  (cost=0.00..674104.80 rows=43757680 width=0)  
   CTE a14  
     ->  Aggregate  (cost=913779.60..913779.61 rows=1 width=8)  
           ->  Seq Scan on ccc14  (cost=0.00..786195.28 rows=51033728 width=0)  
   CTE a15  
     ->  Aggregate  (cost=708653.05..708653.06 rows=1 width=8)  
           ->  Seq Scan on ccc15  (cost=0.00..609709.04 rows=39577604 width=0)  
   CTE a16  
     ->  Aggregate  (cost=736590.70..736590.71 rows=1 width=8)  
           ->  Seq Scan on ccc16  (cost=0.00..633745.96 rows=41137896 width=0)  
   CTE a17  
     ->  Aggregate  (cost=783320.20..783320.21 rows=1 width=8)  
           ->  Seq Scan on ccc17  (cost=0.00..673951.76 rows=43747376 width=0)  
   CTE a18  
     ->  Aggregate  (cost=932607.90..932607.91 rows=1 width=8)  
           ->  Seq Scan on ccc18  (cost=0.00..802394.72 rows=52085272 width=0)  
   CTE a19  
     ->  Aggregate  (cost=615568.50..615568.51 rows=1 width=8)  
           ->  Seq Scan on ccc19  (cost=0.00..529621.20 rows=34378920 width=0)  
   CTE a20  
     ->  Aggregate  (cost=746233.40..746233.41 rows=1 width=8)  
           ->  Seq Scan on ccc20  (cost=0.00..642042.32 rows=41676432 width=0)  
   CTE a21  
     ->  Aggregate  (cost=466366.88..466366.89 rows=1 width=8)  
           ->  Seq Scan on ccc21  (cost=0.00..401251.50 rows=26046150 width=0)  
   CTE a22  
     ->  Aggregate  (cost=783250.55..783250.56 rows=1 width=8)  
           ->  Seq Scan on ccc22  (cost=0.00..673891.04 rows=43743804 width=0)  
   CTE a23  
     ->  Aggregate  (cost=746223.45..746223.46 rows=1 width=8)  
           ->  Seq Scan on ccc23  (cost=0.00..642033.76 rows=41675876 width=0)  
   ->  Nested Loop  (cost=0.00..0.65 rows=1 width=176)  
         ->  Nested Loop  (cost=0.00..0.62 rows=1 width=168)  
               ->  Nested Loop  (cost=0.00..0.59 rows=1 width=160)  
                     ->  Nested Loop  (cost=0.00..0.56 rows=1 width=152)  
                           ->  Nested Loop  (cost=0.00..0.53 rows=1 width=144)  
                                 ->  Nested Loop  (cost=0.00..0.50 rows=1 width=136)  
                                       ->  Nested Loop  (cost=0.00..0.47 rows=1 width=128)  
                                             ->  Nested Loop  (cost=0.00..0.44 rows=1 width=120)  
                                                   ->  Nested Loop  (cost=0.00..0.41 rows=1 width=112)  
                                                         ->  Nested Loop  (cost=0.00..0.38 rows=1 width=104)  
                                                               ->  Nested Loop  (cost=0.00..0.35 rows=1 width=96)  
                                                                     ->  Nested Loop  (cost=0.00..0.32 rows=1 width=88)  
                                                                           ->  Nested Loop  (cost=0.00..0.29 rows=1 width=80)  
                                                                                 ->  Nested Loop  (cost=0.00..0.26 rows=1 width=72)  
                                                                                       ->  Nested Loop  (cost=0.00..0.23 rows=1 width=64)  
                                                                                             ->  Nested Loop  (cost=0.00..0.20 rows=1 width=56)  
                                                                                                   ->  Nested Loop  (cost=0.00..0.17 rows=1 width=48)  
                                                                                                         ->  Nested Loop  (cost=0.00..0.14 rows=1 width=40)  
                                                                                                               ->  Nested Loop  (cost=0.00..0.11 rows=1 width=32)  
                                                                                                                     ->  Nested Loop  (cost=0.00..0.08 rows=1 width=24)  
                                                                                                                           ->  Nested Loop  (cost=0.00..0.05 rows=1 width=16)  
                                                                                                                                 ->  CTE Scan on a9  (cost=0.00..0.02 rows=1 width=8)  
                                                                                                                                 ->  CTE Scan on a13  (cost=0.00..0.02 rows=1 width=8)  
                                                                                                                           ->  CTE Scan on a3  (cost=0.00..0.02 rows=1 width=8)  
                                                                                                                     ->  CTE Scan on a23  (cost=0.00..0.02 rows=1 width=8)  
                                                                                                               ->  CTE Scan on a18  (cost=0.00..0.02 rows=1 width=8)  
                                                                                                         ->  CTE Scan on a14  (cost=0.00..0.02 rows=1 width=8)  
                                                                                                   ->  CTE Scan on a6  (cost=0.00..0.02 rows=1 width=8)  
                                                                                             ->  CTE Scan on a5  (cost=0.00..0.02 rows=1 width=8)  
                                                                                       ->  CTE Scan on a11  (cost=0.00..0.02 rows=1 width=8)  
                                                                                 ->  CTE Scan on a15  (cost=0.00..0.02 rows=1 width=8)  
                                                                           ->  CTE Scan on a4  (cost=0.00..0.02 rows=1 width=8)  
                                                                     ->  CTE Scan on a19  (cost=0.00..0.02 rows=1 width=8)  
                                                               ->  CTE Scan on a2  (cost=0.00..0.02 rows=1 width=8)  
                                                         ->  CTE Scan on a8  (cost=0.00..0.02 rows=1 width=8)  
                                                   ->  CTE Scan on a10  (cost=0.00..0.02 rows=1 width=8)  
                                             ->  CTE Scan on a21  (cost=0.00..0.02 rows=1 width=8)  
                                       ->  CTE Scan on a20  (cost=0.00..0.02 rows=1 width=8)  
                                 ->  CTE Scan on a16  (cost=0.00..0.02 rows=1 width=8)  
                           ->  CTE Scan on a1  (cost=0.00..0.02 rows=1 width=8)  
                     ->  CTE Scan on a17  (cost=0.00..0.02 rows=1 width=8)  
               ->  CTE Scan on a7  (cost=0.00..0.02 rows=1 width=8)  
         ->  CTE Scan on a22  (cost=0.00..0.02 rows=1 width=8)  
   ->  CTE Scan on a12  (cost=0.00..0.02 rows=1 width=8)  
(114 rows)  
  
with           
a0 as (select count(*) from ccc0) ,   
a1 as (select count(*) from ccc1) ,   
a2 as (select count(*) from ccc2) ,  
a3 as (select count(*) from ccc3) ,  
a4 as (select count(*) from ccc4) ,  
a5 as (select count(*) from ccc5) ,  
a6 as (select count(*) from ccc6) ,  
a7 as (select count(*) from ccc7) ,  
a8 as (select count(*) from ccc8) ,  
a9 as (select count(*) from ccc9) ,  
a10 as (select count(*) from ccc10) ,   
a11 as (select count(*) from ccc11) ,   
a12 as (select count(*) from ccc12) ,  
a13 as (select count(*) from ccc13) ,  
a14 as (select count(*) from ccc14) ,  
a15 as (select count(*) from ccc15) ,  
a16 as (select count(*) from ccc16) ,  
a17 as (select count(*) from ccc17) ,  
a18 as (select count(*) from ccc18) ,  
a19 as (select count(*) from ccc19) ,  
a20 as (select count(*) from ccc20) ,   
a21 as (select count(*) from ccc21) ,   
a22 as (select count(*) from ccc22) ,  
a23 as (select count(*) from ccc23)    
select * from a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23;  
  
-[ RECORD 1 ]---  
count | 40624767  
count | 46873456  
count | 35414828  
count | 42693087  
count | 39586204  
count | 40630959  
count | 43742717  
count | 39059426  
count | 39591163  
count | 43758867  
count | 34373867  
count | 53126827  
count | 43757571  
count | 51033662  
count | 39577526  
count | 41137744  
count | 43748296  
count | 52085114  
count | 34378790  
count | 41676297  
count | 26046083  
count | 43743785  
count | 41675706  
  
Time: 65649.973 ms (01:05.650)

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

postgres=# set max_parallel_workers_per_gather =24;              
         
explain 	  
with   
a0 as (select count(*) from ccc0) ,   
a1 as (select count(*) from ccc1) ,  
a2 as (select count(*) from ccc2) ,  
a3 as (select count(*) from ccc3) ,  
a4 as (select count(*) from ccc4) ,  
a5 as (select count(*) from ccc5) ,  
a6 as (select count(*) from ccc6) ,  
a7 as (select count(*) from ccc7) ,  
a8 as (select count(*) from ccc8) ,  
a9 as (select count(*) from ccc9) ,  
a10 as (select count(*) from ccc10) ,   
a11 as (select count(*) from ccc11) ,  
a12 as (select count(*) from ccc12) ,  
a13 as (select count(*) from ccc13) ,  
a14 as (select count(*) from ccc14) ,  
a15 as (select count(*) from ccc15) ,  
a16 as (select count(*) from ccc16) ,  
a17 as (select count(*) from ccc17) ,  
a18 as (select count(*) from ccc18) ,  
a19 as (select count(*) from ccc19) ,  
a20 as (select count(*) from ccc20) ,   
a21 as (select count(*) from ccc21) ,  
a22 as (select count(*) from ccc22) ,  
a23 as (select count(*) from ccc23)   
select * from a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23;  
  
                                                                                      QUERY PLAN                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Nested Loop  (cost=5679348.60..5679349.28 rows=1 width=184)  
   CTE a1  
     ->  Finalize Aggregate  (cost=240752.87..240752.88 rows=1 width=8)  
           ->  Gather  (cost=240752.80..240752.81 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=240752.80..240752.81 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc1  (cost=0.00..236521.04 rows=1692704 width=0)  
   CTE a2  
     ->  Finalize Aggregate  (cost=277784.42..277784.43 rows=1 width=8)  
           ->  Gather  (cost=277784.35..277784.36 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=277784.35..277784.36 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc2  (cost=0.00..272901.68 rows=1953068 width=0)  
   CTE a3  
     ->  Finalize Aggregate  (cost=209877.03..209877.04 rows=1 width=8)  
           ->  Gather  (cost=209876.96..209876.97 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=209876.96..209876.97 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc3  (cost=0.00..206187.97 rows=1475597 width=0)  
   CTE a4  
     ->  Finalize Aggregate  (cost=253010.11..253010.12 rows=1 width=8)  
           ->  Gather  (cost=253010.04..253010.05 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=253010.04..253010.05 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc4  (cost=0.00..248562.83 rows=1778883 width=0)  
   CTE a5  
     ->  Finalize Aggregate  (cost=234597.58..234597.59 rows=1 width=8)  
           ->  Gather  (cost=234597.51..234597.52 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=234597.51..234597.52 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc5  (cost=0.00..230474.01 rows=1649401 width=0)  
   CTE a6  
     ->  Finalize Aggregate  (cost=240788.91..240788.92 rows=1 width=8)  
           ->  Gather  (cost=240788.84..240788.85 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=240788.84..240788.85 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc6  (cost=0.00..236556.47 rows=1692947 width=0)  
   CTE a7  
     ->  Finalize Aggregate  (cost=259230.82..259230.83 rows=1 width=8)  
           ->  Gather  (cost=259230.75..259230.76 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=259230.75..259230.76 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc7  (cost=0.00..254674.20 rows=1822620 width=0)  
   CTE a8  
     ->  Finalize Aggregate  (cost=231476.61..231476.62 rows=1 width=8)  
           ->  Gather  (cost=231476.54..231476.55 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=231476.54..231476.55 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc8  (cost=0.00..227407.83 rows=1627484 width=0)  
   CTE a9  
     ->  Finalize Aggregate  (cost=234627.54..234627.55 rows=1 width=8)  
           ->  Gather  (cost=234627.47..234627.48 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=234627.47..234627.48 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc9  (cost=0.00..230503.37 rows=1649637 width=0)  
   CTE a10  
     ->  Finalize Aggregate  (cost=259326.20..259326.21 rows=1 width=8)  
           ->  Gather  (cost=259326.13..259326.14 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=259326.13..259326.14 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc10  (cost=0.00..254767.91 rows=1823291 width=0)  
   CTE a11  
     ->  Finalize Aggregate  (cost=203708.16..203708.17 rows=1 width=8)  
           ->  Gather  (cost=203708.09..203708.10 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=203708.09..203708.10 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc11  (cost=0.00..200127.47 rows=1432247 width=0)  
   CTE a12  
     ->  Finalize Aggregate  (cost=314843.38..314843.39 rows=1 width=8)  
           ->  Gather  (cost=314843.31..314843.32 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=314843.31..314843.32 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc12  (cost=0.00..309309.25 rows=2213625 width=0)  
   CTE a13  
     ->  Finalize Aggregate  (cost=259318.53..259318.54 rows=1 width=8)  
           ->  Gather  (cost=259318.46..259318.47 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=259318.46..259318.47 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc13  (cost=0.00..254760.37 rows=1823237 width=0)  
   CTE a14  
     ->  Finalize Aggregate  (cost=302438.14..302438.15 rows=1 width=8)  
           ->  Gather  (cost=302438.07..302438.08 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=302438.07..302438.08 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc14  (cost=0.00..297122.05 rows=2126405 width=0)  
   CTE a15  
     ->  Finalize Aggregate  (cost=234546.41..234546.42 rows=1 width=8)  
           ->  Gather  (cost=234546.34..234546.35 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=234546.34..234546.35 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc15  (cost=0.00..230423.67 rows=1649067 width=0)  
   CTE a16  
     ->  Finalize Aggregate  (cost=243793.06..243793.07 rows=1 width=8)  
           ->  Gather  (cost=243792.99..243793.00 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=243792.99..243793.00 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc16  (cost=0.00..239507.79 rows=1714079 width=0)  
   CTE a17  
     ->  Finalize Aggregate  (cost=259263.16..259263.17 rows=1 width=8)  
           ->  Gather  (cost=259263.09..259263.10 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=259263.09..259263.10 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc17  (cost=0.00..254706.07 rows=1822807 width=0)  
   CTE a18  
     ->  Finalize Aggregate  (cost=308669.82..308669.83 rows=1 width=8)  
           ->  Gather  (cost=308669.75..308669.76 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=308669.75..308669.76 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc18  (cost=0.00..303244.20 rows=2170220 width=0)  
   CTE a19  
     ->  Finalize Aggregate  (cost=203737.76..203737.77 rows=1 width=8)  
           ->  Gather  (cost=203737.69..203737.70 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=203737.69..203737.70 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc19  (cost=0.00..200156.55 rows=1432455 width=0)  
   CTE a20  
     ->  Finalize Aggregate  (cost=246984.55..246984.56 rows=1 width=8)  
           ->  Gather  (cost=246984.48..246984.49 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=246984.48..246984.49 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc20  (cost=0.00..242643.18 rows=1736518 width=0)  
   CTE a21  
     ->  Finalize Aggregate  (cost=154355.77..154355.78 rows=1 width=8)  
           ->  Gather  (cost=154355.70..154355.71 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=154355.70..154355.71 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc21  (cost=0.00..151642.56 rows=1085256 width=0)  
   CTE a22  
     ->  Finalize Aggregate  (cost=259236.30..259236.31 rows=1 width=8)  
           ->  Gather  (cost=259236.23..259236.24 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=259236.23..259236.24 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc22  (cost=0.00..254679.58 rows=1822658 width=0)  
   CTE a23  
     ->  Finalize Aggregate  (cost=246981.26..246981.27 rows=1 width=8)  
           ->  Gather  (cost=246981.19..246981.20 rows=24 width=8)  
                 Workers Planned: 24  
                 ->  Partial Aggregate  (cost=246981.19..246981.20 rows=1 width=8)  
                       ->  Parallel Seq Scan on ccc23  (cost=0.00..242639.95 rows=1736495 width=0)  
   ->  Nested Loop  (cost=0.00..0.65 rows=1 width=176)  
         ->  Nested Loop  (cost=0.00..0.62 rows=1 width=168)  
               ->  Nested Loop  (cost=0.00..0.59 rows=1 width=160)  
                     ->  Nested Loop  (cost=0.00..0.56 rows=1 width=152)  
                           ->  Nested Loop  (cost=0.00..0.53 rows=1 width=144)  
                                 ->  Nested Loop  (cost=0.00..0.50 rows=1 width=136)  
                                       ->  Nested Loop  (cost=0.00..0.47 rows=1 width=128)  
                                             ->  Nested Loop  (cost=0.00..0.44 rows=1 width=120)  
                                                   ->  Nested Loop  (cost=0.00..0.41 rows=1 width=112)  
                                                         ->  Nested Loop  (cost=0.00..0.38 rows=1 width=104)  
                                                               ->  Nested Loop  (cost=0.00..0.35 rows=1 width=96)  
                                                                     ->  Nested Loop  (cost=0.00..0.32 rows=1 width=88)  
                                                                           ->  Nested Loop  (cost=0.00..0.29 rows=1 width=80)  
                                                                                 ->  Nested Loop  (cost=0.00..0.26 rows=1 width=72)  
                                                                                       ->  Nested Loop  (cost=0.00..0.23 rows=1 width=64)  
                                                                                             ->  Nested Loop  (cost=0.00..0.20 rows=1 width=56)  
                                                                                                   ->  Nested Loop  (cost=0.00..0.17 rows=1 width=48)  
                                                                                                         ->  Nested Loop  (cost=0.00..0.14 rows=1 width=40)  
                                                                                                               ->  Nested Loop  (cost=0.00..0.11 rows=1 width=32)  
                                                                                                                     ->  Nested Loop  (cost=0.00..0.08 rows=1 width=24)  
                                                                                                                           ->  Nested Loop  (cost=0.00..0.05 rows=1 width=16)  
                                                                                                                                 ->  CTE Scan on a9  (cost=0.00..0.02 rows=1 width=8)  
                                                                                                                                 ->  CTE Scan on a13  (cost=0.00..0.02 rows=1 width=8)  
                                                                                                                           ->  CTE Scan on a3  (cost=0.00..0.02 rows=1 width=8)  
                                                                                                                     ->  CTE Scan on a23  (cost=0.00..0.02 rows=1 width=8)  
                                                                                                               ->  CTE Scan on a18  (cost=0.00..0.02 rows=1 width=8)  
                                                                                                         ->  CTE Scan on a14  (cost=0.00..0.02 rows=1 width=8)  
                                                                                                   ->  CTE Scan on a6  (cost=0.00..0.02 rows=1 width=8)  
                                                                                             ->  CTE Scan on a5  (cost=0.00..0.02 rows=1 width=8)  
                                                                                       ->  CTE Scan on a11  (cost=0.00..0.02 rows=1 width=8)  
                                                                                 ->  CTE Scan on a15  (cost=0.00..0.02 rows=1 width=8)  
                                                                           ->  CTE Scan on a4  (cost=0.00..0.02 rows=1 width=8)  
                                                                     ->  CTE Scan on a19  (cost=0.00..0.02 rows=1 width=8)  
                                                               ->  CTE Scan on a2  (cost=0.00..0.02 rows=1 width=8)  
                                                         ->  CTE Scan on a8  (cost=0.00..0.02 rows=1 width=8)  
                                                   ->  CTE Scan on a10  (cost=0.00..0.02 rows=1 width=8)  
                                             ->  CTE Scan on a21  (cost=0.00..0.02 rows=1 width=8)  
                                       ->  CTE Scan on a20  (cost=0.00..0.02 rows=1 width=8)  
                                 ->  CTE Scan on a16  (cost=0.00..0.02 rows=1 width=8)  
                           ->  CTE Scan on a1  (cost=0.00..0.02 rows=1 width=8)  
                     ->  CTE Scan on a17  (cost=0.00..0.02 rows=1 width=8)  
               ->  CTE Scan on a7  (cost=0.00..0.02 rows=1 width=8)  
         ->  CTE Scan on a22  (cost=0.00..0.02 rows=1 width=8)  
   ->  CTE Scan on a12  (cost=0.00..0.02 rows=1 width=8)  
(183 rows)  
  
  
with           
a0 as (select count(*) from ccc0) ,   
a1 as (select count(*) from ccc1) ,   
a2 as (select count(*) from ccc2) ,  
a3 as (select count(*) from ccc3) ,  
a4 as (select count(*) from ccc4) ,  
a5 as (select count(*) from ccc5) ,  
a6 as (select count(*) from ccc6) ,  
a7 as (select count(*) from ccc7) ,  
a8 as (select count(*) from ccc8) ,  
a9 as (select count(*) from ccc9) ,  
a10 as (select count(*) from ccc10) ,   
a11 as (select count(*) from ccc11) ,   
a12 as (select count(*) from ccc12) ,  
a13 as (select count(*) from ccc13) ,  
a14 as (select count(*) from ccc14) ,  
a15 as (select count(*) from ccc15) ,  
a16 as (select count(*) from ccc16) ,  
a17 as (select count(*) from ccc17) ,  
a18 as (select count(*) from ccc18) ,  
a19 as (select count(*) from ccc19) ,  
a20 as (select count(*) from ccc20) ,   
a21 as (select count(*) from ccc21) ,   
a22 as (select count(*) from ccc22) ,  
a23 as (select count(*) from ccc23)    
select * from a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23;  
  
-[ RECORD 1 ]---  
count | 40624767  
count | 46873456  
count | 35414828  
count | 42693087  
count | 39586204  
count | 40630959  
count | 43742717  
count | 39059426  
count | 39591163  
count | 43758867  
count | 34373867  
count | 53126827  
count | 43757571  
count | 51033662  
count | 39577526  
count | 41137744  
count | 43748296  
count | 52085114  
count | 34378790  
count | 41676297  
count | 26046083  
count | 43743785  
count | 41675706  
  
Time: 3328.141 ms (00:03.328)

https://github.com/digoal/blog/blob/master/201903/20190317_17.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_17.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_17.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 并行計算解說之24 - parallel CTE (Common Table Express)

PostgreSQL 并行計算解說之24 - parallel CTE (Common Table Express)

标簽

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

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

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

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

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

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

https://github.com/digoal/blog/blob/master/201903/20190317_17.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 并行計算解說 之24 - parallel CTE (Common Table Express)

标簽

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

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

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

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

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

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

繼續閱讀

PostgreSQL 并行計算解說之24 - parallel CTE (Common Table Express)