2012/7/9 Gregg Jaskiewicz <gryzman@xxxxxxxxx>
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
