LIMIT and OFFSET

Why this stage exists

Since volcano-executor, every operator has worked the same way: asked for a row, it asks the operator below it, does its bit, and hands one row up. So far that was a matter of style; every query read the whole table anyway. LIMIT is where the style pays. select * from t limit 5 needs five rows, and an operator that simply stops asking after the fifth means the scan below it reads five rows out of a million and is never asked for a sixth.

That is the whole stage: a small operator whose most important behaviour is what it does not do. rows_examined, the counter from the index-scan stage, makes it visible.

Stopping early, and when you cannot

limit 5 over a thousand rows examines 5. Add a WHERE that keeps every tenth row, and limit 3 examines 30: the Filter reads rows until it has passed three, and stops being asked. OFFSET is not free: limit 10 offset 100 reads the hundred rows it skips, so it examines 110.

A Sort changes everything. order by name limit 5 must find the five smallest names, and the last row the scan reads might be one of them, so the Sort reads all thousand before it can hand up the first. The Limit still stops after five, but the work below it is already done:

The last useful observation: order by id limit 5 examines only 5, because the planner left the Sort out (the previous stages) and the scan delivers key order by itself. Choosing a plan that can stop early is what the Indexes and the Optimizer part is about.

Order without ORDER BY

In SQL, rows without ORDER BY come back in no promised order, and "LIMIT without ORDER BY returns an arbitrary subset" is the standard warning. In this engine the order is promised: a scan reads the B+Tree from its first leaf to its last, so rows come in ascending key order, and the contract says so. limit 2 is always the two smallest keys the WHERE keeps. Real databases make no such promise, because a parallel scan, a hash join or a different index can change the order; write ORDER BY when you need one.

Laziness, seen through errors

An expression is evaluated only for rows that are asked for. In a table where only row 500 has b = 0, select id, 10 / b from t limit 3 evaluates the division three times and succeeds, while limit 600 reaches row 500 and fails with 01501. Put a Sort below the Limit, order by 10 / b limit 1, and the Sort evaluates its key for every row, so it fails too. And OFFSET is not a way around it: the rows a Limit skips are still asked for, from the Project below it, so limit 1 offset 500 projects row 500 and fails on it, whereas limit 2 offset 400 never reaches it.

At the other end, limit 0 asks for nothing at all, so even a Sort below it never starts: order by name limit 0 examines no row and prints sort_runs=0.

Output contract for this stage

These are the exact lines the tests check. The full definition is on the output contract page, section 46.

An OFFSET past the last row and limit 0 print the header and (0 rows). The Limit node is always at the top of the plan, above the Project.

What you should see when done

$droid --db big.db
Welcome to droid!
>select id, name from t limit 3;
id | name
1 | n919
2 | n838
3 | n757
(3 rows)
[EXEC] rows_examined=3
>select id from t where id % 100 = 0 limit 2 offset 1;
id
200
300
(2 rows)
[EXEC] rows_examined=300
>select id from t order by name limit 2;
id
1000
679
(2 rows)
[EXEC] rows_examined=1000 sort_runs=1
>explain select id from t order by name limit 2;
[PLAN] select id from t order by name limit 2
[PLAN]   └── Limit (count=2, offset=0)
[PLAN]       └── Project (cols=[id])
[PLAN]           └── Sort (key=name ASC)
[PLAN]               └── SeqScan (table=t)
>.exit
exiting.. good bye!

This is the test table: a thousand rows whose names are n and three digits, in an order that has nothing to do with the key. The first query read three rows. The second skipped one matching row and kept two, reading up to row 300. The third read every row, because of the Sort, and only then kept two.

Common bugs in this stage

• Limiting at the end. Collecting every row and printing the first five gives the right rows and rows_examined=1000.
• One row too many. A Limit that asks for the next row before checking its count reads one extra row: limit 5 examines 6.
• Asking the child when the count is 0. limit 0 must examine nothing.
• OFFSET counted against the limit. limit 10 offset 100 hands up 10 rows, not 0 or 90.
• The Limit below the Project. The plan line is in the wrong place, and the skipped rows are no longer projected, so limit 1 offset 500 stops failing (see the next bullet but one).
• Accepting an expression. LIMIT takes a number alone; limit 1 + 1 is a syntax error.
• Skipping rows below the Project. The Limit is on top, so the rows it skips have been projected; a shortcut that skips them earlier hides a division by zero the contract says must fail.
• A Sort that starts before it is asked. Reading the input when the plan is built makes limit 0 examine every row.
• Projecting before the Limit asks. Evaluating every row's select list up front makes limit 3 fail on row 500.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C and C++: the Limit operator is a struct with the child pointer and two counters; nothing else. Parse the number into a uint64_t and compare with 4294967295 before narrowing.
• Rust: if your operators implement Iterator, skip(offset).take(count) is exactly this operator, and take stops calling next on its source.
• Zig: keep the counters as u32 and check the parsed value with std.math.cast before storing it.