Re: Weird query plan

Hmmm...
Indeed. I tried it in 7.2.4 on a couple of empty tables, and it does do
the right thing...
Also, I have another copy (not exact copy, but identical schema, and
similar content... but about twice smaller) of the original database...
I tried my query on it, and it works right too.
So, there must be something wrong with that particular database I suppose...

Any ideas what I should look at?

Thanks a lot!

Dima

Tom Lane wrote:

>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
>
>