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@openratings.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
>
>