Re: Speeding up aggregates

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(at)tm(dot)ee>