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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
