Re: Weird query plan

Dmitry Tkach <dmitry(at)openratings(dot)com> writes:
> The query plan looks identical in both cases:

> Limit (cost=0.00..12.51 rows=1 width=8)
> -> Nested Loop (cost=0.00..1009772807.91 rows=80740598 width=8)
> -> Index Scan using b_pkey on b (cost=0.00..375410773.29
> rows=80740598 width=4)
> -> Index Scan using a_pkey on a (cost=0.00..6.85 rows=1 width=4)

> ... which makes me think that it decides to use b as the outer table
> for both cases (which would obviously make it suck in the first one)... :-(

That's what it says, all right, which seems odd to me. Are you sure you
looked at the right plans?

> This happens on 7.2.4... I have a 7.3 database with the same schema, but
> it is not populated with data, so I could not test it on 7.3...

I could not reproduce a problem on 7.2.4. I get (using toy tables, and
suppressing the planner's urge to use mergejoin instead)

lo=# explain select * from a, b where a.id >= 7901288 and a.id=b.id limit 1;
NOTICE: QUERY PLAN:

Limit (cost=0.00..4.97 rows=1 width=8)
-> Nested Loop (cost=0.00..1657.34 rows=333 width=8)
-> Index Scan using a_pkey on a (cost=0.00..45.50 rows=333 width=4)
-> Index Scan using b_pkey on b (cost=0.00..4.82 rows=1 width=4)

EXPLAIN
lo=# explain select * from a, b where b.id >= 7901288 and a.id=b.id limit 1;
NOTICE: QUERY PLAN:

Limit (cost=0.00..4.97 rows=1 width=8)
-> Nested Loop (cost=0.00..1657.34 rows=333 width=8)
-> Index Scan using b_pkey on b (cost=0.00..45.50 rows=333 width=4)
-> Index Scan using a_pkey on a (cost=0.00..4.82 rows=1 width=4)

EXPLAIN

which looks like the right thing.

regards, tom lane