Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 900C99FA420 for ; Fri, 21 Sep 2007 08:01:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 54829-05 for ; Fri, 21 Sep 2007 08:00:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from outmail129168.authsmtp.net (outmail129168.authsmtp.net [62.13.129.168]) by postgresql.org (Postfix) with ESMTP id 1BC769FA3FD for ; Fri, 21 Sep 2007 08:00:54 -0300 (ADT) Received: from outmail128183.authsmtp.com (outmail128183.authsmtp.com [62.13.128.183]) by punt3.authsmtp.com (8.13.8/8.13.8/Kp) with ESMTP id l8LB0pSM051511; Fri, 21 Sep 2007 12:00:52 +0100 (BST) Received: from [192.168.0.3] (85-211-21-1.dyn.gotadsl.co.uk [85.211.21.1]) (authenticated bits=0) by mail.authsmtp.com (8.13.8/8.13.8/Kp) with ESMTP id l8LB0oY5050378; Fri, 21 Sep 2007 12:00:51 +0100 (BST) Subject: Re: Searching for the cause of a bad plan From: Simon Riggs To: Csaba Nagy Cc: postgres performance list In-Reply-To: <1190369024.4661.173.camel@PCD12478> References: <1190369024.4661.173.camel@PCD12478> Content-Type: text/plain Date: Fri, 21 Sep 2007 11:59:25 +0100 Message-Id: <1190372365.4202.14.camel@ebony.site> Mime-Version: 1.0 X-Mailer: Evolution 2.8.2 Content-Transfer-Encoding: 7bit X-Server-Quench: ea970eee-6831-11dc-b86a-001185d377ca X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MJUcNSQVJ NksadBtFagRbflxB HGQLW1NEUV97WWN/ bgsfZQBDYEtPQQdr TklWQFdNFEs0CWBz BkF3Vxl3dwZBcDBx ZE5kECUKVUN6IBMo X0hcFmwbZGY0O31N WEYJagNUcQJCe0tG bVcsBT1vNG8XDRoV JSEUBRUEdQ5SIT5e QgALIhocRlxDAj4x DxsLAC4iVUoLDyx7 JRUqYkYYFUB5 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: 200709/355 X-Sequence-Number: 26900 On Fri, 2007-09-21 at 12:03 +0200, Csaba Nagy wrote: > prepare test_001(bigint) as > SELECT tb.* > FROM table_a ta > JOIN table_b2 tb ON ta.b=tb.b > WHERE ta.a = $1 > ORDER BY ta.a, ta.b > limit 10; Please re-run everything on clean tables without frigging the stats. We need to be able to trust what is happening is normal. Plan2 sees that b1 is wider, which will require more heap blocks to be retrieved. It also sees b1 is less correlated than b2, so again will require more database blocks to retrieve. Try increasing effective_cache_size. Can you plans with/without LIMIT and with/without cursor, for both b1 and b2? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com