天天看点
返回

PostgreSQL 并行计算解说 之10 - parallel 自定义并行函数(UDF)

标签

PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持

https://github.com/digoal/blog/blob/master/201903/20190317_02.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_02.md#parallel-%E8%87%AA%E5%AE%9A%E4%B9%89%E5%B9%B6%E8%A1%8C%E5%87%BD%E6%95%B0udf parallel 自定义并行函数(UDF)

自定义并行函数(UDF)

数据量：10亿。

场景 数据量 关闭并行 开启并行 并行度 开启并行性能提升倍数
10 亿 456 秒 16.5 秒 30 27.6 倍

UDF例子，取模，求绝对值。

create or replace function udf1(int4, int4) returns int4 as $$  
  select abs(mod($1,$2));  
$$ language sql strict parallel safe;

https://github.com/digoal/blog/blob/master/201903/20190317_02.md#1%E5%85%B3%E9%97%AD%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-4569-%E7%A7%92--4563-%E7%A7%92 1、关闭并行，耗时： 456.9 秒 , 456.3 秒。 

postgres=# explain select abs(mod(i,10)),count(*) from table2 group by abs(mod(i,10));  
                                    QUERY PLAN                                      
----------------------------------------------------------------------------------  
 GroupAggregate  (cost=168911543.27..191411543.27 rows=1000000000 width=12)  
   Group Key: (abs(mod(i, 10)))  
   ->  Sort  (cost=168911543.27..171411543.27 rows=1000000000 width=4)  
         Sort Key: (abs(mod(i, 10)))  
         ->  Seq Scan on table2  (cost=0.00..19424779.00 rows=1000000000 width=4)  
(5 rows)  
  
postgres=# explain select udf1(i,10),count(*) from table2 group by udf1(i,10);  
                                    QUERY PLAN                                      
----------------------------------------------------------------------------------  
 GroupAggregate  (cost=168911543.27..191411543.27 rows=1000000000 width=12)  
   Group Key: (abs(mod(i, 10)))  
   ->  Sort  (cost=168911543.27..171411543.27 rows=1000000000 width=4)  
         Sort Key: (abs(mod(i, 10)))  
         ->  Seq Scan on table2  (cost=0.00..19424779.00 rows=1000000000 width=4)  
(5 rows)  
  
  
postgres=# select abs(mod(i,10)),count(*) from table2 group by abs(mod(i,10));  
 abs |   count     
-----+-----------  
   0 |  99996445  
   1 | 100000179  
   2 | 100000876  
   3 | 100012873  
   4 | 100009015  
   5 |  99999050  
   6 |  99992767  
   7 | 100000912  
   8 | 100009862  
   9 |  99978021  
(10 rows)  
  
Time: 456897.647 ms (07:36.898)  
  
postgres=# select udf1(i,10),count(*) from table2 group by udf1(i,10);  
 udf1 |   count     
------+-----------  
    0 |  99996445  
    1 | 100000179  
    2 | 100000876  
    3 | 100012873  
    4 | 100009015  
    5 |  99999050  
    6 |  99992767  
    7 | 100000912  
    8 | 100009862  
    9 |  99978021  
(10 rows)  
  
Time: 456254.222 ms (07:36.254)

https://github.com/digoal/blog/blob/master/201903/20190317_02.md#2%E5%BC%80%E5%90%AF%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-165-%E7%A7%92 2、开启并行，耗时： 16.5 秒。 

postgres=# explain select abs(mod(i,10)),count(*) from table2 group by abs(mod(i,10));  
                                             QUERY PLAN                                               
----------------------------------------------------------------------------------------------------  
 Finalize GroupAggregate  (cost=9089856.77..57110837.99 rows=1000000000 width=12)  
   Group Key: (abs(mod(i, 10)))  
   ->  Gather Merge  (cost=9089856.77..37110838.04 rows=999999990 width=12)  
         Workers Planned: 30  
         ->  Partial GroupAggregate  (cost=9089856.00..9839855.99 rows=33333333 width=12)  
               Group Key: (abs(mod(i, 10)))  
               ->  Sort  (cost=9089856.00..9173189.33 rows=33333333 width=4)  
                     Sort Key: (abs(mod(i, 10)))  
                     ->  Parallel Seq Scan on table2  (cost=0.00..4924779.00 rows=33333333 width=4)  
(9 rows)  
  
postgres=# explain select udf1(i,10),count(*) from table2 group by udf1(i,10);  
                                             QUERY PLAN                                               
----------------------------------------------------------------------------------------------------  
 Finalize GroupAggregate  (cost=9089856.77..57110837.99 rows=1000000000 width=12)  
   Group Key: (abs(mod(i, 10)))  
   ->  Gather Merge  (cost=9089856.77..37110838.04 rows=999999990 width=12)  
         Workers Planned: 30  
         ->  Partial GroupAggregate  (cost=9089856.00..9839855.99 rows=33333333 width=12)  
               Group Key: (abs(mod(i, 10)))  
               ->  Sort  (cost=9089856.00..9173189.33 rows=33333333 width=4)  
                     Sort Key: (abs(mod(i, 10)))  
                     ->  Parallel Seq Scan on table2  (cost=0.00..4924779.00 rows=33333333 width=4)  
(9 rows)  
  
  
postgres=# select abs(mod(i,10)),count(*) from table2 group by abs(mod(i,10));  
 abs |   count     
-----+-----------  
   0 |  99996445  
   1 | 100000179  
   2 | 100000876  
   3 | 100012873  
   4 | 100009015  
   5 |  99999050  
   6 |  99992767  
   7 | 100000912  
   8 | 100009862  
   9 |  99978021  
(10 rows)  
  
Time: 16500.058 ms (00:16.500)  
  
postgres=# select udf1(i,10),count(*) from table2 group by udf1(i,10);  
 udf1 |   count     
------+-----------  
    0 |  99996445  
    1 | 100000179  
    2 | 100000876  
    3 | 100012873  
    4 | 100009015  
    5 |  99999050  
    6 |  99992767  
    7 | 100000912  
    8 | 100009862  
    9 |  99978021  
(10 rows)  
  
Time: 16490.091 ms (00:16.490)

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

https://github.com/digoal/blog/blob/master/201903/20190317_02.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_02.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_02.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虚拟机

PostgreSQL 并行计算解说 之10 - parallel 自定义并行函数(UDF)
sql 分布式计算 并行计算 算法 关系型数据库 c语言 postgresql c++ 异构计算 rds jdk7并行计算框架介绍 parallel并行计算 jdk7并行计算 c编程并行计算 cte并行计算parallel
上一篇: PostgreSQL 并行计算解说 之3 - parallel agg
下一篇: PostgreSQL sharding extension citus 优化器 Query Processing 之 - Distributed Query Planner、Executor (Real-time Executor, Router Executor, Task Tracker Executor)

继续阅读