Concept · SQL & Querying

LIMIT can change the plan; OFFSET pagination re-scans, keyset pagination doesn't.

LIMIT is a row goal — a hint that few rows are wanted — and the optimizer honors it where the pipeline is streamable: scans stop early, ORDER-BY-LIMIT becomes a bounded heap (the TOP_N node keeps the best few without sorting all). It cannot help where the first row depends on all input: aggregates (a hash aggregate builds every group), window functions over full partitions, DISTINCT. The fix lives below the LIMIT: pre-filter, pre-aggregate, or keyset-paginate.

See it break

sightings

name	bright
a	3
b	9
c	5
d	1
e	7

The trap

SELECT
  name
FROM
  sightings
ORDER BY
  bright DESC
;

The fix

SELECT
  name
FROM
  sightings
ORDER BY
  bright DESC
LIMIT
  3;

The trap

The fix

The full sort returned every sighting when only the brightest 3 were wanted — LIMIT is a row goal the optimizer honors where the pipeline streams (ORDER BY + LIMIT becomes a bounded TOP_N heap), but it can't help an aggregate whose first row needs all input.

The rule

Read the plan for the early-stop node; move selectivity below the limit; keyset for deep pagination.

Shows up elsewhere

Reading a plan for two smells OFFSET pagination drift

Try one →Prove it in practice →