PostgreSQL 多个数组聚合为一维数组加速(array_agg)

点击有惊喜

标签

PostgreSQL,array_agg,arragg

背景

多个数组聚合为一维数组，求PC。业务背景见：

《PostgreSQL APP海量FEED LOG实时质量统计CASE(含percentile_disc)》

由于PostgreSQL内置的聚合函数array_agg支持的数组聚合实际上是将多个数组聚合为多维数组。并不是一维数组。

例如：

postgres=# select array_agg(arr) from (values(array[1,2,255);">3]),(4,255);">5,255);">6])) t(arr);  
     array_agg       
-------------------  
 {{3},{6}}  
(1 row)

而实际上我们要的是一维数组的结果

{1,2,3,4,5,6}

此时需要自定义一个聚合函数

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

效果如下

postgres=# select arragg(arr) from (values(6])) t(arr);  
    arragg       
---------------  
 {3,255);">6}  
( 但是这个新加的聚合用到了array_cat，大量的memcpy导致性能并不好。
 
 
 array_agg性能对比arragg
 
 
 聚合100万个元素.
 
 
 1、array_agg，耗时0.14秒
 
 
postgres=# explain (analyze,verbose,timing,costs,buffers) select array_agg(array[1,6,7,8,9,10]) from generate_series(1,100000); 
                                                                QUERY PLAN                                                                  
------------------------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=12.50..12.51 rows=1 width=32) (actual time=113.134..113.134 rows=1 loops=1)  
   Output: array_agg('{1,10}'::integer[])  
   ->  Function Scan on pg_catalog.generate_series (cost=0.00..10.00 rows=1000 width=0) (actual time=53.585..66.200 rows=100000 loops=1) Output: generate_series Function Call: generate_series(100000) Planning time: 0.064 ms Execution time: 143.075 ms (7 rows) 
 
 
 2、arragg(use array_cat)，耗时108.15秒
 
 
108081.186..108081.186 rows=1)  
   Output: arragg(11.121..81.467 rows=time: 0.148 time: 108154.846 7 rows) 
 
 
 3、unnest聚合，耗时0.59秒
 
 
517.50..517.51 rows=32) (actual time=520.327..520.327 rows=1)
   Output: $0
   InitPlan 1 (returns $0)
     ->  ProjectSet  (cost=0.00..517.50 rows=100000 width=4) (actual 11.979..223.223 rows=1000000 loops=1)
           Output: unnest(10.00 rows=0) (actual 11.972..27.014 rows=1)
                 Output: generate_series
                 Function Call: generate_series(100000)
 Planning time: 0.082 ms
 Execution 590.976 ms
(10 rows)
 
 
 
 
点击有惊喜