This patch changes int2_avg_accum() and int4_avg_accum() use the nodeAgg performance hack Tom introduced recently. This means we can avoid copying the transition array for each input tuple if these functions are invoked as aggregate transition functions.

To test the performance improvement, I created a 1 million row table with a single int4 column. Without the patch, SELECT avg(col) FROM table took about 4.2 seconds (after the data was cached); with the patch, it took about 3.2 seconds. Naturally, the performance improvement for a less trivial query (or a table with wider rows) would be relatively smaller.

It is possible that the transition array might be TOAST'ed (not that I'd expect that to occur in practice, of course). The aggregates should continue to work in this case: PG_DETOAST_DATUM() is equivalent to PG_DETOAST_DATUM_COPY() if the datum is toast'ed, so in effect we just won't implement the nodeAgg performance hack if the transition array is toasted. If I've mucked this up, let me know.

Barring any objections, I'll commit this tomorrow.

-Neil
Index: src/backend/utils/adt/numeric.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/utils/adt/numeric.c,v
retrieving revision 1.81
diff -c -r1.81 numeric.c
*** src/backend/utils/adt/numeric.c	1 Jan 2005 05:43:07 -0000	1.81
--- src/backend/utils/adt/numeric.c	4 Apr 2005 11:00:41 -0000
***************
*** 2462,2478 ****
  Datum
  int2_avg_accum(PG_FUNCTION_ARGS)
  {
! 	ArrayType  *transarray = PG_GETARG_ARRAYTYPE_P_COPY(0);
  	int16		newval = PG_GETARG_INT16(1);
  	Int8TransTypeData *transdata;
  
  	/*
! 	 * We copied the input array, so it's okay to scribble on it directly.
  	 */
  	if (ARR_SIZE(transarray) != ARR_OVERHEAD(1) + sizeof(Int8TransTypeData))
  		elog(ERROR, "expected 2-element int8 array");
- 	transdata = (Int8TransTypeData *) ARR_DATA_PTR(transarray);
  
  	transdata->count++;
  	transdata->sum += newval;
  
--- 2462,2485 ----
  Datum
  int2_avg_accum(PG_FUNCTION_ARGS)
  {
! 	ArrayType  *transarray;
  	int16		newval = PG_GETARG_INT16(1);
  	Int8TransTypeData *transdata;
  
  	/*
! 	 * If we're invoked by nodeAgg, we can cheat and modify our first
! 	 * parameter in-place to reduce palloc overhead. Otherwise we need
! 	 * to make a copy of it before scribbling on it.
  	 */
+ 	if (fcinfo->context && IsA(fcinfo->context, AggState))
+ 		transarray = PG_GETARG_ARRAYTYPE_P(0);
+ 	else
+ 		transarray = PG_GETARG_ARRAYTYPE_P_COPY(0);
+ 
  	if (ARR_SIZE(transarray) != ARR_OVERHEAD(1) + sizeof(Int8TransTypeData))
  		elog(ERROR, "expected 2-element int8 array");
  
+ 	transdata = (Int8TransTypeData *) ARR_DATA_PTR(transarray);
  	transdata->count++;
  	transdata->sum += newval;
  
***************
*** 2482,2498 ****
  Datum
  int4_avg_accum(PG_FUNCTION_ARGS)
  {
! 	ArrayType  *transarray = PG_GETARG_ARRAYTYPE_P_COPY(0);
  	int32		newval = PG_GETARG_INT32(1);
  	Int8TransTypeData *transdata;
  
  	/*
! 	 * We copied the input array, so it's okay to scribble on it directly.
  	 */
  	if (ARR_SIZE(transarray) != ARR_OVERHEAD(1) + sizeof(Int8TransTypeData))
  		elog(ERROR, "expected 2-element int8 array");
- 	transdata = (Int8TransTypeData *) ARR_DATA_PTR(transarray);
  
  	transdata->count++;
  	transdata->sum += newval;
  
--- 2489,2512 ----
  Datum
  int4_avg_accum(PG_FUNCTION_ARGS)
  {
! 	ArrayType  *transarray;
  	int32		newval = PG_GETARG_INT32(1);
  	Int8TransTypeData *transdata;
  
  	/*
! 	 * If we're invoked by nodeAgg, we can cheat and modify our first
! 	 * parameter in-place to reduce palloc overhead. Otherwise we need
! 	 * to make a copy of it before scribbling on it.
  	 */
+ 	if (fcinfo->context && IsA(fcinfo->context, AggState))
+ 		transarray = PG_GETARG_ARRAYTYPE_P(0);
+ 	else
+ 		transarray = PG_GETARG_ARRAYTYPE_P_COPY(0);
+ 
  	if (ARR_SIZE(transarray) != ARR_OVERHEAD(1) + sizeof(Int8TransTypeData))
  		elog(ERROR, "expected 2-element int8 array");
  
+ 	transdata = (Int8TransTypeData *) ARR_DATA_PTR(transarray);
  	transdata->count++;
  	transdata->sum += newval;
  
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to