PostgreSQL 9.6 聚合运算180倍性能提升如何做到? 聚合代码优化OP复用浅析

PostgreSQL 9.6 内核优化之 聚合代码优化OP复用浅析

作者

digoal

日期

2016-10-08

标签

PostgreSQL , 9.6 , 内核优化 , 聚合代码优化 , OP复用


背景

聚合操作指将分组的数据聚合为一个结果输出。

聚合通常用在统计应用中,例如统计分组的最大值,最小值,记录数,平均值,方差,截距,相关性。

聚合也可能被用于文本分析或者图像分析等,例如最佳相似度,行列变换,聚合为数组或JSON,图像堆叠等。

因此聚合通常需要启动值,行的处理,以及结果的格式转换3个过程。

PostgreSQL的聚合也包含了以上三个过程,创建一个聚合函数的语法如下:

CREATE AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , SSPACE = state_data_size ]
    [ , FINALFUNC = ffunc ]
    [ , FINALFUNC_EXTRA ]
    [ , COMBINEFUNC = combinefunc ]
    [ , SERIALFUNC = serialfunc ]
    [ , DESERIALFUNC = deserialfunc ]
    [ , INITCOND = initial_condition ]
    [ , MSFUNC = msfunc ]
    [ , MINVFUNC = minvfunc ]
    [ , MSTYPE = mstate_data_type ]
    [ , MSSPACE = mstate_data_size ]
    [ , MFINALFUNC = mffunc ]
    [ , MFINALFUNC_EXTRA ]
    [ , MINITCOND = minitial_condition ]
    [ , SORTOP = sort_operator ]
    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
)

例子

CREATE AGGREGATE avg (float8)
(
    sfunc = float8_accum,
    stype = float8[],
    finalfunc = float8_avg,
    initcond = '{0,0,0}'
);

参考

https://www.postgresql.org/docs/9.6/static/xaggr.html

https://www.postgresql.org/docs/9.6/static/sql-createaggregate.html

PostgreSQL 聚合处理流程如图

pic1

1. 使用initcond指定internal-state的初始值,没有则为空。    

2. 每条记录(作为next-data-values输入),调用 sfunc( internal-state, next-data-values ) ---> next-internal-state  
   输出的结果作为中间结果继续调用sfunc

3. ffunc( internal-state ) ---> aggregate-value
   可选,作为结果转换使用

9.6 聚合OP优化

pic2

如果initcond与sfunc一致,在同一个聚合分组内,sfunc只需要计算一遍所有记录,而不需要计算多遍。

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=804163bc25e979fcd91b02e58fa2d1c6b587cc65

Share transition state between different aggregates when possible.

If there are two different aggregates in the query with same inputs, 
and the aggregates have the same initial condition and transition function,
only calculate the state value once, and only call the final functionsseparately. 
For example, AVG(x) and SUM(x) aggregates have the same transition function, which accumulates the sum and number of input tuples.
For a query like "SELECT AVG(x), SUM(x) FROM x", we can therefore accumulate the state function only once, which gives a nice speedup.

David Rowley, reviewed and edited by me.

我们可以通过以下SQL查看可以共享OP的聚合函数,rank一致的都可以共享。

postgres=# select rank() over (partition by 1 order by aggtransfn,agginitval),
           row_number() over (partition by aggtransfn,agginitval order by aggfnoid) rn,
           aggfnoid,aggtransfn,agginitval from pg_aggregate ;
rankrnaggfnoidaggtransfnagginitval
11pg_catalog.sumfloat4plnone
21pg_catalog.avgfloat4_accum{0,0,0}
22pg_catalog.variancefloat4_accum{0,0,0}
23pg_catalog.stddevfloat4_accum{0,0,0}
24pg_catalog.var_sampfloat4_accum{0,0,0}
25pg_catalog.stddev_sampfloat4_accum{0,0,0}
26pg_catalog.var_popfloat4_accum{0,0,0}
27pg_catalog.stddev_popfloat4_accum{0,0,0}
91pg_catalog.maxfloat4largernone
101pg_catalog.minfloat4smallernone
111pg_catalog.sumfloat8plnone
121pg_catalog.avgfloat8_accum{0,0,0}
122pg_catalog.variancefloat8_accum{0,0,0}
123pg_catalog.stddevfloat8_accum{0,0,0}
124pg_catalog.var_sampfloat8_accum{0,0,0}
125pg_catalog.stddev_sampfloat8_accum{0,0,0}
126pg_catalog.var_popfloat8_accum{0,0,0}
127pg_catalog.stddev_popfloat8_accum{0,0,0}
191pg_catalog.maxfloat8largernone
201pg_catalog.minfloat8smallernone
211pg_catalog.maxtext_largernone
221pg_catalog.mintext_smallernone
231pg_catalog.maxarray_largernone
241pg_catalog.minarray_smallernone
251pg_catalog.maxint4largernone
252pg_catalog.maxint4largernone
271pg_catalog.minint4smallernone
272pg_catalog.minint4smallernone
291pg_catalog.maxint2largernone
301pg_catalog.minint2smallernone
311pg_catalog.sumcash_plnone
321pg_catalog.maxcashlargernone
331pg_catalog.mincashsmallernone
341pg_catalog.maxbpchar_largernone
351pg_catalog.minbpchar_smallernone
361pg_catalog.maxdate_largernone
371pg_catalog.mindate_smallernone
381pg_catalog.suminterval_plnone
391pg_catalog.mintimestamptz_smallernone
401pg_catalog.maxtimestamptz_largernone
411pg_catalog.mininterval_smallernone
421pg_catalog.maxinterval_largernone
431pg_catalog.countint8inc0
441pg_catalog.maxint8largernone
451pg_catalog.minint8smallernone
461pg_catalog.maxtime_largernone
471pg_catalog.mintime_smallernone
481pg_catalog.maxtimetz_largernone
491pg_catalog.mintimetz_smallernone
501pg_catalog.bit_andbitandnone
511pg_catalog.bit_orbitornone
521pg_catalog.minnumeric_smallernone
531pg_catalog.maxnumeric_largernone
541pg_catalog.variancenumeric_accumnone
542pg_catalog.stddevnumeric_accumnone
543pg_catalog.var_sampnumeric_accumnone
544pg_catalog.stddev_sampnumeric_accumnone
545pg_catalog.var_popnumeric_accumnone
546pg_catalog.stddev_popnumeric_accumnone
601pg_catalog.varianceint2_accumnone
602pg_catalog.stddevint2_accumnone
603pg_catalog.var_sampint2_accumnone
604pg_catalog.stddev_sampint2_accumnone
605pg_catalog.var_popint2_accumnone
606pg_catalog.stddev_popint2_accumnone
661pg_catalog.varianceint4_accumnone
662pg_catalog.stddevint4_accumnone
663pg_catalog.var_sampint4_accumnone
664pg_catalog.stddev_sampint4_accumnone
665pg_catalog.var_popint4_accumnone
666pg_catalog.stddev_popint4_accumnone
721pg_catalog.varianceint8_accumnone
722pg_catalog.stddevint8_accumnone
723pg_catalog.var_sampint8_accumnone
724pg_catalog.stddev_sampint8_accumnone
725pg_catalog.var_popint8_accumnone
726pg_catalog.stddev_popint8_accumnone
781pg_catalog.sumint2_sumnone
791pg_catalog.sumint4_sumnone
801pg_catalog.avginterval_accum{0 second,0 second}
811pg_catalog.bit_andint2andnone
821pg_catalog.bit_orint2ornone
831pg_catalog.bit_andint4andnone
841pg_catalog.bit_orint4ornone
851pg_catalog.bit_andint8andnone
861pg_catalog.bit_orint8ornone
871pg_catalog.avgint2_avg_accum{0,0}
881pg_catalog.avgint4_avg_accum{0,0}
891pg_catalog.maxoidlargernone
901pg_catalog.minoidsmallernone
911pg_catalog.mintimestamp_smallernone
921pg_catalog.maxtimestamp_largernone
931pg_catalog.array_aggarray_agg_transfnnone
941bool_andbooland_statefuncnone
942everybooland_statefuncnone
961bool_orboolor_statefuncnone
971pg_catalog.avgint8_avg_accumnone
972pg_catalog.sumint8_avg_accumnone
991pg_catalog.maxtidlargernone
1001pg_catalog.mintidsmallernone
1011pg_catalog.countint8inc_any0
1021regr_countint8inc_float8_float80
1031regr_sxxfloat8_regr_accum{0,0,0,0,0,0}
1032regr_syyfloat8_regr_accum{0,0,0,0,0,0}
1033regr_sxyfloat8_regr_accum{0,0,0,0,0,0}
1034regr_avgxfloat8_regr_accum{0,0,0,0,0,0}
1035regr_avgyfloat8_regr_accum{0,0,0,0,0,0}
1036regr_r2float8_regr_accum{0,0,0,0,0,0}
1037regr_slopefloat8_regr_accum{0,0,0,0,0,0}
1038regr_interceptfloat8_regr_accum{0,0,0,0,0,0}
1039covar_popfloat8_regr_accum{0,0,0,0,0,0}
10310covar_sampfloat8_regr_accum{0,0,0,0,0,0}
10311corrfloat8_regr_accum{0,0,0,0,0,0}
1141pg_catalog.avgnumeric_avg_accumnone
1142pg_catalog.sumnumeric_avg_accumnone
1161xmlaggxmlconcat2none
1171json_aggjson_agg_transfnnone
1181json_object_aggjson_object_agg_transfnnone
1191jsonb_aggjsonb_agg_transfnnone
1201jsonb_object_aggjsonb_object_agg_transfnnone
1211pg_catalog.minenum_smallernone
1221pg_catalog.maxenum_largernone
1231pg_catalog.string_aggstring_agg_transfnnone
1241pg_catalog.string_aggbytea_string_agg_transfnnone
1251pg_catalog.maxnetwork_largernone
1261pg_catalog.minnetwork_smallernone
1271pg_catalog.percentile_discordered_set_transitionnone
1272pg_catalog.percentile_contordered_set_transitionnone
1273pg_catalog.percentile_contordered_set_transitionnone
1274pg_catalog.percentile_discordered_set_transitionnone
1275pg_catalog.percentile_contordered_set_transitionnone
1276pg_catalog.percentile_contordered_set_transitionnone
1277modeordered_set_transitionnone
1341pg_catalog.rankordered_set_transition_multinone
1342pg_catalog.percent_rankordered_set_transition_multinone
1343pg_catalog.cume_distordered_set_transition_multinone
1344pg_catalog.dense_rankordered_set_transition_multinone
1381pg_catalog.array_aggarray_agg_array_transfnnone

我接下来抽取几个数据统计相关的,验证9.6的优化效果

  103 |  1 | regr_sxx                   | float8_regr_accum            | {0,0,0,0,0,0}
  103 |  2 | regr_syy                   | float8_regr_accum            | {0,0,0,0,0,0}
  103 |  3 | regr_sxy                   | float8_regr_accum            | {0,0,0,0,0,0}
  103 |  4 | regr_avgx                  | float8_regr_accum            | {0,0,0,0,0,0}
  103 |  5 | regr_avgy                  | float8_regr_accum            | {0,0,0,0,0,0}
  103 |  6 | regr_r2                    | float8_regr_accum            | {0,0,0,0,0,0}
  103 |  7 | regr_slope                 | float8_regr_accum            | {0,0,0,0,0,0}
  103 |  8 | regr_intercept             | float8_regr_accum            | {0,0,0,0,0,0}
  103 |  9 | covar_pop                  | float8_regr_accum            | {0,0,0,0,0,0}
  103 | 10 | covar_samp                 | float8_regr_accum            | {0,0,0,0,0,0}
  103 | 11 | corr                       | float8_regr_accum            | {0,0,0,0,0,0}

这几个聚合函数的用法如下

https://www.postgresql.org/docs/9.6/static/functions-aggregate.html

FunctionArgument TypeReturn TypePartial ModeDescription
corr(Y, X)double precisiondouble precisionYescorrelation coefficient
covar_pop(Y, X)double precisiondouble precisionYespopulation covariance
covar_samp(Y, X)double precisiondouble precisionYessample covariance
regr_avgx(Y, X)double precisiondouble precisionYesaverage of the independent variable (sum(X)/N)
regr_avgy(Y, X)double precisiondouble precisionYesaverage of the dependent variable (sum(Y)/N)
regr_intercept(Y, X)double precisiondouble precisionYesy-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs
regr_r2(Y, X)double precisiondouble precisionYessquare of the correlation coefficient
regr_slope(Y, X)double precisiondouble precisionYesslope of the least-squares-fit linear equation determined by the (X, Y) pairs
regr_sxx(Y, X)double precisiondouble precisionYessum(X^2) - sum(X)^2/N ("sum of squares" of the independent variable)
regr_sxy(Y, X)double precisiondouble precisionYessum(X*Y) - sum(X) * sum(Y)/N ("sum of products" of independent times dependent variable)
regr_syy(Y, X)double precisiondouble precisionYessum(Y^2) - sum(Y)^2/N ("sum of squares" of the dependent variable)

对比测试

测试5000万条记录

postgres=# create table agg_test(x float8, y float8);
postgres=# insert into agg_test select 10000*random(), 10000*random() from generate_series(1,50000000);

1. 9.6 非并行
聚合计算耗费了7.1秒

postgres=# show max_parallel_workers_per_gather ;
 max_parallel_workers_per_gather 
---------------------------------
 0
(1 row)

postgres=# explain (analyze,verbose,timing,costs,buffers) select corr(y,x), covar_pop(y,x), covar_samp(y,x), regr_avgx(y,x), regr_avgy(y,x), regr_intercept(y,x), regr_r2(y,x), regr_slope(y,x), regr_sxx(y,x), regr_sxy(y,x), regr_syy(y,x) from agg_test ;
                                                                                            QUERY PLAN                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2145276.13..2145276.14 rows=1 width=88) (actual time=11703.472..11703.472 rows=1 loops=1)
   Output: corr(y, x), covar_pop(y, x), covar_samp(y, x), regr_avgx(y, x), regr_avgy(y, x), regr_intercept(y, x), regr_r2(y, x), regr_slope(y, x), regr_sxx(y, x), regr_sxy(y, x), regr_syy(y, x)
   Buffers: shared hit=270271
   ->  Seq Scan on public.agg_test  (cost=0.00..770272.36 rows=50000136 width=16) (actual time=0.010..4594.588 rows=50000000 loops=1)
         Output: x, y
         Buffers: shared hit=270271
 Planning time: 0.082 ms
 Execution time: 11703.541 ms
(8 rows)

2. 9.5
聚合计算耗费了36.1秒

postgres=# explain (analyze,verbose,timing,costs,buffers) select corr(y,x), covar_pop(y,x), covar_samp(y,x), regr_avgx(y,x), regr_avgy(y,x), regr_intercept(y,x), regr_r2(y,x), regr_slope(y,x), regr_sxx(y,x), regr_sxy(y,x), regr_syy(y,x) from agg_test ;
                                                                                            QUERY PLAN                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2145276.13..2145276.14 rows=1 width=16) (actual time=40563.668..40563.669 rows=1 loops=1)
   Output: corr(y, x), covar_pop(y, x), covar_samp(y, x), regr_avgx(y, x), regr_avgy(y, x), regr_intercept(y, x), regr_r2(y, x), regr_slope(y, x), regr_sxx(y, x), regr_sxy(y, x), regr_syy(y, x)
   Buffers: shared hit=270271
   ->  Seq Scan on public.agg_test  (cost=0.00..770272.36 rows=50000136 width=16) (actual time=0.009..4481.032 rows=50000000 loops=1)
         Output: x, y
         Buffers: shared hit=270271
 Planning time: 0.063 ms
 Execution time: 40563.742 ms
(8 rows)

3. 9.6 并行
聚合计算约耗费0.2秒

postgres=# set max_parallel_workers_per_gather =128;
postgres=# set force_parallel_mode =on;  
postgres=# alter table agg_test set (parallel_workers =32);
postgres=# explain (analyze,verbose,timing,costs,buffers) select corr(y,x), covar_pop(y,x), covar_samp(y,x), regr_avgx(y,x), regr_avgy(y,x), regr_intercept(y,x), regr_r2(y,x), regr_slope(y,x), regr_sxx(y,x), regr_sxy(y,x), regr_syy(y,x) from agg_test ;
                                                                                                                                                      QUERY PLAN                                                 

 Finalize Aggregate  (cost=329869.02..329869.03 rows=1 width=88) (actual time=456.718..456.718 rows=1 loops=1)
   Output: corr(y, x), covar_pop(y, x), covar_samp(y, x), regr_avgx(y, x), regr_avgy(y, x), regr_intercept(y, x), regr_r2(y, x), regr_slope(y, x), regr_sxx(y, x), regr_sxy(y, x), regr_syy(y, x)
   Buffers: shared hit=275071
   ->  Gather  (cost=329864.90..329868.11 rows=32 width=352) (actual time=456.567..456.612 rows=33 loops=1)
         Output: (PARTIAL corr(y, x)), (PARTIAL covar_pop(y, x)), (PARTIAL covar_samp(y, x)), (PARTIAL regr_avgx(y, x)), (PARTIAL regr_avgy(y, x)), (PARTIAL regr_intercept(y, x)), (PARTIAL regr_r2(y, x)), (PARTIAL regr_slope(y, x)), (PARTIAL regr_sxx(y, x)), (PARTIAL regr_sxy(y, x)), (PARTIAL regr_syy(y, x))
         Workers Planned: 32
         Workers Launched: 32
         Buffers: shared hit=275071
         ->  Partial Aggregate  (cost=328864.90..328864.91 rows=1 width=352) (actual time=451.769..451.769 rows=1 loops=33)
               Output: PARTIAL corr(y, x), PARTIAL covar_pop(y, x), PARTIAL covar_samp(y, x), PARTIAL regr_avgx(y, x), PARTIAL regr_avgy(y, x), PARTIAL regr_intercept(y, x), PARTIAL regr_r2(y, x), PARTIAL regr_slope(y, x), PARTIAL regr_sxx(y, x), PARTIAL regr_sxy(y, x), PARTIAL regr_syy(y, x)
               Buffers: shared hit=270655
               Worker 0: actual time=448.888..448.888 rows=1 loops=1
                 Buffers: shared hit=8265
               Worker 1: actual time=449.881..449.881 rows=1 loops=1
                 Buffers: shared hit=8357
               Worker 2: actual time=450.175..450.176 rows=1 loops=1
                 Buffers: shared hit=8295
               Worker 3: actual time=450.306..450.306 rows=1 loops=1
                 Buffers: shared hit=8357
               Worker 4: actual time=449.567..449.567 rows=1 loops=1
                 Buffers: shared hit=6844
               Worker 5: actual time=450.467..450.467 rows=1 loops=1
                 Buffers: shared hit=8360
               Worker 6: actual time=450.574..450.574 rows=1 loops=1
                 Buffers: shared hit=7898
               Worker 7: actual time=450.665..450.665 rows=1 loops=1
                 Buffers: shared hit=8397
               Worker 8: actual time=450.719..450.719 rows=1 loops=1
                 Buffers: shared hit=8084
               Worker 9: actual time=450.922..450.922 rows=1 loops=1
                 Buffers: shared hit=8405
               Worker 10: actual time=451.004..451.004 rows=1 loops=1
                 Buffers: shared hit=5421
               Worker 11: actual time=451.175..451.175 rows=1 loops=1
                 Buffers: shared hit=8431
               Worker 12: actual time=451.316..451.316 rows=1 loops=1
                 Buffers: shared hit=8276
               Worker 13: actual time=451.457..451.457 rows=1 loops=1
                 Buffers: shared hit=8431
               Worker 14: actual time=451.506..451.506 rows=1 loops=1
                 Buffers: shared hit=8163
               Worker 15: actual time=451.670..451.670 rows=1 loops=1
                 Buffers: shared hit=7959
               Worker 16: actual time=451.797..451.797 rows=1 loops=1
                 Buffers: shared hit=8428
               Worker 17: actual time=451.875..451.875 rows=1 loops=1
                 Buffers: shared hit=8265
               Worker 18: actual time=451.982..451.982 rows=1 loops=1
                 Buffers: shared hit=8444
               Worker 19: actual time=452.127..452.127 rows=1 loops=1
                 Buffers: shared hit=7717
               Worker 20: actual time=452.232..452.232 rows=1 loops=1
                 Buffers: shared hit=8450
               Worker 21: actual time=452.331..452.331 rows=1 loops=1
                 Buffers: shared hit=8304
               Worker 22: actual time=452.450..452.450 rows=1 loops=1
                 Buffers: shared hit=8455
               Worker 23: actual time=452.592..452.592 rows=1 loops=1
                 Buffers: shared hit=8367
               Worker 24: actual time=452.679..452.679 rows=1 loops=1
                 Buffers: shared hit=8460
               Worker 25: actual time=452.814..452.815 rows=1 loops=1
                 Buffers: shared hit=8445
               Worker 26: actual time=452.969..452.969 rows=1 loops=1
                 Buffers: shared hit=8465
               Worker 27: actual time=452.999..452.999 rows=1 loops=1
                 Buffers: shared hit=8454
               Worker 28: actual time=453.193..453.193 rows=1 loops=1
                 Buffers: shared hit=8462
               Worker 29: actual time=452.985..452.985 rows=1 loops=1
                 Buffers: shared hit=8437
               Worker 30: actual time=453.482..453.483 rows=1 loops=1
                 Buffers: shared hit=8348
               Worker 31: actual time=453.505..453.505 rows=1 loops=1
                 Buffers: shared hit=8182
               ->  Parallel Seq Scan on public.agg_test  (cost=0.00..285896.04 rows=1562504 width=16) (actual time=0.046..248.331 rows=1515152 loops=33)
                     Output: y, x
                     Buffers: shared hit=270655
                     Worker 0: actual time=0.058..247.983 rows=1526805 loops=1
                       Buffers: shared hit=8265
                     Worker 1: actual time=0.047..249.121 rows=1543825 loops=1
                       Buffers: shared hit=8357
                     Worker 2: actual time=0.047..249.206 rows=1532355 loops=1
                       Buffers: shared hit=8295
                     Worker 3: actual time=0.047..249.914 rows=1543825 loops=1
                       Buffers: shared hit=8357
                     Worker 4: actual time=0.069..244.072 rows=1263920 loops=1
                       Buffers: shared hit=6844
                     Worker 5: actual time=0.046..250.046 rows=1544380 loops=1
                       Buffers: shared hit=8360
                     Worker 6: actual time=0.047..247.860 rows=1458910 loops=1
                       Buffers: shared hit=7898
                     Worker 7: actual time=0.045..249.471 rows=1551225 loops=1
                       Buffers: shared hit=8397
                     Worker 8: actual time=0.047..247.850 rows=1493320 loops=1
                       Buffers: shared hit=8084
                     Worker 9: actual time=0.049..249.905 rows=1552705 loops=1
                       Buffers: shared hit=8405
                     Worker 10: actual time=0.048..240.578 rows=1000665 loops=1
                       Buffers: shared hit=5421
                     Worker 11: actual time=0.043..249.234 rows=1557515 loops=1
                       Buffers: shared hit=8431
                     Worker 12: actual time=0.044..248.830 rows=1528840 loops=1
                       Buffers: shared hit=8276
                     Worker 13: actual time=0.046..249.576 rows=1557515 loops=1
                       Buffers: shared hit=8431
                     Worker 14: actual time=0.043..248.819 rows=1507935 loops=1
                       Buffers: shared hit=8163
                     Worker 15: actual time=0.046..248.303 rows=1470195 loops=1
                       Buffers: shared hit=7959
                     Worker 16: actual time=0.045..249.997 rows=1556960 loops=1
                       Buffers: shared hit=8428
                     Worker 17: actual time=0.046..249.282 rows=1526805 loops=1
                       Buffers: shared hit=8265
                     Worker 18: actual time=0.043..249.849 rows=1559785 loops=1
                       Buffers: shared hit=8444
                     Worker 19: actual time=0.047..247.241 rows=1425425 loops=1
                       Buffers: shared hit=7717
                     Worker 20: actual time=0.043..250.134 rows=1561030 loops=1
                       Buffers: shared hit=8450
                     Worker 21: actual time=0.044..249.316 rows=1534020 loops=1
                       Buffers: shared hit=8304
                     Worker 22: actual time=0.043..250.169 rows=1561955 loops=1
                       Buffers: shared hit=8455
                     Worker 23: actual time=0.045..249.550 rows=1545675 loops=1
                       Buffers: shared hit=8367
                     Worker 24: actual time=0.044..250.062 rows=1562880 loops=1
                       Buffers: shared hit=8460
                     Worker 25: actual time=0.043..250.298 rows=1560105 loops=1
                       Buffers: shared hit=8445
                     Worker 26: actual time=0.043..249.939 rows=1563805 loops=1
                       Buffers: shared hit=8465
                     Worker 27: actual time=0.049..250.511 rows=1561770 loops=1
                       Buffers: shared hit=8454
                     Worker 28: actual time=0.045..250.523 rows=1563250 loops=1
                       Buffers: shared hit=8462
                     Worker 29: actual time=0.049..250.492 rows=1558625 loops=1
                       Buffers: shared hit=8437
                     Worker 30: actual time=0.053..247.131 rows=1542160 loops=1
                       Buffers: shared hit=8348
                     Worker 31: actual time=0.053..249.789 rows=1511450 loops=1
                       Buffers: shared hit=8182
 Planning time: 0.101 ms
 Execution time: 483.888 ms
(144 rows)

9.6的优化效果很明显,在没有使用并行的情况下,聚合操作已经有约5倍的性能提升。

结果对比

版本9.69.59.6并行(32)
5000万记录(11个聚合函数)耗时(秒)7.136.10.2

pic3

代码

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=804163bc25e979fcd91b02e58fa2d1c6b587cc65

涉及如下

src/backend/executor/execQual.c     diff | blob | blame | history
src/backend/executor/nodeAgg.c      diff | blob | blame | history
src/backend/executor/nodeWindowAgg.c        diff | blob | blame | history
src/backend/parser/parse_agg.c      diff | blob | blame | history
src/include/nodes/execnodes.h       diff | blob | blame | history
src/include/parser/parse_agg.h      diff | blob | blame | history
src/test/regress/expected/aggregates.out        diff | blob | blame | history
src/test/regress/sql/aggregates.sql     diff | blob | blame | history

小结

在统计学中,大多数的统计算法的中间结果都是可以共用的,例如sum,avg; 方差,相关性,count,sum等运算;

PostgreSQL 9.6很好的抓住了这样的特征,对初始条件一致,中间算法一致的聚合函数,在同一个分组中数据只需要计算一遍,大大降低了CPU的开销,提高了统计效率。

这个思路与LLVM有一些神似的地方,不过LLVM的适用场景更广。

Count

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值