X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id B1DD09DC809 for ; Wed, 11 Jan 2006 00:38:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 80416-06 for ; Wed, 11 Jan 2006 00:38:22 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 4BEC09DC801 for ; Wed, 11 Jan 2006 00:38:21 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k0B4cKnW008322; Tue, 10 Jan 2006 23:38:20 -0500 (EST) To: "Dave Dutcher" cc: pgsql-performance@postgresql.org Subject: Re: Left Join Performance vs Inner Join Performance In-reply-to: <011f01c61653$96c509f0$8300a8c0@tridecap.com> References: <011f01c61653$96c509f0$8300a8c0@tridecap.com> Comments: In-reply-to "Dave Dutcher" message dated "Tue, 10 Jan 2006 20:06:08 -0600" Date: Tue, 10 Jan 2006 23:38:20 -0500 Message-ID: <8321.1136954300@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.08 required=5 tests=[AWL=0.080] X-Spam-Score: 0.08 X-Spam-Level: X-Archive-Number: 200601/99 X-Sequence-Number: 16577 "Dave Dutcher" writes: > I have an inner join query that runs fast, but I when I change to a left > join the query runs 96 times slower. This looks like an issue that is fixed in the latest set of releases, namely that OUTER JOIN ON conditions that reference only the inner side of the join weren't getting pushed down into indexquals. See thread here: http://archives.postgresql.org/pgsql-performance/2005-12/msg00134.php and patches in this and the following messages: http://archives.postgresql.org/pgsql-committers/2005-12/msg00105.php regards, tom lane