Sharing aggregate states between different aggregate functions

Simon and I have been going over some ideas about how to make improvements
to aggregate performance by cutting down on the duplicate work that's done
when 2 aggregate functions are used where one knows how to satisfy all the
requirements of the other.

To cut a long story short, all our ideas will require some additions or
modifications to CREATE AGGREGATE and also pg_dump support.

Tom came up with a more simple idea, that gets us some of the way, without
all that pg_dump stuff.
http://www.postgresql.org/message-id/30851.1433860000@sss.pgh.pa.us

This basically allows an aggregate's state to be shared between other
aggregate functions when both aggregate's transition functions (and a few
other things) match
There's quite a number of aggregates in our standard set which will benefit
from this optimisation.

Please find attached a patch which implements this idea.

The performance improvements are as follows:

create table t1 as
select x.x::numeric from generate_series(1,1000000) x(x);

-- standard case.
select sum(x),avg(x) from t1;

Master:
Time: 350.303 ms
Time: 353.716 ms
Time: 349.703 ms

Patched:
Time: 227.687 ms
Time: 222.563 ms
Time: 224.691 ms

-- extreme case.
select
stddev_samp(x),stddev(x),variance(x),var_samp(x),var_pop(x),stddev_pop(x)
from t1;

Master:
Time: 1464.461 ms
Time: 1462.343 ms
Time: 1450.232 ms

Patched:
Time: 346.473 ms
Time: 348.445 ms
Time: 351.365 ms

Regards

David Rowley

--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Training & Services