Part 2 of 5 · 1 min read
OFFSET pagination and why it gets slower
OFFSET must still generate and discard every earlier row before returning the requested page.
Think of asking someone to count out loud from one up to ten thousand, just to learn what comes next. It would be far simpler to start counting directly from ten thousand and one.
In plain words: OFFSET n tells the database to skip the first n rows of an ordered result and return the rows after that. To do this correctly, the database must still generate those first n rows internally, in sorted order, even though it throws them away without returning them. This means the cost of an OFFSET-based page grows directly with how far into the result set that page is, making page one thousand far more expensive than page one, even though both pages return the same number of rows.
A worked example. SELECT * FROM orders ORDER BY placed_on OFFSET 100000 LIMIT 20; must still sort and discard the first hundred thousand rows internally before returning the twenty rows of this specific page.
The key lines.
SELECT * FROM orders ORDER BY placed_on OFFSET 100000 LIMIT 20;
-- must generate and discard 100000 rows before returning this page
OFFSET pagination
OFFSET pagination skips a stated number of rows in an ordered result before returning the next page, but must still generate and discard every skipped row internally, growing more expensive the further into the result set a page is.
Example: counting out loud to ten thousand just to learn what comes after, instead of starting directly from there, mirrors the wasted work OFFSET performs for a late page.