X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0C20FD1ED12 for ; Fri, 26 Mar 2004 05:54:08 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 40722-09 for ; Fri, 26 Mar 2004 01:53:44 -0400 (AST) Received: from dc2-web14.assortedinternet.com (unknown [66.36.233.99]) by svr1.postgresql.org (Postfix) with ESMTP id 53D5DD1EC53 for ; Fri, 26 Mar 2004 01:53:26 -0400 (AST) Received: from adsl-67-125-157-241.dsl.irvnca.pacbell.net ([67.125.157.241] helo=ARA) by dc2-web14.assortedinternet.com with asmtp (Exim 4.24) id 1B6kHL-00057V-Lv for pgsql-performance@postgresql.org; Fri, 26 Mar 2004 00:53:15 -0500 Message-ID: <001401c412f6$88281700$6401a8c0@ARA> From: "Ara Anjargolian" To: Subject: odd planner choice Date: Thu, 25 Mar 2004 21:52:31 -0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - dc2-web14.assortedinternet.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - jargol.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/385 X-Sequence-Number: 6241 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