X-Original-To: pgsql-performance@postgresql.org Received: from sabre.velocet.net (sabre.velocet.net [216.138.209.205]) by postgresql.org (Postfix) with ESMTP id B75CD4764D7 for ; Wed, 5 Mar 2003 07:42:16 -0500 (EST) Received: from stark.dyndns.tv (H162.C233.tor.velocet.net [216.138.233.162]) by sabre.velocet.net (Postfix) with ESMTP id 1FF8E137F61; Wed, 5 Mar 2003 07:42:19 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 18qYDy-0001Zg-00; Wed, 05 Mar 2003 07:42:18 -0500 To: Richard Huxton Cc: Subject: Re: Planner matching constants across tables in a join References: <200303051113.14320.dev@archonet.com> In-Reply-To: <200303051113.14320.dev@archonet.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 05 Mar 2003 07:42:18 -0500 Message-ID: <87bs0q9e8l.fsf@stark.dyndns.tv> Lines: 25 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200303/24 X-Sequence-Number: 1322 Richard Huxton writes: > I know this has been covered on one of the lists in the past, but I'm damned > if I can find the keywords to locate it. > > If I join two tables with a comparison to a constant on one, why can't the > planner see that the comparison applies to both tables: It sure does. Postgres does an impressive job of tracing equality clauses around for just this purpose. > SELECT a.id FROM a JOIN b ON a.id=b.id WHERE a.id=1; > > runs much slower than > > SELECT a.id FROM a JOIN b ON a.id=b.id WHERE a.id=1 AND b.id=1; Really? They produce virtually the same plan for me. Why do you think it'll run slower? What query are you actually finding slow? -- greg