标签
PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持
背景
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 11 并行计算算法，参数，强制并行度设置》
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';
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)
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)
其他知识
1、优化器自动并行度算法 CBO
《PostgreSQL 11 并行计算算法，参数，强制并行度设置》
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实例)》
暂时不允许并行的场景(将来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://www.postgresql.org/docs/11/parallel-plans.html
《PostgreSQL 11 并行计算算法，参数，强制并行度设置》
《PostgreSQL 11 preview - 并行计算 增强 汇总》
《PostgreSQL 10 自定义并行计算聚合函数的原理与实践 - (含array_agg合并多个数组为单个一元数组的例子)》
免费领取阿里云RDS PostgreSQL实例、ECS虚拟机
