Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit89a6244

Browse files
committed
Create AVG() aggregates for int8 and NUMERIC which do not compute X^2,
as a performance enhancement.Mark Kirkwood
1 parent72a070a commit89a6244

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
returnresult;
21662166
}
21672167

2168+
/*
2169+
* Improve avg performance by not caclulating sum(X*X).
2170+
*/
2171+
staticArrayType*
2172+
do_numeric_avg_accum(ArrayType*transarray,Numericnewval)
2173+
{
2174+
Datum*transdatums;
2175+
intndatums;
2176+
DatumN,
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+
returnresult;
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+
Numericnewval=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+
Datumnewval8=PG_GETARG_DATUM(1);
2276+
Numericnewval;
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,"expected3-element numeric array");
2297+
if (ndatums!=2)
2298+
elog(ERROR,"expected2-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-
#defineCATALOG_VERSION_NO200702161
56+
#defineCATALOG_VERSION_NO200702162
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 (2100int8_accumnumeric_avg01231"{0,0,0}" ));
83+
DATA(insert (2100int8_avg_accumnumeric_avg01231"{0,0}" ));
8484
DATA(insert (2101int4_avg_accumint8_avg01016"{0,0}" ));
8585
DATA(insert (2102int2_avg_accumint8_avg01016"{0,0}" ));
86-
DATA(insert (2103numeric_accumnumeric_avg01231"{0,0,0}" ));
86+
DATA(insert (2103numeric_avg_accumnumeric_avg01231"{0,0}" ));
8787
DATA(insert (2104float4_accumfloat8_avg01022"{0,0,0}" ));
8888
DATA(insert (2105float8_accumfloat8_avg01022"{0,0,0}" ));
8989
DATA(insert (2106interval_accuminterval_avg01187"{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_sampPGNSP PGUID 12 1 0 f f t f i 1 701
27442744
DESCR("STDDEV_SAMP aggregate final function");
27452745
DATA(insertOID=1833 (numeric_accumPGNSPPGUID1210fftfi21231"1231 1700"_null__null__null_numeric_accum-_null_ ));
27462746
DESCR("aggregate transition function");
2747+
DATA(insertOID=2858 (numeric_avg_accumPGNSPPGUID1210fftfi21231"1231 1700"_null__null__null_numeric_avg_accum-_null_ ));
2748+
DESCR("aggregate transition function");
27472749
DATA(insertOID=1834 (int2_accumPGNSPPGUID1210fftfi21231"1231 21"_null__null__null_int2_accum-_null_ ));
27482750
DESCR("aggregate transition function");
27492751
DATA(insertOID=1835 (int4_accumPGNSPPGUID1210fftfi21231"1231 23"_null__null__null_int4_accum-_null_ ));
27502752
DESCR("aggregate transition function");
27512753
DATA(insertOID=1836 (int8_accumPGNSPPGUID1210fftfi21231"1231 20"_null__null__null_int8_accum-_null_ ));
27522754
DESCR("aggregate transition function");
2755+
DATA(insertOID=2746 (int8_avg_accumPGNSPPGUID1210fftfi21231"1231 20"_null__null__null_int8_avg_accum-_null_ ));
2756+
DESCR("aggregate transition function");
27532757
DATA(insertOID=1837 (numeric_avgPGNSPPGUID1210fftfi11700"1231"_null__null__null_numeric_avg-_null_ ));
27542758
DESCR("AVG aggregate final function");
27552759
DATA(insertOID=2514 (numeric_var_popPGNSPPGUID1210fftfi11700"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
externDatumtext_numeric(PG_FUNCTION_ARGS);
842842
externDatumnumeric_text(PG_FUNCTION_ARGS);
843843
externDatumnumeric_accum(PG_FUNCTION_ARGS);
844+
externDatumnumeric_avg_accum(PG_FUNCTION_ARGS);
844845
externDatumint2_accum(PG_FUNCTION_ARGS);
845846
externDatumint4_accum(PG_FUNCTION_ARGS);
846847
externDatumint8_accum(PG_FUNCTION_ARGS);
848+
externDatumint8_avg_accum(PG_FUNCTION_ARGS);
847849
externDatumnumeric_avg(PG_FUNCTION_ARGS);
848850
externDatumnumeric_var_pop(PG_FUNCTION_ARGS);
849851
externDatumnumeric_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
CREATEAGGREGATEnewavg (
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)
[8]ページ先頭
©2009-2025 Movatter.jp