X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8080656715 for ; Thu, 17 Mar 2005 06:41:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05788-06 for ; Thu, 17 Mar 2005 06:41:18 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9CA3856606 for ; Thu, 17 Mar 2005 06:41:17 +0000 (GMT) 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 j2H6fFvb020911; Thu, 17 Mar 2005 01:41:15 -0500 (EST) To: mark.lubratt@indeq.com Cc: pgsql-performance@postgresql.org Subject: Re: Join method influences scan method? In-reply-to: <20050317061624.12920.qmail@gem-wbe01.mesa1.secureserver.net> References: <20050317061624.12920.qmail@gem-wbe01.mesa1.secureserver.net> Comments: In-reply-to mark.lubratt@indeq.com message dated "Wed, 16 Mar 2005 23:16:24 -0700" Date: Thu, 17 Mar 2005 01:41:15 -0500 Message-ID: <20910.1111041675@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/349 X-Sequence-Number: 11054 mark.lubratt@indeq.com writes: > So, it would seem like my optimal plan should have hash joins with index > scans. No. The thing you are looking at here is a nestloop join with inner index scan, which has to be understood as a unit even though EXPLAIN doesn't describe it that way. The inner indexscan is repeated once for each outer row, using a join key from the outer row as part of the index lookup. That's simply not relevant to the other kinds of joins, because they expect the inner and outer relations to be scanned independently. regards, tom lane