"Andrei Bintintan" <[EMAIL PROTECTED]> writes:
If you're using this to provide "pages" of results, could you use a cursor?
What do you mean by that? Cursor?
Yes I'm using this to provide "pages", but If I jump to the last pages it goes very slow.
The best way to do pages for is not to use offset or cursors but to use an index. This only works if you can enumerate all the sort orders the application might be using and can have an index on each of them.
To do this the query would look something like:
SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50
Then you take note of the last value used on a given page and if the user selects "next" you pass that as the starting point for the next page.
Greg's is the most efficient, but you need to make sure you have a suitable key available in the output of your select.
Also, since you are repeating the query you could get different results as people insert/delete rows. This might or might not be what you want.
A similar solution is to partition by date/alphabet or similar, then page those results. That can reduce your resultset to a manageable size.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
