David Rowley <david.rowley@2ndquadrant.com> wrote: > > /* 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)
Of course, with Tom's approach you would see the benefit; the two statements should run at about the same speed.
I am a little curious what sort of machine you're running on, because my i7 is much slower. I ran a few other tests with your table for perspective.
Assert enabled build?
My hardware is very unimpressive... an i5 from Q1 2010. Due to be replaced very soon.
One question that arose in my mind running this was whether might be able to combine sum(x) with count(*) if x was NOT NULL, even though the arguments don't match. It might not be worth the gymnastics of recognizing the special case, and I certainly wouldn't recommend looking at that optimization in a first pass; but it might be worth jotting down on a list somewhere....
I'm just not so sure what the logic would be to decide when we could apply this. The only properties I can see that may be along the right lines are pg_proc.pronargs for int8inc and inc8inc_any.
