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.
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
