Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2747C2E01A2 for ; Tue, 18 Mar 2008 16:46:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 78913-02 for ; Tue, 18 Mar 2008 16:46:49 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rn-out-0910.google.com (rn-out-0910.google.com [64.233.170.189]) by postgresql.org (Postfix) with ESMTP id 757272E016A for ; Tue, 18 Mar 2008 16:46:53 -0300 (ADT) Received: by rn-out-0910.google.com with SMTP id e11so51521rng.17 for ; Tue, 18 Mar 2008 12:46:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=iPV2c8KSZpi7+GM8vX1Y6+NTidzHzrsdTfE1CfUIWA8=; b=QF9ev3Eoi764EgbdG/4DwSNBfDXfe5mgrsBsqOqwS1EEdbdcRjNFsx1Cv8b9mz1nGi1BmiKMj9Jb1JJT9NyKCwNFzkfPIFXKvkPryGAib9Clr2CSlvpyEQ4WpzfY0UNIDnmTZ7Giu0cOKy/d2Fd8lkBNXUXEQ15I0N2OT7DIFKE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=LT1NoArphmY8voEbRtgV3+956NbUvYZQqd0UTQBrjvEua19/F2Lhsrm3mzYgmExykqE2CgF0d8sL50OStb5WGeuEHBVS1qqCzsOFSr6taaRuQRnehe4Sd4UgvtCh4gDRnDGTEF1AvHieI45bol6K33zVSK3QDoAvbOplGiNM5ZY= Received: by 10.114.144.1 with SMTP id r1mr2378090wad.53.1205869612053; Tue, 18 Mar 2008 12:46:52 -0700 (PDT) Received: by 10.114.133.6 with HTTP; Tue, 18 Mar 2008 12:46:51 -0700 (PDT) Message-ID: Date: Tue, 18 Mar 2008 13:46:51 -0600 From: "Scott Marlowe" To: "Chris Kratz" Subject: Re: Planner mis-estimation using nested loops followup Cc: "Joshua D. Drake" , pgsql-performance@postgresql.org In-Reply-To: <3642025c0803180858k704d8c6ah39e30b8ab75f4df0@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <3642025c0803180835n20062314g94870b137d8c3ff7@mail.gmail.com> <20080318085059.7c5da59a@commandprompt.com> <3642025c0803180858k704d8c6ah39e30b8ab75f4df0@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/379 X-Sequence-Number: 29333 On Tue, Mar 18, 2008 at 9:58 AM, Chris Kratz wrote: > Y, turning nested loops off in specific cases has increased performance > greatly. It didn't fix the planner mis-estimation, just the plan it chose. > It's certainly not a panacea, but it's something we now try early on when > trying to speed up a query that matches these characteristics. I have to admit I've had one or two reporting queries in the past that turning off nested_loop was the only reasonable fix due to misestimation. I'd tried changing the stats targets etc and nothing really worked reliably to prevent the nested_loop from showing up in the wrong places.