On 11 June 2015 at 01:39, Kevin Grittner <kgri...@ymail.com> wrote: > David Rowley <david.row...@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 think it's worth looking into that at some stage. I think I might have some of the code that would be required for the NULL checking over here -> http://www.postgresql.org/message-id/CAApHDvqRB-iFBy68=dcgqs46arep7aun2pou4ktwl8kx9yo...@mail.gmail.com 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. Regards David Rowley -- David Rowley http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Training & Services
