From: Chris Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
Date: Wed, 18 May 2005 15:52:33 -0400
Message-ID: <60mzqsqpku.fsf@dba2.int.libertyrms.com> (raw)
References: <DCD4903594CCF34CAFE200BDF3448D8D5B291A@tshuscodenmbx01.ERF.THOMSON.COM>
<20050517170636.GC12405@phlogiston.dyndns.org>
<428A2E81.2060501@pobox.com>
alainm@pobox.com (Alain) writes:
> Andrew Sullivan escreveu:
>> On Thu, May 12, 2005 at 01:07:00PM -0600, Dennis.Jiang@thomson.com wrote:
>>
>>>Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If
>>>so, we can write the following query:
>> No. What is the purpose of your query? You could use ORDER BY and
>> LIMIT..OFFSET to do what you want. I think.
>
> The problem is probably speed. I have done a lot of tests, and when
> OFFSET gets to a few thousands on a multimega-recs database, it gets
> very very slow... Is there any other to work around that?
The other way to do this would involve creating a cursor against the
table, and using suitable FETCHes to grab the portions that you
needed.
In practice, this has seemed to be the relevant answer to what the
application developer actually wanted.
The common "use case" where I see it is in a web application where
they discover that there are 800K records, and the user only wants a
screenful at a time.
Establishing a cursor, and having the web app jump around on it, seems
to be the right answer. (Whether it's reasonably implementable by the
developers may be another question, but that's allowed to be a
separate question ;-).)
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/;
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: cbbrowne@acm.org
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
In-Reply-To: <60mzqsqpku.fsf@dba2.int.libertyrms.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