Re: Improving avg performance for numeric

2013/3/16 Hadi Moshayedi <hadi@moshayedi.net>:

> Revisiting:
> http://www.postgresql.org/message-id/45661BE7.4050205@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
>