Date: Fri, 26 Aug 2005 07:18:30 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Mark Kirkwood <markir@paradise.net.nz>
Cc: Tobias Brox <tobias@nordicbet.com>,
	pgsql-performance@postgresql.org
Subject: Re: Limit + group + join
In-Reply-To: <430E85ED.60109@paradise.net.nz>
Message-ID: <20050826071621.P12124@megazone.bigpanda.com>
References: <20050826002709.GK10328@tobias.lan>
	<430E85ED.60109@paradise.net.nz>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Fri, 26 Aug 2005, Mark Kirkwood wrote:

> However being a bit brutal:
>
> set enable_mergejoin=false;
> set enable_hashjoin=false;
>
> 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.24 rows=5 width=4)
>     ->  Group  (cost=0.00..243798.00 rows=80000 width=4)
>           ->  Nested Loop  (cost=0.00..243598.00 rows=80000 width=4)
>                 ->  Index Scan Backward using c_pkey on c
> (cost=0.00..1518.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)
>
> What is interesting is why this plan is being rejected...

Well, it expects 80000 probles into b_on_c to be more expensive than the
hash join and sort.  I wonder what explain analyze shows for the original
and the version with the enables changed.