Skip site navigation (1) Skip section navigation (2)

Re: optimizing query with multiple aggregates

From: Doug Cole <dougcole(at)gmail(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: optimizing query with multiple aggregates
Date: 2009-10-22 02:21:36
Message-ID: 7b8d80330910211921u4e5db602k1e05ed1ba44b2ac2@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Oct 21, 2009 at 5:39 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
> On Wed, Oct 21, 2009 at 6:51 PM, Doug Cole <dougcole(at)gmail(dot)com> wrote:
> > I have a reporting query that is taking nearly all of it's time in aggregate
> > functions and I'm trying to figure out how to optimize it.  The query takes
> > approximately 170ms when run with "select *", but when run with all the
> > aggregate functions the query takes 18 seconds.  The slowness comes from our
> > attempt to find distribution data using selects of the form:
> >
> > SUM(CASE WHEN field >= x AND field < y THEN 1 ELSE 0 END)
> >
> > repeated across many different x,y values and fields to build out several
> > histograms of the data.  The main culprit appears to be the CASE statement,
> > but I'm not sure what to use instead.  I'm sure other people have had
> > similar queries and I was wondering what methods they used to build out data
> > like this?
>
> have you tried:
>
> count(*) where field >= x AND field < y;
>
> ??
>
> merlin

Unless I'm misunderstanding you, that would require breaking each bin
into a separate sql statement and since I'm trying to calculate more
than 100 bins between the different fields any improvement in the
aggregate functions would be overwhelmed by the cost of the actual
query, which is about 170ms.
Thanks,
Doug

In response to

Responses

pgsql-performance by date

Next:From: David WilsonDate: 2009-10-22 02:47:31
Subject: Re: optimizing query with multiple aggregates
Previous:From: Merlin MoncureDate: 2009-10-22 00:39:48
Subject: Re: optimizing query with multiple aggregates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group