Re: Speeding up aggregates

Поиск
Список
Период
Сортировка
От Hannu Krosing
Тема Re: Speeding up aggregates
Дата
Msg-id 1039428960.7415.3.camel@huli
обсуждение исходный текст
Ответ на Re: Speeding up aggregates  (Joe Conway <mail@joeconway.com>)
Ответы Re: Speeding up aggregates  (Joe Conway <mail@joeconway.com>)
Список 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 по дате отправления:

Предыдущее
От: "Kalle Barck-Holst"
Дата:
Сообщение: is insertion and movement times are correlated to the size of the database?
Следующее
От: Hubert depesz Lubaczewski
Дата:
Сообщение: questions about disk configurations