Re: The Future of Aggregation

On 11 June 2015 at 01:39, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> David Rowley <david(dot)rowley(at)2ndquadrant(dot)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=dCgqS46aRep7AuN2pou4KTwL8kX9YOcTQ@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