Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E6D579FBB1B for ; Mon, 20 Nov 2006 04:28:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 61077-01-7 for ; Mon, 20 Nov 2006 04:28:42 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id 8553E9FB203 for ; Mon, 20 Nov 2006 04:28:11 -0400 (AST) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 98D7214421F; Mon, 20 Nov 2006 08:26:32 +0000 (GMT) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 232D315EAB; Mon, 20 Nov 2006 08:26:32 +0000 (GMT) Message-ID: <45616764.1040902@archonet.com> Date: Mon, 20 Nov 2006 08:29:24 +0000 From: Richard Huxton User-Agent: Thunderbird 1.5.0.7 (X11/20060918) MIME-Version: 1.0 To: Drew Cc: pgsql-sql@postgresql.org Subject: Re: query faster using LEFT OUTER join? References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/209 X-Sequence-Number: 26861 Drew wrote: > I have a planner question about a multi-join query, where if I rewrite > the query excluding the table that is left joined to the other tables my > query speed increases by 75%. > Here's the plan for the first query (using 8.2b3) > ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > HashAggregate (cost=3223.17..3223.41 rows=19 width=18) (actual > time=34.913..34.978 rows=122 loops=1) > And here's the plan for the second query without the first LEFT OUTER > join (using 8.2b3) > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ > > HashAggregate (cost=8927.87..8936.89 rows=721 width=18) (actual > time=39.484..39.551 rows=122 loops=1) Query1: 34.9ms Query2: 39.5ms I'm not seeing a huge difference here. Are these the right EXPLAINs? -- Richard Huxton Archonet Ltd