X-Original-To: pgsql-performance@postgresql.org Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net [194.217.242.88]) by postgresql.org (Postfix) with ESMTP id 8F1CB477330 for ; Wed, 5 Mar 2003 06:14:05 -0500 (EST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-30.mail.demon.net with esmtp (Exim 3.35 #1) id 18qWqd-0004TV-0U for pgsql-performance@postgresql.org; Wed, 05 Mar 2003 11:14:07 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 73AC71756C for ; Wed, 5 Mar 2003 11:14:06 +0000 (GMT) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id D2E4D161D7 for ; Wed, 5 Mar 2003 11:14:05 +0000 (GMT) Content-Type: text/plain; charset="us-ascii" From: Richard Huxton Organization: Archonet Ltd To: Subject: Planner matching constants across tables in a join Date: Wed, 5 Mar 2003 11:13:14 +0000 User-Agent: KMail/1.4.3 MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200303051113.14320.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Archive-Number: 200303/23 X-Sequence-Number: 1321 I know this has been covered on one of the lists in the past, but I'm damne= d=20 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= =20 planner see that the comparison applies to both tables: SELECT a.id FROM a JOIN b ON a.id=3Db.id WHERE a.id=3D1; runs much slower than SELECT a.id FROM a JOIN b ON a.id=3Db.id WHERE a.id=3D1 AND b.id=3D1; It's not a real problem since it's easy to work around, but I was wondering= =20 what the difficulties are for the planner in seeing that query 1 is the sam= e=20 as query 2. Note that it doesn't seem related to JOIN forcing the planner's= =20 hand, the same applies just using WHERE a.id=3Db.id --=20 Richard Huxton