Neil Conway wrote:
Yeah, there might be some others we could improve. float4_accum() and float8_accum() look like they could be improved pretty easily, and do_numeric_accum() should also be fixable with some hackery. I suppose it's also worth optimizing int2_sum(), int4_sum() and int8_sum().

Attached is a patch that applies the same optimization to int2_sum(), int4_sum(), float4_accum(), and float8_accum(). It wasn't possible to optimize do_numeric_accum() or int8_sum() since they both use numerics. Performance gains are similar to those measured when this optimization has been applied to similar aggregates (e.g. avg(float8) goes from about 6400 ms to about 4300 ms on my machine, for a single column of float8 and about 1 million rows).


Barring any objections, I'll apply this to HEAD tomorrow.

-Neil
Index: src/backend/utils/adt/float.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/utils/adt/float.c,v
retrieving revision 1.113
diff -c -r1.113 float.c
*** src/backend/utils/adt/float.c	11 Feb 2005 04:08:58 -0000	1.113
--- src/backend/utils/adt/float.c	6 Apr 2005 07:24:38 -0000
***************
*** 1902,1909 ****
  	float8		N,
  				sumX,
  				sumX2;
- 	Datum		transdatums[3];
- 	ArrayType  *result;
  
  	transvalues = check_float8_array(transarray, "float8_accum");
  	N = transvalues[0];
--- 1902,1907 ----
***************
*** 1914,1928 ****
  	sumX += newval;
  	sumX2 += newval * newval;
  
! 	transdatums[0] = Float8GetDatumFast(N);
! 	transdatums[1] = Float8GetDatumFast(sumX);
! 	transdatums[2] = Float8GetDatumFast(sumX2);
! 
! 	result = construct_array(transdatums, 3,
! 							 FLOAT8OID,
! 						 sizeof(float8), false /* float8 byval */ , 'd');
  
! 	PG_RETURN_ARRAYTYPE_P(result);
  }
  
  Datum
--- 1912,1946 ----
  	sumX += newval;
  	sumX2 += newval * newval;
  
! 	/*
! 	 * If we're invoked by nodeAgg, we can cheat and modify our first
! 	 * parameter in-place to reduce palloc overhead. Otherwise we
! 	 * construct a new array with the updated transition data and
! 	 * return it.
! 	 */
! 	if (fcinfo->context && IsA(fcinfo->context, AggState))
! 	{
! 		transvalues[0] = N;
! 		transvalues[1] = sumX;
! 		transvalues[2] = sumX2;
! 
! 		PG_RETURN_ARRAYTYPE_P(transarray);
! 	}
! 	else
! 	{
! 		Datum		transdatums[3];
! 		ArrayType  *result;
! 
! 		transdatums[0] = Float8GetDatumFast(N);
! 		transdatums[1] = Float8GetDatumFast(sumX);
! 		transdatums[2] = Float8GetDatumFast(sumX2);
! 
! 		result = construct_array(transdatums, 3,
! 								 FLOAT8OID,
! 								 sizeof(float8), false /* float8 byval */ , 'd');
  
! 		PG_RETURN_ARRAYTYPE_P(result);
! 	}
  }
  
  Datum
***************
*** 1935,1942 ****
  				sumX,
  				sumX2,
  				newval;
- 	Datum		transdatums[3];
- 	ArrayType  *result;
  
  	transvalues = check_float8_array(transarray, "float4_accum");
  	N = transvalues[0];
--- 1953,1958 ----
***************
*** 1950,1964 ****
  	sumX += newval;
  	sumX2 += newval * newval;
  
! 	transdatums[0] = Float8GetDatumFast(N);
! 	transdatums[1] = Float8GetDatumFast(sumX);
! 	transdatums[2] = Float8GetDatumFast(sumX2);
! 
! 	result = construct_array(transdatums, 3,
! 							 FLOAT8OID,
! 						 sizeof(float8), false /* float8 byval */ , 'd');
  
! 	PG_RETURN_ARRAYTYPE_P(result);
  }
  
  Datum
--- 1966,2000 ----
  	sumX += newval;
  	sumX2 += newval * newval;
  
! 	/*
! 	 * If we're invoked by nodeAgg, we can cheat and modify our first
! 	 * parameter in-place to reduce palloc overhead. Otherwise we
! 	 * construct a new array with the updated transition data and
! 	 * return it.
! 	 */
! 	if (fcinfo->context && IsA(fcinfo->context, AggState))
! 	{
! 		transvalues[0] = N;
! 		transvalues[1] = sumX;
! 		transvalues[2] = sumX2;
! 
! 		PG_RETURN_ARRAYTYPE_P(transarray);
! 	}
! 	else
! 	{
! 		Datum		transdatums[3];
! 		ArrayType  *result;
! 
! 		transdatums[0] = Float8GetDatumFast(N);
! 		transdatums[1] = Float8GetDatumFast(sumX);
! 		transdatums[2] = Float8GetDatumFast(sumX2);
! 
! 		result = construct_array(transdatums, 3,
! 								 FLOAT8OID,
! 								 sizeof(float8), false /* float8 byval */ , 'd');
  
! 		PG_RETURN_ARRAYTYPE_P(result);
! 	}
  }
  
  Datum
Index: src/backend/utils/adt/numeric.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/utils/adt/numeric.c,v
retrieving revision 1.82
diff -c -r1.82 numeric.c
*** src/backend/utils/adt/numeric.c	4 Apr 2005 23:50:27 -0000	1.82
--- src/backend/utils/adt/numeric.c	6 Apr 2005 08:07:37 -0000
***************
*** 2357,2363 ****
  Datum
  int2_sum(PG_FUNCTION_ARGS)
  {
- 	int64		oldsum;
  	int64		newval;
  
  	if (PG_ARGISNULL(0))
--- 2357,2362 ----
***************
*** 2370,2391 ****
  		PG_RETURN_INT64(newval);
  	}
  
! 	oldsum = PG_GETARG_INT64(0);
  
! 	/* Leave sum unchanged if new input is null. */
! 	if (PG_ARGISNULL(1))
! 		PG_RETURN_INT64(oldsum);
  
! 	/* OK to do the addition. */
! 	newval = oldsum + (int64) PG_GETARG_INT16(1);
  
! 	PG_RETURN_INT64(newval);
  }
  
  Datum
  int4_sum(PG_FUNCTION_ARGS)
  {
- 	int64		oldsum;
  	int64		newval;
  
  	if (PG_ARGISNULL(0))
--- 2369,2407 ----
  		PG_RETURN_INT64(newval);
  	}
  
! 	/*
! 	 * If we're invoked by nodeAgg, we can cheat and modify out first
! 	 * parameter in-place to avoid palloc overhead. If not, we need to
! 	 * return the new value of the transition variable.
! 	 */
! 	if (fcinfo->context && IsA(fcinfo->context, AggState))
! 	{
! 		int64 *oldsum = (int64 *) PG_GETARG_POINTER(0);
  
! 		/* Leave the running sum unchanged in the new input is null */
! 		if (!PG_ARGISNULL(1))
! 			*oldsum = *oldsum + (int64) PG_GETARG_INT16(1);
  
! 		PG_RETURN_POINTER(oldsum);
! 	}
! 	else
! 	{
! 		int64		oldsum = PG_GETARG_INT64(0);
! 
! 		/* Leave sum unchanged if new input is null. */
! 		if (PG_ARGISNULL(1))
! 			PG_RETURN_INT64(oldsum);
! 
! 		/* OK to do the addition. */
! 		newval = oldsum + (int64) PG_GETARG_INT16(1);
  
! 		PG_RETURN_INT64(newval);
! 	}
  }
  
  Datum
  int4_sum(PG_FUNCTION_ARGS)
  {
  	int64		newval;
  
  	if (PG_ARGISNULL(0))
***************
*** 2398,2413 ****
  		PG_RETURN_INT64(newval);
  	}
  
! 	oldsum = PG_GETARG_INT64(0);
  
! 	/* Leave sum unchanged if new input is null. */
! 	if (PG_ARGISNULL(1))
! 		PG_RETURN_INT64(oldsum);
  
! 	/* OK to do the addition. */
! 	newval = oldsum + (int64) PG_GETARG_INT32(1);
  
! 	PG_RETURN_INT64(newval);
  }
  
  Datum
--- 2414,2447 ----
  		PG_RETURN_INT64(newval);
  	}
  
! 	/*
! 	 * If we're invoked by nodeAgg, we can cheat and modify out first
! 	 * parameter in-place to avoid palloc overhead. If not, we need to
! 	 * return the new value of the transition variable.
! 	 */
! 	if (fcinfo->context && IsA(fcinfo->context, AggState))
! 	{
! 		int64 *oldsum = (int64 *) PG_GETARG_POINTER(0);
  
! 		/* Leave the running sum unchanged in the new input is null */
! 		if (!PG_ARGISNULL(1))
! 			*oldsum = *oldsum + (int64) PG_GETARG_INT32(1);
  
! 		PG_RETURN_POINTER(oldsum);
! 	}
! 	else
! 	{
! 		int64		oldsum = PG_GETARG_INT64(0);
  
! 		/* Leave sum unchanged if new input is null. */
! 		if (PG_ARGISNULL(1))
! 			PG_RETURN_INT64(oldsum);
! 
! 		/* OK to do the addition. */
! 		newval = oldsum + (int64) PG_GETARG_INT32(1);
! 
! 		PG_RETURN_INT64(newval);
! 	}
  }
  
  Datum
***************
*** 2426,2431 ****
--- 2460,2471 ----
  		PG_RETURN_DATUM(newval);
  	}
  
+ 	/*
+ 	 * Note that we cannot special-case the nodeAgg case here, as we
+ 	 * do for int2_sum and int4_sum: numeric is of variable size, so
+ 	 * we cannot modify our first parameter in-place.
+ 	 */
+ 
  	oldsum = PG_GETARG_NUMERIC(0);
  
  	/* Leave sum unchanged if new input is null. */
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

Reply via email to