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

4 files changed, 43 insertions, 40 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index cb9021c525a..b0286157f71 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	201311161
+#define CATALOG_VERSION_NO	201311162
 
 #endif
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
index 416d651bf0a..034456a315d 100644
--- a/src/include/catalog/pg_aggregate.h
+++ b/src/include/catalog/pg_aggregate.h
@@ -80,23 +80,23 @@ typedef FormData_pg_aggregate *Form_pg_aggregate;
  */
 
 /* avg */
-DATA(insert ( 2100	int8_avg_accum	numeric_avg		0	1231	0	"{0,0}" ));
+DATA(insert ( 2100	int8_avg_accum	numeric_avg		0	2281	128	_null_ ));
 DATA(insert ( 2101	int4_avg_accum	int8_avg		0	1016	0	"{0,0}" ));
 DATA(insert ( 2102	int2_avg_accum	int8_avg		0	1016	0	"{0,0}" ));
-DATA(insert ( 2103	numeric_avg_accum	numeric_avg 0	1231	0	"{0,0}" ));
+DATA(insert ( 2103	numeric_avg_accum	numeric_avg 0	2281	128	_null_ ));
 DATA(insert ( 2104	float4_accum	float8_avg		0	1022	0	"{0,0,0}" ));
 DATA(insert ( 2105	float8_accum	float8_avg		0	1022	0	"{0,0,0}" ));
 DATA(insert ( 2106	interval_accum	interval_avg	0	1187	0	"{0 second,0 second}" ));
 
 /* sum */
-DATA(insert ( 2107	int8_sum		-				0	1700	0	_null_ ));
+DATA(insert ( 2107	int8_avg_accum	numeric_sum		0	2281	128	_null_ ));
 DATA(insert ( 2108	int4_sum		-				0	20		0	_null_ ));
 DATA(insert ( 2109	int2_sum		-				0	20		0	_null_ ));
 DATA(insert ( 2110	float4pl		-				0	700		0	_null_ ));
 DATA(insert ( 2111	float8pl		-				0	701		0	_null_ ));
 DATA(insert ( 2112	cash_pl			-				0	790		0	_null_ ));
 DATA(insert ( 2113	interval_pl		-				0	1186	0	_null_ ));
-DATA(insert ( 2114	numeric_add		-				0	1700	0	_null_ ));
+DATA(insert ( 2114	numeric_avg_accum	numeric_sum	0	2281	128	_null_ ));
 
 /* max */
 DATA(insert ( 2115	int8larger		-				413		20		0	_null_ ));
@@ -147,52 +147,52 @@ DATA(insert ( 2147	int8inc_any		-				0		20		0	"0" ));
 DATA(insert ( 2803	int8inc			-				0		20		0	"0" ));
 
 /* var_pop */
-DATA(insert ( 2718	int8_accum	numeric_var_pop 0	1231	0	"{0,0,0}" ));
-DATA(insert ( 2719	int4_accum	numeric_var_pop 0	1231	0	"{0,0,0}" ));
-DATA(insert ( 2720	int2_accum	numeric_var_pop 0	1231	0	"{0,0,0}" ));
+DATA(insert ( 2718	int8_accum	numeric_var_pop 0	2281	128	_null_ ));
+DATA(insert ( 2719	int4_accum	numeric_var_pop 0	2281	128	_null_ ));
+DATA(insert ( 2720	int2_accum	numeric_var_pop 0	2281	128	_null_ ));
 DATA(insert ( 2721	float4_accum	float8_var_pop 0	1022	0	"{0,0,0}" ));
 DATA(insert ( 2722	float8_accum	float8_var_pop 0	1022	0	"{0,0,0}" ));
-DATA(insert ( 2723	numeric_accum  numeric_var_pop 0	1231	0	"{0,0,0}" ));
+DATA(insert ( 2723	numeric_accum  numeric_var_pop 0	2281	128	_null_ ));
 
 /* var_samp */
-DATA(insert ( 2641	int8_accum	numeric_var_samp	0	1231	0	"{0,0,0}" ));
-DATA(insert ( 2642	int4_accum	numeric_var_samp	0	1231	0	"{0,0,0}" ));
-DATA(insert ( 2643	int2_accum	numeric_var_samp	0	1231	0	"{0,0,0}" ));
+DATA(insert ( 2641	int8_accum	numeric_var_samp	0	2281	128	_null_ ));
+DATA(insert ( 2642	int4_accum	numeric_var_samp	0	2281	128	_null_ ));
+DATA(insert ( 2643	int2_accum	numeric_var_samp	0	2281	128	_null_ ));
 DATA(insert ( 2644	float4_accum	float8_var_samp 0	1022	0	"{0,0,0}" ));
 DATA(insert ( 2645	float8_accum	float8_var_samp 0	1022	0	"{0,0,0}" ));
-DATA(insert ( 2646	numeric_accum  numeric_var_samp 0	1231	0	"{0,0,0}" ));
+DATA(insert ( 2646	numeric_accum  numeric_var_samp 0	2281	128	_null_ ));
 
 /* variance: historical Postgres syntax for var_samp */
-DATA(insert ( 2148	int8_accum	numeric_var_samp	0	1231	0	"{0,0,0}" ));
-DATA(insert ( 2149	int4_accum	numeric_var_samp	0	1231	0	"{0,0,0}" ));
-DATA(insert ( 2150	int2_accum	numeric_var_samp	0	1231	0	"{0,0,0}" ));
+DATA(insert ( 2148	int8_accum	numeric_var_samp	0	2281	128	_null_ ));
+DATA(insert ( 2149	int4_accum	numeric_var_samp	0	2281	128	_null_ ));
+DATA(insert ( 2150	int2_accum	numeric_var_samp	0	2281	128	_null_ ));
 DATA(insert ( 2151	float4_accum	float8_var_samp 0	1022	0	"{0,0,0}" ));
 DATA(insert ( 2152	float8_accum	float8_var_samp 0	1022	0	"{0,0,0}" ));
-DATA(insert ( 2153	numeric_accum  numeric_var_samp 0	1231	0	"{0,0,0}" ));
+DATA(insert ( 2153	numeric_accum  numeric_var_samp 0	2281	128	_null_ ));
 
 /* stddev_pop */
-DATA(insert ( 2724	int8_accum	numeric_stddev_pop		0	1231	0	"{0,0,0}" ));
-DATA(insert ( 2725	int4_accum	numeric_stddev_pop		0	1231	0	"{0,0,0}" ));
-DATA(insert ( 2726	int2_accum	numeric_stddev_pop		0	1231	0	"{0,0,0}" ));
+DATA(insert ( 2724	int8_accum	numeric_stddev_pop		0	2281	128	_null_ ));
+DATA(insert ( 2725	int4_accum	numeric_stddev_pop		0	2281	128	_null_ ));
+DATA(insert ( 2726	int2_accum	numeric_stddev_pop		0	2281	128	_null_ ));
 DATA(insert ( 2727	float4_accum	float8_stddev_pop	0	1022	0	"{0,0,0}" ));
 DATA(insert ( 2728	float8_accum	float8_stddev_pop	0	1022	0	"{0,0,0}" ));
-DATA(insert ( 2729	numeric_accum	numeric_stddev_pop	0	1231	0	"{0,0,0}" ));
+DATA(insert ( 2729	numeric_accum	numeric_stddev_pop	0	2281	128	_null_ ));
 
 /* stddev_samp */
-DATA(insert ( 2712	int8_accum	numeric_stddev_samp		0	1231	0	"{0,0,0}" ));
-DATA(insert ( 2713	int4_accum	numeric_stddev_samp		0	1231	0	"{0,0,0}" ));
-DATA(insert ( 2714	int2_accum	numeric_stddev_samp		0	1231	0	"{0,0,0}" ));
+DATA(insert ( 2712	int8_accum	numeric_stddev_samp		0	2281	128	_null_ ));
+DATA(insert ( 2713	int4_accum	numeric_stddev_samp		0	2281	128	_null_ ));
+DATA(insert ( 2714	int2_accum	numeric_stddev_samp		0	2281	128	_null_ ));
 DATA(insert ( 2715	float4_accum	float8_stddev_samp	0	1022	0	"{0,0,0}" ));
 DATA(insert ( 2716	float8_accum	float8_stddev_samp	0	1022	0	"{0,0,0}" ));
-DATA(insert ( 2717	numeric_accum	numeric_stddev_samp 0	1231	0	"{0,0,0}" ));
+DATA(insert ( 2717	numeric_accum	numeric_stddev_samp 0	2281	128	_null_ ));
 
 /* stddev: historical Postgres syntax for stddev_samp */
-DATA(insert ( 2154	int8_accum	numeric_stddev_samp		0	1231	0	"{0,0,0}" ));
-DATA(insert ( 2155	int4_accum	numeric_stddev_samp		0	1231	0	"{0,0,0}" ));
-DATA(insert ( 2156	int2_accum	numeric_stddev_samp		0	1231	0	"{0,0,0}" ));
+DATA(insert ( 2154	int8_accum	numeric_stddev_samp		0	2281	128	_null_ ));
+DATA(insert ( 2155	int4_accum	numeric_stddev_samp		0	2281	128	_null_ ));
+DATA(insert ( 2156	int2_accum	numeric_stddev_samp		0	2281	128	_null_ ));
 DATA(insert ( 2157	float4_accum	float8_stddev_samp	0	1022	0	"{0,0,0}" ));
 DATA(insert ( 2158	float8_accum	float8_stddev_samp	0	1022	0	"{0,0,0}" ));
-DATA(insert ( 2159	numeric_accum	numeric_stddev_samp 0	1231	0	"{0,0,0}" ));
+DATA(insert ( 2159	numeric_accum	numeric_stddev_samp 0	2281	128	_null_ ));
 
 /* SQL2003 binary regression aggregates */
 DATA(insert ( 2818	int8inc_float8_float8		-				0	20		0	"0" ));
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index ca4fc62bcdb..c5d5d291558 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2390,27 +2390,29 @@ DATA(insert OID = 2513 (  float8_stddev_pop PGNSP PGUID 12 1 0 0 0 f f f f t f i
 DESCR("aggregate final function");
 DATA(insert OID = 1832 (  float8_stddev_samp	PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 701 "1022" _null_ _null_ _null_ _null_ float8_stddev_samp _null_ _null_ _null_ ));
 DESCR("aggregate final function");
-DATA(insert OID = 1833 (  numeric_accum    PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1231 "1231 1700" _null_ _null_ _null_ _null_ numeric_accum _null_ _null_ _null_ ));
+DATA(insert OID = 1833 (  numeric_accum    PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 1700" _null_ _null_ _null_ _null_ numeric_accum _null_ _null_ _null_ ));
 DESCR("aggregate transition function");
-DATA(insert OID = 2858 (  numeric_avg_accum    PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1231 "1231 1700" _null_ _null_ _null_ _null_ numeric_avg_accum _null_ _null_ _null_ ));
+DATA(insert OID = 2858 (  numeric_avg_accum    PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 1700" _null_ _null_ _null_ _null_ numeric_avg_accum _null_ _null_ _null_ ));
 DESCR("aggregate transition function");
-DATA(insert OID = 1834 (  int2_accum	   PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1231 "1231 21" _null_ _null_ _null_ _null_ int2_accum _null_ _null_ _null_ ));
+DATA(insert OID = 1834 (  int2_accum	   PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 21" _null_ _null_ _null_ _null_ int2_accum _null_ _null_ _null_ ));
 DESCR("aggregate transition function");
-DATA(insert OID = 1835 (  int4_accum	   PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1231 "1231 23" _null_ _null_ _null_ _null_ int4_accum _null_ _null_ _null_ ));
+DATA(insert OID = 1835 (  int4_accum	   PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 23" _null_ _null_ _null_ _null_ int4_accum _null_ _null_ _null_ ));
 DESCR("aggregate transition function");
-DATA(insert OID = 1836 (  int8_accum	   PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1231 "1231 20" _null_ _null_ _null_ _null_ int8_accum _null_ _null_ _null_ ));
+DATA(insert OID = 1836 (  int8_accum	   PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 20" _null_ _null_ _null_ _null_ int8_accum _null_ _null_ _null_ ));
 DESCR("aggregate transition function");
-DATA(insert OID = 2746 (  int8_avg_accum	   PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1231 "1231 20" _null_ _null_ _null_ _null_ int8_avg_accum _null_ _null_ _null_ ));
+DATA(insert OID = 2746 (  int8_avg_accum	   PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 20" _null_ _null_ _null_ _null_ int8_avg_accum _null_ _null_ _null_ ));
 DESCR("aggregate transition function");
-DATA(insert OID = 1837 (  numeric_avg	   PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 1700 "1231" _null_ _null_ _null_ _null_ numeric_avg _null_ _null_ _null_ ));
+DATA(insert OID = 3178 (  numeric_sum	   PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_ numeric_sum _null_ _null_ _null_ ));
 DESCR("aggregate final function");
-DATA(insert OID = 2514 (  numeric_var_pop  PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 1700 "1231" _null_ _null_ _null_ _null_ numeric_var_pop _null_ _null_ _null_ ));
+DATA(insert OID = 1837 (  numeric_avg	   PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_ numeric_avg _null_ _null_ _null_ ));
 DESCR("aggregate final function");
-DATA(insert OID = 1838 (  numeric_var_samp PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 1700 "1231" _null_ _null_ _null_ _null_ numeric_var_samp _null_ _null_ _null_ ));
+DATA(insert OID = 2514 (  numeric_var_pop  PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_ numeric_var_pop _null_ _null_ _null_ ));
 DESCR("aggregate final function");
-DATA(insert OID = 2596 (  numeric_stddev_pop PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 1700 "1231" _null_ _null_ _null_ _null_	numeric_stddev_pop _null_ _null_ _null_ ));
+DATA(insert OID = 1838 (  numeric_var_samp PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_ numeric_var_samp _null_ _null_ _null_ ));
 DESCR("aggregate final function");
-DATA(insert OID = 1839 (  numeric_stddev_samp	PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 1700 "1231" _null_ _null_ _null_ _null_ numeric_stddev_samp _null_ _null_ _null_ ));
+DATA(insert OID = 2596 (  numeric_stddev_pop PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_	numeric_stddev_pop _null_ _null_ _null_ ));
+DESCR("aggregate final function");
+DATA(insert OID = 1839 (  numeric_stddev_samp	PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_ numeric_stddev_samp _null_ _null_ _null_ ));
 DESCR("aggregate final function");
 DATA(insert OID = 1840 (  int2_sum		   PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 20 "20 21" _null_ _null_ _null_ _null_ int2_sum _null_ _null_ _null_ ));
 DESCR("aggregate transition function");
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index ce3f00bc12d..f25a9de8d31 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -989,6 +989,7 @@ extern Datum int4_accum(PG_FUNCTION_ARGS);
 extern Datum int8_accum(PG_FUNCTION_ARGS);
 extern Datum int8_avg_accum(PG_FUNCTION_ARGS);
 extern Datum numeric_avg(PG_FUNCTION_ARGS);
+extern Datum numeric_sum(PG_FUNCTION_ARGS);
 extern Datum numeric_var_pop(PG_FUNCTION_ARGS);
 extern Datum numeric_var_samp(PG_FUNCTION_ARGS);
 extern Datum numeric_stddev_pop(PG_FUNCTION_ARGS);