From: | Hadi Moshayedi <hadi(at)moshayedi(dot)net> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Cc: | Mark Kirkwood <markir(at)paradise(dot)net(dot)nz> |
Subject: | Improving avg performance for numeric |
Date: | 2013-03-16 04:15:11 |
Message-ID: | CAK=1=WrmCkWc_xQXs_bpUyswCPr7O9zkLmm8Oa7_nT2vybvBEQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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?
Thanks,
-- Hadi
Attachment | Content-Type | Size |
---|---|---|
numeric-avg-optimize.patch | application/octet-stream | 9.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2013-03-16 04:56:10 | Re: Improving avg performance for numeric |
Previous Message | Alvaro Herrera | 2013-03-16 01:16:53 | Re: Materialized view assertion failure in HEAD |