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 5B1FDD1BAAD for ; Mon, 7 Jun 2004 17:31:45 -0300 (ADT) 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 13027-05 for ; Mon, 7 Jun 2004 20:31:43 +0000 (GMT) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id DCDD9D1BA91 for ; Mon, 7 Jun 2004 17:31:40 -0300 (ADT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 5298169; Mon, 07 Jun 2004 13:33:18 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: "Merlin Moncure" , Subject: Re: is it possible to for the planner to optimize this form? Date: Mon, 7 Jun 2004 13:31:37 -0700 User-Agent: KMail/1.4.3 References: <6EE64EF3AB31D5448D0007DD34EEB34101AE56@Herge.rcsinc.local> In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB34101AE56@Herge.rcsinc.local> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200406071331.37140.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/71 X-Sequence-Number: 7129 Merlin, > select t.key, t.field from t a > where > ( > select count(*) from t b > where b.field > a.field > ) = k > > The subplan (either index or seq. scan) executes once for each row in t, > which of course takes forever. > > This query is a way of achieving LIMIT type results (substitute n-1 > desired rows for k) using standard SQL, which is desirable in some > circumstances. Is it theoretically possible for this to be optimized? I don't think so, no. PostgreSQL does have some issues using indexes for count() queires which makes the situation worse. However, with the query you presented, I don't see any way around the planner executing the subquery once for every row in t. Except, of course, for some kind of scheme involving materialized views, if you don't need up-to-the minute data. In that case, you could store in a table the count(*)s of t for each threshold value of b.field. But, dynamically, that would be even slower. -- Josh Berkus Aglio Database Solutions San Francisco