PostgreSQL 并行计算解说之9 - parallel 自定义并行聚合-阿里云开发者社区

PostgreSQL 并行计算解说之9 - parallel 自定义并行聚合

2020-02-25 607

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云原生数据库 PolarDB MySQL 版，通用型 2核4GB 50GB

云原生数据库 PolarDB PostgreSQL 版，标准版 2核4GB 50GB

简介： 标签 PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持背景 PostgreSQL 11 优化器已经支持了非常多场合的并行。简单估计，已支持27余种场景的并行计算。 parallel seq scan parallel index scan

背景

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 并行计算算法，参数，强制并行度设置》

parallel 自定义并行聚合

自定义并行聚合

数据量：10亿。

场景	数据量	关闭并行	开启并行	并行度	开启并行性能提升倍数
自定义并行聚合1(求 distinct 数组字段元素、以及count distinct)	10 亿	298.8 秒	8.7 秒	36	34.3 倍
自定义并行聚合2(求 distinct 普通字段元素、以及count distinct)	10 亿	96.5 秒	3.43 秒	36	28 倍

自定义聚合函数例子1，合并数组，并去重：

数组去重函数：

postgres=# create or replace function uniq(int2[]) returns int2[] as $$    
  select array( select unnest($1) group by 1);    
$$ language sql strict parallel safe;    
CREATE FUNCTION

数组合并与去重函数：

postgres=# create or replace function array_uniq_cat(anyarray,anyarray) returns anyarray as $$    
  select uniq(array_cat($1,$2));     
$$ language sql strict parallel safe;    
CREATE FUNCTION

有combinefunc，同时支持并行聚合，并行扫描。

create aggregate arragg (anyarray) (sfunc = array_uniq_cat, stype=anyarray, COMBINEFUNC = array_uniq_cat, PARALLEL=safe);

自定义聚合函数例子2，数据聚合插件：count_distinct 自定义并行聚合加速

https://github.com/tvondra/count_distinct

git clone https://github.com/tvondra/count_distinct  
  
cd count_distinct/  
  
USE_PGXS=1 make  
  
USE_PGXS=1 make install  
  
create extension count_distinct ;

并行聚合函数例子

/* Create the aggregate functions */  
CREATE AGGREGATE count_distinct(anyelement) (  
       SFUNC = count_distinct_append,  
       STYPE = internal,  
       FINALFUNC = count_distinct,  
       COMBINEFUNC = count_distinct_combine,  
       SERIALFUNC = count_distinct_serial,  
       DESERIALFUNC = count_distinct_deserial,  
       PARALLEL = SAFE  
);  
  
CREATE AGGREGATE array_agg_distinct(anynonarray) (  
       SFUNC = count_distinct_append,  
       STYPE = internal,  
       FINALFUNC = array_agg_distinct,  
       FINALFUNC_EXTRA,  
       COMBINEFUNC = count_distinct_combine,  
       SERIALFUNC = count_distinct_serial,  
       DESERIALFUNC = count_distinct_deserial,  
       PARALLEL = SAFE  
);  
  
CREATE AGGREGATE count_distinct_elements(anyarray) (  
       SFUNC = count_distinct_elements_append,  
       STYPE = internal,  
       FINALFUNC = count_distinct,  
       COMBINEFUNC = count_distinct_combine,  
       SERIALFUNC = count_distinct_serial,  
       DESERIALFUNC = count_distinct_deserial,  
       PARALLEL = SAFE  
);  
  
CREATE AGGREGATE array_agg_distinct_elements(anyarray) (  
       SFUNC = count_distinct_elements_append,  
       STYPE = internal,  
       FINALFUNC = array_agg_distinct,  
       FINALFUNC_EXTRA,  
       COMBINEFUNC = count_distinct_combine,  
       SERIALFUNC = count_distinct_serial,  
       DESERIALFUNC = count_distinct_deserial,  
       PARALLEL = SAFE  
);

测试数据10亿

create unlogged table table3 (i int2[]);  
insert into table3 values (array[1,2,3]), (array[3,3,4,6,100,2,3]);  
insert into table3 select array[109,209,3223] from generate_series(1,1000000000);

1、关闭并行，耗时： 298.8 秒 , 96.5 秒。

postgres=# explain select array_agg_distinct_elements(i) from table3;  
                                 QUERY PLAN                                    
-----------------------------------------------------------------------------  
 Aggregate  (cost=19852943.60..19852943.61 rows=1 width=32)  
   ->  Seq Scan on table3  (cost=0.00..17352943.28 rows=1000000128 width=27)  
(2 rows)  
  
postgres=# explain select array_agg_distinct(i) from table1;  
                                 QUERY PLAN                                   
----------------------------------------------------------------------------  
 Aggregate  (cost=16924779.00..16924779.01 rows=1 width=32)  
   ->  Seq Scan on table1  (cost=0.00..14424779.00 rows=1000000000 width=2)  
(2 rows)  
  
  
postgres=# select array_agg_distinct_elements(i) from table3;  
 array_agg_distinct_elements    
------------------------------  
 {1,2,3,4,6,100,109,3223,209}  
(1 row)  
  
Time: 298794.796 ms (04:58.795)  
  
postgres=# select array_agg_distinct(i) from table1;  
 array_agg_distinct   
--------------------  
 {1}  
(1 row)  
  
Time: 96477.082 ms (01:36.477)

2、开启并行，耗时： 8.75 秒 , 3.43 秒。

postgres=# explain select array_agg_distinct_elements(i) from table3;  
                                          QUERY PLAN                                             
-----------------------------------------------------------------------------------------------  
 Finalize Aggregate  (cost=7700164.46..7700164.47 rows=1 width=32)  
   ->  Gather  (cost=7700164.27..7700164.28 rows=36 width=32)  
         Workers Planned: 36  
         ->  Partial Aggregate  (cost=7700164.27..7700164.28 rows=1 width=32)  
               ->  Parallel Seq Scan on table3  (cost=0.00..7630719.81 rows=27777781 width=27)  
(5 rows)  
  
postgres=# explain select array_agg_distinct(i) from table1;           
                                          QUERY PLAN                                            
----------------------------------------------------------------------------------------------  
 Finalize Aggregate  (cost=4772001.42..4772001.43 rows=1 width=32)  
   ->  Gather  (cost=4772001.23..4772001.24 rows=36 width=32)  
         Workers Planned: 36  
         ->  Partial Aggregate  (cost=4772001.23..4772001.24 rows=1 width=32)  
               ->  Parallel Seq Scan on table1  (cost=0.00..4702556.78 rows=27777778 width=2)  
(5 rows)  
  
  
postgres=# select array_agg_distinct_elements(i) from table3;  
 array_agg_distinct_elements    
------------------------------  
 {1,2,3,4,6,100,109,3223,209}  
(1 row)  
  
Time: 8752.115 ms (00:08.752)  
  
postgres=# select array_agg_distinct(i) from table1;  
 array_agg_distinct   
--------------------  
 {1}  
(1 row)  
  
Time: 3427.029 ms (00:03.427)

自定义聚合的效率取决于自定义聚合代码本身的效率，SQL语言写的自定义聚合效率比较低，建议使用C语言写这种需要进行大数据量运算的FUNCTION。

其他知识

1、优化器自动并行度算法 CBO

《PostgreSQL 9.6 并行计算优化器算法浅析》

《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实例)》

《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://www.postgresql.org/docs/11/parallel-plans.html

《PostgreSQL 11 并行计算算法，参数，强制并行度设置》

《PostgreSQL 11 preview - 并行计算增强汇总》

《PostgreSQL 10 自定义并行计算聚合函数的原理与实践 - (含array_agg合并多个数组为单个一元数组的例子)》

《PostgreSQL 9.6 并行计算优化器算法浅析》

PostgreSQL 并行计算解说之9 - parallel 自定义并行聚合

标签

背景

parallel 自定义并行聚合

自定义聚合函数例子1，合并数组，并去重：

自定义聚合函数例子2，数据聚合插件：count_distinct 自定义并行聚合加速

测试数据10亿

1、关闭并行，耗时： 298.8 秒 , 96.5 秒。

2、开启并行，耗时： 8.75 秒 , 3.43 秒。

其他知识

参考

免费领取阿里云RDS PostgreSQL实例、ECS虚拟机

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

PostgreSQL 并行计算解说 之9 - parallel 自定义并行聚合

标签

背景

parallel 自定义并行聚合

自定义聚合函数例子1，合并数组，并去重：

自定义聚合函数例子2，数据聚合插件：count_distinct 自定义并行聚合加速

测试数据10亿

1、关闭并行，耗时： 298.8 秒 , 96.5 秒。

2、开启并行，耗时： 8.75 秒 , 3.43 秒。

其他知识

参考

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

PostgreSQL 并行计算解说之9 - parallel 自定义并行聚合