标签
PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持
https://github.com/digoal/blog/blob/master/201903/20190317_11.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
接下来进行一一介绍。
关键知识请先自行了解：
1、优化器自动并行度算法 CBO
《PostgreSQL 9.6 并行计算 优化器算法浅析》 《PostgreSQL 11 并行计算算法，参数，强制并行度设置》https://github.com/digoal/blog/blob/master/201903/20190317_11.md#parallel-hash-join parallel hash join
并行hash JOIN
数据量：10亿 join 10亿 on (i=i)。
| 场景 | 数据量 | 关闭并行 | 开启并行 | 并行度 | 开启并行性能提升倍数 |
|---|---|---|---|---|---|
| 10亿 join 10亿 using (i) where t1.i<10000000 and t2.i<10000000 | 8.1 秒 | 1 秒 | 20 | 8.1 倍 | |
| 10亿 join 10亿 using (i) | 1071 秒 | 92.3 秒 | 11.6 倍 |
https://github.com/digoal/blog/blob/master/201903/20190317_11.md#1%E5%85%B3%E9%97%AD%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-1071-%E7%A7%92--81-%E7%A7%92 1、关闭并行，耗时： 1071 秒 , 8.1 秒。
postgres=# explain select count(*) from table5 t1 join table5 t2 using (i) ;
QUERY PLAN
-------------------------------------------------------------------------------------------
Aggregate (cost=1321974645.04..1321974645.05 rows=1 width=8)
-> Hash Join (cost=30831031.44..1319474644.88 rows=1000000064 width=0)
Hash Cond: (t1.i = t2.i)
-> Seq Scan on table5 t1 (cost=0.00..14424779.64 rows=1000000064 width=4)
-> Hash (cost=14424779.64..14424779.64 rows=1000000064 width=4)
-> Seq Scan on table5 t2 (cost=0.00..14424779.64 rows=1000000064 width=4)
(6 rows)
postgres=# select count(*) from table5 t1 join table5 t2 using (i) ;
count
------------
1000000000
(1 row)
Time: 1071102.574 ms (17:51.103)
postgres=# explain select count(*) from table5 t1 join table5 t2 using (i) where t1.i<10000000 and t2.i<10000000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Aggregate (cost=557583.02..557583.03 rows=1 width=8)
-> Hash Join (cost=278347.86..557405.33 rows=71076 width=0)
Hash Cond: (t1.i = t2.i)
-> Index Only Scan using idx_table5_2 on table5 t1 (cost=0.57..172964.32 rows=8430637 width=4)
Index Cond: (i < 10000000)
-> Hash (cost=172964.32..172964.32 rows=8430637 width=4)
-> Index Only Scan using idx_table5_2 on table5 t2 (cost=0.57..172964.32 rows=8430637 width=4)
Index Cond: (i < 10000000)
(8 rows)
postgres=# select count(*) from table5 t1 join table5 t2 using (i) where t1.i<10000000 and t2.i<10000000;
count
---------
9999999
(1 row)
Time: 8130.739 ms (00:08.131)
https://github.com/digoal/blog/blob/master/201903/20190317_11.md#2%E5%BC%80%E5%90%AF%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-923-%E7%A7%92--1-%E7%A7%92 2、开启并行，耗时： 92.3 秒 , 1 秒。
postgres=# explain select count(*) from table5 t1 join table5 t2 using (i) ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=14655810.36..14655810.37 rows=1 width=8)
-> Gather (cost=14655810.30..14655810.31 rows=20 width=8)
Workers Planned: 20
-> Partial Aggregate (cost=14655810.30..14655810.31 rows=1 width=8)
-> Parallel Hash Join (cost=5745092.07..14530810.30 rows=50000003 width=0)
Hash Cond: (t1.i = t2.i)
-> Parallel Seq Scan on table5 t1 (cost=0.00..4924779.03 rows=50000003 width=4)
-> Parallel Hash (cost=4924779.03..4924779.03 rows=50000003 width=4)
-> Parallel Seq Scan on table5 t2 (cost=0.00..4924779.03 rows=50000003 width=4)
(9 rows)
postgres=# select count(*) from table5 t1 join table5 t2 using (i) ;
count
------------
1000000000
(1 row)
Time: 92307.627 ms (01:32.308)
postgres=# explain select count(*) from table5 t1 join table5 t2 using (i) where t1.i<10000000 and t2.i<10000000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=192607.16..192607.17 rows=1 width=8)
-> Gather (cost=192607.10..192607.11 rows=20 width=8)
Workers Planned: 20
-> Partial Aggregate (cost=192607.10..192607.11 rows=1 width=8)
-> Parallel Hash Join (cost=98143.00..192598.22 rows=3554 width=0)
Hash Cond: (t1.i = t2.i)
-> Parallel Index Only Scan using idx_table5_2 on table5 t1 (cost=0.57..92873.27 rows=421532 width=4)
Index Cond: (i < 10000000)
-> Parallel Hash (cost=92873.27..92873.27 rows=421532 width=4)
-> Parallel Index Only Scan using idx_table5_2 on table5 t2 (cost=0.57..92873.27 rows=421532 width=4)
Index Cond: (i < 10000000)
(11 rows)
postgres=# select count(*) from table5 t1 join table5 t2 using (i) where t1.i<10000000 and t2.i<10000000;
count
---------
9999999
(1 row)
Time: 1007.838 ms (00:01.008)
hash join , hash agg ，性能与并行度相关，同时与work_mem的设置非常相关。
hash join , 当外部条件t2.i<10000000未设置时，不会在hash table中过滤，这个query rewrite优化器有改进空间。
postgres=# explain select count(*) from table5 t1 join table5 t2 using (i) where t1.i<10000000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=5211686.12..5211686.13 rows=1 width=8)
-> Gather (cost=5211686.06..5211686.07 rows=20 width=8)
Workers Planned: 20
-> Partial Aggregate (cost=5211686.06..5211686.07 rows=1 width=8)
-> Parallel Hash Join (cost=98142.42..5210632.23 rows=421532 width=0)
Hash Cond: (t2.i = t1.i)
-> Parallel Seq Scan on table5 t2 (cost=0.00..4924779.03 rows=50000003 width=4)
-> Parallel Hash (cost=92873.27..92873.27 rows=421532 width=4)
-> Parallel Index Only Scan using idx_table5_2 on table5 t1 (cost=0.57..92873.27 rows=421532 width=4)
Index Cond: (i < 10000000)
(10 rows)
https://github.com/digoal/blog/blob/master/201903/20190317_11.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_11.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_11.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陷阱与缺陷②(语法陷阱之优先级)一、错误案列 二、优先级规律[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)