From a plan to rows

The PostgreSQL planner turns a query into a tree of plan nodes, the tree EXPLAIN prints. The executor runs it on demand: the top node asks its child for a row, which asks its own children, and so on (the Volcano or iterator model). Some nodes return rows as they get them (Seq Scan, Index Scan, Nested Loop, Merge Join, GroupAggregate, Limit); others must consume their whole input first (Hash, HashAggregate, Sort). The animation draws the tree top left, highlights the node at work, and counts the rows each node returns, as EXPLAIN ANALYZE does.

Plan tree for the All three query: Sort on total DESC (2 rows) over HashAggregate on c.city (2) over Hash Join (8), whose children are Seq Scan on orders (8) and Hash (5) over Seq Scan on customers (5). Sort, HashAggregate and Hash must read all their input first; the rest pass rows on at once.
EXPLAIN's tree is the executor's call tree: each node pulls rows from its children, and Sort, HashAggregate and Hash consume their whole input before returning anything.

The data is tiny so every step fits on screen, but the plans are the ones the planner chooses when the tables are large:

CREATE TABLE customers (id int PRIMARY KEY, name text, city text);         -- 5 rows
CREATE TABLE orders    (id int PRIMARY KEY, cust_id int, amount int);      -- 8 rows
CREATE INDEX idx_cust_amt ON orders (cust_id, amount) INCLUDE (id);

A PostgreSQL table is a heap: rows in no particular order, with every index pointing into it. Because idx_cust_amt holds every column these queries need from orders, the planner can use an Index Only Scan, which skips the heap for pages the visibility map marks all-visible (after VACUUM, Heap Fetches: 0). The plan control forces a join or aggregate method, as SET enable_hashjoin = off and friends would; work_mem can be made to hold only 3 rows.

JOIN: three methods

  • Nested Loop. For each outer row, run the inner plan. With an index on the inner join column that is one index probe per outer row: ideal when the outer side is small (here, the 2 customers in 'HN'). Without an index the inner side is scanned again for every outer row, which is why the planner avoids that except for tiny inputs (usually with a Materialize node so the rescans come from memory).
  • Hash Join. The Hash node reads the inner input (the smaller one) into a hash table in work_mem × hash_mem_multiplier; then the outer input is read once and each row probes its bucket. Equality joins only. If the table would not fit, the join runs in batches: rows are split by hash, batch 0 stays in memory and the other batches of both inputs go to temporary files, to be joined one batch at a time (Batches: 2 in EXPLAIN ANALYZE).
  • Merge Join. Both inputs sorted on the join key (from an index or a Sort node) are walked side by side: advance whichever side has the smaller key, return pairs when the keys are equal. Each input is read once. Good for large inputs that are already sorted, and it works for range-ordered data where a hash table would be too big.
The HN join three ways. Nested Loop: Ann 1 and Cid 3 each probe the index and find orders 102, 106 and 104, 101, 107. Hash Join: a hash table 1 → Ann, 3 → Cid is built and all 8 orders probe it, 5 match. Merge Join: customers 1 and 3 and orders sorted by cust_id are walked side by side and matching keys are paired.
Nested Loop probes per outer row, Hash Join builds and probes, Merge Join walks two sorted inputs together; all three return the same 5 rows.

GROUP BY: two methods

  • HashAggregate. One hash-table entry per group, updated by each input row; groups are returned at the end in hash order. Input order does not matter. Since PostgreSQL 13 it spills to disk if the groups exceed work_mem × hash_mem_multiplier.
  • GroupAggregate. Needs input sorted on the group key (from an index scan or a Sort) and keeps only the current group: when the key changes, the group is complete and is returned at once. Constant memory, and it can feed a LIMIT without reading everything.
Left: GroupAggregate reads orders sorted by cust_id and returns each group (1: 2 rows 55, 2: 1 row 60, 3: 3 rows 110, 5: 2 rows 90) as soon as the key changes. Right: HashAggregate reads orders in table order into a hash table of groups and returns them in hash order only after all input is read.
GroupAggregate needs sorted input but keeps one group at a time; HashAggregate takes any order but holds every group until the end.

PostgreSQL has no loose index scan for GROUP BY: for MAX(amount) per cust_id it reads every index entry, where MySQL reads one per group. (A recursive CTE can emulate the skip.)

ORDER BY: Sort methods

  • An index in the right order needs no Sort node at all.
  • quicksort: all rows fit in work_mem, so they are sorted in memory.
  • top-N heapsort: a Limit above tells the Sort that only N rows are wanted (a bounded sort). It keeps a heap of the best N and drops every other row.
  • external merge: the rows do not fit. Sorted runs are written to temporary files in base/pgsql_tmp and merged at the end. log_temp_files logs these files; seeing them for a common query is a sign to raise work_mem for it.
  • (Not drawn: Incremental Sort, when the input is already sorted on a prefix of the keys, sorts each group of equal prefix separately.)

All three in one query

For SELECT c.city, COUNT(*), SUM(o.amount) AS total … GROUP BY c.city ORDER BY total DESC, the plan stacks Sort → HashAggregate → Hash Join. Joined rows flow straight into the aggregate; the final Sort can only start once all groups are known. Force GroupAggregate and a Sort by city appears between the join and the aggregate.

Reading EXPLAIN ANALYZE

You seeIt means
Nested Loop with an inner Index Scan, loops=None index probe per outer row
Hash … Batches: 2 (or more)the hash table did not fit in memory; temporary files were used
Sort Method: external merge Disk: …the sort spilled; consider more work_mem
Sort Method: top-N heapsorta bounded sort under a Limit
Index Only Scan … Heap Fetches: 0answered from the index; the pages were all-visible
HashAggregate vs GroupAggregatehash table of groups vs sorted input, one group at a time

What the animation leaves out

  • Cost estimates: the plans are fixed per query and setting, as the planner would choose them for large tables. On 8-row tables the real planner would often just use sequential scans.
  • Parallel query (Gather, parallel hash join), Materialize and Memoize nodes, Incremental Sort, and the exact bucket and batch arithmetic of the hash join.
  • Row-at-a-time interleaving in Merge Join: its two input streams are drawn first, then the merge.

Practical consequences

  • Index foreign keys used in joins; PostgreSQL does not create those indexes for you.
  • work_mem applies per sort or hash node, per query, per parallel worker: raise it for the session that runs the big report, not globally.
  • Keep tables vacuumed so index-only scans stay index-only.
  • Check EXPLAIN (ANALYZE, BUFFERS): estimated rows far from actual rows is the usual cause of a bad join method; ANALYZE the table or add extended statistics.