odd planner choice
От | Ara Anjargolian |
---|---|
Тема | odd planner choice |
Дата | |
Msg-id | 001401c412f6$88281700$6401a8c0@ARA обсуждение исходный текст |
Ответы |
Re: odd planner choice
Re: odd planner choice |
Список | 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
В списке pgsql-performance по дате отправления: