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@tm.ee>