Re: Speeding up aggregates - Mailing list pgsql-performance

From Hannu Krosing
Subject Re: Speeding up aggregates
Date
Msg-id 1039428960.7415.3.camel@huli
Whole thread Raw
In response to Re: Speeding up aggregates  (Joe Conway <mail@joeconway.com>)
Responses Re: Speeding up aggregates
List pgsql-performance
On Sun, 2002-12-08 at 19:31, Joe Conway wrote:

> parts=# explain analyze select i.part_id, sum(w.qty_oh) as total_oh from inv
> i, iwhs w where i.part_id = w.part_id group by i.part_id having sum(w.qty_oh) > 0;
>                                                              QUERY PLAN
...
>   Total runtime: 3282.27 msec
> (10 rows)
>
>
> Note that similar to Josh, I saw a nice improvement when using the
> HashAggregate on the simpler case, but as soon as I added a HAVING clause the
> optimizer switched back to GroupAggregate.
>
> I'll try to play around with this a bit more later today.

Try turning the having into subquery + where:

explain analyze
select * from (
    select i.part_id, sum(w.qty_oh) as total_oh
      from inv i, iwhs w
     where i.part_id = w.part_id
     group by i.part_id) sub
where total_oh > 0;

--
Hannu Krosing <hannu@tm.ee>

pgsql-performance by date:

Previous
From: "Kalle Barck-Holst"
Date:
Subject: is insertion and movement times are correlated to the size of the database?
Next
From: Hubert depesz Lubaczewski
Date:
Subject: questions about disk configurations