Re: Limit + group + join

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.