postgres pagination - ghdrako/doc_snipets GitHub Wiki

• https://leopard.in.ua/2014/10/11/postgresql-paginattion
• https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/
• https://use-the-index-luke.com/blog/2013-07/pagination-done-the-postgresql-way

** Never use offset!**

The offset clause is going to cause your SQL query plan to read all the result anyway and then discard most of it until reaching the offset count. When paging through lots of results, it’s less and less efficient with each additional page you fetch that way.

The proper way to implement pagination is to use index lookups, and if you have multiple columns in your ordering clause, you can do that with her row() construct.

First page

select lap, drivers.code, position, 
 milliseconds * interval '1ms' as laptime 
 from laptimes 
 join drivers using(driverid) 
 where raceid = 972 
 order by lap, position 
 fetch first 3 rows only;

Next page

select lap, drivers.code, position, 
 milliseconds * interval '1ms' as laptime 
 from laptimes 
 join drivers using(driverid) 
 where raceid = 972 
 and row(lap, position) > (1, 3) 
 order by lap, position 
 fetch first 3 rows only;