X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id C2A0D11F60AE for ; Fri, 14 Apr 2006 13:13:42 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -0.354 X-Spam-Level: X-Spam-Status: No, score=-0.354 required=5 tests=[AWL=-1.571, BAYES_00=-2.599, SPF_HELO_SOFTFAIL=2.432, SPF_SOFTFAIL=1.384] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id s1JiessGCozd for ; Fri, 14 Apr 2006 13:13:28 -0300 (ADT) 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 4229711F61BA for ; Fri, 14 Apr 2006 13:13:26 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k3EGDPe7010001; Fri, 14 Apr 2006 12:13:25 -0400 (EDT) To: Ian Westmacott cc: pgsql-performance@postgresql.org Subject: Re: merge>hash>loop In-reply-to: <1145029009.3422.31.camel@spectre.intellivid.com> References: <1145029009.3422.31.camel@spectre.intellivid.com> Comments: In-reply-to Ian Westmacott message dated "Fri, 14 Apr 2006 11:36:48 -0400" Date: Fri, 14 Apr 2006 12:13:25 -0400 Message-ID: <10000.1145031205@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200604/336 X-Sequence-Number: 18420 Ian Westmacott writes: > I have this query, where PG (8.1.2) prefers Merge Join over Hash Join > over Nested Loop. However, this order turns out to increase in > performance. I was hoping someone might be able to shed some light on > why PG chooses the plans in this order, and what I might do to > influence it otherwise. Thanks, Reducing random_page_cost would influence it to prefer the nestloop. However, I doubt you're ever going to get really ideal results for this query --- the estimated row counts are too far off, and the WHERE conditions are sufficiently bizarre that there's not much hope that they'd ever be much better. regards, tom lane