Skip site navigation (1) Skip section navigation (2)

optimizing query with multiple aggregates

From: Doug Cole <dougcole(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: optimizing query with multiple aggregates
Date: 2009-10-21 22:51:25
Message-ID: 7b8d80330910211551u5f003583y89e9c80220451acd@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Merlin MoncureDate: 2009-10-22 00:39:48
Subject: Re: optimizing query with multiple aggregates
Previous:From: Scott MarloweDate: 2009-10-21 22:06:10
Subject: Re: There is a statistic table?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group