Re: optimizing query with multiple aggregates

On Wed, Oct 21, 2009 at 10:21 PM, Doug Cole <dougcole(at)gmail(dot)com> wrote:
> 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.

Well, you might be able to use subselects to fetch all the results in
a single query, but it might still be slow.

...Robert