2013/3/16 Hadi Moshayedi <h...@moshayedi.net>: > Revisiting: > http://www.postgresql.org/message-id/45661be7.4050...@paradise.net.nz > > I think the reasons which the numeric average was slow were: > (1) Using Numeric for count, which is slower than int8 to increment, > (2) Constructing/deconstructing arrays at each transition step. > > This is also discussed at: > http://www.citusdata.com/blog/53-postgres-performance-to-avg-or-to-sum-divided-by-count > > So, I think we can improve the speed of numeric average by keeping the > transition state as an struct in the aggregate context, and just passing the > pointer to that struct from/to the aggregate transition function. > > The attached patch uses this method. > > I tested it using the data generated using: > CREATE TABLE avg_test AS SELECT (random() * 999)::decimal(5,2) as d FROM > generate_series(1, 10000000) s; > > After applying this patch, run time of "SELECT avg(d) FROM avg_test;" > improves from 10.701 seconds to 5.204 seconds, which seems to be a huge > improvement. > > I think we may also be able to use a similar method to improve performance > of some other numeric aggregates (like stddev). But I want to know your > feedback first. > > Is this worth working on?
I checked this patch and it has a interesting speedup - and a price of this methoud should not be limited to numeric type only Pavel > > Thanks, > -- Hadi > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
