Re: Limit + group + join

From: Tom Lane
Subject: Re: Limit + group + join
Date: ,
Msg-id: 10922.1125181071@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Limit + group + join  (Mark Kirkwood)
Responses: Re: Limit + group + join  (Mark Kirkwood)
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", )

Mark Kirkwood <> writes:
> 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;
> [ fails to pick an available index-scan-backward plan ]

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.

            regards, tom lane


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