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