Thread: Slow HashAggregate : How to optimize ?

Hello !

I'm having a problem with a query that takes more or less 3.2 seconds to
be executed.

This query uses a view which encapsulates some calculations (in order to
avoid duplicating theses calculations at several places in the project).

In order to keep that post readable, I've put the view details on that
page : <http://pastebin.com/m1523940>

Here's the EXPLAIN ANALYZE of a query using that view :
<http://pastebin.com/m1b94cd9b>

As I read the query plan, the HashAggregate takes near than 3 seconds,
which represent 90% of the query duration time.

How can I see which part of the query causes the HashAggregate to be so
slow ?

How can I optimize that view to reduce the execution duration time ?

To be accurate, I'm working on PostgreSQL 8.3.5.

Many thanks in advance for any tips about that ! :-)

Best Regards,

--
Bruno Baguette - bruno.baguette@gmail.com

> I'm having a problem with a query that takes more or less 3.2 seconds to be
> executed.
>
> This query uses a view which encapsulates some calculations (in order to
> avoid duplicating theses calculations at several places in the project).
>
> In order to keep that post readable, I've put the view details on that page
> : <http://pastebin.com/m1523940>
>
> Here's the EXPLAIN ANALYZE of a query using that view :
> <http://pastebin.com/m1b94cd9b>
>
> As I read the query plan, the HashAggregate takes near than 3 seconds, which
> represent 90% of the query duration time.

Well, it IS aggregating almost 80,000 rows.  That doesn't sound that
bad to me.  What kind of hardware are you running this on?

...Robert