Re: Limit + group + join

From: Mark Kirkwood
Subject: Re: Limit + group + join
Date: ,
Msg-id: 431109FB.6000505@paradise.net.nz
(view: Whole thread, Raw)
In response to: Re: Limit + group + join  (Tom Lane)
Responses: Re: Limit + group + join  (Tobias Brox)
List: pgsql-performance

Tree view

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 by date:

From: Tobias Brox
Date:
Subject: Re: Limit + group + join
From: Arjen van der Meijden
Date:
Subject: Re: Inefficient queryplan for query with intersectable