PostgreSQL 并行计算解说之24 - parallel CTE (Common Table Express)

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)