Skip to content

Commit 89a6244

Browse files
bmomjianbmomjian
committed
Create AVG() aggregates for int8 and NUMERIC which do not compute X^2,
as a performance enhancement. Mark Kirkwood
1 parent 72a070a commit 89a6244

File tree

7 files changed

+84
-17
lines changed

7 files changed

+84
-17
lines changed

‎src/backend/utils/adt/numeric.c

Lines changed: 65 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
* Copyright (c) 1998-2007, PostgreSQL Global Development Group
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/src/backend/utils/adt/numeric.c,v 1.99 2007/01/16 21:41:13 neilc Exp $
17+
* $PostgreSQL: pgsql/src/backend/utils/adt/numeric.c,v 1.100 2007/02/17 00:55:57 momjian Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -2165,6 +2165,40 @@ do_numeric_accum(ArrayType *transarray, Numeric newval)
21652165
return result;
21662166
}
21672167

2168+
/*
2169+
* Improve avg performance by not caclulating sum(X*X).
2170+
*/
2171+
static ArrayType *
2172+
do_numeric_avg_accum(ArrayType *transarray, Numeric newval)
2173+
{
2174+
Datum *transdatums;
2175+
int ndatums;
2176+
Datum N,
2177+
sumX;
2178+
ArrayType *result;
2179+
2180+
/* We assume the input is array of numeric */
2181+
deconstruct_array(transarray,
2182+
NUMERICOID, -1, false, 'i',
2183+
&transdatums, NULL, &ndatums);
2184+
if (ndatums != 2)
2185+
elog(ERROR, "expected 2-element numeric array");
2186+
N = transdatums[0];
2187+
sumX = transdatums[1];
2188+
2189+
N = DirectFunctionCall1(numeric_inc, N);
2190+
sumX = DirectFunctionCall2(numeric_add, sumX,
2191+
NumericGetDatum(newval));
2192+
2193+
transdatums[0] = N;
2194+
transdatums[1] = sumX;
2195+
2196+
result = construct_array(transdatums, 2,
2197+
NUMERICOID, -1, false, 'i');
2198+
2199+
return result;
2200+
}
2201+
21682202
Datum
21692203
numeric_accum(PG_FUNCTION_ARGS)
21702204
{
@@ -2174,6 +2208,18 @@ numeric_accum(PG_FUNCTION_ARGS)
21742208
PG_RETURN_ARRAYTYPE_P(do_numeric_accum(transarray, newval));
21752209
}
21762210

2211+
/*
2212+
* Optimized case for average of numeric.
2213+
*/
2214+
Datum
2215+
numeric_avg_accum(PG_FUNCTION_ARGS)
2216+
{
2217+
ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
2218+
Numeric newval = PG_GETARG_NUMERIC(1);
2219+
2220+
PG_RETURN_ARRAYTYPE_P(do_numeric_avg_accum(transarray, newval));
2221+
}
2222+
21772223
/*
21782224
* Integer data types all use Numeric accumulators to share code and
21792225
* avoid risk of overflow. For int2 and int4 inputs, Numeric accumulation
@@ -2219,6 +2265,22 @@ int8_accum(PG_FUNCTION_ARGS)
22192265
PG_RETURN_ARRAYTYPE_P(do_numeric_accum(transarray, newval));
22202266
}
22212267

2268+
/*
2269+
* Optimized case for average of int8.
2270+
*/
2271+
Datum
2272+
int8_avg_accum(PG_FUNCTION_ARGS)
2273+
{
2274+
ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
2275+
Datum newval8 = PG_GETARG_DATUM(1);
2276+
Numeric newval;
2277+
2278+
newval = DatumGetNumeric(DirectFunctionCall1(int8_numeric, newval8));
2279+
2280+
PG_RETURN_ARRAYTYPE_P(do_numeric_avg_accum(transarray, newval));
2281+
}
2282+
2283+
22222284
Datum
22232285
numeric_avg(PG_FUNCTION_ARGS)
22242286
{
@@ -2232,11 +2294,10 @@ numeric_avg(PG_FUNCTION_ARGS)
22322294
deconstruct_array(transarray,
22332295
NUMERICOID, -1, false, 'i',
22342296
&transdatums, NULL, &ndatums);
2235-
if (ndatums != 3)
2236-
elog(ERROR, "expected 3-element numeric array");
2297+
if (ndatums != 2)
2298+
elog(ERROR, "expected 2-element numeric array");
22372299
N = DatumGetNumeric(transdatums[0]);
22382300
sumX = DatumGetNumeric(transdatums[1]);
2239-
/* ignore sumX2 */
22402301

22412302
/* SQL92 defines AVG of no values to be NULL */
22422303
/* N is zero iff no digits (cf. numeric_uminus) */

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.385 2007/02/16 07:46:55 petere Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.386 2007/02/17 00:55:57 momjian Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200702161
56+
#define CATALOG_VERSION_NO 200702162
5757

5858
#endif

‎src/include/catalog/pg_aggregate.h

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.60 2007/01/20 09:27:19 petere Exp $
11+
* $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.61 2007/02/17 00:55:57 momjian Exp $
1212
*
1313
* NOTES
1414
* the genbki.sh script reads this file and generates .bki
@@ -80,10 +80,10 @@ typedef FormData_pg_aggregate *Form_pg_aggregate;
8080
*/
8181

8282
/* avg */
83-
DATA(insert ( 2100 int8_accum numeric_avg 0 1231 "{0,0,0}" ));
83+
DATA(insert ( 2100 int8_avg_accum numeric_avg 0 1231 "{0,0}" ));
8484
DATA(insert ( 2101 int4_avg_accum int8_avg 0 1016 "{0,0}" ));
8585
DATA(insert ( 2102 int2_avg_accum int8_avg 0 1016 "{0,0}" ));
86-
DATA(insert ( 2103 numeric_accum numeric_avg 0 1231 "{0,0,0}" ));
86+
DATA(insert ( 2103 numeric_avg_accum numeric_avg 0 1231 "{0,0}" ));
8787
DATA(insert ( 2104 float4_accum float8_avg 0 1022 "{0,0,0}" ));
8888
DATA(insert ( 2105 float8_accum float8_avg 0 1022 "{0,0,0}" ));
8989
DATA(insert ( 2106 interval_accum interval_avg 0 1187 "{0 second,0 second}" ));

‎src/include/catalog/pg_proc.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.444 2007/02/16 07:46:55 petere Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.445 2007/02/17 00:55:57 momjian Exp $
1111
*
1212
* NOTES
1313
* 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
27442744
DESCR("STDDEV_SAMP aggregate final function");
27452745
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_ ));
27462746
DESCR("aggregate transition function");
2747+
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_ ));
2748+
DESCR("aggregate transition function");
27472749
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_ ));
27482750
DESCR("aggregate transition function");
27492751
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_ ));
27502752
DESCR("aggregate transition function");
27512753
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_ ));
27522754
DESCR("aggregate transition function");
2755+
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_ ));
2756+
DESCR("aggregate transition function");
27532757
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_ ));
27542758
DESCR("AVG aggregate final function");
27552759
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_ ));

‎src/include/utils/builtins.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.287 2007/01/28 16:16:54 neilc Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.288 2007/02/17 00:55:58 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -841,9 +841,11 @@ extern Datum numeric_float4(PG_FUNCTION_ARGS);
841841
extern Datum text_numeric(PG_FUNCTION_ARGS);
842842
extern Datum numeric_text(PG_FUNCTION_ARGS);
843843
extern Datum numeric_accum(PG_FUNCTION_ARGS);
844+
extern Datum numeric_avg_accum(PG_FUNCTION_ARGS);
844845
extern Datum int2_accum(PG_FUNCTION_ARGS);
845846
extern Datum int4_accum(PG_FUNCTION_ARGS);
846847
extern Datum int8_accum(PG_FUNCTION_ARGS);
848+
extern Datum int8_avg_accum(PG_FUNCTION_ARGS);
847849
extern Datum numeric_avg(PG_FUNCTION_ARGS);
848850
extern Datum numeric_var_pop(PG_FUNCTION_ARGS);
849851
extern Datum numeric_var_samp(PG_FUNCTION_ARGS);

‎src/test/regress/expected/create_aggregate.out

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3,9 +3,9 @@
33
--
44
-- all functions CREATEd
55
CREATE AGGREGATE newavg (
6-
sfunc = int4_accum, basetype = int4, stype = _numeric,
7-
finalfunc = numeric_avg,
8-
initcond1 = '{0,0,0}'
6+
sfunc = int4_avg_accum, basetype = int4, stype = _int8,
7+
finalfunc = int8_avg,
8+
initcond1 = '{0,0}'
99
);
1010
-- test comments
1111
COMMENT ON AGGREGATE newavg_wrong (int4) IS 'an agg comment';

‎src/test/regress/sql/create_aggregate.sql

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -4,9 +4,9 @@
44

55
-- all functions CREATEd
66
CREATE AGGREGATE newavg (
7-
sfunc = int4_accum, basetype = int4, stype = _numeric,
8-
finalfunc = numeric_avg,
9-
initcond1 = '{0,0,0}'
7+
sfunc = int4_avg_accum, basetype = int4, stype = _int8,
8+
finalfunc = int8_avg,
9+
initcond1 = '{0,0}'
1010
);
1111

1212
-- test comments

0 commit comments

Comments
 (0)