Re: [PERFORM] optimizing query with multiple aggregates

2009-10-29 Thread Doug Cole
On Thu, Oct 22, 2009 at 6:22 AM, Kenneth Marshall 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 170m

Re: [PERFORM] optimizing query with multiple aggregates

2009-10-26 Thread Marc Mamin
..@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Nikolas Everett Sent: Thursday, October 22, 2009 4:48 AM To: Doug Cole Cc: pgsql-performance Subject: Re: [PERFORM] optimizing query with multiple aggregates So you've got a query like: SELECT SUM(CASE WHEN fie

Re: [PERFORM] optimizing query with multiple aggregates

2009-10-22 Thread Scott Carey
On 10/21/09 3:51 PM, "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 t

Re: [PERFORM] optimizing query with multiple aggregates

2009-10-22 Thread Robert Haas
On Wed, Oct 21, 2009 at 10:21 PM, Doug Cole wrote: > On Wed, Oct 21, 2009 at 5:39 PM, Merlin Moncure wrote: >> >> On Wed, Oct 21, 2009 at 6:51 PM, 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

Re: [PERFORM] optimizing query with multiple aggregates

2009-10-22 Thread Kenneth Marshall
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 > aggrega

Re: [PERFORM] optimizing query with multiple aggregates

2009-10-21 Thread Nikolas Everett
So you've got a query like: SELECT SUM(CASE WHEN field >= 0 AND field < 10 THEN 1 ELSE 0 END) as zeroToTen, SUM(CASE WHEN field >= 10 AND field < 20 THEN 1 ELSE 0 END) as tenToTwenty, SUM(CASE WHEN field >= 20 AND field < 30 THEN 1 ELSE 0 END) as tenToTwenty, ... FROM b

Re: [PERFORM] optimizing query with multiple aggregates

2009-10-21 Thread David Wilson
On Wed, Oct 21, 2009 at 6:51 PM, Doug Cole wrote: > > 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 a

Re: [PERFORM] optimizing query with multiple aggregates

2009-10-21 Thread Doug Cole
On Wed, Oct 21, 2009 at 5:39 PM, Merlin Moncure wrote: > > On Wed, Oct 21, 2009 at 6:51 PM, 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 whe

Re: [PERFORM] optimizing query with multiple aggregates

2009-10-21 Thread Merlin Moncure
On Wed, Oct 21, 2009 at 6:51 PM, 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 funct

[PERFORM] optimizing query with multiple aggregates

2009-10-21 Thread Doug Cole
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 o