optimizing query with multiple aggregates

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.
Thanks,
Doug

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 bigtable


My guess is this forcing a whole bunch of if checks and your getting cpu
bound. Could you try something like:

SELECT SUM(CASE WHEN field >= 0 AND field < 10 THEN count ELSE 0 END) as
zeroToTen,
SUM(CASE WHEN field >= 10 AND field < 20 THEN count ELSE 0
END) as tenToTwenty,
SUM(CASE WHEN field >= 20 AND field < 30 THEN count ELSE 0
END) as tenToTwenty,
...
FROM (SELECT field, count(*) FROM bigtable GROUP BY field)

which will allow a hash aggregate? You'd do a hash aggregate on the whole
table which should be quick and then you'd summarize your bins.

This all supposes that you don't want to just query postgres's column
statistics.

Hello,

I didn't try it, but following should be slightly faster:

COUNT( CASE WHEN field >= x AND field < y THEN true END)
intead of
SUM( CASE WHEN field >= x AND field < y THEN 1 ELSE 0 END)

HTH,

Marc Mamin


________________________________

From: pgsql-performance-owner@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 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 bigtable




My guess is this forcing a whole bunch of if checks and your getting cpu
bound. Could you try something like:


SELECT SUM(CASE WHEN field >= 0 AND field < 10 THEN count ELSE 0 END) as
zeroToTen,
SUM(CASE WHEN field >= 10 AND field < 20 THEN count ELSE
END) as tenToTwenty,
SUM(CASE WHEN field >= 20 AND field < 30 THEN count ELSE
END) as tenToTwenty,
...
FROM (SELECT field, count(*) FROM bigtable GROUP BY field)


which will allow a hash aggregate? You'd do a hash aggregate on the
whole table which should be quick and then you'd summarize your bins.


This all supposes that you don't want to just query postgres's column
statistics.


On Wed, Oct 21, 2009 at 10:21 PM, Doug Cole wrote:


On Wed, Oct 21, 2009 at 5:39 PM, Merlin Moncure
wrote:
>
wrote:
time in aggregate
The query takes
with all the
slowness comes from our
> >
> >
build out several
CASE statement,
have had
to build out data
>

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.
Thanks,
Doug

Well, you might be able to use subselects to fetch all the results in
a single query, but it might still be slow.

...Robert

Use group by with an appropriate division/rounding to create the appropriate
buckets, if they're all the same size.

select round(field/100) as bucket, count(*) as cnt from foo group by
round(field/100);

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

You might be able to do this with plain aggregates. Define a function that
generates your partitions that you can group by, then aggregate functions
for the outputs

In either case, rather than each result being a column in one result row,
each result will be its own row.

Each row would have a column that defines the type of the result (that you
grouped on), and one with the result value. If each is just a sum, its
easy. If there are lots of different calculation types, it would be harder.
Potentially, you could wrap that in a subselect to pull out each into its
own column but that is a bit messy.

Also, in 8.4 window functions could be helpful. PARTITION BY something that
represents your buckets perhaps?
http://developer.postgresql.org/pgdocs/postgres/tutorial-window.html

This will generally force a sort, but shouldn't be that bad.

The function used for the group by or partition by could just be a big case
statement to generate a unique int per bucket, or a truncate/rounding
function. It just needs to spit out a unique result for each bucket for the
group or partition.