On Thu, Oct 22, 2009 at 6:22 AM, Kenneth Marshall <ktm@xxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
