X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id BC8A5D1B50C for ; Wed, 12 Nov 2003 05:18:34 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 19283-02 for ; Wed, 12 Nov 2003 01:18:04 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id C9E22D1B550 for ; Wed, 12 Nov 2003 01:18:03 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hAC5Hx19005856; Wed, 12 Nov 2003 00:17:59 -0500 (EST) To: josh@agliodbs.com Cc: Troels Arvin , pgsql-sql@postgresql.org Subject: Re: Quota query with decent performance? In-reply-to: <200311111541.34660.josh@agliodbs.com> References: <200311111541.34660.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Tue, 11 Nov 2003 15:41:34 -0800" Date: Wed, 12 Nov 2003 00:17:58 -0500 Message-ID: <5855.1068614278@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/133 X-Sequence-Number: 15948 Josh Berkus writes: > Second, the query you post is one "SQL Standard" way, which is good for > portability but not for speed. Frankly, I'm not convinced that it's even the > best SQL standard way. On the other databases, you seem happy to use > non-SQL-standard syntax, so let me give you one such solution in PostgreSQL: > [snip] I don't know of any very good solution in bog-standard SQL either. Aside from the LIMIT-based solution that Josh offered, I recall that Oleg Bartunov and Teodor Sigaev had some ideas about top-N-aggregate solutions. We didn't accept those into the main distribution (yet) but if you dig in the PG list archives I think there is working code available. Try searching for "partial sorting". regards, tom lane