On Wed, Oct 21, 2009 at 5:39 PM, Merlin Moncure <
mmoncure@gmail.com> wrote:
>
> On Wed, Oct 21, 2009 at 6:51 PM, Doug Cole <
dougcole@gmail.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