Re: Limit + group + join

От: Mark Kirkwood
Тема: Re: Limit + group + join
Дата: ,
Msg-id: 431109FB.6000505@paradise.net.nz
(см: обсуждение, исходный текст)
Ответ на: Re: Limit + group + join  (Tom Lane)
Ответы: Re: Limit + group + join  (Tobias Brox)
Список: pgsql-performance

Скрыть дерево обсуждения

Limit + group + join  (Tobias Brox, )
 Re: Limit + group + join  ("Jeffrey W. Baker", )
  Re: Limit + group + join  (Tobias Brox, )
  Re: Limit + group + join  ("Jeffrey W. Baker", )
 Re: Limit + group + join  (Mark Kirkwood, )
  Re: Limit + group + join  (Tobias Brox, )
  Re: Limit + group + join  (Stephan Szabo, )
  Re: Limit + group + join  (Tom Lane, )
   Re: Limit + group + join  (Mark Kirkwood, )
    Re: Limit + group + join  (Tom Lane, )
   Re: Limit + group + join  (Mark Kirkwood, )
    Re: Limit + group + join  (Tom Lane, )
     Re: Limit + group + join  (Mark Kirkwood, )
      Re: Limit + group + join  (Tobias Brox, )
   Re: Limit + group + join  (Greg Stark, )
 Re: Limit + group + join  ("Merlin Moncure", )
 Re: Limit + group + join  ("Merlin Moncure", )

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 по дате сообщения:

От: Tobias Brox
Дата:
Сообщение: Re: Limit + group + join
От: Arjen van der Meijden
Дата:
Сообщение: Re: Inefficient queryplan for query with intersectable