Sergey Konoplev-2 wrote > As an alternative solution for pagination (OFFSET) problem you might > also use the "prev/next" technique, like > > SELECT * FROM table > WHERE id > :current_last_id > ORDER BY id LIMIT 10 > > for "next", and > > SELECT * FROM ( > SELECT * FROM table > WHERE id < :current_first_id > ORDER BY id DESC > LIMIT 10 > ) AS sq ORDER BY id > > for "prev". It will be very fast. Even being fairly experienced at SQL generally because I haven't explored pagination that much my awareness of the OFFSET issue led me to conclude bad things. Thank you for thinking to take the time for a brief moment of enlightenment of something you likely take for granted by now. Curious how much slower/faster these queries would run if you added: SELECT *, first_value(id) OVER (...), last_value(id) OVER (...) --note the window specifications need to overcome the "ORDER BY" limitation noted in the documentation. to the query. Using the window functions you know at each record what the first and last ids are for its window. Applicability would be application/need specific but it would avoid having to calculate/maintain these two values in a separate part of the application. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/ORDER-BY-LIMIT-and-indexes-tp5766413p5766429.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
