标簽
PostgreSQL , cpu 并行 , smp 并行 , 并行計算 , gpu 并行 , 并行過程支援
https://github.com/digoal/blog/blob/master/201903/20190317_12.md#%E8%83%8C%E6%99%AF 背景
PostgreSQL 11 優化器已經支援了非常多場合的并行。簡單估計，已支援27餘種場景的并行計算。
parallel seq scan
parallel index scan
parallel index only scan
parallel bitmap scan
parallel filter
parallel hash agg
parallel group agg
parallel cte
parallel subquery
parallel create table
parallel create index
parallel select into
parallel CREATE MATERIALIZED VIEW
parallel 排序 : gather merge
parallel nestloop join
parallel hash join
parallel merge join
parallel 自定義并行聚合
parallel 自定義并行UDF
parallel append
parallel union
parallel fdw table scan
parallel partition join
parallel partition agg
parallel gather
parallel gather merge
parallel rc 并行
parallel rr 并行
parallel GPU 并行
parallel unlogged table
lead parallel
接下來進行一一介紹。
關鍵知識請先自行了解：
1、優化器自動并行度算法 CBO
《PostgreSQL 9.6 并行計算 優化器算法淺析》 《PostgreSQL 11 并行計算算法，參數，強制并行度設定》https://github.com/digoal/blog/blob/master/201903/20190317_12.md#parallel-partition-table-wise-join parallel partition table wise join
并行分區表智能JOIN（類似MPP）
當兩個JOIN的分區表JOIN字段類型一緻，并且分區在JOIN字段上，并且分區類型一緻（枚舉、LIST、範圍、HASH），并且分區個數一緻。滿足這些條件時，PostgreSQL優化器會選擇并行分區智能JOIN，子分區各自JOIN子分區。
《PostgreSQL 11 preview - 分區表智能并行聚合、分組計算(已類似MPP架構，性能暴增)》 《PostgreSQL 11 preview - 分區表智能并行JOIN (已類似MPP架構，性能暴增)》資料量：10億 join 10億
| 場景 | 資料量 | 關閉并行 | 開啟并行 | 并行度 | 開啟并行性能提升倍數 |
|---|---|---|---|---|---|
| 10億 join 10億 using (i) | 1006 秒 | 76 秒 | 24 | 13.2 倍 |
例子，24個分區的HASH分區表。
CREATE unlogged TABLE aaa (
order_id bigint not null,
cust_id bigint not null,
status text
) PARTITION BY HASH (order_id);
CREATE unlogged TABLE bbb (
order_id bigint not null,
cust_id bigint not null,
status text
) PARTITION BY HASH (order_id);
do language plpgsql $$
declare
begin
for i in 0..23 loop
execute format('CREATE unlogged TABLE %s%s PARTITION OF %s FOR VALUES WITH (MODULUS %s, REMAINDER %s)', 'aaa', i, 'aaa', 24, i);
execute format('CREATE unlogged TABLE %s%s PARTITION OF %s FOR VALUES WITH (MODULUS %s, REMAINDER %s)', 'bbb', i, 'bbb', 24, i);
execute format('alter table %s%s set(parallel_workers =64)', 'aaa',i);
execute format('alter table %s%s set(parallel_workers =64)', 'bbb',i);
end loop;
end;
$$;
postgres=# \d aaa
Unlogged table "public.aaa"
Column | Type | Collation | Nullable | Default
----------+--------+-----------+----------+---------
order_id | bigint | | not null |
cust_id | bigint | | not null |
status | text | | |
Partition key: HASH (order_id)
Number of partitions: 24 (Use \d+ to list them.)
postgres=# \d bbb
Unlogged table "public.bbb"
Column | Type | Collation | Nullable | Default
----------+--------+-----------+----------+---------
order_id | bigint | | not null |
cust_id | bigint | | not null |
status | text | | |
Partition key: HASH (order_id)
Number of partitions: 24 (Use \d+ to list them.)
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_join =on;
postgres=# set work_mem ='128MB';
分别寫入10億資料
insert into aaa select i, random()*100000 from generate_series(1,1000000000) t(i);
insert into bbb select i, random()*100000 from generate_series(1,1000000000) t(i);
vacuum (analyze,verbose) aaa;
vacuum (analyze,verbose) bbb;
https://github.com/digoal/blog/blob/master/201903/20190317_12.md#1%E5%85%B3%E9%97%AD%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-1006-%E7%A7%92 1、關閉并行，耗時： 1006 秒。
postgres=# set max_parallel_workers_per_gather =0;
postgres=# set enable_parallel_append =off;
postgres=# set enable_partitionwise_join =off;
postgres=# explain select count(*) from aaa join bbb using (order_id);
QUERY PLAN
-----------------------------------------------------------------------------------------------
Aggregate (cost=380800207.07..380800207.08 rows=1 width=8)
-> Merge Join (cost=355800197.59..378300206.45 rows=1000000248 width=0)
Merge Cond: (bbb0.order_id = aaa0.order_id)
-> Sort (cost=177900158.19..180400160.45 rows=1000000904 width=8)
Sort Key: bbb0.order_id
-> Append (cost=0.00..20405431.56 rows=1000000904 width=8)
-> Seq Scan on bbb0 (cost=0.00..641834.24 rows=41662924 width=8)
-> Seq Scan on bbb1 (cost=0.00..641805.76 rows=41661076 width=8)
-> Seq Scan on bbb2 (cost=0.00..641899.80 rows=41667180 width=8)
-> Seq Scan on bbb3 (cost=0.00..641919.76 rows=41668476 width=8)
-> Seq Scan on bbb4 (cost=0.00..641896.96 rows=41666996 width=8)
-> Seq Scan on bbb5 (cost=0.00..641854.20 rows=41664220 width=8)
-> Seq Scan on bbb6 (cost=0.00..641839.96 rows=41663296 width=8)
-> Seq Scan on bbb7 (cost=0.00..641894.08 rows=41666808 width=8)
-> Seq Scan on bbb8 (cost=0.00..641723.12 rows=41655712 width=8)
-> Seq Scan on bbb9 (cost=0.00..641754.88 rows=41657688 width=8)
-> Seq Scan on bbb10 (cost=0.00..641848.48 rows=41663848 width=8)
-> Seq Scan on bbb11 (cost=0.00..641771.56 rows=41658856 width=8)
-> Seq Scan on bbb12 (cost=0.00..642119.24 rows=41681424 width=8)
-> Seq Scan on bbb13 (cost=0.00..642059.68 rows=41677368 width=8)
-> Seq Scan on bbb14 (cost=0.00..641765.84 rows=41658484 width=8)
-> Seq Scan on bbb15 (cost=0.00..641703.16 rows=41654416 width=8)
-> Seq Scan on bbb16 (cost=0.00..641794.36 rows=41660336 width=8)
-> Seq Scan on bbb17 (cost=0.00..641842.80 rows=41663480 width=8)
-> Seq Scan on bbb18 (cost=0.00..642127.80 rows=41681980 width=8)
-> Seq Scan on bbb19 (cost=0.00..642048.00 rows=41676800 width=8)
-> Seq Scan on bbb20 (cost=0.00..641999.56 rows=41673656 width=8)
-> Seq Scan on bbb21 (cost=0.00..641971.04 rows=41671804 width=8)
-> Seq Scan on bbb22 (cost=0.00..641976.00 rows=41671900 width=8)
-> Seq Scan on bbb23 (cost=0.00..641976.76 rows=41672176 width=8)
-> Materialize (cost=177900039.40..182900040.64 rows=1000000248 width=8)
-> Sort (cost=177900039.40..180400040.02 rows=1000000248 width=8)
Sort Key: aaa0.order_id
-> Append (cost=0.00..20405421.72 rows=1000000248 width=8)
-> Seq Scan on aaa0 (cost=0.00..641834.24 rows=41662924 width=8)
-> Seq Scan on aaa1 (cost=0.00..641805.76 rows=41661076 width=8)
-> Seq Scan on aaa2 (cost=0.00..641899.80 rows=41667180 width=8)
-> Seq Scan on aaa3 (cost=0.00..641919.76 rows=41668476 width=8)
-> Seq Scan on aaa4 (cost=0.00..641896.96 rows=41666996 width=8)
-> Seq Scan on aaa5 (cost=0.00..641854.20 rows=41664220 width=8)
-> Seq Scan on aaa6 (cost=0.00..641839.96 rows=41663296 width=8)
-> Seq Scan on aaa7 (cost=0.00..641894.08 rows=41666808 width=8)
-> Seq Scan on aaa8 (cost=0.00..641723.12 rows=41655712 width=8)
-> Seq Scan on aaa9 (cost=0.00..641757.28 rows=41657928 width=8)
-> Seq Scan on aaa10 (cost=0.00..641848.48 rows=41663848 width=8)
-> Seq Scan on aaa11 (cost=0.00..641771.56 rows=41658856 width=8)
-> Seq Scan on aaa12 (cost=0.00..642119.24 rows=41681424 width=8)
-> Seq Scan on aaa13 (cost=0.00..642065.12 rows=41677912 width=8)
-> Seq Scan on aaa14 (cost=0.00..641757.00 rows=41657600 width=8)
-> Seq Scan on aaa15 (cost=0.00..641703.16 rows=41654416 width=8)
-> Seq Scan on aaa16 (cost=0.00..641794.36 rows=41660336 width=8)
-> Seq Scan on aaa17 (cost=0.00..641842.80 rows=41663480 width=8)
-> Seq Scan on aaa18 (cost=0.00..642127.80 rows=41681980 width=8)
-> Seq Scan on aaa19 (cost=0.00..642048.00 rows=41676800 width=8)
-> Seq Scan on aaa20 (cost=0.00..641999.56 rows=41673656 width=8)
-> Seq Scan on aaa21 (cost=0.00..641959.04 rows=41670604 width=8)
-> Seq Scan on aaa22 (cost=0.00..641982.44 rows=41672544 width=8)
-> Seq Scan on aaa23 (cost=0.00..641976.76 rows=41672176 width=8)
(58 rows)
postgres=# select count(*) from aaa join bbb using (order_id);
count
------------
1000000000
(1 row)
Time: 1005965.972 ms (16:45.966)
https://github.com/digoal/blog/blob/master/201903/20190317_12.md#2%E5%BC%80%E5%90%AF%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-76-%E7%A7%92 2、開啟并行，耗時： 76 秒。
postgres=# set max_parallel_workers_per_gather =24;
postgres=# set enable_parallel_append =off;
postgres=# set enable_partitionwise_join =on;
postgres=# explain select count(*) from aaa join bbb using (order_id);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=12494865.77..12494865.78 rows=1 width=8)
-> Gather (cost=520571.51..12494864.33 rows=576 width=8)
Workers Planned: 24
-> Append (cost=520571.51..12494864.33 rows=24 width=8)
-> Partial Aggregate (cost=520571.51..520571.52 rows=1 width=8)
-> Parallel Hash Join (cost=264263.99..516231.63 rows=1735955 width=0)
Hash Cond: (aaa0.order_id = bbb0.order_id)
-> Parallel Seq Scan on aaa0 (cost=0.00..242564.55 rows=1735955 width=8)
-> Parallel Hash (cost=242564.55..242564.55 rows=1735955 width=8)
-> Parallel Seq Scan on bbb0 (cost=0.00..242564.55 rows=1735955 width=8)
-> Partial Aggregate (cost=520548.40..520548.41 rows=1 width=8)
-> Parallel Hash Join (cost=264252.26..516208.71 rows=1735878 width=0)
Hash Cond: (aaa1.order_id = bbb1.order_id)
-> Parallel Seq Scan on aaa1 (cost=0.00..242553.78 rows=1735878 width=8)
-> Parallel Hash (cost=242553.78..242553.78 rows=1735878 width=8)
-> Parallel Seq Scan on bbb1 (cost=0.00..242553.78 rows=1735878 width=8)
-> Partial Aggregate (cost=520624.68..520624.69 rows=1 width=8)
-> Parallel Hash Join (cost=264290.98..516284.35 rows=1736132 width=0)
Hash Cond: (aaa2.order_id = bbb2.order_id)
-> Parallel Seq Scan on aaa2 (cost=0.00..242589.33 rows=1736132 width=8)
-> Parallel Hash (cost=242589.33..242589.33 rows=1736132 width=8)
-> Parallel Seq Scan on bbb2 (cost=0.00..242589.33 rows=1736132 width=8)
-> Partial Aggregate (cost=520640.86..520640.87 rows=1 width=8)
-> Parallel Hash Join (cost=264299.19..516300.40 rows=1736186 width=0)
Hash Cond: (aaa3.order_id = bbb3.order_id)
-> Parallel Seq Scan on aaa3 (cost=0.00..242596.86 rows=1736186 width=8)
-> Parallel Hash (cost=242596.86..242596.86 rows=1736186 width=8)
-> Parallel Seq Scan on bbb3 (cost=0.00..242596.86 rows=1736186 width=8)
-> Partial Aggregate (cost=520622.39..520622.40 rows=1 width=8)
-> Parallel Hash Join (cost=264289.81..516282.07 rows=1736125 width=0)
Hash Cond: (aaa4.order_id = bbb4.order_id)
-> Parallel Seq Scan on aaa4 (cost=0.00..242588.25 rows=1736125 width=8)
-> Parallel Hash (cost=242588.25..242588.25 rows=1736125 width=8)
-> Parallel Seq Scan on bbb4 (cost=0.00..242588.25 rows=1736125 width=8)
-> Partial Aggregate (cost=520587.70..520587.71 rows=1 width=8)
-> Parallel Hash Join (cost=264272.20..516247.68 rows=1736009 width=0)
Hash Cond: (aaa5.order_id = bbb5.order_id)
-> Parallel Seq Scan on aaa5 (cost=0.00..242572.09 rows=1736009 width=8)
-> Parallel Hash (cost=242572.09..242572.09 rows=1736009 width=8)
-> Parallel Seq Scan on bbb5 (cost=0.00..242572.09 rows=1736009 width=8)
-> Partial Aggregate (cost=520576.15..520576.16 rows=1 width=8)
-> Parallel Hash Join (cost=264266.34..516236.23 rows=1735971 width=0)
Hash Cond: (aaa6.order_id = bbb6.order_id)
-> Parallel Seq Scan on aaa6 (cost=0.00..242566.71 rows=1735971 width=8)
-> Parallel Hash (cost=242566.71..242566.71 rows=1735971 width=8)
-> Parallel Seq Scan on bbb6 (cost=0.00..242566.71 rows=1735971 width=8)
-> Partial Aggregate (cost=520620.06..520620.07 rows=1 width=8)
-> Parallel Hash Join (cost=264288.63..516279.77 rows=1736117 width=0)
Hash Cond: (aaa7.order_id = bbb7.order_id)
-> Parallel Seq Scan on aaa7 (cost=0.00..242587.17 rows=1736117 width=8)
-> Parallel Hash (cost=242587.17..242587.17 rows=1736117 width=8)
-> Parallel Seq Scan on bbb7 (cost=0.00..242587.17 rows=1736117 width=8)
-> Partial Aggregate (cost=520481.38..520481.39 rows=1 width=8)
-> Parallel Hash Join (cost=264218.23..516142.25 rows=1735655 width=0)
Hash Cond: (aaa8.order_id = bbb8.order_id)
-> Parallel Seq Scan on aaa8 (cost=0.00..242522.55 rows=1735655 width=8)
-> Parallel Hash (cost=242522.55..242522.55 rows=1735655 width=8)
-> Parallel Seq Scan on bbb8 (cost=0.00..242522.55 rows=1735655 width=8)
-> Partial Aggregate (cost=520508.85..520508.86 rows=1 width=8)
-> Parallel Hash Join (cost=264232.08..516169.51 rows=1735737 width=0)
Hash Cond: (aaa9.order_id = bbb9.order_id)
-> Parallel Seq Scan on aaa9 (cost=0.00..242535.47 rows=1735747 width=8)
-> Parallel Hash (cost=242535.37..242535.37 rows=1735737 width=8)
-> Parallel Seq Scan on bbb9 (cost=0.00..242535.37 rows=1735737 width=8)
-> Partial Aggregate (cost=520583.08..520583.09 rows=1 width=8)
-> Parallel Hash Join (cost=264269.86..516243.10 rows=1735994 width=0)
Hash Cond: (aaa10.order_id = bbb10.order_id)
-> Parallel Seq Scan on aaa10 (cost=0.00..242569.94 rows=1735994 width=8)
-> Parallel Hash (cost=242569.94..242569.94 rows=1735994 width=8)
-> Parallel Seq Scan on bbb10 (cost=0.00..242569.94 rows=1735994 width=8)
-> Partial Aggregate (cost=520520.67..520520.68 rows=1 width=8)
-> Parallel Hash Join (cost=264238.18..516181.21 rows=1735786 width=0)
Hash Cond: (aaa11.order_id = bbb11.order_id)
-> Parallel Seq Scan on aaa11 (cost=0.00..242540.86 rows=1735786 width=8)
-> Parallel Hash (cost=242540.86..242540.86 rows=1735786 width=8)
-> Parallel Seq Scan on bbb11 (cost=0.00..242540.86 rows=1735786 width=8)
-> Partial Aggregate (cost=520802.68..520802.69 rows=1 width=8)
-> Parallel Hash Join (cost=264381.34..516460.87 rows=1736726 width=0)
Hash Cond: (aaa12.order_id = bbb12.order_id)
-> Parallel Seq Scan on aaa12 (cost=0.00..242672.26 rows=1736726 width=8)
-> Parallel Hash (cost=242672.26..242672.26 rows=1736726 width=8)
-> Parallel Seq Scan on bbb12 (cost=0.00..242672.26 rows=1736726 width=8)
-> Partial Aggregate (cost=520758.19..520758.20 rows=1 width=8)
-> Parallel Hash Join (cost=264358.53..516416.80 rows=1736557 width=0)
Hash Cond: (aaa13.order_id = bbb13.order_id)
-> Parallel Seq Scan on aaa13 (cost=0.00..242651.80 rows=1736580 width=8)
-> Parallel Hash (cost=242651.57..242651.57 rows=1736557 width=8)
-> Parallel Seq Scan on bbb13 (cost=0.00..242651.57 rows=1736557 width=8)
-> Partial Aggregate (cost=520515.10..520515.11 rows=1 width=8)
-> Parallel Hash Join (cost=264235.00..516175.77 rows=1735733 width=0)
Hash Cond: (bbb14.order_id = aaa14.order_id)
-> Parallel Seq Scan on bbb14 (cost=0.00..242538.70 rows=1735770 width=8)
-> Parallel Hash (cost=242538.33..242538.33 rows=1735733 width=8)
-> Parallel Seq Scan on aaa14 (cost=0.00..242538.33 rows=1735733 width=8)
-> Partial Aggregate (cost=520465.20..520465.21 rows=1 width=8)
-> Parallel Hash Join (cost=264210.02..516126.20 rows=1735601 width=0)
Hash Cond: (aaa15.order_id = bbb15.order_id)
-> Parallel Seq Scan on aaa15 (cost=0.00..242515.01 rows=1735601 width=8)
-> Parallel Hash (cost=242515.01..242515.01 rows=1735601 width=8)
-> Parallel Seq Scan on bbb15 (cost=0.00..242515.01 rows=1735601 width=8)
-> Partial Aggregate (cost=520539.16..520539.17 rows=1 width=8)
-> Parallel Hash Join (cost=264247.56..516199.54 rows=1735847 width=0)
Hash Cond: (aaa16.order_id = bbb16.order_id)
-> Parallel Seq Scan on aaa16 (cost=0.00..242549.47 rows=1735847 width=8)
-> Parallel Hash (cost=242549.47..242549.47 rows=1735847 width=8)
-> Parallel Seq Scan on bbb16 (cost=0.00..242549.47 rows=1735847 width=8)
-> Partial Aggregate (cost=520578.45..520578.46 rows=1 width=8)
-> Parallel Hash Join (cost=264267.51..516238.50 rows=1735978 width=0)
Hash Cond: (aaa17.order_id = bbb17.order_id)
-> Parallel Seq Scan on aaa17 (cost=0.00..242567.78 rows=1735978 width=8)
-> Parallel Hash (cost=242567.78..242567.78 rows=1735978 width=8)
-> Parallel Seq Scan on bbb17 (cost=0.00..242567.78 rows=1735978 width=8)
-> Partial Aggregate (cost=520809.61..520809.62 rows=1 width=8)
-> Parallel Hash Join (cost=264384.85..516467.74 rows=1736749 width=0)
Hash Cond: (aaa18.order_id = bbb18.order_id)
-> Parallel Seq Scan on aaa18 (cost=0.00..242675.49 rows=1736749 width=8)
-> Parallel Hash (cost=242675.49..242675.49 rows=1736749 width=8)
-> Parallel Seq Scan on bbb18 (cost=0.00..242675.49 rows=1736749 width=8)
-> Partial Aggregate (cost=520744.89..520744.90 rows=1 width=8)
-> Parallel Hash Join (cost=264352.00..516403.55 rows=1736533 width=0)
Hash Cond: (aaa19.order_id = bbb19.order_id)
-> Parallel Seq Scan on aaa19 (cost=0.00..242645.33 rows=1736533 width=8)
-> Parallel Hash (cost=242645.33..242645.33 rows=1736533 width=8)
-> Parallel Seq Scan on bbb19 (cost=0.00..242645.33 rows=1736533 width=8)
-> Partial Aggregate (cost=520705.59..520705.60 rows=1 width=8)
-> Parallel Hash Join (cost=264332.05..516364.58 rows=1736402 width=0)
Hash Cond: (aaa20.order_id = bbb20.order_id)
-> Parallel Seq Scan on aaa20 (cost=0.00..242627.02 rows=1736402 width=8)
-> Parallel Hash (cost=242627.02..242627.02 rows=1736402 width=8)
-> Parallel Seq Scan on bbb20 (cost=0.00..242627.02 rows=1736402 width=8)
-> Partial Aggregate (cost=520681.20..520681.21 rows=1 width=8)
-> Parallel Hash Join (cost=264319.19..516340.52 rows=1736275 width=0)
Hash Cond: (bbb21.order_id = aaa21.order_id)
-> Parallel Seq Scan on bbb21 (cost=0.00..242616.25 rows=1736325 width=8)
-> Parallel Hash (cost=242615.75..242615.75 rows=1736275 width=8)
-> Parallel Seq Scan on aaa21 (cost=0.00..242615.75 rows=1736275 width=8)
-> Partial Aggregate (cost=520691.04..520691.05 rows=1 width=8)
-> Parallel Hash Join (cost=264324.40..516350.21 rows=1736329 width=0)
Hash Cond: (aaa22.order_id = bbb22.order_id)
-> Parallel Seq Scan on aaa22 (cost=0.00..242620.56 rows=1736356 width=8)
-> Parallel Hash (cost=242620.29..242620.29 rows=1736329 width=8)
-> Parallel Seq Scan on bbb22 (cost=0.00..242620.29 rows=1736329 width=8)
-> Partial Aggregate (cost=520687.11..520687.12 rows=1 width=8)
-> Parallel Hash Join (cost=264322.67..516346.26 rows=1736341 width=0)
Hash Cond: (aaa23.order_id = bbb23.order_id)
-> Parallel Seq Scan on aaa23 (cost=0.00..242618.41 rows=1736341 width=8)
-> Parallel Hash (cost=242618.41..242618.41 rows=1736341 width=8)
-> Parallel Seq Scan on bbb23 (cost=0.00..242618.41 rows=1736341 width=8)
(148 rows)
postgres=# select count(*) from aaa join bbb using (order_id);
count
------------
1000000000
(1 row)
Time: 76118.036 ms (01:16.118)
https://github.com/digoal/blog/blob/master/201903/20190317_12.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_12.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_12.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虛拟機
![2021年危險化學品經營機關安全管理人員考試題庫及危險化學品經營機關安全管理人員考試技巧[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)