> The idea I discussed in the link in item 5 above gets around this > problem, but it's a perhaps more surprise filled implementation > as it will mean "select avg(x),sum(x),count(x) from t" is > actually faster than "select sum(x),count(x) from t" as the agg > state for avg() will satisfy sum and count too.
I'm skeptical that it will be noticeably faster. It's easy to see why this optimization will make a query *with all three* faster, but I would not expect the process of accumulating the sum and count to be about the same speed whether performed by one transition function or two. Of course I could be persuaded by a benchmark showing otherwise.
Thanks for looking at this.
Assuming that if we reuse the avg(x) state for count(x) and sum(x) then it will perform almost exactly like a query containing just avg(x), the only additional overhead is the call to the final functions per group, so in the following case that's likely immeasurable:
/* setup */ create table millionrowtable as select generate_series(1,1000000)::numeric as x;
/* test 1 */ SELECT sum(x) / count(x) from millionrowtable; /* test 2 */ SELECT avg(x) from millionrowtable;
Test 1:
274.979 ms
272.104 ms
269.915 ms
Test 2:
229.619 ms
220.703 ms
234.743 ms
(About 19% slower)
The good news is that it's not slower than before, so should be acceptable, though hard to explain to people.
