Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 74C2F9F927C
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  6 Nov 2007 02:59:07 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 79724-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  6 Nov 2007 02:58:43 -0400 (AST)
Received: from oxford.xeocode.com (unknown [87.127.95.196])
	by postgresql.org (Postfix) with ESMTP id DDBF89F9281
	for <pgsql-sql@postgresql.org>; Tue,  6 Nov 2007 02:59:02 -0400 (AST)
Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com)
	by oxford.xeocode.com with esmtp (Exim 4.67)
	(envelope-from <stark@enterprisedb.com>)
	id 1IpIOp-0003Xp-Ih; Tue, 06 Nov 2007 06:58:59 +0000
To: "Andreas Joseph Krogh" <andreak@officenet.no>
Cc: <pgsql-sql@postgresql.org>,  "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: Returning the total number of rows as a separate column when
	using limit
In-Reply-To: <87fxzkwmir.fsf@oxford.xeocode.com> (Gregory Stark's message of
	"Mon\, 05 Nov 2007 18\:54\:52 +0000")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.sql" 1290)
References: <200711050836.38130.andreak@officenet.no>
	<19503.1194274853@sss.pgh.pa.us> <87ir4g67co.fsf@oxford.xeocode.com>
	<200711051733.10324.andreak@officenet.no>
	<87fxzkwmir.fsf@oxford.xeocode.com>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Tue, 06 Nov 2007 06:58:59 +0000
Message-ID: <873avjx3kc.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200711/20
X-Sequence-Number: 29548

"Gregory Stark" <stark@enterprisedb.com> writes:

> "Andreas Joseph Krogh" <andreak@officenet.no> writes:
>
>> That's what I'm doing now. I run the query with "limit+1" as limit and if it 
>> results in more than limit, I know there is more data and I run count(*) to 
>> count them all. But count(*) cannot use indices in PG so it's limited in 
>> speed anyway AFAICS.
>
> Well count(*) can use indexes the same as the query can.
>
>> I really hoped there was an "Oracle over()" equivalent way in PG. I understand 
>> that Oracle's LIMIT-hack with "3 subselects and rownum between 1 AND 20" is 
>> rather expensive compared to PG's implementation of LIMIT. Oralce keeps 
>> snapshot-info in the index, so counting only involves the index AFAIK.
>
> Well that's only going to be true if the index satisfies the whole query which
> is not going to be true for the simplest cases.

er, *except* for the simplest cases.


-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!