Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 76B519FB31A for ; Wed, 10 Oct 2007 03:31:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 25638-03 for ; Wed, 10 Oct 2007 03:31:41 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from outmail136083.authsmtp.com (outmail136083.authsmtp.com [62.13.136.83]) by postgresql.org (Postfix) with ESMTP id D61929FB2D6 for ; Wed, 10 Oct 2007 03:31:42 -0300 (ADT) Received: from outmail128185.authsmtp.co.uk (outmail128185.authsmtp.co.uk [62.13.128.185]) by punt2.authsmtp.com (8.13.8/8.13.8/Kp) with ESMTP id l9A6Ve78076156; Wed, 10 Oct 2007 07:31:40 +0100 (BST) Received: from [192.168.0.3] (85-211-149-22.dyn.gotadsl.co.uk [85.211.149.22]) (authenticated bits=0) by mail.authsmtp.com (8.13.8/8.13.8/Kp) with ESMTP id l9A6VbFP018896; Wed, 10 Oct 2007 07:31:38 +0100 (BST) Subject: Re: hashjoin chosen over 1000x faster plan From: Simon Riggs To: Kevin Grittner Cc: pgsql-performance@postgresql.org In-Reply-To: <470B99BE.EE98.0025.0@wicourts.gov> References: <470B99BE.EE98.0025.0@wicourts.gov> Content-Type: text/plain Date: Wed, 10 Oct 2007 07:31:44 +0100 Message-Id: <1191997904.4233.125.camel@ebony.site> Mime-Version: 1.0 X-Mailer: Evolution 2.8.2 Content-Transfer-Encoding: 7bit X-Server-Quench: 74f1727f-76fa-11dc-b86a-001185d377ca X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MJUcNSQVJ NksadBtFaQVbYlpF HGQLW1NEUFp7WmJ/ aAMfZQBDYEtPQQRu WVZLQE1QERpoT0AI dxh4AG8SdwROcHd2 K0diV3gVW0AsdEF/ RRhJEmtQNHphaTRM TRJQcVJJcANLfxdA PlN4UyBcLwdSbGoL JyIJERcLGAAXKC1O QgMKLFRaSkYMBTM6 DxccEC9nRBVdHzV7 IRU9NlMGVF4VO0Na X-Authentic-SMTP: 61633235383639.squirrel.dmpriest.net.uk:752/Kp X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-Virus-Status: No virus detected - but ensure you scan with your own anti-virus system! X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/92 X-Sequence-Number: 27114 On Tue, 2007-10-09 at 15:09 -0500, Kevin Grittner wrote: > I have a situation where a query is running much slower than I would > expect. The ANALYZE showed that it is hashing some information which > is rarely needed. When I set enable_hashjoin = off for the > connection the query run in 1/1000 the time. Can you confirm the two queries give identical outputs? It isn't clear to me why the second sort is (never executed) in your second plan, which I would only expect to see for an inner merge join. Can you show the details for ControlRecord also. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com