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.

See a blocking node in action

That claim isn't hand-waving. ORDER BY … LIMIT 3 still makes the Sort read all 100,000 rows before it can emit the smallest three — look at the Sort node's actual rows:

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

Now give it an index that already returns rows in v order. The Sort disappears, the Limit short-circuits an Index Scan, and the cost falls from ~2,700 to a fraction:

sql · live postgresan ordered index turns the blocking sort into a short-circuit
⌘/ctrl + enter

Sorts, hashes, and work_mem

Blocking nodes have to hold all that buffered data somewhere, and that somewhere is capped by work_mem — allotted per sort or hash, per node. While the data fits, a sort runs entirely in memory (a fast quicksort); when it overflows, it spills to disk as an external merge sort — far slower. Watch the same sort switch modes when work_mem is tiny:

sql · live postgresthe same sort — on disk, then back in memory
⌘/ctrl + enter

The Sort Method: line tells you which happened. Because work_mem is charged per node, one query with several sorts and hashes can use several multiples of it at once — and multiplied across many connections that adds up dangerously fast, which is why it's set low (a few MB) and raised per-query only when a specific query needs it.

loops, and why nested loops multiply

In EXPLAIN ANALYZE, an inner node's actual time and rows are reported per loop. A Nested Loop runs its inner side once per outer row, so the inner node shows loops=N — and its real total is actual time × loops. Here three ids drive three passes over the inner scan of t, so the inner node reads loops=3:

sql · live postgresthe inner side runs once per outer row
⌘/ctrl + enter

Reading that multiplication is how you catch a nested loop being executed thousands of times — the classic slow-query signature. And it's where the Planner lesson comes back to bite: if a bad row estimate makes the outer side far bigger than expected, a nested loop meant to run a handful of times explodes into millions of inner scans.

// 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: a Sort under ORDER BY … LIMIT still reads the whole table (100k rows here, cost ~2700), so an ordered index that removes the sort (cost ~0.4) is dramatically faster.
  • 05Sorts and hashes are capped by work_mem, per node: within it a sort is an in-memory quicksort, beyond it it spills to disk as an external merge (much slower). It's set low because it multiplies across nodes and connections.
  • 06In EXPLAIN ANALYZE an inner node's time/rows are per loop; multiply by loops for the true cost — a bad row estimate can turn a few loops into millions (a nested loop gone wrong).
  • 07Parser → 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