X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7F39B528A7 for ; Wed, 18 May 2005 17:04:12 -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 57509-01 for ; Wed, 18 May 2005 20:04:05 +0000 (GMT) Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8296A528A4 for ; Wed, 18 May 2005 17:04:03 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 6A32530952; Wed, 18 May 2005 22:01:24 +0200 (MET DST) From: Chris Browne X-Newsgroups: pgsql.sql Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Date: Wed, 18 May 2005 15:52:33 -0400 Organization: cbbrowne Computing Inc Lines: 35 Message-ID: <60mzqsqpku.fsf@dba2.int.libertyrms.com> References: <20050517170636.GC12405@phlogiston.dyndns.org> <428A2E81.2060501@pobox.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Jumbo Shrimp, linux) Cancel-Lock: sha1:RCJFX3urZy5/O1xixJ/AlLywLqY= To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.152 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/156 X-Sequence-Number: 21469 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.''"