Postgres Pro
Downloads
Home   >   mailing lists

Thread: odd planner choice

odd planner choice

From
"Ara Anjargolian"
Date:
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

Re: odd planner choice

From
Tom Lane
Date:
"Ara Anjargolian" <ara@jargol.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

Re: odd planner choice

From
"scott.marlowe"
Date:
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?