OFFSET Doesn't Skip Rows: What 10M Rows in PostgreSQL Actually Cost
OFFSET 9999980 LIMIT 20 does not skip 9,999,980 rows. It reads every one of them, builds each tuple, and throws it away. To hand back 20 rows it touches 110,659 buffer pages — 865 MB of a 1.2 GB table — and EXPLAIN reports it without flinching. I knew deep pages were slow. Asked to explain why , I said "it skips ahead, and skipping that far costs something" — which is wrong in the one way that…
Offset does not skip rows when retrieving a large number of records in PostgreSQL. Instead, it reads and discards all the rows, building each tuple and throwing it away. This process can be extremely resource-intensive. For example, when using `OFFSET 9999980 LIMIT 20`, PostgreSQL must touch 110,659 buffer pages (865 MB) of a 1.2 GB table to return just 20 rows.
The `EXPLAIN` command reports this process without any hesitation, despite the fact that the offset is skipping 9,999,980 rows. This behavior is due to the fact that PostgreSQL's B-tree cannot determine the rank of a specific row, so it must walk through the entire index and heap to find the desired rows. The only way to optimize this process is by using a deferred join, which skips the discarded rows and fetches only the necessary data from the heap.
This method reduces the number of buffer pages from 110,659 to 27,408 and cuts the execution time from 1,172 ms to 800 ms.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
