avg(int2) and avg(int8) micro-opt - Mailing list pgsql-patches

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;