On Wed, Oct 21, 2009 at 6:51 PM, Doug Cole <dougcole@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
