digoal
2016-10-08
postgresql , 9.6 , 内核优化 , 聚合代码优化 , op复用
聚合操作指将分组的数据聚合为一个结果输出。
聚合通常用在统计应用中，例如统计分组的最大值，最小值，记录数，平均值，方差，截距，相关性。
聚合也可能被用于文本分析或者图像分析等，例如最佳相似度，行列变换，聚合为数组或json，图像堆叠等。
因此聚合通常需要启动值，行的处理，以及结果的格式转换3个过程。
postgresql的聚合也包含了以上三个过程，创建一个聚合函数的语法如下：
例子
参考
<a href="https://www.postgresql.org/docs/9.6/static/xaggr.html">https://www.postgresql.org/docs/9.6/static/xaggr.html</a>
<a href="https://www.postgresql.org/docs/9.6/static/sql-createaggregate.html">https://www.postgresql.org/docs/9.6/static/sql-createaggregate.html</a>
postgresql 聚合处理流程如图
如果initcond与sfunc一致，在同一个聚合分组内，sfunc只需要计算一遍所有记录，而不需要计算多遍。
<a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=804163bc25e979fcd91b02e58fa2d1c6b587cc65">https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=804163bc25e979fcd91b02e58fa2d1c6b587cc65</a>
我们可以通过以下sql查看可以共享op的聚合函数，rank一致的都可以共享。
rank
rn
aggfnoid
aggtransfn
agginitval
1
pg_catalog.sum
float4pl
none
2
pg_catalog.avg
float4_accum
{0,0,0}
pg_catalog.variance
3
pg_catalog.stddev
4
pg_catalog.var_samp
5
pg_catalog.stddev_samp
6
pg_catalog.var_pop
7
pg_catalog.stddev_pop
9
pg_catalog.max
float4larger
10
pg_catalog.min
float4smaller
11
float8pl
12
float8_accum
19
float8larger
20
float8smaller
21
text_larger
22
text_smaller
23
array_larger
24
array_smaller
25
int4larger
27
int4smaller
29
int2larger
30
int2smaller
31
cash_pl
32
cashlarger
33
cashsmaller
34
bpchar_larger
35
bpchar_smaller
36
date_larger
37
date_smaller
38
interval_pl
39
timestamptz_smaller
40
timestamptz_larger
41
interval_smaller
42
interval_larger
43
pg_catalog.count
int8inc
44
int8larger
45
int8smaller
46
time_larger
47
time_smaller
48
timetz_larger
49
timetz_smaller
50
pg_catalog.bit_and
bitand
51
pg_catalog.bit_or
bitor
52
numeric_smaller
53
numeric_larger
54
numeric_accum
60
int2_accum
66
int4_accum
72
int8_accum
78
int2_sum
79
int4_sum
80
interval_accum
{0 second,0 second}
81
int2and
82
int2or
83
int4and
84
int4or
85
int8and
86
int8or
87
int2_avg_accum
{0,0}
88
int4_avg_accum
89
oidlarger
90
oidsmaller
91
timestamp_smaller
92
timestamp_larger
93
pg_catalog.array_agg
array_agg_transfn
94
bool_and
booland_statefunc
every
96
bool_or
boolor_statefunc
97
int8_avg_accum
99
tidlarger
100
tidsmaller
101
int8inc_any
102
regr_count
int8inc_float8_float8
103
regr_sxx
float8_regr_accum
{0,0,0,0,0,0}
regr_syy
regr_sxy
regr_avgx
regr_avgy
regr_r2
regr_slope
8
regr_intercept
covar_pop
covar_samp
corr
114
numeric_avg_accum
116
xmlagg
xmlconcat2
117
json_agg
json_agg_transfn
118
json_object_agg
json_object_agg_transfn
119
jsonb_agg
jsonb_agg_transfn
120
jsonb_object_agg
jsonb_object_agg_transfn
121
enum_smaller
122
enum_larger
123
pg_catalog.string_agg
string_agg_transfn
124
bytea_string_agg_transfn
125
network_larger
126
network_smaller
127
pg_catalog.percentile_disc
ordered_set_transition
pg_catalog.percentile_cont
mode
134
pg_catalog.rank
ordered_set_transition_multi
pg_catalog.percent_rank
pg_catalog.cume_dist
pg_catalog.dense_rank
138
array_agg_array_transfn
我接下来抽取几个数据统计相关的，验证9.6的优化效果
这几个聚合函数的用法如下
<a href="https://www.postgresql.org/docs/9.6/static/functions-aggregate.html">https://www.postgresql.org/docs/9.6/static/functions-aggregate.html</a>
function
argument type
return type
partial mode
description
corr(y, x)
double precision
yes
correlation coefficient
covar_pop(y, x)
population covariance
covar_samp(y, x)
sample covariance
regr_avgx(y, x)
average of the independent variable (sum(x)/n)
regr_avgy(y, x)
average of the dependent variable (sum(y)/n)
regr_intercept(y, x)
y-intercept of the least-squares-fit linear equation determined by the (x, y) pairs
regr_r2(y, x)
square of the correlation coefficient
regr_slope(y, x)
slope of the least-squares-fit linear equation determined by the (x, y) pairs
regr_sxx(y, x)
sum(x^2) - sum(x)^2/n ("sum of squares" of the independent variable)
regr_sxy(y, x)
sum(x*y) - sum(x) * sum(y)/n ("sum of products" of independent times dependent variable)
regr_syy(y, x)
sum(y^2) - sum(y)^2/n ("sum of squares" of the dependent variable)
测试5000万条记录
1. 9.6 非并行
聚合计算耗费了7.1秒
2. 9.5
聚合计算耗费了36.1秒
3. 9.6 并行
聚合计算约耗费0.2秒
9.6的优化效果很明显，在没有使用并行的情况下，聚合操作已经有约5倍的性能提升。
结果对比
版本
9.6
9.5
9.6并行(32)
5000万记录(11个聚合函数)耗时(秒)
7.1
36.1
0.2
涉及如下
在统计学中，大多数的统计算法的中间结果都是可以共用的，例如sum,avg; 方差,相关性,count,sum等运算;
postgresql 9.6很好的抓住了这样的特征，对初始条件一致，中间算法一致的聚合函数，在同一个分组中数据只需要计算一遍，大大降低了cpu的开销，提高了统计效率。
这个思路与llvm有一些神似的地方，不过llvm的适用场景更广。
<a href="http://info.flagcounter.com/h9v1">count</a>