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.