Re: Paged Query

2012/7/9 Gregg Jaskiewicz <gryzman(at)gmail(dot)com>

> Use cursors.
> By far the most flexible. offset/limit have their down sides.
>

Well, I am not aware what down sides there are in LIMIT OFFSET what does
not exist in any other solutions for paged queries... But agree there
always must be some compromise between flexibility and response time (as
long user "have" impression he works "immediatly" so is query executed in
1ms od 1s - not important...)

Query must be parsed and executed (inside DB, before returns results... -
so this time is unavoidable) Cursors will ensure just to take (executed
results) 1 by 1 from DB,,, OK in Cursor scenario parse and Execute is done
just once... But execution plans are cached - though I don't see big
downside if it is executed thousands times... you will notice in Pg that
second query is much faster then 1st one...

So if you need to go straight forward form page 1 to page 576 (in
situations bellow 100 pages - 50 rows by page - no point to discuss
performance... You can get all rows from DB at once and do "paging" in
client side in memory) - I agree response will be a bit slower in
LIMIT/OFFSET case, however not sure in CURSOR scenario it will be much
faster, to be more worth then many others limits of Cursors in General...
(Personally I have not used them more then 7 years - Really don't see need
for them todays when hardware have more and more power...)

From my experience users even very rare go to ending pages... easier to
them would be to sort data by field to get those rows in very first pages...

Kind Regards,

Misa