Re: optimizing query with multiple aggregates

On Thu, Oct 22, 2009 at 6:22 AM, Kenneth Marshall <ktm(at)rice(dot)edu> wrote:
> 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
>

Thanks Ken,
I ended up going with this approach - it meant I had to break it
into a lot more queries, one for each histogram, but even with that
added overhead I cut the time down from 18 seconds to right around 1
second.
Doug