From: | "Ara Anjargolian" <ara(at)jargol(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | odd planner choice |
Date: | 2004-03-26 05:52:31 |
Message-ID: | 001401c412f6$88281700$6401a8c0@ARA |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Manfred Spraul | 2004-03-26 06:25:53 | Re: [HACKERS] fsync method checking |
Previous Message | Christopher Kings-Lynne | 2004-03-26 03:38:47 | Re: column size too large, is this a bug? |