Every paginated list in a web application has a "last page" button. In most systems, it looks like the least interesting feature — users rarely use it, and it works fine in testing. But in production, on tables with millions of rows, that button can trigger one of the most expensive operations a database is asked to perform. And the reason it is expensive is not a bug or a misconfiguration. It is how pagination works at the storage level in PostgreSQL, and it is something the database has no way to avoid with the tools it currently has.
How OFFSET Actually Works
When you ask PostgreSQL to return rows starting from position one million, the query looks something like this: order the rows by some column, skip the first one million, return the next eighty. That skip step sounds fast on paper. In reality, PostgreSQL has no way to jump directly to row one million. It starts from the beginning of the index or the table, reads and evaluates every row in order, counts them, and discards them one by one until it reaches the right position. Only then does it start returning results.
This is not a shortcut that PostgreSQL is choosing not to take. There is no shortcut available. An index tells you where the smallest value lives and where the next one is and the one after that. It does not tell you where the millionth value lives without walking the chain from the beginning. Every row before the offset position is real work — real I/O, real CPU, real buffer reads — and all of it is thrown away the moment the discard count is satisfied.
What This Looks Like at Production Scale
On a real accounting table with 1.95 million rows — the kind of volume a mid-sized Odoo installation accumulates after a few years of operation — the numbers are stark. A "jump to last page" query asks for the final eighty rows of the ordered result set. That means the offset is 1,949,920. PostgreSQL reads all 1.95 million rows from the index, evaluates the filter conditions on each one, discards 1,949,920 of them, and returns the final eighty.
The measured execution time for this query is over five and a half seconds. Not because the query is poorly written. Not because the indexes are wrong. The index is present and used — PostgreSQL is doing an index scan backward through the primary key, exactly as intended. It is just that the index has 1.95 million entries to walk, and there is no way to skip to the end.
The multi-column case is even more expensive. When the sort order involves date and name — the real ordering Odoo uses for its journal items view — PostgreSQL cannot walk an index backward and must instead perform a full sequential scan of 1.95 million rows followed by an external merge sort that writes 47 megabytes of temporary data to disk. That query takes nearly eleven seconds. All of it returns eighty rows.
Why This Problem Is Invisible Until It Is Not
Development databases have hundreds or thousands of rows. Test datasets have tens of thousands at most. At that scale, an OFFSET of a few thousand takes milliseconds, and nobody notices. The test passes. The feature ships. The real data accumulates in production over months and years, and one day a user navigates to the last page of a list and waits. And waits.
The query that was instantaneous in testing is now taking eight seconds in production. The index is still there. The query plan looks identical to what it always was. Nothing changed except the volume of data — and the offset grew in proportion to it. As the table doubles in size, the last-page query doubles in execution time. There is no natural ceiling on this.
The Specific Pain in ERP Systems
Enterprise applications built on PostgreSQL — Odoo, ERPNext, and similar platforms — are particularly vulnerable to this problem because their core use case involves paginated browsing of large operational datasets. Accountants browse journal items. Warehouse managers browse stock moves. Sales teams browse customer orders. The default view in most of these modules shows the most recent records first, which means the last page of the list is the oldest data — sitting behind the largest possible OFFSET.
Odoo's journal items view (account_move_line) is a concrete example. A company that has been running Odoo for three years might have two million journal lines. The default sort order is by date descending, move name descending, and id ascending. An index exists for exactly this sort order. But navigating to the last page still requires PostgreSQL to walk through all two million entries in that index, applying filter conditions at each step, before it can return the eighty oldest entries. The index that exists to make the query fast does not help with the overhead of counting to the end.
Why PostgreSQL Cannot Simply Fix This
The problem is not unique to PostgreSQL. Any relational database that uses offset-based pagination faces the same fundamental constraint. The offset tells the database how many rows to skip, but skipping requires counting, and counting requires reading. The database does not know, when it starts scanning, which physical page contains the millionth row. The rows are not stored in sorted order on disk. The index stores them in sorted order, but traversing a sorted index from the beginning is the only way to walk through it.
The only way to get the last rows efficiently with a known total count would be to approach the problem differently — to restate the question so that the database is asked for the first rows rather than the last rows, or to use a different navigation mechanism that does not rely on absolute position. Neither of those is something the query planner can currently do automatically when it receives a standard OFFSET query. It sees the OFFSET, it uses the index, it counts, it discards.
This is a real gap in how pagination at scale works in PostgreSQL. Developers work around it in various ways at the application layer. But the gap exists at the engine level, and it costs real time on every system where large paginated lists are a daily part of how users work.