postgres://internals

The Executor

// the problem

The planner picks a plan; the executor runs it. But it doesn't compute each step fully and hand a big result to the next — it works like a set of nested straws. The top node asks for one row, that request cascades down to a scan, and a single row flows back up through the whole tree. This pull-based design (the “volcano” model) is why a LIMIT can make a query touch only a handful of rows instead of the whole table.

Pull rows one at a time and watch them flow up the plan tree. Then notice the readout: with a LIMIT, the scan stops as soon as enough rows have come out.

fig.01 · the executor (pull model)SELECT v FROM t WHERE v is even LIMIT 3
rows scanned
0
rows returned
0
limit
3
status
running

plan tree

Limit
stop at 3
↑ one rownext() ↓
Filter
v is even
↑ one rownext() ↓
Seq Scan
table t

table t — one row pulled at a time

385294761101215141120131816192224211723

amber = passed the filter and flowed up · struck = dropped · cyan ring = next to scan

each pull asks the Limit, which asks the Filter, which asks the Scan for one more row — rows are never all materialized at once

A plan is a tree of iterators

Every plan node — Seq Scan, Filter, Sort, Hash Join, Limit, Aggregate — implements the same tiny interface: next(), “give me one more row.” A node answers by calling next() on its children as needed:

  • Seq Scan.next() reads and returns the next tuple from the heap.
  • Filter.next() pulls rows from its child and returns the first that passes the condition (dropping the rest).
  • Limit.next() passes rows through until it has returned N, then reports “done.”

Execution starts at the root pulling one row, which pulls from its child, all the way down to a scan — then the row travels back up. Repeat until the root says done.

Why pull, not push? Pipelining.

Because rows are produced on demand, nodes are pipelined: a row can flow from the scan all the way to the output without the intermediate steps materializing their full results. The big payoff is early termination — a Limit (or an EXISTS, or a semi-join) can stop pulling the moment it's satisfied, and everything beneath it stops too.

Watch it for real. With a LIMIT, the scan under it reads only enough rows — look at the tiny buffer count and row count on the scan node:

sql · live postgresrun me first
⌘/ctrl + enter

Compare that to counting all matches — now the scan must read the whole table:

sql · live postgreseditable — run it
⌘/ctrl + enter

The LIMIT version's scan shows actual rows and Buffers far smaller — the executor simply stopped pulling.

// note · blocking nodes break the pipeline

Not every node can stream. A Sort must read all its input before it can emit the first row (it can't know the minimum until it's seen everything); a Hash Join must build its whole hash table first. These are blocking nodes — a LIMIT above a Sort still forces the full sort. That's why an index that already provides the needed order can be dramatically faster for ORDER BY … LIMIT: it skips the blocking sort entirely.

loops, and why nested loops multiply

In EXPLAIN ANALYZE, an inner node's cost is per loop. A Nested Loop that runs its inner side once per outer row shows loops=N on the inner node — and its real total is actual time × loops. Reading that multiplication is how you spot a nested loop that's being executed thousands of times.

// why it matters · this is the whole query lifecycle

Parser → Planner (picks the plan) → Executor (this lesson, pulls rows through the tree) → access methods (index & heap, from the B-tree and Storage lessons) → buffer cache & WAL. You've now seen every stage a query passes through.

Your turn

// challengeuses table t from the setup cell above

Prove that LIMIT lets the executor stop early. Add a clause so the query returns just 5 rows where v = 7, and show the plan gains a Limit node on top.

⌘/ctrl + enter
// challengeuses table t from the setup cell above

How many rows actually match v = 7 in the whole table? Return the count.

⌘/ctrl + enter

// what you now understand

  • 01The executor runs a plan as a tree of iterators; every node exposes next() — 'give me one row'.
  • 02Execution is pull-based: the root pulls a row, the request cascades to a scan, and one row flows back up — repeated until done.
  • 03Pull-based execution is pipelined, so a Limit (or EXISTS/semi-join) can stop early and the nodes beneath it stop too — touching far fewer rows.
  • 04Blocking nodes (Sort, Hash build) must consume all input before emitting a row, so a Limit above them doesn't help — an ordered index can skip the sort.
  • 05In EXPLAIN ANALYZE an inner node's time is per loop; multiply by loops for its true cost (how you catch a nested loop running too many times).
  • 06Parser → planner → executor → access methods → buffers/WAL is the full path of every query.

// self-test

A query is `SELECT * FROM big WHERE status = 'x' LIMIT 10`. Roughly how much of the table does the executor read?

// self-test

Why doesn't `ORDER BY created_at LIMIT 10` stop early the way a plain `LIMIT` does — unless there's an index on created_at?

// go deeper

nextDebugging Slow Queries