Pagination & reading plans

Page through results, and what EXPLAIN ANALYZE adds.

Serving results a page at a time

The obvious paginator is LIMIT n OFFSET m:

SELECT title, price FROM books ORDER BY price LIMIT 3 OFFSET 3;  -- page 2

But OFFSET still scans and discards the skipped rows, so deep pages get slow. Keyset (cursor) pagination is the scalable alternative — remember the last value seen and ask for rows after it:

SELECT id, title FROM books WHERE id > 5 ORDER BY id LIMIT 3;

To measure for real, EXPLAIN ANALYZE actually runs the query and reports estimated vs. actual rows and time — the first thing to reach for when a query is slow. (EXPLAIN alone only estimates.)

LIMIT 3 OFFSET 3 against books ordered by price. OFFSET does not skip work — the first three rows are produced and thrown away, and on page 100 that is 297 rows produced for nothing.

Keyset pagination reads only the rows it returns, at any depth, because the WHERE clause does the skipping and an index on id can jump straight there. The cost is that you page by a remembered key, not by a page number.