Re: Planner doesn't look at LIMIT?

Dawid Kuroczko <qnex42(at)gmail(dot)com> writes:
> qnex=# EXPLAIN SELECT * FROM log NATURAL JOIN useragents LIMIT 1;

> Limit (cost=15912.20..15912.31 rows=1 width=272)
> -> Hash Join (cost=15912.20..5328368.96 rows=47044336 width=272)

> If I set enable_hashjoin=false:

> qnex=# EXPLAIN ANALYZE SELECT * FROM log NATURAL LEFT JOIN useragents LIMIT 1;

> Limit (cost=0.00..3.07 rows=1 width=272) (actual time=74.214..74.216
> rows=1 loops=1)
> -> Nested Loop Left Join (cost=0.00..144295895.01 rows=47044336
> width=272) (actual time=74.204..74.204 rows=1 loops=1)

This is quite strange. The nestloop plan definitely should be preferred
in the context of the LIMIT, considering that it has far lower estimated
cost. And it is preferred in simple tests for me. It seems there must
be something specific to your installation that's causing the planner to
go wrong. Can you develop a self-contained test case that behaves this
way for you?

I recall we saw a similar complaint a month or two back, but the
complainant never followed up with anything useful for tracking down
the problem.

regards, tom lane