Improve performance of numeric sum(), avg(), stddev(), variance(), etc.

This patch improves performance of most built-in aggregates that formerly
used a NUMERIC or NUMERIC array as their transition type; this includes
not only aggregates on numeric inputs, but some aggregates on integer
inputs where overflow of an int8 value is a possibility.  The code now
uses a special-purpose data structure to avoid array construction and
deconstruction overhead, as well as packing and unpacking overhead for
numeric values.

These aggregates' transition type is now declared as INTERNAL, since
it doesn't correspond to any SQL data type.  To keep the planner from
thinking that that means a lot of storage will be used, we make use
of the just-added pg_aggregate.aggtransspace feature.  The space estimate
is set to 128 bytes, which is at least in the right ballpark.

Hadi Moshayedi, reviewed by Pavel Stehule and Tomas Vondra

2 files changed, 73 insertions, 0 deletions

diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 298b2e4eb9c..e17881c59f4 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -137,6 +137,67 @@ SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
           0 |            
 (1 row)
 
+-- verify correct results for null and NaN inputs
+select sum(null::int4) from generate_series(1,3);
+ sum 
+-----
+    
+(1 row)
+
+select sum(null::int8) from generate_series(1,3);
+ sum 
+-----
+    
+(1 row)
+
+select sum(null::numeric) from generate_series(1,3);
+ sum 
+-----
+    
+(1 row)
+
+select sum(null::float8) from generate_series(1,3);
+ sum 
+-----
+    
+(1 row)
+
+select avg(null::int4) from generate_series(1,3);
+ avg 
+-----
+    
+(1 row)
+
+select avg(null::int8) from generate_series(1,3);
+ avg 
+-----
+    
+(1 row)
+
+select avg(null::numeric) from generate_series(1,3);
+ avg 
+-----
+    
+(1 row)
+
+select avg(null::float8) from generate_series(1,3);
+ avg 
+-----
+    
+(1 row)
+
+select sum('NaN'::numeric) from generate_series(1,3);
+ sum 
+-----
+ NaN
+(1 row)
+
+select avg('NaN'::numeric) from generate_series(1,3);
+ avg 
+-----
+ NaN
+(1 row)
+
 -- SQL2003 binary aggregates
 SELECT regr_count(b, a) FROM aggtest;
  regr_count 
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 397edffd3ad..1bbe073351c 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -39,6 +39,18 @@ SELECT var_samp(b::numeric) FROM aggtest;
 SELECT var_pop(1.0), var_samp(2.0);
 SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
 
+-- verify correct results for null and NaN inputs
+select sum(null::int4) from generate_series(1,3);
+select sum(null::int8) from generate_series(1,3);
+select sum(null::numeric) from generate_series(1,3);
+select sum(null::float8) from generate_series(1,3);
+select avg(null::int4) from generate_series(1,3);
+select avg(null::int8) from generate_series(1,3);
+select avg(null::numeric) from generate_series(1,3);
+select avg(null::float8) from generate_series(1,3);
+select sum('NaN'::numeric) from generate_series(1,3);
+select avg('NaN'::numeric) from generate_series(1,3);
+
 -- SQL2003 binary aggregates
 SELECT regr_count(b, a) FROM aggtest;
 SELECT regr_sxx(b, a) FROM aggtest;