标签
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 递归查询, 树状查询, 异构查询, CTE, recursive CTE, connect by
parallel subquery
parallel create table
parallel create index
parallel CREATE INDEX CONCURRENTLY - 不堵塞读写
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, recursive CTE, connect by
支持并行递归查询
数据量：异构数据1亿，日志数据10亿
| 场景 | 数据量 | 关闭并行 | 开启并行 | 并行度 | 开启并行性能提升倍数 |
|---|---|---|---|---|---|
| parallel 递归查询, 树状查询, 异构查询, CTE, recursive CTE, connect by | 异构数据1亿，日志数据10亿 | 5.14 秒 | 0.29 秒 | 24 | 17.7 倍 |
测试用例
《PostgreSQL 递归应用实践 - 非“传销”的高并发实时藤、树状佣金分配体系》
统计树中每个ID在日志表中的聚合。
1、树状表结构设计
create unlogged table tbl (
uid int8 primary key, -- 用户ID
pid int8 -- 直接上游ID,如果一个用户是ROOT用户，则PID为 null
);
create index idx_tbl_1 on tbl (pid);
2、创建一个函数，按规则返回它的上游
create or replace function gen_pid(int8) returns int8 as $$
-- 生成它的上游ID，200万以内的ID为根ID。其他都取比它小200万对应的那个ID，形成一颗50级的树。
select case when $1<=2000000 then null else $1-2000000 end;
$$ language sql strict;
3、树状数据，写入1亿数据，形成深度为50的树。
insert into tbl select id, gen_pid(id) from generate_series(1,100000000) t(id) on conflict do nothing;
4、行为数据10亿
create unlogged table log (uid int, info text, crt_time timestamp);
insert into log select random()*10+1 , '', now() from generate_series(1,1000000000);
create index idx_log_1 on log(uid);
alter table tbl set (parallel_workers =24);
alter table log set (parallel_workers =24);
vacuum analyze tbl;
vacuum analyze log;
set min_parallel_index_scan_size =0;
set max_parallel_workers=64;
set max_parallel_workers_per_gather =24;
set parallel_setup_cost =0;
set parallel_tuple_cost =0;
set work_mem ='1GB';
set parallel_leader_participation=off;
获取树状值对应行为数据的统计信息。
1、关闭并行，耗时： 5.14 秒。
postgres=# explain with recursive tmp as (
select uid,pid from tbl where pid =1
union all
select tbl.uid,tbl.pid from tbl join tmp on (tmp.uid=tbl.pid) where tbl.* is not null
) ,
b as
(select uid, count(*) cnt from log where uid = any
(array(
select pid from tmp
))
group by uid)
select tmp.*, case when b.cnt is not null then b.cnt else 0 end as cnt from tmp left join b on (tmp.pid=b.uid);
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Hash Left Join (cost=15566024.20..15566026.71 rows=101 width=24)
Hash Cond: (tmp.pid = b.uid)
CTE tmp
-> Recursive Union (cost=0.57..286.31 rows=101 width=16)
-> Index Scan using idx_tbl_1 on tbl (cost=0.57..2.79 rows=1 width=16)
Index Cond: (pid = 1)
-> Nested Loop (cost=0.57..28.15 rows=10 width=16)
-> WorkTable Scan on tmp tmp_1 (cost=0.00..0.20 rows=10 width=8)
-> Index Scan using idx_tbl_1 on tbl tbl_1 (cost=0.57..2.79 rows=1 width=16)
Index Cond: (pid = tmp_1.uid)
Filter: (tbl_1.* IS NOT NULL)
CTE b
-> GroupAggregate (cost=2.59..15565737.54 rows=11 width=12)
Group Key: log.uid
InitPlan 2 (returns $3)
-> CTE Scan on tmp tmp_2 (cost=0.00..2.02 rows=101 width=8)
-> Index Only Scan using idx_log_1 on log (cost=0.57..12493451.46 rows=614456789 width=4)
Index Cond: (uid = ANY ($3))
-> CTE Scan on tmp (cost=0.00..2.02 rows=101 width=16)
-> Hash (cost=0.22..0.22 rows=11 width=12)
-> CTE Scan on b (cost=0.00..0.22 rows=11 width=12)
(21 rows)
Time: 0.803 ms
postgres=# with recursive tmp as (
select uid,pid from tbl where pid =1
union all
select tbl.uid,tbl.pid from tbl join tmp on (tmp.uid=tbl.pid) where tbl.* is not null
) ,
b as
(select uid, count(*) cnt from log where uid = any
(array(
select pid from tmp
))
group by uid)
select tmp.*, case when b.cnt is not null then b.cnt else 0 end as cnt from tmp left join b on (tmp.pid=b.uid);
uid | pid | cnt
----------+----------+----------
2000001 | 1 | 50004739
4000001 | 2000001 | 0
6000001 | 4000001 | 0
8000001 | 6000001 | 0
10000001 | 8000001 | 0
12000001 | 10000001 | 0
14000001 | 12000001 | 0
16000001 | 14000001 | 0
18000001 | 16000001 | 0
20000001 | 18000001 | 0
22000001 | 20000001 | 0
24000001 | 22000001 | 0
26000001 | 24000001 | 0
28000001 | 26000001 | 0
30000001 | 28000001 | 0
32000001 | 30000001 | 0
34000001 | 32000001 | 0
36000001 | 34000001 | 0
38000001 | 36000001 | 0
40000001 | 38000001 | 0
42000001 | 40000001 | 0
44000001 | 42000001 | 0
46000001 | 44000001 | 0
48000001 | 46000001 | 0
50000001 | 48000001 | 0
52000001 | 50000001 | 0
54000001 | 52000001 | 0
56000001 | 54000001 | 0
58000001 | 56000001 | 0
60000001 | 58000001 | 0
62000001 | 60000001 | 0
64000001 | 62000001 | 0
66000001 | 64000001 | 0
68000001 | 66000001 | 0
70000001 | 68000001 | 0
72000001 | 70000001 | 0
74000001 | 72000001 | 0
76000001 | 74000001 | 0
78000001 | 76000001 | 0
80000001 | 78000001 | 0
82000001 | 80000001 | 0
84000001 | 82000001 | 0
86000001 | 84000001 | 0
88000001 | 86000001 | 0
90000001 | 88000001 | 0
92000001 | 90000001 | 0
94000001 | 92000001 | 0
96000001 | 94000001 | 0
98000001 | 96000001 | 0
(49 rows)
Time: 5142.932 ms (00:05.143)
2、开启并行，耗时： 0.29 秒。
postgres=# explain with recursive tmp as (
select uid,pid from tbl where pid =1
union all
select tbl.uid,tbl.pid from tbl join tmp on (tmp.uid=tbl.pid) where tbl.* is not null
) ,
b as
(select uid, count(*) cnt from log where uid = any
(array(
select pid from tmp
))
group by uid)
select tmp.*, case when b.cnt is not null then b.cnt else 0 end as cnt from tmp left join b on (tmp.pid=b.uid);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=6733216.66..6733219.17 rows=101 width=24)
Hash Cond: (tmp.pid = b.uid)
CTE tmp
-> Recursive Union (cost=0.57..286.31 rows=101 width=16)
-> Index Scan using idx_tbl_1 on tbl (cost=0.57..2.79 rows=1 width=16)
Index Cond: (pid = 1)
-> Nested Loop (cost=0.57..28.15 rows=10 width=16)
-> WorkTable Scan on tmp tmp_1 (cost=0.00..0.20 rows=10 width=8)
-> Index Scan using idx_tbl_1 on tbl tbl_1 (cost=0.57..2.79 rows=1 width=16)
Index Cond: (pid = tmp_1.uid)
Filter: (tbl_1.* IS NOT NULL)
CTE b
-> Finalize GroupAggregate (cost=3.18..6732930.00 rows=11 width=12)
Group Key: log.uid
InitPlan 2 (returns $3)
-> CTE Scan on tmp tmp_2 (cost=0.00..2.02 rows=101 width=8)
-> Gather Merge (cost=1.16..6732926.55 rows=264 width=12)
Workers Planned: 24
Params Evaluated: $3
-> Partial GroupAggregate (cost=0.57..6732919.18 rows=11 width=12)
Group Key: log.uid
-> Parallel Index Only Scan using idx_log_1 on log (cost=0.57..6604907.24 rows=25602366 width=4)
Index Cond: (uid = ANY ($3))
-> CTE Scan on tmp (cost=0.00..2.02 rows=101 width=16)
-> Hash (cost=0.22..0.22 rows=11 width=12)
-> CTE Scan on b (cost=0.00..0.22 rows=11 width=12)
(26 rows)
Time: 0.793 ms
postgres=# with recursive tmp as (
select uid,pid from tbl where pid =1
union all
select tbl.uid,tbl.pid from tbl join tmp on (tmp.uid=tbl.pid) where tbl.* is not null
) ,
b as
(select uid, count(*) cnt from log where uid = any
(array(
select pid from tmp
))
group by uid)
select tmp.*, case when b.cnt is not null then b.cnt else 0 end as cnt from tmp left join b on (tmp.pid=b.uid);
uid | pid | cnt
----------+----------+----------
2000001 | 1 | 50004739
4000001 | 2000001 | 0
6000001 | 4000001 | 0
8000001 | 6000001 | 0
10000001 | 8000001 | 0
12000001 | 10000001 | 0
14000001 | 12000001 | 0
16000001 | 14000001 | 0
18000001 | 16000001 | 0
20000001 | 18000001 | 0
22000001 | 20000001 | 0
24000001 | 22000001 | 0
26000001 | 24000001 | 0
28000001 | 26000001 | 0
30000001 | 28000001 | 0
32000001 | 30000001 | 0
34000001 | 32000001 | 0
36000001 | 34000001 | 0
38000001 | 36000001 | 0
40000001 | 38000001 | 0
42000001 | 40000001 | 0
44000001 | 42000001 | 0
46000001 | 44000001 | 0
48000001 | 46000001 | 0
50000001 | 48000001 | 0
52000001 | 50000001 | 0
54000001 | 52000001 | 0
56000001 | 54000001 | 0
58000001 | 56000001 | 0
60000001 | 58000001 | 0
62000001 | 60000001 | 0
64000001 | 62000001 | 0
66000001 | 64000001 | 0
68000001 | 66000001 | 0
70000001 | 68000001 | 0
72000001 | 70000001 | 0
74000001 | 72000001 | 0
76000001 | 74000001 | 0
78000001 | 76000001 | 0
80000001 | 78000001 | 0
82000001 | 80000001 | 0
84000001 | 82000001 | 0
86000001 | 84000001 | 0
88000001 | 86000001 | 0
90000001 | 88000001 | 0
92000001 | 90000001 | 0
94000001 | 92000001 | 0
96000001 | 94000001 | 0
98000001 | 96000001 | 0
(49 rows)
Time: 289.225 ms
注意不要使用以下写法，性能不太好：
非并行, 4秒
with recursive tmp as (
select uid,pid from tbl where pid =1
union all
select tbl.uid,tbl.pid from tbl join tmp on (tmp.uid=tbl.pid) where tbl.* is not null
)
select *,(select count(*) from log where uid=tmp.pid) from tmp;
非多阶段并行聚合, 慢
with recursive tmp as (
select uid,pid from tbl where pid =1
union all
select tbl.uid,tbl.pid from tbl join tmp on (tmp.uid=tbl.pid) where tbl.* is not null
)
select tmp.pid,count(*) from tmp left join log on (tmp.pid=log.uid) group by tmp.pid;
其他知识
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虚拟机
