Re: Performance gain from reduction of GROUP BY memory allocations

Simon Riggs <simon@2ndquadrant.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?

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.Thispatch optimizes int2_sum(), int4_sum(), float4_accum() andfloat8_accum() to avoid needing to copy the
transitionfunction'sstate for each input tuple of the aggregate. In an extreme case(e.g. SELECT sum(int2_col) FROM
tablewhere table has a singlecolumn), it improves performance by about 20%. For more complexqueries or tables with
widerrows, the relative performanceimprovement will not be as significant.
 

2005-04-04 19:50  neilc
* src/backend/utils/adt/numeric.c: This patch changesint2_avg_accum() and int4_avg_accum() use the nodeAgg
performancehackTom introduced recently. This means we can avoid copying thetransition array for each input tuple if
thesefunctions areinvoked as aggregate transition functions.To test the performance improvement, I created a 1 million
rowtablewith a single int4 column. Without the patch, SELECT avg(col)FROM table took about 4.2 seconds (after the data
wascached); withthe patch, it took about 3.2 seconds. Naturally, the performanceimprovement for a less trivial query
(ora 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 functioncalls so that a
C-codedfunction can tell whether it is being usedas an aggregate or not.  This allows such a function to
avoidre-pallocinga pass-by-reference transition value; normally itwould be unsafe for a function to scribble on an
input,but in theaggregate case it's safe to reuse the old transition value.  Makeint8inc() do this.  This gets a useful
improvementin the speed ofCOUNT(*), at least on narrow tables (it seems to be swamped by I/Owhen the table rows are
wide).   Per a discussion in early Decemberwith Neil Conway.  I also fixed int_aggregate.c to check this,thereby
turningit into something approaching a supportabletechnique instead of being a crude hack.
 

I don't recall how Neil's original patch differed from what got
applied...
        regards, tom lane