Re: Performance gain from reduction of GROUP BY memory

On Mon, 2005-08-29 at 20:25 -0400, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > I notice that Neil's patch regarding reducing the number of memory
> > allocations during aggregation operations isn't mentioned. It was
> > originally discussed in 8.0beta (2-3?) time.
>
> > What happened there?
> > - patch not committed in the end
> > - committed but not mentioned, as a dropped item
> > - committed but not mentioned, since part of a larger patch
>
> Are you speaking of these patches?

Yes, those look like the ones I mentioned.

Those seem to have a useful performance improvement?

At very least, the change in Aggregate function API should be mentioned,
no?

> 2005-04-06 19:56 neilc
>
> * src/backend/utils/adt/: float.c, numeric.c: Apply the "nodeAgg"
> optimization to more of the builtin transition functions. This
> patch optimizes int2_sum(), int4_sum(), float4_accum() and
> float8_accum() to avoid needing to copy the transition function's
> state for each input tuple of the aggregate. In an extreme case
> (e.g. SELECT sum(int2_col) FROM table where table has a single
> column), it improves performance by about 20%. For more complex
> queries or tables with wider rows, the relative performance
> improvement will not be as significant.
>
> 2005-04-04 19:50 neilc
>
> * src/backend/utils/adt/numeric.c: 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.
>
> 2005-03-12 15:25 tgl
>
> * contrib/intagg/int_aggregate.c,
> contrib/intagg/int_aggregate.sql.in, doc/src/sgml/xaggr.sgml,
> doc/src/sgml/xfunc.sgml, src/backend/executor/nodeAgg.c,
> src/backend/utils/adt/int8.c: Adjust the API for aggregate function
> calls so that a C-coded function can tell whether it is being used
> as an aggregate or not. This allows such a function to avoid
> re-pallocing a pass-by-reference transition value; normally it
> would be unsafe for a function to scribble on an input, but in the
> aggregate case it's safe to reuse the old transition value. Make
> int8inc() do this. This gets a useful improvement in the speed of
> COUNT(*), at least on narrow tables (it seems to be swamped by I/O
> when the table rows are wide). Per a discussion in early December
> with Neil Conway. I also fixed int_aggregate.c to check this,
> thereby turning it into something approaching a supportable
> technique instead of being a crude hack.

I'll search CVS directly next time. Thanks.

Best Regards, Simon Riggs