Message-ID: <428AA569.3010708@pobox.com>
Date: Tue, 17 May 2005 23:16:09 -0300
From: Alain <alainm@pobox.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Sql-Postgre <pgsql-sql@postgresql.org>
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
References: 
 <DCD4903594CCF34CAFE200BDF3448D8D5B291A@tshuscodenmbx01.ERF.THOMSON.COM>	
	<20050517170636.GC12405@phlogiston.dyndns.org>
	<428A2E81.2060501@pobox.com>	
	<1116353539.26520.5.camel@localhost.localdomain>	
	<428A3B54.4070902@pobox.com>
	<1116367382.9362.11.camel@localhost.localdomain>
In-Reply-To: <1116367382.9362.11.camel@localhost.localdomain>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit


Ragnar Hafstað escreveu:
>>[how to solve the get next 100 records problem]
> 
>>I tried that. It does not work in the generic case: 6 MegaRec, telephone 
>>listing, alphabetical order.
> 
> lets say pkey is your primary key and skey is your sort key, and 
> there exists an index on (skey,pkey)
> 
> your first select is
> select ... from tab ORDER by skey,pkey LIMIT 100;
> 
> your subsequent selects are
> select ... from tab WHERE skey>skey_last 
>                        OR (skey=skey_last AND pkey>pkey_last) 
>                     ORDER BY skey,pkey
>                     LIMIT 100 OFFSET 100;

I tied that, it is veeery slow, probably due to the OR operand :(

BUT, I think that this is close to a final solution, I made some 
preliminary test ok. Please tell me what you think about this.

Fisrt let's state that I am reading records to put on a screen (in a 
Table/Grid). I separated the problem is *3* parts

-first select is as above:
select ... from tab ORDER by skey,pkey LIMIT 100;

-second method for next 100:
select ... from tab WHERE skey>=skey_last
                     ORDER BY skey,pkey
                     LIMIT 100;
but here I test for repetitions using pkey and discard them

-now if I get all repetitions or the last 100 have the same skey with 
the second method, I use
select ... from tab WHERE skey=skey_last AND pkey>pkey_last
                     ORDER BY skey,pkey
                     LIMIT 100;
until I get an empty response, then I go back to the second method.

All queries are extremely fast with 6000000 records and it looks like 
the few redundant or empty queries (but very fast) will not be a problem.

What is your opinion about this (apart that it is a bit complex :) ??

Alain