Re: Weird query plan

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