Re: odd planner choice

I've run into this odd planner choice which I don't quite understand.

I have two tables articles, users and
articles.article_id and users.user_id are primary keys.

Insides articles there are two optional fields author_id1, author_id2
which all reference users.user_id.

And now the plans:
(by the way this is pg 7.4 and I set enable_seqscan to off).

jargol=# explain select user_id, first_names, last_name from articles, users
where article_id = 5027 and (articles.author_id1 = users.user_id);
QUERY PLAN
----------------------------------------------------------------------------
------
Nested Loop (cost=0.00..4.04 rows=1 width=26)
-> Index Scan using articles_pk on articles (cost=0.00..2.01 rows=1
width=4)
Index Cond: (article_id = 5027)
-> Index Scan using users_pk on users (cost=0.00..2.01 rows=1 width=26)
Index Cond: ("outer".author_id1 = users.user_id)
(5 rows)

jargol=# explain select user_id, first_names, last_name from articles, users
where article_id = 5027 and (articles.author_id1 = users.user_id or
articles.author_id2 = users.user_id);
QUERY PLAN
----------------------------------------------------------------------------
-----------------------
Nested Loop (cost=100000000.00..100000003.11 rows=2 width=26)
Join Filter: (("outer".author_id1 = "inner".user_id) OR
("outer".author_id2 = "inner".user_id))
-> Index Scan using articles_pk on articles (cost=0.00..2.01 rows=1
width=8)
Index Cond: (article_id = 5027)
-> Seq Scan on users (cost=100000000.00..100000001.04 rows=4 width=26)
(5 rows)

Why does it think it MUST do a seq-scan in the second case? users.user_id is
a primary key,
so shouldn't it behave exactly as in the first case?

Any enlightenment on this problem will be much appreciated.

thanks,
Ara Anjargolian

On Thu, 25 Mar 2004, Ara Anjargolian wrote:

> I've run into this odd planner choice which I don't quite understand.
>
> I have two tables articles, users and
> articles.article_id and users.user_id are primary keys.
>
> Insides articles there are two optional fields author_id1, author_id2
> which all reference users.user_id.
>
> And now the plans:
> (by the way this is pg 7.4 and I set enable_seqscan to off).
>
> jargol=# explain select user_id, first_names, last_name from articles, users
> where article_id = 5027 and (articles.author_id1 = users.user_id);
> QUERY PLAN
> ----------------------------------------------------------------------------
> ------
> Nested Loop (cost=0.00..4.04 rows=1 width=26)
> -> Index Scan using articles_pk on articles (cost=0.00..2.01 rows=1
> width=4)
> Index Cond: (article_id = 5027)
> -> Index Scan using users_pk on users (cost=0.00..2.01 rows=1 width=26)
> Index Cond: ("outer".author_id1 = users.user_id)
> (5 rows)
>
> jargol=# explain select user_id, first_names, last_name from articles, users
> where article_id = 5027 and (articles.author_id1 = users.user_id or
> articles.author_id2 = users.user_id);
> QUERY PLAN
> ----------------------------------------------------------------------------
> -----------------------
> Nested Loop (cost=100000000.00..100000003.11 rows=2 width=26)
> Join Filter: (("outer".author_id1 = "inner".user_id) OR
> ("outer".author_id2 = "inner".user_id))
> -> Index Scan using articles_pk on articles (cost=0.00..2.01 rows=1
> width=8)
> Index Cond: (article_id = 5027)
> -> Seq Scan on users (cost=100000000.00..100000001.04 rows=4 width=26)
> (5 rows)
>
> Why does it think it MUST do a seq-scan in the second case? users.user_id is
> a primary key,
> so shouldn't it behave exactly as in the first case?
>
> Any enlightenment on this problem will be much appreciated.

Are articles.author_id1 and users.user_id the same type? Have you tried
casting one to the other's type if they're different?

"Ara Anjargolian" <ara(at)jargol(dot)com> writes:
> jargol=# explain select user_id, first_names, last_name from articles, users
> where article_id = 5027 and (articles.author_id1 = users.user_id or
> articles.author_id2 = users.user_id);

> Why does it think it MUST do a seq-scan in the second case?

There's no support for generating an OR indexscan in the context of a
join.

regards, tom lane