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.1433860...@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
sharing_agg_states_2c3d4a9_2015-06-15.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
