Build 1 publisher2 min readPublished September 15, 2026 at 5:06 PM PDT

Postgres walks all 110,659 pages of a 1.2 GB table to answer OFFSET 9999980 LIMIT 20

One dev.to benchmark on 10 million rows measures deep pagination as a full read and discard. A PostgreSQL B-tree stores keys and not ranks, so counting from the start is the only way to reach the 9,999,981st row.

The Engineer · Build desk

Illustration accompanying Postgres walks all 110,659 pages of a 1.2 GB table to answer OFFSET 9999980 LIMIT 20

What happened

A query with OFFSET 9999980 LIMIT 20 touched 110,659 buffer pages and 865 MB of a 1.2 GB table to hand back 20 rows, and EXPLAIN reported all of it.
In the plan, the Limit node returned 20 rows while its Index Scan child on orders_pkey produced 10,000,000, with execution time at 1,172 ms.
OFFSET time tracked depth linearly, ten times the depth for ten times the time, while the keyset cursor stayed between 0.016 and 0.027 ms across a 100,000x change in depth.
The benchmark ran 10 million generated rows on PostgreSQL 17.11 under Docker on a Ryzen 7 5700X with 78 GB of RAM, with shared_buffers left at the default 128 MB.

Compiled by The EngineerSomething wrong?How this is made

Why it matters

cost Serving the last page costs 499,999 rows of tuple construction for every row delivered, and that bill is set by the page number, not by the twenty rows the client asked for.
constraint At about 6.8 times the configured 128 MB shared_buffers, the read set will not sit in the buffer pool, so a bigger cache or a faster disk moves the constant and leaves the curve where it is.
decision Because the cursor is a different complexity class, switching to keyset pagination is a schema and API decision made before launch: the client has to be handed a stable sort key to come back with.
exposure Any endpoint that accepts a page number from the caller lets the caller pick how deep the walk goes, and cost rises in step with the number they send.

The counting happens in the Limit node, one tuple at a time. Limit takes rows from its child, keeps a running count, and drops everything below the 9,999,981st ^[1]. The post puts most of the run in that discard path, with the Index Scan node alone at 934 ms, about 80 percent of execution time ^[5]^[5].

The page count resolves back into the physical table. Buffers reported `shared read=110659`, and at 8 KB a page that is 864.5 MB ^[4]^[7]^[1]. It decomposes as well: the primary key index is roughly 27,000 pages and the heap roughly 83,000, which sum to about 110,000 ^[6]. Both were walked end to end so the plan could emit 20 rows at `width=16`, or 320 bytes ^[3]^[7].

For 865 MB to be the number on your table, your projection has to look like this one. The scan returns `created_at`, which the primary key index does not contain ^[21]. Dropping the heap's 83,000 pages needs a projection that fits inside the index being scanned, and even then the walk still covers about 27,000 index pages, roughly 211 MB ^[3]. The post does not measure that case.

Descending by rank would need subtree counts. Order-statistic trees and ranked B-trees store exactly those, and the post is explicit that PostgreSQL's B-tree is not one of them ^[13]. Its leaves are chained together in key order, so walking is the operation on offer ^[24]. MVCC closes the other route. Visibility is not in the index, and checking it generally means visiting the heap, with the visibility map skipping that only when a page is known all-visible ^[14]. A subtree counter would count index entries, and OFFSET needs the count of rows visible to your transaction ^[15].

The author's own correction is the part to read before defending an existing endpoint. The earlier explanation offered was that OFFSET "skips ahead, and skipping that far costs something", which the post calls "wrong in the one way that matters" ^[16]^[17]. The same care shows in the numbers. The cursor timings sit at 0.016 to 0.027 ms, close to measurement resolution, so the roughly 49,000x ratio is not precise and the claim is only that the column is flat ^[10]. The five-run table put this query at 791 ms where the instrumented plan put it at 1,172 ms, a gap the post attributes to EXPLAIN (ANALYZE, BUFFERS) timestamping every node and charging more the more rows pass through ^[18].

What to watch

Part 2 of the post, which the author says handles created_at repeating every 100 rows, the tie case a keyset cursor has to survive.
A repeat with shared_buffers raised above the default 128 MB, which would separate I/O time from tuple-construction time in the OFFSET curve.
A run on a table larger than the test box's 78 GB of RAM, where the walked pages become real disk reads.

Claim ledger

Ranked by verification strength, evidence, and original report placement.

[9]
The cursor column does not move at all across a 100,000x change in depth; the post calls this a different complexity class, not a constant factor.
ReportedSupported2 sources— sign in to open View cited source
[10]
The cursor timings sit at 0.016 to 0.027 ms, close to measurement resolution, so the about 49,000x ratio should not be read as precise; the claim is that the column is flat.
ReportedSupported2 sources— sign in to open View cited source
[1]
OFFSET 9999980 LIMIT 20 does not skip 9,999,980 rows: PostgreSQL reads every one of them, builds each tuple, and throws it away, with the Limit node discarding 9,999,980 rows and passing 20 up.
ReportedSupportedView cited source
[2]
To hand back 20 rows the query touches 110,659 buffer pages, 865 MB of a 1.2 GB table, and EXPLAIN reports it.
ReportedSupportedView cited source
[3]
The plan at depth 9,999,980 shows Limit (cost=343020.75..343021.43 rows=20 width=16) (actual time=1171.800..1171.803 rows=20 loops=1) above an Index Scan using orders_pkey returning rows=10000000, with Execution Time: 1172.019 ms.
ReportedSupportedView cited source
[4]
The plan reports Buffers: shared read=110659 at both the Limit node and the Index Scan node.
ReportedSupportedView cited source
[5]
Most of the time is the discarding: Index Scan alone is 934 ms of the 1,172 ms total.
ReportedSupportedView cited source
[6]
The primary key index is about 27,000 pages and the heap about 83,000, and they sum to about 110,000, matching the 110,659 buffers in the plan.
ReportedSupportedView cited source
[7]
The Buffers numbers resolve back into the physical sizes at 8 KB per page.
ReportedSupportedView cited source
[8]
OFFSET is cleanly linear in depth: ten times the depth, ten times the time, and Buffers scales with depth, not with the result size.
ReportedSupportedView cited source
[11]
Every query in the benchmark returns exactly 20 rows and selects only id, created_at, with five runs each in the same session and no EXPLAIN attached for the timing table.
ReportedSupportedView cited source
[12]
A PostgreSQL B-tree cannot answer which row is the 9,999,981st when sorted, because rank is not stored anywhere in it: every node knows which key ranges lie beneath it, and none knows how many entries do.
ReportedSupportedView cited source
[13]
Order-statistic trees and ranked B-trees store subtree counts and allow descent by rank; PostgreSQL's B-tree is not one of them.
ReportedSupportedView cited source
[14]
MVCC visibility is not in the index: whether an index entry is visible to your transaction generally requires checking the heap, and the visibility map can skip this only when a page is known all-visible.
ReportedSupportedView cited source
[15]
Even a subtree counter would count index entries, not rows currently visible to the transaction, which is what OFFSET needs.
ReportedSupportedView cited source
[16]
"it skips ahead, and skipping that far costs something"
ReportedSupportedSource: the explanation the post's author gave before measuringView cited source
[17]
The post says that explanation is "wrong in the one way that matters", because nothing jumps.
ReportedSupportedView cited source
[18]
The average timing table said 791 ms for this query while the plan says 1,172 ms; the table is five bare runs and the plan is one run under EXPLAIN (ANALYZE, BUFFERS), which timestamps every node and charges more the more rows pass through.
ReportedSupportedView cited source
[19]
The ten million generated rows have one deliberate property: created_at repeats every 100 rows, because real order tables have ties within the same second, and the post says that matters in part 2.
ReportedSupportedView cited source
[20]
The environment was PostgreSQL 17.11 (postgres:17-alpine), Docker 29.4.1, a Ryzen 7 5700X with 78 GB RAM, and shared_buffers left at the default 128 MB.
ReportedSupportedView cited source
[21]
The scan also returns created_at, which is not in the primary key index.
ReportedSupportedView cited source
[22]
The orders table has id bigint PRIMARY KEY, created_at timestamptz, customer_id bigint, status varchar(16) and total_amount integer, with an index on (created_at DESC, id DESC).
ReportedSupportedView cited source
[24]
The B-tree leaves are chained together in key order, so walking the whole set is easy, and walking is the only thing on offer.
ReportedSupportedView cited source
[d1]
110,659 buffer pages at 8 KB a page is 864.5 MB, which is the 865 MB the post reports.
DerivedSupported1 source— sign in to open
[d3]
Walking the primary key index alone, without the heap pages, is about 27,000 pages or roughly 211 MB.
DerivedSupported1 source— sign in to open
[d4]
The deep page reads 499,999 rows for every row it returns.
DerivedSupported1 source— sign in to open
[d5]
The Index Scan node accounts for about 80 percent of the query's execution time.
DerivedSupported1 source— sign in to open
[d6]
The 865 MB read set is about 6.8 times the configured shared_buffers of 128 MB.
DerivedSupported1 source— sign in to open
[d7]
Twenty rows at the plan's width=16 is 320 bytes of output.
DerivedSupported1 source— sign in to open

Sources

Publishers with included, body-backed reporting in this cluster.

dev.to
1 article · September 15, 2026
OFFSET Doesn't Skip Rows: What 10M Rows in PostgreSQL Actually Cost

Ask Clarity

Have a question this piece left open? The answer comes from its reporting and claim ledger — nothing else.

Try

Clarity analysis

How the coverage leans and what the record actually supports. Read the methodology

Perspective Coverage

How much of the coverage speaks to builders, operators and investors. It measures emphasis, not whether the story is right.

The two numbers to read sit on adjacent lines of the plan dev.to quotes: Index Scan rows=10000000, Limit rows=20. Everything else follows from that gap. The post also splits the cost of discarding into two parts, tuple production and heap visits, and points out that created_at is absent from orders_pkey, so every one of the ten million discarded rows fetches from the heap. Its deferred join gets the scan to Heap Fetches: 0 and buffers of 27,408, and execution falls only from about 1,172 ms to 800 ms, so pages were not what dominated. rows=10000000 is unchanged. What the buffer and timing figures show is that the cost tracks page depth, not row count.

Reality

Evidence, adoption, hype gap, incentives and confidence, each read independently from the current record on the scale shown beneath it. How these are measured.

Evidence72

0100

Plans quoted, buffer counts reconcile

The post shows its working. Plan text is reproduced with actual times and Buffers lines, 110,659 pages at 8 KB lands on the 865 MB it claims, and the roughly 27,000-page index plus roughly 83,000 heap pages adds back to the same figure. The schema, the generated data's one quirk and the container image are all disclosed. Against that, the per-depth timing table behind the 49,000x ratio is not visible in the text we hold, it was five bare runs on one desktop machine, and dev.to is the only place any of these numbers appear.

Adoption20

0100

One container, one desktop

All of it happened inside a single postgres:17-alpine container on a Ryzen 7 5700X, with one session and the buffer pool untouched. The behaviour belongs to PostgreSQL's stock B-tree and should reproduce elsewhere, though no second run appears in our coverage and no production system is cited.

Hype gap−12

−100 understated0 aligned+100 overstated

The author argues his own headline number down

The biggest figure in the post is the one it tells you to distrust: 49,000x, which it caveats because the cursor timings sit between 0.016 and 0.027 ms, close to what the clock can resolve. It also refuses to compare its averaged table with its plans, calling them different instruments. The claim it does press, that OFFSET cost grows with depth because the index stores no rank, comes from the structure of a B-tree and does not lean on the ratio.

Incentives25

0100

Part one of two, no vendor present

The only pull visible is toward the sequel: the generated rows repeat created_at every 100 rows because, the post says, that matters in part 2. No product, sponsor or managed-database vendor appears, and the framing is a correction of an explanation the author had given himself.

Confidence66

0100

Design claims checkable, timings unreplicated

Two kinds of claim sit side by side here. That a PostgreSQL B-tree stores key ranges without subtree counts, and that visibility usually needs a heap check the visibility map can only sometimes skip, are matters of documented design a reader can confirm. The millisecond and page figures are one person's runs in Docker, carried by one publisher, on hardware nobody else has repeated them on.

Topics and entities

Follow any of these and your For You feed starts watching them — no settings page required.

Topics

Entities

build
Eleven agent sessions on one machine settled CPU contention by writing to each other
1 publisher
build
In Postgres an UPDATE is an insert, and your page space pays for it
1 publisher
build
Postgres 18 hands the audit row to the statement that overwrote the balance
1 publisher
build
Why an uncommitted DELETE of a million rows is invisible: nothing was ever deleted
1 publisher

What happened

Why it matters

What to watch

Claim ledger

Sources

Let Clarity write the post for you.

Topics and entities

Topics

Entities

Related stories

Claim ledger

Let Clarity write the post for you.

Topics and entities

Topics

Entities

Perspective Coverage

Reality

Related stories

Sources