Re: Limit + group + join

Поиск
Список
Период
Сортировка
От Mark Kirkwood
Тема Re: Limit + group + join
Дата
Msg-id 431109FB.6000505@paradise.net.nz
обсуждение исходный текст
Ответ на Re: Limit + group + join  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: Limit + group + join  (Tobias Brox <tobias@nordicbet.com>)
Список pgsql-performance
Tom Lane wrote:
>
> I looked into this and found that indeed the desirable join plan was
> getting generated, but it wasn't picked because query_planner didn't
> have an accurate idea of how much of the join needed to be scanned to
> satisfy the GROUP BY step.  I've committed some changes that hopefully
> will let 8.1 be smarter about GROUP BY ... LIMIT queries.
>

Very nice :-)

joinlimit=# EXPLAIN SELECT c.id FROM c JOIN b ON c_id=c.id  GROUP BY
c.id ORDER BY c.id DESC LIMIT 5;
                                             QUERY PLAN

--------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..15.23 rows=5 width=4)
    ->  Group  (cost=0.00..243730.00 rows=80000 width=4)
          ->  Nested Loop  (cost=0.00..243530.00 rows=80000 width=4)
                ->  Index Scan Backward using c_pkey on c
(cost=0.00..1450.00 rows=80000 width=4)
                ->  Index Scan using b_on_c on b  (cost=0.00..3.01
rows=1 width=4)
                      Index Cond: (b.c_id = "outer".id)
(6 rows)

This is 8.1devel from today.

regards

Mark

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Limit + group + join
Следующее
От: Tobias Brox
Дата:
Сообщение: Re: Limit + group + join