Why does LIMIT 20 OFFSET 200000 get slower as the offset grows?
answer
- cost tracks the offset, not the page size
- the engine cannot address row 200,001
- rows before the page are produced, then thrown away
- scan-and-discard
basics
~20 sOFFSET is scan-and-discard. The engine still produces the first 200,000 rows in ORDER BY sequence and throws them away before emitting 20, so the work grows with the offset and deep pages get steadily slower.
solid answer
~50 s`OFFSET n` does not tell the engine to *start* at row n+1 — a result set has no addressable row numbers. The only way to know which row occupies position 200,001 in the `ORDER BY` sequence is to produce the 200,000 rows in front of it, count them, and discard them. The work is therefore proportional to `offset + limit`, not to the page size: page 1 touches 20 rows, page 10,000 touches 200,020. An index on the sort columns helps by supplying the order without a separate sort, but it still cannot skip — the engine walks that many entries anyway, plus any table lookups those rows require. The symptom is classic: page 1 is instant, page 1,000 times out. The fix is keyset (seek) pagination, where the page boundary is a *value* you compare against instead of a count of rows to throw away.
go deeper
Be ready to say plainly that OFFSET skips by producing and discarding rows, so cost grows with the offset. Knowing that page 1 and page 1000 do very different amounts of work is the whole screening answer.
Explain the cost as offset + limit rows produced, and why an index removes the sort but cannot skip ahead. Be able to name keyset pagination as the structural fix.
Show you have diagnosed this in production: latency correlating with page number, a crawler or export loop turning quadratic, deep pages hitting statement timeouts. Say what you capped or rewrote and how you confirmed the improvement.
Own the product-level call: numbered pages are a UI promise that forces offset semantics. Decide whether to cap reachable depth, replace paging with filtered search, or expose next/prev cursors only, and make that policy consistent across every listing endpoint.
## What OFFSET actually asks the engine to do `SELECT ... ORDER BY created_at DESC LIMIT 20 OFFSET 200000` reads as "skip 200,000 rows, then give me 20". It is tempting to hear "skip" as "jump straight to", but a SQL result set is not an array and carries no positional index the engine can address. The rows occupying positions 1..200,000 are *defined by the query*: they are whatever the `WHERE` clause admits, arranged by the `ORDER BY`. There is no way to know which row sits at position 200,001 without first determining the 200,000 rows ahead of it. So that is what happens. The engine produces those rows in order, counts them off, discards them, and only then starts handing rows to the client. Nothing about the offset reduces the work; it *is* the work. ## The cost model Rows produced ≈ `offset + limit`. That single line explains everything you observe: - Page 1 (`OFFSET 0`) produces 20 rows. - Page 100 (`OFFSET 1980`) produces 2,000. - Page 10,000 (`OFFSET 199980`) produces 200,000. The cost is linear in the offset and essentially independent of the page size. Each produced row is not free either: the engine reads an index entry or a table row, and often both when the query selects columns the index does not carry. If no index supplies the `ORDER BY` sequence, it is worse. The engine must order the qualifying set itself, and a small `LIMIT` no longer prunes much — a top-N strategy still has to retain `offset + limit` entries, so `LIMIT 20 OFFSET 200000` needs the top 200,020, not the top 20. ## Why an index does not rescue deep offsets An index on the sort columns removes the *sort*, letting the engine read entries already in order and stop as soon as it has enough. That is a real and large win, and it is why `ORDER BY created_at DESC LIMIT 20` with no offset can be near-instant on a huge table. But an index entry has no ordinal: there is no "give me the 200,001st entry" operation. The engine still walks entries one by one. The index converts a sort into a scan; it does not convert a scan into a seek. ```sql -- Same index, same table, two very different amounts of work SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 20; -- ~20 entries SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 200000; -- ~200,020 entries ``` ## Paging through everything is quadratic The trap compounds when something walks *all* the pages — an export job, a crawler, a "sync everything" loop. Paging an N-row table p rows at a time costs roughly p + 2p + 3p + ... ≈ N²/(2p) row visits. For a million rows at 20 per page that is about 25 billion row visits to read a table that a single ordered scan reads in one million. The loop starts fast and ends unusable, which is why it usually passes review and fails in production. ## What it looks like in production - Page 1 responds in milliseconds; page 1,000 takes seconds; page 20,000 hits the statement timeout. - Latency correlates with the page number in your metrics, not with the result size. - A single client walking deep pages consumes far more database CPU and I/O than its request rate suggests. ## What to do about it The structural fix is keyset (seek) pagination: remember the sort-key values of the last row shown and filter with `WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20`. Every page then costs the same as page 1, because the engine positions on a value instead of counting rows. When keyset is not an option — the UI genuinely needs numbered pages, or the sort key is user-chosen and volatile — the mitigations are policy rather than SQL: cap the maximum reachable offset, replace numbered pages with "load more", or narrow the result with better filters so the deep pages stop existing. Small offsets are perfectly fine; it is the tail that hurts.
- Does adding an index on the ORDER BY column make a deep OFFSET fast?No. It removes the sort, so the engine reads entries already in order and can stop early — a big win for `LIMIT` without an offset. But index entries have no ordinal, so reaching position 200,001 still means walking 200,000 entries. An index turns a sort into a scan; only a value predicate turns a scan into a seek.
- Roughly what does it cost to walk an entire million-row table 20 rows at a time using LIMIT/OFFSET?About N²/(2p) row visits — roughly 25 billion for a million rows at 20 per page, versus one million for a single ordered scan. The loop is quadratic: each page costs a little more than the last, so an export that looks fine in testing becomes unusable at full data volume.
- Is OFFSET ever acceptable?Yes, for shallow offsets. The cost is `offset + limit`, so the first few dozen pages of a normal listing are cheap, and OFFSET keeps the query trivial while letting users jump to page 7. It becomes a defect when offsets reach the thousands, or when a job walks every page of a large table.
saying these in an interview costs you the question
- Says OFFSET makes the engine start reading directly at row 200001
- Claims an index makes deep OFFSET constant time
- Thinks only the 20 returned rows are actually read
- Blames network or result serialization for deep-page latency
- Suggests a bigger page size as the fix