Re: optimizing query with multiple aggregates - Mailing list pgsql-performance

From Kenneth Marshall
Subject Re: optimizing query with multiple aggregates
Date
Msg-id 20091022132214.GC1199@it.is.rice.edu
Whole thread Raw
In response to optimizing query with multiple aggregates  (Doug Cole <dougcole@gmail.com>)
Responses Re: optimizing query with multiple aggregates  (Doug Cole <dougcole@gmail.com>)
List pgsql-performance
On Wed, Oct 21, 2009 at 03:51:25PM -0700, Doug Cole 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?
> Thanks for your help,
> Doug

Hi Doug,

Have you tried using the width_bucket() function? Here is a nice
article describing its use for making histograms:

http://quantmeditate.blogspot.com/2005/03/creating-histograms-using-sql-function.html

Regards,
Ken

pgsql-performance by date:

Previous
From: Cédric Villemain
Date:
Subject: Re: There is a statistic table?
Next
From: Robert Haas
Date:
Subject: Re: optimizing query with multiple aggregates