Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 217F52E002C for ; Thu, 29 May 2008 14:59:49 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 81074-04 for ; Thu, 29 May 2008 14:59:33 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by developer.postgresql.org (Postfix) with ESMTP id 59D172E0093 for ; Thu, 29 May 2008 14:59:15 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m4THxBfK001867; Thu, 29 May 2008 13:59:11 -0400 (EDT) To: Chris Shoemaker cc: Shane Ambler , pgsql-performance@postgresql.org, ejones@engineyard.com Subject: Re: Adding "LIMIT 1" kills performance. In-reply-to: <20080529174712.GB18895@pe.Belkin> References: <20080529154734.GA18273@pe.Belkin> <483EDF9A.20100@Sheeky.Biz> <20080529174712.GB18895@pe.Belkin> Comments: In-reply-to Chris Shoemaker message dated "Thu, 29 May 2008 13:47:12 -0400" Date: Thu, 29 May 2008 13:59:11 -0400 Message-ID: <1866.1212083951@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/374 X-Sequence-Number: 30342 Chris Shoemaker writes: > Still stumped, The short answer here is that the planner is guessing that scanning the index in ID order will come across the desired row (ie, the first one matching the join condition) in less time than it will take to select all the joinable rows, sort them by ID, and take the first one. It's wrong in this case, but the plan is not unreasonable on its face. The problem boils down to a misestimate of how many join rows there are. You might get better results by increasing the statistics targets. There are plenty of similar cases in the list archives. regards, tom lane