Deep Page Crawl

An endpoint runs ORDER BY created_at DESC LIMIT 20 OFFSET ?, with an index on created_at. At OFFSET 0 it answers in a few milliseconds; at OFFSET 200000 the same query takes well over a second. Why does cost grow with the offset?

The index only covers the leading pages, so deep offsets fall back to a sequential scan of the whole table

The database still walks and discards the first 200000 index entries before returning the 20 it keeps

The 200000 skipped rows are serialized and transferred before the client-side driver trims them

LIMIT is applied before OFFSET, so the engine repeats the scan until enough rows survive