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
..@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
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
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
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
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
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
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
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
