Why is a large OFFSET slow?

OFFSET still computes and discards every skipped row. OFFSET 100000 does the work for 100,000 rows before returning the next page. Keyset ('seek') pagination — WHERE id > :last ORDER BY id LIMIT n — stays fast at any depth.

Limit

Appears in EXPLAIN asLimit

Stops after N rows (and skips OFFSET rows).

What it is

A Limit node passes through at most LIMIT rows after discarding OFFSET rows, then stops pulling from its child — which can let the whole plan finish early.

When the planner picks it

For LIMIT / OFFSET and FETCH FIRST queries.

Is it good or bad?

Cheap and powerful: a 'top-N' Limit over an index that already supplies the order is one of the fastest things PostgreSQL does. Large OFFSETs are the anti-pattern — the skipped rows are still produced and thrown away; prefer keyset pagination.

In depth

What "early stop" really means

A Limit node doesn't filter at the end — it stops pulling from its child once it has enough rows. The child sees a closed cursor and shuts down. For a plan whose child can return rows incrementally (an Index Scan, an Append, a Sort that uses top-N heapsort), this means the rest of the work simply never happens.

That's why ORDER BY … LIMIT 20 over an index can be effectively free even on a billion-row table: the Index Scan returns 20 rows in order, the Limit stops it, and nothing else runs.

Top-N heapsort

When a Sort feeds a Limit, PostgreSQL detects it and switches to a top-N heapsort — it keeps only the top N rows in a small heap and discards the rest as it scans. The plan shows it:

Sort Method: top-N heapsort  Memory: 27kB

Even over a huge input, that uses tiny memory and never spills — provided the Limit is small enough.

OFFSET still does the work

Large OFFSET defeats the early stop: PostgreSQL has to produce every skipped row before discarding it. OFFSET 100000 runs work for 100,000 rows even though you see none. Use keyset (seek) pagination instead:

SELECT id, ...
FROM events
WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;

That stays fast at any depth because the index lets the scan start exactly where the previous page ended.

Settings that influence it

enable_incremental_sort

How we tune these →

FAQ

Why is a large OFFSET slow?: OFFSET still computes and discards every skipped row. OFFSET 100000 does the work for 100,000 rows before returning the next page. Keyset ('seek') pagination — WHERE id > :last ORDER BY id LIMIT n — stays fast at any depth.