From: Gregory Stark <stark@enterprisedb.com>
To: Andreas Joseph Krogh <andreak@officenet.no>
Cc: pgsql-sql@postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Returning the total number of rows as a separate column when using limit
Date: Mon, 05 Nov 2007 18:54:52 +0000
Message-ID: <87fxzkwmir.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <200711051733.10324.andreak@officenet.no>
References: <200711050836.38130.andreak@officenet.no>
<19503.1194274853@sss.pgh.pa.us>
<87ir4g67co.fsf@oxford.xeocode.com>
<200711051733.10324.andreak@officenet.no>
"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.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: stark@enterprisedb.com, andreak@officenet.no, 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>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox