Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id B51B09FB53B for ; Sat, 21 Apr 2007 23:45:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 95825-01 for ; Sat, 21 Apr 2007 23:45:08 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 7C9C39FB4F9 for ; Sat, 21 Apr 2007 23:45:14 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l3M2jB13016597; Sat, 21 Apr 2007 22:45:11 -0400 (EDT) To: Colin McGuigan cc: pgsql-performance@postgresql.org Subject: Re: Odd problem with planner choosing seq scan In-reply-to: <462ABEE6.4060803@earthcomber.com> References: <462A2ED2.20501@earthcomber.com> <25028.1177175197@sss.pgh.pa.us> <462ABEE6.4060803@earthcomber.com> Comments: In-reply-to Colin McGuigan message dated "Sat, 21 Apr 2007 20:48:22 -0500" Date: Sat, 21 Apr 2007 22:45:11 -0400 Message-ID: <16596.1177209911@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/367 X-Sequence-Number: 24244 Colin McGuigan writes: > I know I can do it by adjusting cost parameters, but I was really > curious as to why adding a "LIMIT 5000" onto a SELECT from a table with > only 530 rows in it would affect matters at all. The LIMIT prevents the sub-select from being flattened into the main query. In the current code this has a side-effect of preventing any statistical information from being used to estimate the selectivity of the filter conditions --- so you get a default rowcount estimate that's way too small, and that changes the shape of the join plan. It's giving you the "right" answer for entirely the wrong reason. regards, tom lane