X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CD82A3A63A9 for ; Sat, 11 Dec 2004 18:06:37 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 99934-06 for ; Sat, 11 Dec 2004 18:06:27 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 35C0E3A63B3 for ; Sat, 11 Dec 2004 18:06:27 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iBBI6OLb017529; Sat, 11 Dec 2004 13:06:24 -0500 (EST) To: Josh Berkus Cc: pgsql-performance@postgresql.org, Mike Rylander Subject: Re: LIMIT causes SEQSCAN in subselect In-reply-to: <200412102140.18456.josh@agliodbs.com> References: <200412102140.18456.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Fri, 10 Dec 2004 21:40:18 -0800" Date: Sat, 11 Dec 2004 13:06:23 -0500 Message-ID: <17528.1102788383@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200412/149 X-Sequence-Number: 9524 Josh Berkus writes: > The fact that the estimator knows that the LIMIT is pointless because there > are less rows in the subselect than the LIMIT will return is not something we > want to count on; sometimes the estimator has innaccurate information. However, when the estimator is basing that estimate on the existence of a unique index for the column, the estimate could be trusted. There are a couple of reasons that we don't perform that optimization at present, though: 1. If the finished query plan doesn't actually *use* the index in question, then dropping the index would not directly invalidate the query plan, but nonetheless the query would be broken. You could subsequently get silently-wrong answers. 2. For the particular point at hand, there's an implementation problem, which is that decisions about whether to flatten subqueries are taken before we do any rowcount estimation. So even if we discarded the LIMIT clause once we realized it was redundant, it'd be too late to get the optimal overall plan. Point #1 is something I would like to fix whenever we get around to implementing proper invalidation of cached plans. There would need to be a way to list "indirect" as well as direct dependencies of a plan. regards, tom lane