Skip to content

PATTERN Cited by 1 source

Deferred join for offset pagination

Pattern: when an application needs to paginate a large table with LIMIT N OFFSET M and M becomes large, rewrite the query as an inner index-only primary-key selection + outer primary-key hydration, so the expensive clustered-index lookups apply only to the N rows actually returned, not to the M + N rows the naive query would walk past.

(Source: sources/2026-04-21-planetscale-introducing-fastpage-faster-offset-pagination-for-rails-apps.)

The rewrite

Before — single wide query:

SELECT * FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 10000;

After — two-phase rewrite. Phase 1: index-only select of primary keys.

SELECT id FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 10000;

Phase 2: hydrate exactly those primary keys.

SELECT * FROM posts WHERE id IN (…50 ids…) ORDER BY created_at DESC;

Equivalently as a single nested query (the shape in High Performance MySQL):

SELECT * FROM posts
INNER JOIN (
  SELECT id FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 10000
) AS lim USING(id);

The first query is an index-only scan — the secondary index on created_at already contains both the sort key and the primary-key pointer, so the engine walks 10,050 leaf entries without touching the clustered index. The second query is 50 primary-key point lookups, one per returned row.

The cost transformation

Given table T with secondary index IDX_K on the sort/filter columns:

Phase Baseline Deferred join
Index-only entries walked M + N M + N
Clustered-index hydrations M + N N
Full rows materialised M + N N
Rows returned N N
Round-trips 1 2 (or 1 nested)

The M + N clustered-index lookups collapse to N — for deep pages where M >> N, this is the dominant cost and the rewrite wins strongly.

Quantified: Coutermarsh benchmark

1M-row AuditLogEvent table, indexed on both owner and created_at:

Path At offset 100 At offset 2000 pages
Baseline (1 query) 1,228.7 ms Steeply climbing
Deferred join (2 queries) 456.9 ms + 0.4 ms = ~457 ms Near-flat
Speedup 2.7× Much larger

The chart in the post shows the baseline line climbing steeply across 2000 pages while the deferred-join line stays near-flat — the asymptotic cost difference widens superlinearly.

Recipe

  1. Identify a candidate query: ORDER BY key LIMIT N OFFSET M where M routinely gets large.
  2. Verify the sort/filter columns are indexed. Without a matching index the first query can't be index-only and the optimisation's premise collapses. For filtered paginations, a composite index on (filter_col, sort_col) is often correct.
  3. Rewrite to the two-phase shape. Either nested subquery (one round-trip, relies on planner) or two separate round-trips (what fast_page does — better for slow-query-log observability).
  4. Add a tiebreaker to ORDER BY to keep both phases stable against concurrent writes: ORDER BY created_at DESC, id DESC.
  5. Apply conditionally — the rewrite is a loss for shallow pages (round-trip tax > savings). Use a page- depth threshold: patterns/conditional-optimization-by-page-depth.
  6. Benchmark on your own data. Crossover depth depends on row width, index depth, and network RTT. Coutermarsh uses params[:page] > 5 as a starting point and explicitly recommends empirical confirmation.

ORM integrations

Rewriting at the ORM layer is more ergonomic than writing the SQL by hand. Two PlanetScale- / Hammerstone-authored gems make this a one-method-chain change:

Ecosystem Package Invocation
Rails planetscale/fast_page .fast_page
Laravel hammerstonedev/fast-paginate ->fastPaginate()

Sample Rails usage (verbatim from post):

posts = Post.all.page(params[:page]).per(25)
# Use fast page after page 5, improves query performance
posts = posts.fast_page if params[:page] > 5

Anti-patterns

  • Applying unconditionally at OFFSET 0. The two-round- trip tax makes shallow pages slower. Measure the crossover and gate with a page-depth check.
  • Missing ORDER BY. Without ORDER BY, the first query has no index to prefer and the optimiser may table-scan anyway.
  • Sort key not indexed. Same failure mode — the first query is no longer index-only.
  • SELECT * when a narrower covering index already exists. If the entire required column list fits in one index, the baseline is already fast and the rewrite is overhead for no gain.
  • Treating it as a cursor-pagination substitute. For next/previous-only UX, cursor pagination is strictly better — O(1) vs O(M + N). Deferred join is the offset-preserving middle ground, not a replacement for cursor where cursor applies.

Relationship to other patterns

  • patterns/conditional-optimization-by-page-depth — the natural companion. The deferred-join rewrite is one concrete instance of a cost transformation that's a win at one operating point and a loss at another.
  • concepts/cursor-pagination — the competing alternative. Dominates on performance, loses on random-page UX.
  • concepts/composite-index — when the query has a WHERE predicate, a composite index on (predicate_col, sort_col) keeps the first query index-only despite the filter.

Seen in

Last updated · 378 distilled / 1,213 read