From 89a624439eb0ac15f99f20397b6bdf0150e1900d Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sat, 17 Feb 2007 00:55:58 +0000 Subject: [PATCH] Create AVG() aggregates for int8 and NUMERIC which do not compute X^2, as a performance enhancement. Mark Kirkwood --- src/backend/utils/adt/numeric.c | 69 +++++++++++++++++-- src/include/catalog/catversion.h | 4 +- src/include/catalog/pg_aggregate.h | 6 +- src/include/catalog/pg_proc.h | 6 +- src/include/utils/builtins.h | 4 +- .../regress/expected/create_aggregate.out | 6 +- src/test/regress/sql/create_aggregate.sql | 6 +- 7 files changed, 84 insertions(+), 17 deletions(-) diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c index 7e51873fd1..19fc4d4505 100644 --- a/src/backend/utils/adt/numeric.c +++ b/src/backend/utils/adt/numeric.c @@ -14,7 +14,7 @@ * Copyright (c) 1998-2007, PostgreSQL Global Development Group * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/numeric.c,v 1.99 2007/01/16 21:41:13 neilc Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/numeric.c,v 1.100 2007/02/17 00:55:57 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -2165,6 +2165,40 @@ do_numeric_accum(ArrayType *transarray, Numeric newval) return result; } +/* + * Improve avg performance by not caclulating sum(X*X). + */ +static ArrayType * +do_numeric_avg_accum(ArrayType *transarray, Numeric newval) +{ + Datum *transdatums; + int ndatums; + Datum N, + sumX; + ArrayType *result; + + /* We assume the input is array of numeric */ + deconstruct_array(transarray, + NUMERICOID, -1, false, 'i', + &transdatums, NULL, &ndatums); + if (ndatums != 2) + elog(ERROR, "expected 2-element numeric array"); + N = transdatums[0]; + sumX = transdatums[1]; + + N = DirectFunctionCall1(numeric_inc, N); + sumX = DirectFunctionCall2(numeric_add, sumX, + NumericGetDatum(newval)); + + transdatums[0] = N; + transdatums[1] = sumX; + + result = construct_array(transdatums, 2, + NUMERICOID, -1, false, 'i'); + + return result; +} + Datum numeric_accum(PG_FUNCTION_ARGS) { @@ -2174,6 +2208,18 @@ numeric_accum(PG_FUNCTION_ARGS) PG_RETURN_ARRAYTYPE_P(do_numeric_accum(transarray, newval)); } +/* + * Optimized case for average of numeric. + */ +Datum +numeric_avg_accum(PG_FUNCTION_ARGS) +{ + ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0); + Numeric newval = PG_GETARG_NUMERIC(1); + + PG_RETURN_ARRAYTYPE_P(do_numeric_avg_accum(transarray, newval)); +} + /* * Integer data types all use Numeric accumulators to share code and * avoid risk of overflow. For int2 and int4 inputs, Numeric accumulation @@ -2219,6 +2265,22 @@ int8_accum(PG_FUNCTION_ARGS) PG_RETURN_ARRAYTYPE_P(do_numeric_accum(transarray, newval)); } +/* + * Optimized case for average of int8. + */ +Datum +int8_avg_accum(PG_FUNCTION_ARGS) +{ + ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0); + Datum newval8 = PG_GETARG_DATUM(1); + Numeric newval; + + newval = DatumGetNumeric(DirectFunctionCall1(int8_numeric, newval8)); + + PG_RETURN_ARRAYTYPE_P(do_numeric_avg_accum(transarray, newval)); +} + + Datum numeric_avg(PG_FUNCTION_ARGS) { @@ -2232,11 +2294,10 @@ numeric_avg(PG_FUNCTION_ARGS) deconstruct_array(transarray, NUMERICOID, -1, false, 'i', &transdatums, NULL, &ndatums); - if (ndatums != 3) - elog(ERROR, "expected 3-element numeric array"); + if (ndatums != 2) + elog(ERROR, "expected 2-element numeric array"); N = DatumGetNumeric(transdatums[0]); sumX = DatumGetNumeric(transdatums[1]); - /* ignore sumX2 */ /* SQL92 defines AVG of no values to be NULL */ /* N is zero iff no digits (cf. numeric_uminus) */ diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 88e4459f90..5402186326 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.385 2007/02/16 07:46:55 petere Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.386 2007/02/17 00:55:57 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200702161 +#define CATALOG_VERSION_NO 200702162 #endif diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h index 1f2ba57147..9036b1d4a6 100644 --- a/src/include/catalog/pg_aggregate.h +++ b/src/include/catalog/pg_aggregate.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.60 2007/01/20 09:27:19 petere Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.61 2007/02/17 00:55:57 momjian Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -80,10 +80,10 @@ typedef FormData_pg_aggregate *Form_pg_aggregate; */ /* avg */ -DATA(insert ( 2100 int8_accum numeric_avg 0 1231 "{0,0,0}" )); +DATA(insert ( 2100 int8_avg_accum numeric_avg 0 1231 "{0,0}" )); DATA(insert ( 2101 int4_avg_accum int8_avg 0 1016 "{0,0}" )); DATA(insert ( 2102 int2_avg_accum int8_avg 0 1016 "{0,0}" )); -DATA(insert ( 2103 numeric_accum numeric_avg 0 1231 "{0,0,0}" )); +DATA(insert ( 2103 numeric_avg_accum numeric_avg 0 1231 "{0,0}" )); DATA(insert ( 2104 float4_accum float8_avg 0 1022 "{0,0,0}" )); DATA(insert ( 2105 float8_accum float8_avg 0 1022 "{0,0,0}" )); DATA(insert ( 2106 interval_accum interval_avg 0 1187 "{0 second,0 second}" )); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index a8fc694b0c..6a93090e88 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.444 2007/02/16 07:46:55 petere Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.445 2007/02/17 00:55:57 momjian Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -2744,12 +2744,16 @@ DATA(insert OID = 1832 ( float8_stddev_samp PGNSP PGUID 12 1 0 f f t f i 1 701 DESCR("STDDEV_SAMP aggregate final function"); DATA(insert OID = 1833 ( numeric_accum PGNSP PGUID 12 1 0 f f t f i 2 1231 "1231 1700" _null_ _null_ _null_ numeric_accum - _null_ )); DESCR("aggregate transition function"); +DATA(insert OID = 2858 ( numeric_avg_accum PGNSP PGUID 12 1 0 f f t f i 2 1231 "1231 1700" _null_ _null_ _null_ numeric_avg_accum - _null_ )); +DESCR("aggregate transition function"); DATA(insert OID = 1834 ( int2_accum PGNSP PGUID 12 1 0 f f t f i 2 1231 "1231 21" _null_ _null_ _null_ int2_accum - _null_ )); DESCR("aggregate transition function"); DATA(insert OID = 1835 ( int4_accum PGNSP PGUID 12 1 0 f f t f i 2 1231 "1231 23" _null_ _null_ _null_ int4_accum - _null_ )); DESCR("aggregate transition function"); DATA(insert OID = 1836 ( int8_accum PGNSP PGUID 12 1 0 f f t f i 2 1231 "1231 20" _null_ _null_ _null_ int8_accum - _null_ )); DESCR("aggregate transition function"); +DATA(insert OID = 2746 ( int8_avg_accum PGNSP PGUID 12 1 0 f f t f i 2 1231 "1231 20" _null_ _null_ _null_ int8_avg_accum - _null_ )); +DESCR("aggregate transition function"); DATA(insert OID = 1837 ( numeric_avg PGNSP PGUID 12 1 0 f f t f i 1 1700 "1231" _null_ _null_ _null_ numeric_avg - _null_ )); DESCR("AVG aggregate final function"); DATA(insert OID = 2514 ( numeric_var_pop PGNSP PGUID 12 1 0 f f t f i 1 1700 "1231" _null_ _null_ _null_ numeric_var_pop - _null_ )); diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index aee4095654..db4357c17b 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.287 2007/01/28 16:16:54 neilc Exp $ + * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.288 2007/02/17 00:55:58 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -841,9 +841,11 @@ extern Datum numeric_float4(PG_FUNCTION_ARGS); extern Datum text_numeric(PG_FUNCTION_ARGS); extern Datum numeric_text(PG_FUNCTION_ARGS); extern Datum numeric_accum(PG_FUNCTION_ARGS); +extern Datum numeric_avg_accum(PG_FUNCTION_ARGS); extern Datum int2_accum(PG_FUNCTION_ARGS); 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_var_pop(PG_FUNCTION_ARGS); extern Datum numeric_var_samp(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/create_aggregate.out b/src/test/regress/expected/create_aggregate.out index 08daaa8ee3..1c540b2d95 100644 --- a/src/test/regress/expected/create_aggregate.out +++ b/src/test/regress/expected/create_aggregate.out @@ -3,9 +3,9 @@ -- -- all functions CREATEd CREATE AGGREGATE newavg ( - sfunc = int4_accum, basetype = int4, stype = _numeric, - finalfunc = numeric_avg, - initcond1 = '{0,0,0}' + sfunc = int4_avg_accum, basetype = int4, stype = _int8, + finalfunc = int8_avg, + initcond1 = '{0,0}' ); -- test comments COMMENT ON AGGREGATE newavg_wrong (int4) IS 'an agg comment'; diff --git a/src/test/regress/sql/create_aggregate.sql b/src/test/regress/sql/create_aggregate.sql index 891b0e0892..9d02048e24 100644 --- a/src/test/regress/sql/create_aggregate.sql +++ b/src/test/regress/sql/create_aggregate.sql @@ -4,9 +4,9 @@ -- all functions CREATEd CREATE AGGREGATE newavg ( - sfunc = int4_accum, basetype = int4, stype = _numeric, - finalfunc = numeric_avg, - initcond1 = '{0,0,0}' + sfunc = int4_avg_accum, basetype = int4, stype = _int8, + finalfunc = int8_avg, + initcond1 = '{0,0}' ); -- test comments -- 2.40.0