PostgreSQL11preview-ParallelAppend(多表平行計算)sharding架構平行計算核心功能之一
標籤
PostgreSQL , 多表並行 , parallel append , sharding , 外部表 , 繼承 , 分割槽表 , union , pg_pathman , inherit
背景
append是資料庫執行計劃中很場景的一個NODE，資料來自掃描多個物件的集合時，都需要APPEND。比如：
1、掃描分割槽表
2、掃描主表（包含若干繼承表時）
3、UNION ALL語句。(union 暫時不支援)
使用parallel append的功能，可以設計出非常靈活的架構，例如sharding可以在資料庫核心層面並行，不需要依賴中介軟體例如plproxy了。(暫時還不支援直接用foreign table+inherit的模式，不過可以用pg_pathman)
parallel append 引數開關
enable_parallel_append (boolean)
Enables or disables the query planner`s use of parallel-aware append plan types.
The default is on.
測試
# 全域性可使用WORKER程式數
max_worker_processes = 128
# 全域性可開平行計算的WORKER程式數
max_parallel_workers = 128
# 平行計算COST計算依賴的幾個引數
set parallel_tuple_cost =0;
set parallel_setup_cost =0;
# 最小多大的表才會使用平行計算
set min_parallel_index_scan_size =0;
set min_parallel_table_scan_size =0;
# 每個gather可以建立多個worker process
set max_parallel_workers_per_gather =32;
生成測試資料
1、建立本地分割槽表
create unlogged table p(id int8, info text) partition by hash(id);
CREATE unlogged TABLE p0 PARTITION OF p FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE unlogged TABLE p1 PARTITION OF p FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE unlogged TABLE p2 PARTITION OF p FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE unlogged TABLE p3 PARTITION OF p FOR VALUES WITH (MODULUS 4, REMAINDER 3);
2、寫入1億測試資料
insert into p select generate_series(1,100000000), `test`;
3、設定分割槽並行度為0，防止單個分割槽並行掃描太快，看不出效能差異。
alter table p0 set ( parallel_workers=0 );
alter table p1 set ( parallel_workers=0 );
alter table p2 set ( parallel_workers=0 );
alter table p3 set ( parallel_workers=0 );
當每個分割槽都返回大量資料時
這裡測試了兩個CASE，一個含並行聚合，一個不含平行計算（全量返回）。實際上parallel append適合一路並行，而不適合上層沒什麼計算，序列接收大量APPEND資料的場景。
1、含並行聚合（上層直接對接partial agg worker，所以流式處理掉了），並行append
postgres=# set enable_parallel_append =on;
SET
postgres=# explain (analyze,verbose) select count(*) from p;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=850840.80..850840.81 rows=1 width=8) (actual time=6400.860..6400.861 rows=1 loops=1)
Output: count(*)
-> Gather (cost=850840.78..850840.79 rows=3 width=8) (actual time=5630.195..6400.849 rows=4 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 3
Workers Launched: 3
-> Partial Aggregate (cost=850840.78..850840.79 rows=1 width=8) (actual time=6133.146..6133.147 rows=1 loops=4)
Output: PARTIAL count(*)
Worker 0: actual time=6253.609..6253.609 rows=1 loops=1
Worker 1: actual time=6395.587..6395.588 rows=1 loops=1
Worker 2: actual time=6253.407..6253.407 rows=1 loops=1
-> Parallel Append (cost=0.00..770195.40 rows=32258152 width=0) (actual time=0.027..4772.225 rows=25000000 loops=4)
Worker 0: actual time=0.030..4882.573 rows=24999575 loops=1
Worker 1: actual time=0.030..5025.288 rows=25002155 loops=1
Worker 2: actual time=0.035..4906.483 rows=25002850 loops=1
-> Seq Scan on public.p3 (cost=0.00..385180.36 rows=25002936 width=0) (actual time=0.033..3137.362 rows=25002850 loops=1)
Worker 2: actual time=0.033..3137.362 rows=25002850 loops=1
-> Seq Scan on public.p1 (cost=0.00..385168.96 rows=25002196 width=0) (actual time=0.030..3253.775 rows=25002155 loops=1)
Worker 1: actual time=0.030..3253.775 rows=25002155 loops=1
-> Seq Scan on public.p0 (cost=0.00..385129.04 rows=24999604 width=0) (actual time=0.029..3110.662 rows=24999575 loops=1)
Worker 0: actual time=0.029..3110.662 rows=24999575 loops=1
-> Seq Scan on public.p2 (cost=0.00..385066.36 rows=24995536 width=0) (actual time=0.011..2512.500 rows=24995420 loops=1)
Planning time: 0.261 ms
Execution time: 6463.125 ms
(24 rows)
2、序列APPEND
postgres=# set enable_parallel_append =off;
SET
postgres=# explain (analyze,verbose) select count(*) from p;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1790545.40..1790545.41 rows=1 width=8) (actual time=21705.971..21705.972 rows=1 loops=1)
Output: count(*)
-> Append (cost=0.00..1540544.72 rows=100000272 width=0) (actual time=0.010..16055.808 rows=100000000 loops=1)
-> Seq Scan on public.p0 (cost=0.00..385129.04 rows=24999604 width=0) (actual time=0.010..2214.981 rows=24999575 loops=1)
-> Seq Scan on public.p1 (cost=0.00..385168.96 rows=25002196 width=0) (actual time=0.011..2225.458 rows=25002155 loops=1)
-> Seq Scan on public.p2 (cost=0.00..385066.36 rows=24995536 width=0) (actual time=0.013..2264.015 rows=24995420 loops=1)
-> Seq Scan on public.p3 (cost=0.00..385180.36 rows=25002936 width=0) (actual time=0.013..2214.180 rows=25002850 loops=1)
Planning time: 0.111 ms
Execution time: 21706.010 ms
(9 rows)
3、不含並行聚合（上層返回所有資料，效能反而下降），並行append
postgres=# explain (analyze,verbose)
select * from p;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=0.00..770195.40 rows=100000272 width=13) (actual time=0.238..72791.861 rows=100000000 loops=1)
Output: p3.id, p3.info
Workers Planned: 3
Workers Launched: 3
-> Parallel Append (cost=0.00..770195.40 rows=32258152 width=13) (actual time=0.019..4450.007 rows=25000000 loops=4)
Worker 0: actual time=0.021..4713.479 rows=24999575 loops=1
Worker 1: actual time=0.021..4705.110 rows=25002155 loops=1
Worker 2: actual time=0.023..4710.256 rows=25002850 loops=1
-> Seq Scan on public.p3 (cost=0.00..385180.36 rows=25002936 width=13) (actual time=0.022..2955.118 rows=25002850 loops=1)
Output: p3.id, p3.info
Worker 2: actual time=0.022..2955.118 rows=25002850 loops=1
-> Seq Scan on public.p1 (cost=0.00..385168.96 rows=25002196 width=13) (actual time=0.020..2949.203 rows=25002155 loops=1)
Output: p1.id, p1.info
Worker 1: actual time=0.020..2949.203 rows=25002155 loops=1
-> Seq Scan on public.p0 (cost=0.00..385129.04 rows=24999604 width=13) (actual time=0.021..2957.799 rows=24999575 loops=1)
Output: p0.id, p0.info
Worker 0: actual time=0.021..2957.799 rows=24999575 loops=1
-> Seq Scan on public.p2 (cost=0.00..385066.36 rows=24995536 width=13) (actual time=0.009..1919.412 rows=24995420 loops=1)
Output: p2.id, p2.info
Planning time: 0.156 ms
Execution time: 76464.568 ms
(21 rows)
4、序列APPEND
postgres=# set enable_parallel_append =off;
SET
postgres=# explain (analyze,verbose)
select * from p;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..1540544.72 rows=100000272 width=13) (actual time=0.009..14691.301 rows=100000000 loops=1)
-> Seq Scan on public.p0 (cost=0.00..385129.04 rows=24999604 width=13) (actual time=0.008..1930.118 rows=24999575 loops=1)
Output: p0.id, p0.info
-> Seq Scan on public.p1 (cost=0.00..385168.96 rows=25002196 width=13) (actual time=0.012..1946.220 rows=25002155 loops=1)
Output: p1.id, p1.info
-> Seq Scan on public.p2 (cost=0.00..385066.36 rows=24995536 width=13) (actual time=0.011..1911.555 rows=24995420 loops=1)
Output: p2.id, p2.info
-> Seq Scan on public.p3 (cost=0.00..385180.36 rows=25002936 width=13) (actual time=0.013..1933.505 rows=25002850 loops=1)
Output: p3.id, p3.info
Planning time: 0.111 ms
Execution time: 18336.654 ms
(11 rows)
當每個分割槽僅返回少量資料時
1、並行append
postgres=# set enable_parallel_append =on;
SET
postgres=# explain (analyze,verbose) select count(*) from p where id=1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=895183.26..895183.27 rows=1 width=8) (actual time=2315.544..2315.545 rows=1 loops=1)
Output: count(*)
-> Gather (cost=0.00..895183.25 rows=4 width=0) (actual time=1769.974..2315.536 rows=1 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Parallel Append (cost=0.00..895183.25 rows=1 width=0) (actual time=1591.915..2169.437 rows=0 loops=4)
Worker 0: actual time=0.025..2310.110 rows=1 loops=1
Worker 1: actual time=2286.699..2286.699 rows=0 loops=1
Worker 2: actual time=2311.206..2311.206 rows=0 loops=1
-> Seq Scan on public.p3 (cost=0.00..447687.70 rows=1 width=0) (actual time=2311.205..2311.205 rows=0 loops=1)
Filter: (p3.id = 1)
Rows Removed by Filter: 25002850
Worker 2: actual time=2311.205..2311.205 rows=0 loops=1
-> Seq Scan on public.p1 (cost=0.00..447674.45 rows=1 width=0) (actual time=2286.697..2286.697 rows=0 loops=1)
Filter: (p1.id = 1)
Rows Removed by Filter: 25002155
Worker 1: actual time=2286.697..2286.697 rows=0 loops=1
-> Seq Scan on public.p0 (cost=0.00..447628.05 rows=1 width=0) (actual time=0.024..2310.109 rows=1 loops=1)
Filter: (p0.id = 1)
Rows Removed by Filter: 24999574
Worker 0: actual time=0.024..2310.109 rows=1 loops=1
-> Seq Scan on public.p2 (cost=0.00..447555.20 rows=1 width=0) (actual time=1769.730..1769.730 rows=0 loops=1)
Filter: (p2.id = 1)
Rows Removed by Filter: 24995420
Planning time: 0.138 ms
Execution time: 2365.247 ms
(26 rows)
2、序列APPEND
postgres=# set enable_parallel_append =off;
SET
postgres=# explain (analyze,verbose) select count(*) from p where id=1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1790545.41..1790545.42 rows=1 width=8) (actual time=6989.018..6989.018 rows=1 loops=1)
Output: count(*)
-> Append (cost=0.00..1790545.40 rows=4 width=0) (actual time=0.011..6989.011 rows=1 loops=1)
-> Seq Scan on public.p0 (cost=0.00..447628.05 rows=1 width=0) (actual time=0.011..1788.032 rows=1 loops=1)
Filter: (p0.id = 1)
Rows Removed by Filter: 24999574
-> Seq Scan on public.p1 (cost=0.00..447674.45 rows=1 width=0) (actual time=1732.249..1732.249 rows=0 loops=1)
Filter: (p1.id = 1)
Rows Removed by Filter: 25002155
-> Seq Scan on public.p2 (cost=0.00..447555.20 rows=1 width=0) (actual time=1731.916..1731.916 rows=0 loops=1)
Filter: (p2.id = 1)
Rows Removed by Filter: 24995420
-> Seq Scan on public.p3 (cost=0.00..447687.70 rows=1 width=0) (actual time=1736.809..1736.809 rows=0 loops=1)
Filter: (p3.id = 1)
Rows Removed by Filter: 25002850
Planning time: 0.259 ms
Execution time: 6989.060 ms
(17 rows)
測試union all的parallel append
postgres=# set enable_parallel_append =on;
SET
postgres=#
explain (analyze,verbose)
select count(*) from p0 where id=1
union all
select count(*) from p1 where id=1
union all
select count(*) from p2 where id=1
union all
select count(*) from p3 where id=1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Gather (cost=447627.70..895181.50 rows=4 width=8) (actual time=1855.298..2363.268 rows=4 loops=1)
Output: (count(*))
Workers Planned: 3
Workers Launched: 3
-> Parallel Append (cost=447627.70..895181.50 rows=1 width=8) (actual time=2215.816..2215.817 rows=1 loops=4)
Worker 0: actual time=2356.711..2356.712 rows=1 loops=1
Worker 1: actual time=2336.656..2336.657 rows=1 loops=1
Worker 2: actual time=2314.918..2314.919 rows=1 loops=1
-> Aggregate (cost=447686.63..447686.64 rows=1 width=8) (actual time=2314.917..2314.918 rows=1 loops=1)
Output: count(*)
Worker 2: actual time=2314.917..2314.918 rows=1 loops=1
-> Seq Scan on public.p3 (cost=0.00..447686.62 rows=1 width=0) (actual time=2314.906..2314.906 rows=0 loops=1)
Output: p3.id, p3.info
Filter: (p3.id = 1)
Rows Removed by Filter: 25002850
Worker 2: actual time=2314.906..2314.906 rows=0 loops=1
-> Aggregate (cost=447673.95..447673.96 rows=1 width=8) (actual time=2336.655..2336.655 rows=1 loops=1)
Output: count(*)
Worker 1: actual time=2336.655..2336.655 rows=1 loops=1
-> Seq Scan on public.p1 (cost=0.00..447673.95 rows=1 width=0) (actual time=2336.646..2336.646 rows=0 loops=1)
Output: p1.id, p1.info
Filter: (p1.id = 1)
Rows Removed by Filter: 25002155
Worker 1: actual time=2336.646..2336.646 rows=0 loops=1
-> Aggregate (cost=447627.70..447627.71 rows=1 width=8) (actual time=2356.710..2356.710 rows=1 loops=1)
Output: count(*)
Worker 0: actual time=2356.710..2356.710 rows=1 loops=1
-> Seq Scan on public.p0 (cost=0.00..447627.70 rows=1 width=0) (actual time=0.027..2356.702 rows=1 loops=1)
Output: p0.id, p0.info
Filter: (p0.id = 1)
Rows Removed by Filter: 24999574
Worker 0: actual time=0.027..2356.702 rows=1 loops=1
-> Aggregate (cost=447553.75..447553.76 rows=1 width=8) (actual time=1854.978..1854.979 rows=1 loops=1)
Output: count(*)
-> Seq Scan on public.p2 (cost=0.00..447553.75 rows=1 width=0) (actual time=1854.973..1854.973 rows=0 loops=1)
Output: p2.id, p2.info
Filter: (p2.id = 1)
Rows Removed by Filter: 24995420
Planning time: 0.160 ms
Execution time: 2413.145 ms
(40 rows)
測試本地繼承表的parallel append
1、準備資料
create table pp (like p);
create table pp0 (like p) inherits(pp);
create table pp1 (like p) inherits(pp);
create table pp2 (like p) inherits(pp);
create table pp3 (like p) inherits(pp);
insert into pp0 select * from p0;
insert into pp1 select * from p1;
insert into pp2 select * from p2;
insert into pp3 select * from p3;
alter table pp set (parallel_workers =0);
alter table pp0 set (parallel_workers =0);
alter table pp1 set (parallel_workers =0);
alter table pp2 set (parallel_workers =0);
alter table pp3 set (parallel_workers =0);
2、parallel append
postgres=# set enable_parallel_append =on;
SET
postgres=# explain (analyze,verbose) select count(*) from pp where id=1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=896183.57..896183.58 rows=1 width=8) (actual time=2726.483..2726.483 rows=1 loops=1)
Output: count(*)
-> Gather (cost=896183.25..896183.56 rows=3 width=8) (actual time=2644.834..2726.474 rows=4 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 3
Workers Launched: 3
-> Partial Aggregate (cost=895183.25..895183.26 rows=1 width=8) (actual time=2617.010..2617.010 rows=1 loops=4)
Output: PARTIAL count(*)
Worker 0: actual time=2469.011..2469.011 rows=1 loops=1
Worker 1: actual time=2721.059..2721.059 rows=1 loops=1
Worker 2: actual time=2633.534..2633.534 rows=1 loops=1
-> Parallel Append (cost=0.00..895183.25 rows=1 width=0) (actual time=1999.759..2617.002 rows=0 loops=4)
Worker 0: actual time=0.034..2469.004 rows=1 loops=1
Worker 1: actual time=2721.048..2721.048 rows=0 loops=1
Worker 2: actual time=2633.525..2633.525 rows=0 loops=1
-> Seq Scan on public.pp3 (cost=0.00..447687.70 rows=1 width=0) (actual time=2633.523..2633.523 rows=0 loops=1)
Filter: (pp3.id = 1)
Rows Removed by Filter: 25002850
Worker 2: actual time=2633.523..2633.523 rows=0 loops=1
-> Seq Scan on public.pp1 (cost=0.00..447674.45 rows=1 width=0) (actual time=2721.047..2721.047 rows=0 loops=1)
Filter: (pp1.id = 1)
Rows Removed by Filter: 25002155
Worker 1: actual time=2721.047..2721.047 rows=0 loops=1
-> Seq Scan on public.pp0 (cost=0.00..447628.05 rows=1 width=0) (actual time=0.034..2469.002 rows=1 loops=1)
Filter: (pp0.id = 1)
Rows Removed by Filter: 24999574
Worker 0: actual time=0.034..2469.002 rows=1 loops=1
-> Seq Scan on public.pp2 (cost=0.00..447555.20 rows=1 width=0) (actual time=2644.426..2644.426 rows=0 loops=1)
Filter: (pp2.id = 1)
Rows Removed by Filter: 24995420
-> Seq Scan on public.pp (cost=0.00..0.00 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (pp.id = 1)
Planning time: 0.080 ms
Execution time: 2777.958 ms
(34 rows)
3、序列append
postgres=# set enable_parallel_append =off;
SET
postgres=# explain (analyze,verbose) select count(*) from pp where id=1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1790545.41..1790545.42 rows=1 width=8) (actual time=7619.788..7619.788 rows=1 loops=1)
Output: count(*)
-> Append (cost=0.00..1790545.40 rows=5 width=0) (actual time=0.015..7619.783 rows=1 loops=1)
-> Seq Scan on public.pp (cost=0.00..0.00 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (pp.id = 1)
-> Seq Scan on public.pp0 (cost=0.00..447628.05 rows=1 width=0) (actual time=0.013..1999.372 rows=1 loops=1)
Filter: (pp0.id = 1)
Rows Removed by Filter: 24999574
-> Seq Scan on public.pp1 (cost=0.00..447674.45 rows=1 width=0) (actual time=1984.480..1984.480 rows=0 loops=1)
Filter: (pp1.id = 1)
Rows Removed by Filter: 25002155
-> Seq Scan on public.pp2 (cost=0.00..447555.20 rows=1 width=0) (actual time=1713.703..1713.703 rows=0 loops=1)
Filter: (pp2.id = 1)
Rows Removed by Filter: 24995420
-> Seq Scan on public.pp3 (cost=0.00..447687.70 rows=1 width=0) (actual time=1922.221..1922.221 rows=0 loops=1)
Filter: (pp3.id = 1)
Rows Removed by Filter: 25002850
Planning time: 0.079 ms
Execution time: 7619.821 ms
(19 rows)
外部表並行append？
1、建立postgres_fdw
CREATE EXTENSION postgres_fdw;
2、建立外部資料來源
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host `127.0.0.1`, port `9999`, dbname `postgres`);
3、設定外部資料來源訪問祕鑰
CREATE USER MAPPING FOR postgres
SERVER foreign_server
OPTIONS (user `postgres`, password `password`);
4、建立外部表
CREATE FOREIGN TABLE f0 (
id int8,
info text
)
SERVER foreign_server
OPTIONS (schema_name `public`, table_name `p0`);
CREATE FOREIGN TABLE f1 (
id int8,
info text
)
SERVER foreign_server
OPTIONS (schema_name `public`, table_name `p1`);
CREATE FOREIGN TABLE f2 (
id int8,
info text
)
SERVER foreign_server
OPTIONS (schema_name `public`, table_name `p2`);
CREATE FOREIGN TABLE f3 (
id int8,
info text
)
SERVER foreign_server
OPTIONS (schema_name `public`, table_name `p3`);
5、建立外部表繼承關係
create table f(id int8, info text);
alter foreign table f0 inherit f;
alter foreign table f1 inherit f;
alter foreign table f2 inherit f;
alter foreign table f3 inherit f;
64個分割槽的分割槽表測試
1、建立本地分割槽表
create unlogged table ppp(id int8, info text) partition by hash(id);
do language plpgsql $$
declare
begin
for i in 0..63 loop
execute format(`CREATE unlogged TABLE ppp%s PARTITION OF ppp FOR VALUES WITH (MODULUS 64, REMAINDER %s)`, i, i);
execute format(`alter table ppp%s set ( parallel_workers=0 )`, i);
end loop;
end;
$$;
2、寫入2億測試資料
insert into ppp select generate_series(1,100000000), `test`;
insert into ppp select generate_series(1,100000000), `test`;
3、序列append測試
set enable_parallel_append =off;
explain (analyze,verbose) select count(*) from ppp where id=1;
postgres=# explain (analyze,verbose) select count(*) from ppp where id=1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3581110.32..3581110.33 rows=1 width=8) (actual time=14179.260..14179.260 rows=1 loops=1)
Output: count(*)
-> Append (cost=0.00..3581110.00 rows=128 width=0) (actual time=12412.108..14179.251 rows=2 loops=1)
-> Seq Scan on public.ppp0 (cost=0.00..55939.20 rows=2 width=0) (actual time=220.643..220.643 rows=0 loops=1)
Filter: (ppp0.id = 1)
Rows Removed by Filter: 3124096
-> Seq Scan on public.ppp1 (cost=0.00..55926.80 rows=2 width=0) (actual time=218.816..218.816 rows=0 loops=1)
Filter: (ppp1.id = 1)
Rows Removed by Filter: 3123424
.......
-> Seq Scan on public.ppp62 (cost=0.00..55997.28 rows=2 width=0) (actual time=218.784..218.784 rows=0 loops=1)
Filter: (ppp62.id = 1)
Rows Removed by Filter: 3127382
-> Seq Scan on public.ppp63 (cost=0.00..55895.03 rows=2 width=0) (actual time=218.490..218.490 rows=0 loops=1)
Filter: (ppp63.id = 1)
Rows Removed by Filter: 3121682
Planning time: 0.753 ms
Execution time: 14179.495 ms
(197 rows)
4、並行append測試
set enable_parallel_append =on;
alter table ppp0 set (parallel_workers =64);
postgres=# explain (analyze,verbose) select count(*) from ppp where id=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=73565.00..73565.01 rows=1 width=8) (actual time=685.176..685.176 rows=1 loops=1)
Output: count(*)
-> Gather (cost=73564.83..73564.84 rows=64 width=8) (actual time=293.716..685.121 rows=65 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 64
Workers Launched: 64
-> Partial Aggregate (cost=73564.83..73564.84 rows=1 width=8) (actual time=523.155..523.155 rows=1 loops=65)
Output: PARTIAL count(*)
Worker 0: actual time=532.810..532.810 rows=1 loops=1
Worker 1: actual time=507.106..507.106 rows=1 loops=1
Worker 2: actual time=584.379..584.380 rows=1 loops=1
Worker 3: actual time=506.344..506.344 rows=1 loops=1
.........
Worker 61: actual time=539.029..539.029 rows=1 loops=1
Worker 62: actual time=536.463..536.463 rows=1 loops=1
Worker 63: actual time=533.323..533.323 rows=1 loops=1
-> Parallel Append (cost=0.00..73564.82 rows=2 width=0) (actual time=514.156..523.146 rows=0 loops=65)
Worker 0: actual time=532.802..532.802 rows=0 loops=1
Worker 1: actual time=507.098..507.098 rows=0 loops=1
Worker 2: actual time=0.025..584.367 rows=2 loops=1
Worker 3: actual time=506.334..506.334 rows=0 loops=1
...............
Worker 60: actual time=528.767..528.767 rows=0 loops=1
Worker 61: actual time=539.020..539.020 rows=0 loops=1
Worker 62: actual time=536.454..536.454 rows=0 loops=1
Worker 63: actual time=533.313..533.313 rows=0 loops=1
-> Seq Scan on public.ppp13 (cost=0.00..56066.65 rows=2 width=0) (actual time=533.312..533.312 rows=0 loops=1)
Filter: (ppp13.id = 1)
Rows Removed by Filter: 3131252
Worker 63: actual time=533.312..533.312 rows=0 loops=1
-> Seq Scan on public.ppp6 (cost=0.00..56056.88 rows=2 width=0) (actual time=536.452..536.452 rows=0 loops=1)
Filter: (ppp6.id = 1)
Rows Removed by Filter: 3130710
Worker 62: actual time=536.452..536.452 rows=0 loops=1
....................
-> Seq Scan on public.ppp58 (cost=0.00..55880.68 rows=2 width=0) (actual time=292.269..292.269 rows=0 loops=1)
Filter: (ppp58.id = 1)
Rows Removed by Filter: 3120854
Worker 5: actual time=292.269..292.269 rows=0 loops=1
-> Seq Scan on public.ppp56 (cost=0.00..55874.90 rows=2 width=0) (actual time=0.016..224.329 rows=2 loops=1)
Filter: (ppp56.id = 1)
Rows Removed by Filter: 3120550
-> Parallel Seq Scan on public.ppp0 (cost=0.00..18108.35 rows=1 width=0) (actual time=225.194..225.194 rows=0 loops=1)
Filter: (ppp0.id = 1)
Rows Removed by Filter: 3124096
Planning time: 0.807 ms
Execution time: 655.200 ms
(329 rows)
.........
測試外部表是否可以使用parallel append
postgres=# set enable_parallel_append =on;
SET
1、測試外部表的繼承表主表
postgres=#
explain (analyze,verbose)
select count(*) from f where id=1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=612.18..612.19 rows=1 width=8) (actual time=7071.067..7071.068 rows=1 loops=1)
Output: count(*)
-> Append (cost=0.00..612.01 rows=69 width=0) (actual time=1740.313..7071.060 rows=1 loops=1)
-> Seq Scan on public.f (cost=0.00..0.00 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (f.id = 1)
-> Foreign Scan on public.f0 (cost=100.00..153.00 rows=17 width=0) (actual time=1740.310..1740.311 rows=1 loops=1)
Remote SQL: SELECT NULL FROM public.p0 WHERE ((id = 1))
-> Foreign Scan on public.f1 (cost=100.00..153.00 rows=17 width=0) (actual time=1860.924..1860.924 rows=0 loops=1)
Remote SQL: SELECT NULL FROM public.p1 WHERE ((id = 1))
-> Foreign Scan on public.f2 (cost=100.00..153.00 rows=17 width=0) (actual time=1733.198..1733.198 rows=0 loops=1)
Remote SQL: SELECT NULL FROM public.p2 WHERE ((id = 1))
-> Foreign Scan on public.f3 (cost=100.00..153.00 rows=17 width=0) (actual time=1736.619..1736.619 rows=0 loops=1)
Remote SQL: SELECT NULL FROM public.p3 WHERE ((id = 1))
Planning time: 0.192 ms
Execution time: 7071.329 ms
(15 rows)
2、使用union all查詢多個外部表
postgres=#
explain (analyze,verbose)
select count(*) from f0 where id=1
union all
select count(*) from f1 where id=1
union all
select count(*) from f2 where id=1
union all
select count(*) from f3 where id=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Append (cost=100.04..610.98 rows=4 width=8) (actual time=1731.125..6926.563 rows=4 loops=1)
-> Foreign Scan (cost=100.04..152.73 rows=1 width=8) (actual time=1731.125..1731.125 rows=1 loops=1)
Output: (count(*))
Relations: Aggregate on (public.f0)
Remote SQL: SELECT count(*) FROM public.p0 WHERE ((id = 1))
-> Foreign Scan (cost=100.04..152.73 rows=1 width=8) (actual time=1732.174..1732.175 rows=1 loops=1)
Output: (count(*))
Relations: Aggregate on (public.f1)
Remote SQL: SELECT count(*) FROM public.p1 WHERE ((id = 1))
-> Foreign Scan (cost=100.04..152.73 rows=1 width=8) (actual time=1731.794..1731.795 rows=1 loops=1)
Output: (count(*))
Relations: Aggregate on (public.f2)
Remote SQL: SELECT count(*) FROM public.p2 WHERE ((id = 1))
-> Foreign Scan (cost=100.04..152.73 rows=1 width=8) (actual time=1731.465..1731.465 rows=1 loops=1)
Output: (count(*))
Relations: Aggregate on (public.f3)
Remote SQL: SELECT count(*) FROM public.p3 WHERE ((id = 1))
Planning time: 0.205 ms
Execution time: 6926.909 ms
(19 rows)
小結
1、parallel append功能，在每個參與append的資料分片計算並返回的記錄數比較少時，效能提升幾乎是線性的。
2、當每個參與append的資料分片都要返回大量資料時，需要注意是否有用到支援並行的聚合，如果沒有，那麼上層還需要處理大量資料量PARALLEL APPEND效果就不明顯。否則不要使用parallel append。(實際上parallel append適合一路並行，而不適合上層沒什麼計算，序列接收大量APPEND資料的場景。)
3、parallel append + 外部表 + pushdown，可以實現sharding 架構下的併發計算。（例如求SUM,AVG,COUNG,MIN,MAX等，不過針對外部表的parallel append核心層面還沒有支援好，需要CUSTOM SCAN）
4、parallel append VS append 效能
| case | parallel append耗時 | 序列 append耗時 | parallel append效能提升 | 點評 |
|---|---|---|---|---|
| 1億，4個並行分片，每個分片返回少量資料 | 2.37 秒 | 6.99 秒 | 2.95 倍 | 幾乎線性提升 |
| 1億，4個並行分片，每個分片返回大量資料（但是下一個NODE包括並行聚合） | 6.46 秒 | 21.7 秒 | 3.36 倍 | 幾乎線性提升 |
| 1億，4個並行分片，每個分片返回大量資料（下一個NODE需要序列返回大量資料） | 76.5 秒 | 18.3 秒 | – 4.18 倍 | append的下一個NODE需要返回大量資料時不適合使用並行append |
| 2億，64個並行分片，每個分片返回少量資料 | 0.655 秒 | 14.18 秒 | 21.65 倍 | 並行越大，提升越明顯，這裡還需要考慮記憶體頻寬瓶頸（20多倍時，處理速度為 12.9 GB/s） |
參考
https://commitfest.postgresql.org/16/987/
《PostgreSQL 9.6 sharding based on FDW & pg_pathman》
《PostgreSQL 9.5+ 高效分割槽表實現 – pg_pathman》
相關文章
- 雲端計算分散式平行計算：系統架構分散式架構
- 平行計算π值
- Oracle平行計算Oracle
- 平行計算cuda
- GPU：平行計算利器GPU
- JDK7的平行計算功能升級JDK
- 淺談.NET下的多執行緒和平行計算（十四）平行計算前言執行緒
- java8平行計算Java
- 平行計算與Neon簡介
- HPC高效能運算知識: 異構平行計算
- 大文字平行計算實現方式
- OpenCV使用ParallelLoopBody進行平行計算OpenCVParallelOOP
- springboot~CompletableFuture平行計算Spring Boot
- 瞭解Flow -- elixir的平行計算庫
- 引文——平行計算的學習之殤
- 多核平行計算時代的來臨
- 請問，平行計算和資料庫資料庫
- 後端請求中的非同步計算與平行計算後端非同步
- 黃仁勳：序列計算過時平行計算是未來
- [930]python平行計算框架pathos模組Python框架
- 【1】Embarrassingly Parallel（易平行計算問題）Parallel
- 大資料平行計算利器之MPI/OpenMP大資料
- 推薦文章：多執行緒平行計算執行緒
- 完數的OpenMP並行程式設計-平行計算並行行程程式設計
- 完數的MPI並行程式設計-平行計算並行行程程式設計
- PostgreSQL11preview-平行計算增強彙總SQLView
- Java通過Fork/Join來優化平行計算Java優化
- 在“平行計算”中增加了幾篇文章
- [索引]Oracle RAC資料庫平行計算的使用索引Oracle資料庫
- 論文榮登計算機體系結構頂會ISCA，晶片架構成為邊緣AI最佳平行計算選擇計算機晶片架構AI
- cuda程式設計與gpu平行計算（四）：cuda程式設計模型程式設計GPU模型
- 使用 QuTrunk+Amazon ParallelCluster3 進行平行計算Parallel
- 第二篇：從 GPU 的角度理解平行計算GPU
- Linux叢集的安裝與平行計算（轉）Linux
- .NET4.0平行計算技術基礎（2）
- .NET4.0平行計算技術基礎（1）
- [python] Python平行計算庫Joblib使用指北Python
- 平行計算與並行程式設計課程內容介紹並行行程程式設計