Date: Sun, 28 Aug 2005 12:48:59 +1200
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Limit + group + join
In-reply-to: <10922.1125181071@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Tobias Brox <tobias@nordicbet.com>,
	pgsql-performance@postgresql.org
Message-id: <431109FB.6000505@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050726)
References: <20050826002709.GK10328@tobias.lan>
	<430E85ED.60109@paradise.net.nz> <9501.1125071193@sss.pgh.pa.us>
	<430FD61A.3020902@paradise.net.nz> <10922.1125181071@sss.pgh.pa.us>

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