DEV Community: JoongHyuk Shin

Closing Chapter 1: From Query to Data

JoongHyuk Shin — Sun, 21 Jun 2026 09:18:04 +0000

We opened Chapter 1 with a single line, SELECT * FROM users WHERE id = 1. For that line to leave the client and come back as a result row, the PostgreSQL backend went through five stages. First it decided which processing path the message should take; then the parser and analyzer turned the text into a tree and gave it meaning from the catalog. The rewriter expanded views and injected policies to transform the tree, the planner weighed the possible execution paths by cost and picked the cheapest one, and the executor followed that plan, pulling up one tuple at a time and sending them back to the client.

Chapter 1 was a story about how a query is processed. What tree a given SQL becomes, what plan it turns into, in what order it runs. From start to finish, a chain of logical transformations.

But what every one of those stages ultimately deals with is data. The executor pulls up tuples, yet where on disk those tuples lie and in what shape, how they come up into memory, Chapter 1 never asked. When the planner judged an index scan cheaper than a sequential scan, it never opened up what that index physically is. Chapter 1 followed only the logical journey of a query, leaving untouched the substance of the data that journey stands on.

Chapter 2, Storage & Access Methods, opens up that substance. In what unit data sits on disk (page), where disk and memory meet (buffer manager), where and how a row survives (heap), and how that row is found quickly (B-tree and the specialized indexes). The very tuple the planner weighed by cost and the executor pulled up in Chapter 1, where it actually came from and how it came to be there, is what Chapter 2 reveals.

If Chapter 1 was the logical life of a query, Chapter 2 is the physical dwelling of data. We now look at how the data a query reaches for actually lives on disk.

1.5.5 Parallel Query: How Worker Processes Cooperate

JoongHyuk Shin — Sun, 21 Jun 2026 09:16:59 +0000

The executor we have seen so far is a single flow: one process pulls the plan tree from the top. An aggregate or join over a large table forces that one process to read every page from start to finish, so even when several CPU cores sit idle, only one of them works. Parallel query hands that work to several processes. But PostgreSQL's parallelism is not threads inside one process. It is separate processes cooperating. Since processes do not share memory, three structural challenges follow: distributing pages, sharing state, and forbidding writes.

Gather: Parallelism Happens in Only One Stretch of the Plan Tree

A parallel query does not run the whole plan tree in parallel. Somewhere in the tree sits a node called Gather, and only the subtree below that Gather is split across processes. Everything above Gather runs in a single process, as usual.

Gather does two things. It launches the worker processes, and it merges the rows those workers produce together with its own rows into a single output stream for its parent. The process that launches the workers is called the leader. The leader does not just sit and collect: it also runs the subtree below it and produces rows itself. So with two workers, three participants run the same plan, counting the leader.

This only works under one condition. The subtree below Gather must produce no duplicate rows even when the leader and the workers each run it independently. If three participants run the same subtree with no coordination, every row comes out three times and the result triples, which is wrong. The node that solves this is a 'parallel-aware' node. A parallel-aware scan node coordinates among the processes so that each one reads a different part of the table even though they run the same node. This is the node EXPLAIN shows as Parallel Seq Scan. A plain Seq Scan has one process read the whole table; a Parallel Seq Scan has several processes running the same node split the pages among themselves.

How Workers Split Pages Without Overlap

So three workers read the same table. How do they avoid reading the same page twice? The answer is surprisingly simple. There is one counter in shared memory, and they advance it atomically to divide the pages. (Atomic here means that even when several processes touch the same value at the same instant, the operation completes as one indivisible step, with no one slipping in halfway.)

A table lays out its pages in order, from page 0 to page N. The shared state of a parallel sequential scan holds a counter called phs_nallocated, which says how many pages have been handed out to workers so far. When a worker runs out of pages to read, it does a fetch-and-add on this counter. Fetch-and-add is an atomic operation that reads the counter's current value and adds to it in the same step, so even if several workers call it at the same moment, each one receives a different value. One worker gets "starting at 100," and the next automatically gets the range after it. Coordinating through this single counter means the workers never have to take locks and talk to each other.

There is one more piece to how pages get handed out. A worker does not take pages one at a time. It takes a run of consecutive pages at once, and that run is called a chunk. If a worker took only page 100, then knocked on the counter again and got page 250, its reads would scatter all over the table. From the OS's point of view the disk accesses would look sparse, and read-ahead (the OS prefetching the next pages from disk before they are asked for) would not kick in, because each backend is a separate process and the OS cannot recognize them as one sequential stream. So PG hands a worker a whole consecutive chunk, and only after the worker finishes that chunk does it knock on the counter again for the next one. Since a chunk is contiguous, reads within it are sequential and read-ahead stays alive.

Chunk size is not fixed; it is derived from the table size. PG splits the table into roughly 1024 to 2048 pieces and uses that as the base chunk size, capped at 8192 pages so it never grows too large. Then, as the scan nears the end, when only about 64 chunks remain, it halves the chunk size. This final touch matters. With large chunks, one worker can pick up the last big chunk late and keep reading alone for a long time while the others sit idle with nothing to do. Splitting the tail into smaller chunks spreads the remaining work evenly across the workers, so they all finish at about the same time.

How Workers See the Same Data as the Leader

Splitting the pages is settled. But there is a deeper problem. A worker is a separate process, so it starts fresh, inheriting none of the in-memory state the leader holds. The worker does not know how far the transaction has progressed, which snapshot it is viewing data through (a snapshot is the point in time a transaction sees its data from, and it determines which rows are visible and which are not), or what the GUC settings are. Left like this, a worker could see different data than the leader. For instance, inside the same transaction, a worker could take a different snapshot, so a row invisible to the leader becomes visible to the worker.

The snapshot, transaction IDs, and row visibility (which rows are visible to this transaction right now) that show up here are all covered in detail in Chapter 3, Transactions and MVCC.

PG solves this by copying state. Before launching workers, the leader allocates a piece of dynamic shared memory (a region of memory that several processes look into together, abbreviated DSM) and serializes into it the state a worker needs to behave just like the leader. As soon as a worker starts, it attaches to this DSM and restores that state as its own. The key pieces copied are:

The transaction snapshot and the active snapshot. This is the most direct mechanism for making a worker see data from the same point in time as the leader.
The current transaction's XID and the list of in-progress XIDs. This makes a tuple's visibility check return the same answer in the worker and the leader.
The combo CID mappings. This is the information needed to handle consistently the cases where a visibility decision gets complicated, such as a transaction that created a row and then deleted it.
All GUC values. So a worker runs the plan with the same work_mem and the same settings as the leader.
The authenticated user ID, the database, and the security context. The worker connects to the same database with the same privileges.

Once the copy is done, a worker runs the plan on top of the same transaction, the same snapshot, and the same settings as the leader. So no matter who reads which page, the visibility decisions match.

How do the result rows a worker produces get back to the leader? Through a queue set up inside the DSM. Each worker has its own shared-memory queue (a tuple queue): the worker writes the rows it produces into that queue, and the leader cycles through the queues pulling rows out. From the leader's side, it gathers its own rows and the rows pulled from the workers' queues into a single stream and sends them up.

These queues carry more than results; they carry errors too. When an error happens in a worker, the error message is placed on a dedicated queue and the worker signals the leader. At its next interrupt check, the leader reads that message and re-throws it as if it had raised the error itself. So an error that fired inside a worker still appears to the user as a single, ordinary query error.

Why Parallel Queries Are Read-Only

Parallel query carries one big restriction. While running below Gather, it cannot write to the database and cannot run DDL. The parallel stretch is strictly read-only. This is not an unfinished implementation or a temporary limit; it follows directly from the state-copying structure above.

At the heart of it is the combo CID. When a transaction deletes or updates, within the same transaction, a row it just created, the ordinary XID alone is not enough to decide that row's visibility, so an extra mapping called the combo CID is built. In parallel mode the leader copies its combo CID mapping to the workers at startup. The catch is that this copy happens once, at the start. If a worker were to write data mid-execution, a new combo CID would be created, and there would be no way to tell the leader or the other workers about it. The processes inside the same transaction would then diverge, each holding different visibility information. There is no way to prevent that divergence, so the choice was to forbid writes outright.

PG enforces this read-only rule in code as well. Before building a parallel context, it enters parallel mode and arms checks that block unsafe operations, then disarms them when the parallel work ends. While those checks are armed, attempting a dangerous operation such as a data write or a permanent GUC change fails with an error.

Because of this, the SELECT inside a data-modifying statement like INSERT ... SELECT or UPDATE gets no parallelism, no matter how heavy that SELECT is. The whole statement is a writing transaction.

Not every write is like that, though. CREATE TABLE AS, SELECT INTO, and CREATE MATERIALIZED VIEW are allowed to go parallel. The trick is that the part that runs in parallel is only the read. CREATE TABLE AS ... SELECT has the workers split the reading of its SELECT, while the leader alone loads the gathered results into the new table. The workers still only read, so the read-only rule is not broken. On top of that, the table the results go into is being created at that very moment, so no worker can see or touch it, and it collides with no one's visibility. That is why PG allows parallelism for these three commands.

Gather vs GatherMerge: Dropping Order or Keeping It

There are two kinds of nodes that collect workers' results: Gather and GatherMerge. The difference is how they handle result order.

Gather does not care about order. Whichever worker delivers a row first, that row goes out first, so the workers' results come out interleaved as they arrive. For a query that needs no ordering, such as a plain aggregate or a scan with only a filter, this is the lightest option.

GatherMerge preserves order. It is used when each worker delivers sorted results. Since each worker's output is sorted within itself, the leader merges while preserving the overall sort order: it compares only the front rows of each worker's queue and emits the smallest. To do this comparison efficiently it uses a heap internally (a data structure that quickly yields the smallest among many values). GatherMerge is chosen for an ORDER BY parallel query where each worker sorts separately and the results have to be combined without breaking that sort.

What This Means in Practice

For a job that precomputes a heavy aggregate, pulling the result into a new table and swapping it in by name preserves the parallel gain, rather than loading directly into an existing summary table. An INSERT INTO or UPDATE is a writing transaction and loses parallelism even for the SELECT inside it, whereas CREATE TABLE AS lets the workers split the reading part among themselves.

1.5.4 Aggregation: HashAgg, GroupAgg, Sort-Based

JoongHyuk Shin — Sun, 21 Jun 2026 09:15:54 +0000

A scan node reads rows from a table one at a time, and a join node pairs up rows arriving from its two children. Both can take one input row and emit one result row right away. Aggregation breaks that pattern. In SELECT region, COUNT(*) FROM sales GROUP BY region, producing the single row for region = 'Seoul' requires counting every row that belongs to 'Seoul' first. A group's answer is only settled after all of that group's rows have been seen.

But the executor runs on a pull model that drags results up one row at a time. The top node asks for "the next row," that request travels down the tree, and a row climbs back up one level at a time. The "you have to see everything before you get an answer" nature of aggregation collides, at first glance, with "pull one row at a time." PostgreSQL resolves this in two ways. One is GroupAggregate, which sorts the input by the grouping key so that rows of the same group come out one after another. The other is HashAggregate, which skips sorting and accumulates per-group state in a hash table. The GroupAggregate and HashAggregate nodes you see in EXPLAIN for a GROUP BY query are exactly these two.

Aggregation builds its answer by accumulating state

Before looking at how the two methods differ, we need to see how a single group's answer is built, because both methods share this accumulation engine.

Each aggregate function carries one running state value, the transition value. It starts by setting that state to an initial condition, and every time a row in the group arrives, a transition function updates the state. Once the group's last row is processed, a final function extracts the final result from that state. A comment in the PostgreSQL source sums this up in three lines.

transvalue = initcond
foreach input_tuple do
    transvalue = transfunc(transvalue, input_value)
result = finalfunc(transvalue)

Take SUM(amount). The initial condition is 0, and the transition function is "add this row's amount to the running sum." As rows come in as 10, 20, 30, the state grows 0 → 10 → 30 → 60. SUM has no separate final function because the accumulated value is the answer. AVG, by contrast, accumulates both a sum and a count, and at the end its final function computes "sum divided by count" to produce the average. Whether or not a final function exists varies by aggregate, but the flow of carrying a state, updating it per row, and finishing at the end is the same for every aggregate.

One thing becomes clear here: aggregation has to push every row of a group through the transition function before that group's state is complete. So the key question becomes "how do you gather all the rows of the same group into one state value, missing none?" GroupAggregate and HashAggregate only differ in how they gather; the way they accumulate is identical.

The figure below shows, side by side, how the two methods handle the same input. The grouping key has three values a, b, c, and the number attached to each row is the value to SUM. Adding the numbers within the same key gives that group's result (a is 10 + 20 = 30).

GroupAggregate: catch the point where a group ends in sorted input

GroupAggregate starts from the assumption that the input is already sorted by the grouping key. When it is sorted, rows of the same group come out one after another without a break. If you group by region, all the 'Busan' rows come out, then the 'Seoul' rows follow, then the 'Incheon' rows.

Because they are grouped together this way, you can tell immediately where a group ends. Compare the grouping key of the previous row with the one just received: if they are equal, it is the same group, so add it to the state through the transition function; if they differ, that spot is a group boundary. A changed key means you have seen the previous group's last row, so at that moment you run the previous group's final function, complete one result row, and send it up to the parent. Then you reset the new group's state to the initial condition and start accumulating the new group from the row you just received.

This behavior meshes cleanly with the pull model. GroupAggregate does not gather the entire input and wait. Each time a group ends, it emits that group's result right away. When the parent asks for the first row, it pulls input only as far as the first group's end, returns one result row, and stops. If there is a LIMIT 3 above it, it produces only three groups and never touches the rest of the input. What it holds in memory is just the state value of the one group it is currently processing, so even with millions of groups, only one sits in memory at a time.

The catch is that the "input must be sorted" assumption is not free. If the input arrives already sorted from a grouping-key index, it splits groups with no extra cost; otherwise a Sort node is placed below GroupAggregate to sort the input first, and aggregation only starts after that. In EXPLAIN, whether a Sort hangs under GroupAggregate, or a sorted Index Scan feeds in directly, tells you whether this sorting cost was paid.

HashAggregate: gather groups in a hash table without sorting

HashAggregate drops the sorting assumption and uses a hash table instead. Feeding the grouping key into a hash function yields a fixed slot per key, and the same key always goes to the same slot. So no matter what order the input comes in, you hash a row's key, find that key's slot, and apply the transition function to the state sitting there. A 'Seoul' row, wherever it is scattered across the input, all gathers into the one 'Seoul' slot.

Not needing sorting is HashAggregate's strength. Since there is no need to line up the input in advance, it saves the entire sorting cost in situations where there is no suitable index and a sort would otherwise be required. It shines especially when the number of groups is small. With only a handful of regions, the hash table holds only a few slots, and you can read a large input quickly while just accumulating into those few slots.

In return, a constraint that GroupAggregate did not have appears. HashAggregate cannot emit a single result row until it has pulled in all of the input and completed the hash table. Until the last row arrives, no group can be sure "this is all of this group." So in the build phase it exhausts the input to the end and completes the state of every group, and only then does it scan the table and pull out group results one by one to send up. Unlike GroupAggregate, which emits a result every time a group ends, HashAggregate cannot emit even the first row before the input is fully read. Even with a LIMIT 3 above it, the build phase must read the entire input, so it cannot stop as early as the sort-based method.

When memory runs out: HashAggregate's disk spill

HashAggregate's other weakness is that the hash table takes up memory. When groups are few, only a handful of slots are needed, so it is light; but when the grouping key is nearly unique and groups balloon into the millions, the hash table presses on memory. The memory limit available here is work_mem multiplied by hash_mem_multiplier. work_mem is the amount of memory a single operation like sorting or hashing can use before spilling to disk, and hash_mem_multiplier is a factor that raises that limit several-fold for hash-based operations only. Since its default is 2.0, the hash table can use up to twice work_mem by default.

When this limit is exceeded, HashAggregate enters spill mode. Groups that already have a slot in the table keep accumulating in memory, while a row that would need to create a new group does not claim another slot in memory and is instead written aside to disk. When writing to disk, rows are split across several partitions by the hash value of the key. After reading through the input once and completing the in-memory groups, the partitions stacked on disk are pulled back in one at a time and aggregated the same way. If a partition is too large to fit in memory again, that partition is split into finer pieces.

Concretely, it looks like this. Suppose work_mem is 4MB and you run SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id over 10 million orders. If there are 2 million customers, the hash table needs 2 million slots, and the moment these slots exceed the memory limit (4MB times hash_mem_multiplier) during accumulation, it switches to spill mode. The customer slots that had entered the table up to that point keep adding their sums in memory, while rows of new customers that did not yet have a slot are pushed to disk partitions. After reading through all 10 million orders once, the sums of the customers remaining in memory are completed and emitted as results, and the customers pushed to disk are pulled back partition by partition and finished. Whether a given customer stays in memory or is pushed to disk is just a matter of whether its slot was created before or after the limit was crossed.

This disk split resembles the way HashJoin, seen earlier, splits into batches when the hash table overflows memory. Both follow the same principle of spilling to disk once the hash table exceeds the work_mem limit; they only differ in purpose, with the join matching pairs across both sides and the aggregate accumulating per-group state.

EXPLAIN ANALYZE shows in numbers whether this spill happened. A Batches value greater than 1 on the HashAggregate node means it was split and processed on disk, Memory Usage reports the amount of memory used, and Disk Usage reports how much was written to disk. A Batches of 1 signals that the hash table fit entirely in memory.

Disk spill is a feature introduced in PostgreSQL 13. Thanks to it, HashAggregate can be attempted even when groups are so numerous that the hash table does not fit entirely in memory.

1.5.3 Join Nodes: NestLoop, HashJoin, MergeJoin

JoongHyuk Shin — Sun, 21 Jun 2026 09:14:47 +0000

A scan node sits at the leaf of the tree and pulls rows from a single table. A join node sits in the middle and brings together the rows that its two children send up. It takes one row from users, one row from orders, checks whether they belong to the same user, and if they match, emits the combined row. PostgreSQL has three nodes for this one job: NestLoop, HashJoin, and MergeJoin. The reason a single task splits into three nodes is much like the reason scans did. There is more than one way to find matching pairs from two inputs, and which way is cheapest depends on the size of the inputs and the shape of the join condition.

Deciding which way is cheapest, by costing the alternatives, was the planner's job in an earlier chapter. This section looks at what those three nodes actually do when they execute. Given the same two tables, the three find matches in completely different ways, and that difference in approach is exactly what tells them apart.

How the three nodes route requests

All three join nodes are internal nodes with two children. One child is called the outer, the other the inner. All three run on the Volcano model's pull framework: when the parent asks for the next row, the join node takes rows from its two children, builds one matched row, and sends it up. The only difference is the order and manner in which it routes pull requests to its two children.

NestLoop pulls the inner from the start all over again for each outer row it receives. HashJoin slurps the inner in one pass to build an index in memory, then takes outer rows one at a time and probes that index. MergeJoin, on the assumption that both sides are sorted in the same order, advances both sides one step at a time in lockstep.

NestLoop: rescan the inner for every outer row

The simplest method is NestLoop. As the name says, the loops are nested. The outer loop takes one row from the outer; the inner loop scans the inner from beginning to end, looking for inner rows that match that outer row. When the inner is exhausted, the outer loop moves to its next row and the inner loop scans the inner from the start again. If the outer has N rows and the inner has M rows, the node performs N×M match comparisons.

At runtime the node keeps only two pieces of state: whether the inner scan for the current outer row is finished so the next outer row is needed, and whether the current outer row has matched the inner at least once. That second flag exists for outer joins like LEFT JOIN. A LEFT JOIN must emit even an outer row that never finds a match, filling the inner side with NULLs, and that NULL-filled row should be produced only when the inner has been fully scanned without a single match. So the node remembers whether a match has happened. A plain INNER JOIN, which simply discards an unmatched outer row, has no need for this flag. When the next outer row is needed, the node tells the inner child to start over, and that rewind resets the inner subtree so it flows again from its first row.

Judging by the number N×M alone, NestLoop looks like a textbook case of inefficiency. Yet there is a decisive reason PostgreSQL picks it often: when the inner can use an index, the inner loop's "scan the whole inner" turns into "fetch just one row by index." The key is that when the outer loop takes an outer row, it passes that row's join key value down to the inner. The inner plugs that value into its index condition and, instead of scanning the whole table, goes straight to the matching rows through the index.

SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01';

Suppose customers.id has a primary key index. When the planner picks NestLoop here, the outer loop pulls qualifying orders rows one at a time and passes each row's customer_id down to the inner. The inner does not scan customers from the start; it resolves WHERE c.id = <that customer_id> through the index and fetches just that one customer. So even with ten thousand outer rows, the inner's real work is "ten thousand index lookups," not "ten thousand full table scans." In EXPLAIN, when an Index Scan sits under the inner side of a Nested Loop and its index condition references an outer column, this is the shape you are seeing.

A second strength of NestLoop follows from this: it does not care about the shape of the join condition. All NestLoop does is pair an outer row with an inner row and evaluate the join condition, so the condition can be an equality (=), an inequality (<), a range, or a function call. The HashJoin and MergeJoin we will see next work only when the join condition is an equality; NestLoop has no such restriction. A join that connects two tables with an inequality, or matches them through an arbitrary function like a distance calculation, has no option other than NestLoop.

The trouble is when the inner cannot use an index and there are many matches. Scanning the entire inner for every outer row makes N×M a reality, and with even tens of thousands of outer rows the comparison count climbs into the billions. When the planner misjudges this boundary and picks NestLoop, you get the classic incident of a single query spinning the CPU without end.

HashJoin: build a hash table from the inner, look up from the outer

HashJoin's idea is not to rescan the inner over and over, but to read it just once and build a fast index in memory. That index is the hash table.

HashJoin runs in two phases. First, in the build phase, it pulls the entire inner and builds a hash table keyed on the join key. It runs the key through a hash function to decide which bucket it lands in, and collects inner rows with the same hash value into the same bucket. This phase slurps the inner all at once: unlike other nodes that emit one row at a time, it consumes the entire inner to complete the table before moving on. Then, in the probe phase, it takes outer rows one at a time, runs each row's join key through the same hash function to find its bucket, and compares only against the inner rows in that bucket.

This asymmetry is the heart of HashJoin. The inner is read exactly once to become an index, and after that only the outer flows. It is the opposite of NestLoop, which reads the inner as many times as there are outer rows. Processing one outer row compares not against the whole inner but against the few inner rows in the same bucket, so in a well-distributed hash table the comparisons per outer row drop to a handful.

In return, HashJoin carries two restrictions NestLoop does not. First, it works only for equi-joins. Hashing guarantees "equal values land in the same bucket," but it tells you nothing about "greater than" or "less than." So an equality condition like a.x = b.y can be resolved, but an inequality like a.x < b.y cannot be narrowed by hashing. Second, the hash table occupies memory. As long as the inner is small enough to fit in memory there is no problem, but once the inner exceeds the memory limit, the story changes.

That memory limit is not work_mem itself but work_mem multiplied by hash_mem_multiplier (default 2). In other words, with the default setting the hash table can use up to twice work_mem. If, during the build phase, the hash table looks like it will not fit within this limit, PostgreSQL splits the inner and outer into several batches by the hash value of the join key in advance. It loads one batch into memory to finish build and probe, then writes the remaining batches to temporary files on disk and brings them back one at a time. The Buckets: ... Batches: ... Memory Usage: ... printed on the Hash node in EXPLAIN ANALYZE shows this state. Batches of 1 means the hash table fit entirely in memory; 2 or more means it was split out to disk, adding that much temporary-file I/O. A large Batches is a signal that work_mem is short for this join.

PostgreSQL adds one more optimization here, for the case where the data is not evenly spread but skewed toward particular values. Picture joining an orders table to customers where one large customer accounts for a sizable share of all orders. In that case a single customer's id appears unusually often in the outer's join key. PostgreSQL can know from statistics which values appear this often (the most common values), and it pulls the inner rows for those popular values into a dedicated space that is never spilled to a disk batch. This dedicated space is called the skew bucket: skew means the distribution leans to one side, and this is a separate bucket for those leaning values. Those values will be probed frequently from the outer, and if their inner rows were sitting in a disk batch, each probe would have to read them back from disk. Holding them in memory in the skew bucket eliminates that repeated disk read.

MergeJoin: advance two sorted inputs in lockstep

The third method starts from the assumption that both inputs are already sorted by the join key. If both are sorted lists, you can match them by walking down both at once, one step at a time, without indexing one side wholesale or rescanning it. It is like laying two sorted name lists side by side and walking down to find the same name.

It begins with a read position on each side. It compares the join keys at the two positions: if they are equal, that is a match, so it emits the combined row; if they differ, it advances only the position on the smaller side by one. A smaller value means that row's match has already passed or will never come, so it is safe to drop it and move on. Since the two positions only ever move in one direction until each reaches its end, the join finishes after reading each input exactly once. Unlike NestLoop, which reads the inner repeatedly, and HashJoin, which loads the whole inner into memory, MergeJoin streams both sides exactly once.

For MergeJoin to work, both sides must be sorted, and that sorting is not free. If both inputs happen to come up already sorted because they were read through a join-key index, the merge can begin right away at no sorting cost; otherwise a Sort node is placed under one or both sides to sort the input first, and only then does the merge begin. In EXPLAIN, whether a Sort hangs under the Merge Join or a sorted Index Scan feeds straight in tells you whether that sorting cost was paid.

A tricky case slips in here: when the join key has duplicate values. If the outer has the key 5 twice and the inner has 5 three times, the first outer 5 must match all three inner 5 rows, and the second outer 5 must also match those same three inner 5 rows. But by the time the first outer 5 is processed, the inner's read position has already moved past the three 5 rows to the next value. To match the second outer 5, the inner position must be moved back to where 5 began.

This rewinding is called mark and restore. A mark is placed at the position where a run of equal values begins on the inner, and when the same value appears again on the outer, the inner position is restored to that mark. So although we said MergeJoin reads both sides only once, more precisely a stretch of the inner can be rewound and reread because of duplicate values on the outer. If the join key is nearly unique on both sides, this rewinding barely happens and the join finishes most cleanly; if there are many duplicates, that much rewinding cost is added.

One more thing: mark and restore work only if the inner child can accept the request "remember this position and go back to it." Not every execution node can return to an arbitrary past position. A node that streams sort output or another join's result one row at a time has no way back to a row it has already passed. In that case PostgreSQL inserts a Materialize node above the inner. Materialize is a node that stacks up the rows the inner sends, in memory (spilling to disk if it overflows). Because mark and restore happen on this stacked-up copy rather than on the inner original, the copy can be walked back and forth freely. The Materialize you see under a Merge Join in EXPLAIN is that mechanism.

1.5.2 Scan Nodes: SeqScan, IndexScan, BitmapScan

JoongHyuk Shin — Sun, 21 Jun 2026 09:13:42 +0000

A scan node is a leaf of the executor tree. Having no children, it reads rows straight from disk. That single phrase, "reads straight from disk," actually hides three different methods. To pull rows that pass the same condition from the same table, PG has three separate scan nodes: SeqScan, IndexScan, and BitmapScan. The job is identical, so why three nodes? One reason: depending on what fraction of the rows pass the condition, a different way of reading the heap pages turns out to be cheaper.

All three run on the same skeleton. The procedure a scan node follows to produce one row is to fetch a raw tuple through the access method, check the qual (the filter expression, like a WHERE condition), and if it passes, project out the needed columns and return it. The difference between the three nodes lies in the first step: where, and in what order, the next raw tuple comes from. SeqScan sweeps the whole table, IndexScan probes only the spots the index points to, and BitmapScan gathers those spots all at once and then reads them in an organized order.

Sequential scan: read every table page from first to last

The simplest method is the sequential scan. Without going through an index, it reads the table's heap pages in their physical storage order, from the first page to the last. From each page it pulls rows one by one, checks the qual, and sends up only those that pass. When a query like SELECT * FROM orders WHERE amount > 100 matches a large portion of the table, most pages have to be read anyway, so this method is fastest.

A sequential scan is fast for two reasons. First, there is no side work of searching an index. Second, reading pages in storage order makes it sequential I/O for the disk. Disks and the OS apply read-ahead, prefetching the next pages while reading consecutive blocks, which is far more efficient than hunting down scattered locations one at a time. This is also the basis on which the planner assigns different costs to sequential access and random access, and that cost model is covered in 1.4.

Inside PG, the per-row supply for a sequential scan is handled by SeqNext. It just calls the access method that sweeps the heap in order and hands back the next tuple, and in that same step it also judges whether the tuple it read is visible (visibility) under the current transaction's snapshot. The specific rules this visibility judgment follows are covered in Chapter 3 on transactions and MVCC. Here it is enough to know that this judgment happens together with every row the scan sends up.

Index scan: take an address and probe the heap one row at a time

When only a tiny fraction of the rows pass the condition, the story changes. Sweeping a ten-million-row table to find a single row, as in SELECT * FROM orders WHERE id = 42, is wasteful. This is where the index scan comes in.

To understand the index scan, you first need the idea of a TID. A TID (tuple identifier) is the physical address of a row inside the heap. It is a (page number, offset within the page) pair pointing to which page and which slot the row sits in, and the index's leaf holds exactly this TID alongside the key value. When you follow the index down to find a key, you get a TID saying "the row with this key lives at this heap address."

So one row of an index scan consists of two actions. First it pulls the next matching TID from the index, then it goes to the heap address that TID points to and reads the actual row. The row it reads is checked for snapshot visibility right there. Because one index key can point to several versions of a row (under MVCC, modifying a row can leave the old and new versions coexisting), the index handing back a TID does not guarantee that row is currently visible. So the visibility check has to happen at the heap, not at the index. The index scan repeats this "one TID from the index, one row from the heap, visibility check" bundle for as many matching keys as there are.

A weakness hides in this. The TIDs the index hands back follow the index key order, not the physical storage order in the heap. So the path of probing the heap jumps around between pages, becoming random access. With one or two matches it touches only a page or two and there is no problem, but as matches grow into the thousands, it jumps to scattered pages thousands of times, and may even revisit the same page repeatedly. If a page holds several matching rows that come out far apart in index order, that page is read again each time. Because random access costs more per page than sequential access, the advantage of the index scan fades quickly as matches grow. This is where "reading through an index is always faster" breaks down. If the planner's estimate of few matching rows misses and a large portion of the table actually matches, the index scan ends up slower than reading everything sequentially from the start, because it jumps across scattered pages without end.

In exchange, the index scan has an advantage the sequential scan lacks: it returns rows in index key order. If a query has ORDER BY id and happens to scan via the id index, the rows the index scan sends up are already sorted, so there is no need for a separate Sort node on top. This is why a query like SELECT * FROM orders ORDER BY id LIMIT 10 is fast. Instead of gathering everything to sort it, you just pull the first ten rows in index order and you are done.

A variant of the index scan is the index-only scan. If every column the query needs is contained in the index, the answer can be built by reading only the index tuples, without going to the heap. Visibility still has to be confirmed, but instead of reading the heap directly, it consults the Visibility Map (an auxiliary structure that marks, per page, whether all tuples on that page are visible to everyone). If the page is marked all-visible, it skips the heap visit; otherwise it goes to the heap for just that row.

Bitmap scan: collect all addresses, then read pages once in physical order

There is a middle range where the matching rows are too many for an index scan and too few for a sequential scan. Consider finding a single customer's few hundred orders, as in SELECT * FROM orders WHERE customer_id = 7. A few hundred is too much for a sequential scan that reads all ten million rows, yet probing scattered pages a few hundred times with an index scan, revisiting the same page over and over, is also inefficient. The bitmap scan is for this range.

The idea behind the bitmap scan is to turn the index scan's random access into something close to sequential. The key is to not read rows one by one as you follow the index, but to first gather all the matching TIDs, then sort those addresses into the heap's page order and read them. That way you read front to back in one direction instead of jumping between pages, and even if a page holds several matching rows, that page is read exactly once.

Two nodes split this work. The lower BitmapIndexScan sweeps the index and gathers all matching TIDs into a single bitmap. The bitmap is a data structure that marks "there is a match at this slot of this page" with a set bit. Unlike the other scan nodes that send their results up one row at a time, this node sweeps the index to the end in one shot and hands the completed bitmap up whole. The upper BitmapHeapScan takes that bitmap and visits the heap pages its set bits point to in physical order, emitting rows one by one.

This structure carries one constraint: the size of the bitmap. Recording the exact slot (offset within the page) of every matching row makes the representation precise, but when matches reach into the millions, that bitmap eats too much memory. So when the memory for the bitmap (work_mem) runs short, PG drops the precision by one level. What was marked per row becomes marked per page, recording only "there is a match somewhere on this page." The first, precise method is called exact, the second, coarse one is called lossy.

A lossy page comes with a price. Since you only know "there is a match on this page," after reading the page you have to check again whether the rows on it actually pass the condition. This re-check is called recheck. Rows from an exact page need no recheck because the bitmap pinned the exact slot, but rows from a lossy page have the original condition applied once more to filter them. The Heap Blocks: exact=N lossy=M in EXPLAIN output shows the counts of these two kinds of pages. A large lossy number signals that the bitmap did not fit in memory and gave up precision, and raising work_mem brings that many pages back to exact.

Another advantage of the bitmap approach is that it can combine several indexes. If WHERE customer_id = 7 AND status = 'paid' has a customer_id index and a status index each, two BitmapIndexScans can each build a bitmap, and those two can be overlapped with a bitwise AND to keep only the TIDs that satisfy both conditions. Unlike an index scan that narrows by one index and filters the rest at the heap, this merges both conditions at the bitmap stage before going to the heap. For an OR condition, the two bitmaps are unioned.

The figure above compares how the three scans visit the heap when matching rows are scattered across pages 2, 7, and 9 of the same table. The sequential scan reads every page from 1 to 10 in order, regardless of matches. The index scan touches only the matching pages, but following index key order it jumps to 7, 2, 9, and revisits 7 and 2 when matches come out apart (the numbers in the circles are the visit order). The bitmap scan sorts the same matching pages into the physical order 2, 7, 9 and reads each exactly once. The jagged path of the index scan straightened into a one-directional line in the bitmap scan is the heart of it.

What this means in practice

When an index scan is slow, the culprit is usually a missed estimate and random access. If in EXPLAIN ANALYZE the index scan's estimated rows and actual rows diverge sharply with the actual far higher, the planner chose the index scan expecting few matches while many actually matched, and it is jumping across pages without end. Stale statistics behind a missed estimate are a common cause, so run ANALYZE again first to check whether the estimate matches reality. When the estimate and the actual diverge widely and the matching rows are far more than expected, you can set enable_indexscan = off to force the planner to leave the index scan out of its choices, so you can weigh the cost of a BitmapScan or a SeqScan directly. If the estimate keeps missing even after ANALYZE, that column's data distribution is not being captured well enough by the default statistics target (default_statistics_target, default 100), and the root fix is to raise that column's statistics target with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS.

1.5.1 The Volcano Iterator Model

JoongHyuk Shin — Sun, 21 Jun 2026 09:12:36 +0000

In 1.5 we saw the big picture: the executor pulls results up one row at a time. That pulling structure ties together nodes that do completely different jobs, SeqScan, HashJoin, Sort, into a single execution loop. Nodes that do such different things all run in one loop because every node follows the same interface, and that interface also explains what the actual rows and loops in EXPLAIN ANALYZE are.

Every node follows the same interface

The executor tree has many kinds of nodes. SeqScan sweeps a table, IndexScan follows an index, HashJoin and NestLoop bring two inputs together, Sort orders rows, HashAgg groups and counts. They all do different things. Yet the executor binds them with a single promise. A node is called like a function, and each call hands back exactly one result row. Call the same node again and it gives you the row right after the one it last returned; when there are no more rows, it returns an empty result to signal the end. The unit that travels between nodes one at a time is the tuple.

Inside PG this "next row, please" request is a single function called ExecProcNode. Most nodes (PlanState) such as scans, joins, and sorts carry this function, so the caller can invoke ExecProcNode and get the next tuple without knowing whether the other side is a SeqScan or a HashJoin. The calling code does not have to differ per node type. Some nodes (Sort among them) do need to take in all of their input before they can emit the first row. Those nodes still send their output one row at a time; the only difference is that the first row does not come out until all the input has been gathered.

The name for fitting every operator to the same interface this way is the Volcano model. Goetz Graefe laid it out in 1994: every operator is shaped into three operations, open, next, and close. In PG, open is ExecInitNode, which prepares a node; next is ExecProcNode, which emits the next tuple; close is ExecEndNode, which tears it down. Match those three and any operator slots into the tree.

Why the uniform interface matters becomes clear if you imagine the opposite. If each node had its own output convention, you would need different glue code to put Sort on top of HashJoin versus Sort on top of SeqScan. With N node types the combinations explode. Unified under one next call, Sort only needs to know "pull one row at a time from whatever child it has," and a new operator only needs one next function implemented to sit on top of any existing node. The executor can assemble nodes in the exact shape of the plan tree because of this promise.

One next call goes down to the leaf and back up

A node's next cannot make a tuple on its own, because it needs input. So it calls its child's next, takes one row in, and works it into one result row of its own. That call goes to the child's child, then further down, until it reaches the scan node at the leaf of the tree. The scan node has no child, so it reads a row directly from disk.

Take SELECT * FROM orders WHERE amount > 100 LIMIT 5. The execution tree for this query is two nodes: Limit on top, SeqScan below. The WHERE condition does not become a separate node; it is evaluated as a filter inside the SeqScan node. In EXPLAIN you see Filter: (amount > 100) attached to the SeqScan, with no separate filter node. When Limit's next is called, Limit calls SeqScan's next. SeqScan reads one row from a page, checks right there whether amount exceeds 100, passes it up if it does, and discards it and reads the next row if it does not. When a passing row reaches Limit, Limit sends it to the caller (the client, or the target table of an INSERT). And once Limit has received five rows, it stops calling SeqScan's next.

The key is that the flow runs in two directions. The request (next) goes top to bottom, and the tuple goes bottom to top. Rows are not pushed up from below on their own; only when something above pulls a row does exactly that much get drawn up from below. To generalize the way SeqScan just checked amount, the internal procedure a scan node follows to make one row is this: it fetches one raw tuple through the access method, checks the qual (a filter expression like a WHERE condition, the same idea as the SQL standard's predicate), and if the tuple passes, runs a projection to keep only the needed columns and returns it. A raw tuple that fails the qual is discarded and the next one is fetched. So a single next on a scan node may internally sweep several raw tuples until it finds "the next row that passes the condition."

Why pull one row at a time

Compared with building the whole result and stacking it up, the benefits of pull come out.

First, memory stays constant regardless of result size, and no intermediate result is written to disk. Flowing one row at a time, a node only needs to hold the single tuple it is processing. Whether a query returns ten million rows or ten, the number of tuples any node holds at once is one, and a node reuses its single slot (TupleTableSlot) instead of allocating a fresh tuple. A row that SeqScan read and passed through the filter also rises all the way to the caller in one go, through the nodes above, so there is no need to build an intermediate table between stages. This processing, where one tuple passes through several nodes in a row, is called pipelining. (A node like Sort or HashJoin that must gather all of its input does use memory inside itself, but that is that node's own business and is different from the whole tree stacking up the result.)

Second, stop at the top and the bottom stops too. For a query with LIMIT 5, the topmost Limit node stops calling its child's next after it has received five rows. Then the nodes below it also do only as much work as it took to push up those five rows, and stop. Even if the table has ten million rows, SeqScan reads only as many pages as it takes to pass five rows up. With the build-it-all approach, LIMIT or not, you would compute ten million rows first and then cut to five.

That said, passing tuples one at a time (tuple-at-a-time) is not every database's answer. A function call happens every time a node is called, and when a single query sweeps hundreds of millions of rows, that call cost alone adds up enough to matter. So engines built mainly for analytical queries took a different road. Analytical databases like ClickHouse and DuckDB use vectorized execution, where each call passes not one row but a batch of thousands. DuckDB processes about 2,048 rows at a time, ClickHouse up to 65,536 rows in one chunk, which divides the call cost across the row count and lets the CPU's SIMD instructions (one instruction computing several values at once) process the batch together. On analytical workloads this difference reaches several times. PostgreSQL stays with tuple-at-a-time because it is a general-purpose engine handling both OLTP queries that touch few rows and large analytical queries in one engine. Flowing one row at a time fits patterns that stop early, like the LIMIT we just saw, and fits transaction processing that runs row by row.

A cursor: the client pulls one row at a time

This one-row-at-a-time structure is not confined to the executor's insides. Receiving a large result all at once swells the client's memory by the size of the result, because SELECTing millions of rows dumps all of those millions onto the client. The channel that lets you receive that result in smaller pieces is the cursor. When the client opens a cursor with DECLARE CURSOR and takes a few rows at a time with FETCH, the executor calls next from the top of the tree by that many each time a FETCH arrives. It does not build the whole result up front; it pulls only as much as the FETCH asks for, then and there.

So even a result of millions of rows, taken through a cursor 1,000 at a time, keeps the amount the client holds at once at 1,000. And while you receive through the cursor in pieces, the executor keeps that execution tree and transaction snapshot held as is, so FETCHing across several calls still reads consistently against the data as of when the cursor was opened.

Scan a node more than once and loops climbs

So far the picture has been each node swept once, start to finish. In a nested loop join it is different. Every time a row is pulled from the outer input, the entire inner input must be swept again from the start. If the outer has 1,000 rows, the inner is scanned from the beginning 1,000 times. Resetting a node back to its starting state like this is called a rescan, and PG handles it with ExecReScan.

This rescan count surfaces as loops in EXPLAIN ANALYZE output. The executor gathers statistics while a node runs: each time one scan (one loop) finishes, it accumulates the tuples that loop produced and bumps the loop count by one. So the two numbers are defined like this.

loops: how many times the node was scanned from the start. For a nested loop inner, this equals the outer row count. A node scanned only once has loops of 1.
actual rows: the average tuples produced per loop. The total tuples across all loops, divided by loops.

Here is the point people often get wrong. actual rows is the per-loop average, not the total of tuples the node produced. For example, if a nested loop inner IndexScan shows rows=1.00 loops=1000, it means the node was scanned 1,000 times and produced 1 row on average each time, so the total tuples it actually produced is 1 times 1,000, about 1,000 rows. When you see a node with a large loops, do not read actual rows alone and conclude "this node only touches one row." You have to multiply rows by loops to get the scale of what the node actually did.

Nested Loop  (actual rows=1000.00 loops=1)
  ->  Seq Scan on orders  (actual rows=1000.00 loops=1)
  ->  Index Scan on customers  (actual rows=1.00 loops=1000)

In the output above, the inner Index Scan's loops=1000 means the outer orders had 1,000 rows, so the customers index was re-traversed 1,000 times, and rows=1.00 means each of those 1,000 found 1 row on average. The product, about 1,000 index lookups, is what this node actually did.

What this means in practice

First, LIMIT can stop early only when there is no Sort. A LIMIT after ORDER BY cannot emit its first row until the Sort node has received all input and finished sorting, so even if the top stops early, everything below the Sort has already been read. Which plans can stop early and which cannot is decided by the node types.

Second, holding a cursor open for a long time carries a cost. If you leave a cursor open for a long time because receiving one row at a time feels light, the snapshot survives that much longer, and this leads to the side effects of a long-running transaction (vacuum delay among them, covered in Chapter 3). Close a cursor as soon as you are done reading, and if you are not going to receive the result to the end, do not stretch the transaction out.

1.5 Executor: How Results Come Back

JoongHyuk Shin — Sun, 21 Jun 2026 09:11:30 +0000

By the time 1.4 ends, the planner has produced one PlannedStmt. Inside it is an execution tree built from Plan nodes, frozen into a form you can follow step by step, something like "go into the primary key index on users, fetch the one matching row, then output that whole row." But that is still only a blueprint. Reading actual pages off disk, picking out the rows that match the condition, handing results back to the caller: none of that has happened yet. The stage that takes that blueprint and produces actual rows is the executor.

The difference between the planner and the executor is the difference between deciding and doing. The planner was the stage that weighed "which index, in what order, with what join method" by cost and chose. The executor takes the chosen approach and carries it out as is. There is nothing left to choose. It just runs the nodes baked into the plan tree and pulls rows out of them.

To run it, the executor takes the Plan tree it received and turns it into a PlanState tree. The Plan tree is the static blueprint the planner made, and it does not change during execution. But to actually run, each node needs state that changes as execution proceeds: which row it is reading now, whether the hash table is fully built, what tuple it has buffered from a child. So when execution begins, a PlanState tree with the exact same shape as the Plan tree is created. The blueprint Plan tree is left untouched, and the running state lives in that PlanState tree instead.

How the executor produces result rows is the heart of the stage. The executor does not build the entire result set at once and stack it up. Instead, it asks the topmost node of the tree for "the next row," and that request travels down the tree to the leaves. When a leaf scan node reads one row from a page and passes it up to its parent, that row climbs up one level at a time through joins and filters until it reaches the top. The top sends that single row to the caller (the client, or the target table of an INSERT). When the next row is needed, the top is asked for "the next row" again. Rows are pulled from above whenever they are needed, and that pull propagates downward. This structure is called the pull model, or the Volcano iterator model.

Why pull one row at a time? Building everything at once inflates memory by the size of the result, and even when the client only needs the first 10 rows, as in LIMIT 10, the whole thing gets computed anyway. Pulling one row at a time lets the top stop after it has received ten rows, and then the nodes beneath it do exactly that much work and no more. This is possible because rows do not flow top to bottom: requests flow top to bottom, and rows flow bottom to top.

In the 1.4 planner chapter, the planner decided which scan, join, or aggregation method to use and whether to go parallel, by cost. This chapter looks at how the chosen nodes actually run on top of the pull skeleton.

This chapter splits into five sections.

1.5.1 The Volcano iterator model: a closer look at the "pull one row at a time" skeleton we just saw. Every node returns one next tuple through the same interface, and we explain how that single call ends up driving the whole tree.
1.5.2 Scan nodes: SeqScan, IndexScan, BitmapScan: the nodes at the leaves that actually read rows off disk. The conditions under which each of the three scan methods is used.
1.5.3 Join nodes: NestLoop, HashJoin, MergeJoin: how the three nodes that bring rows from two children together behave on top of the pull skeleton.
1.5.4 Aggregation: HashAgg, GroupAgg, sort-based: the nodes that execute GROUP BY and aggregate functions. How aggregation, which needs to see everything before it can answer, is handled inside a skeleton that pulls one row at a time.
1.5.5 Parallel query: how worker processes cooperate: until now, execution has been one backend process driving the tree. Here we look at parallel execution, where several worker processes split a large scan among themselves and run it concurrently. The shared memory and background worker infrastructure that supports this cooperation is covered in detail in Chapter 6.

The executor is the stage that runs the plan as is, not the stage that makes new decisions. The node tree shown in EXPLAIN output is exactly this execution tree, and the actual rows and loops that EXPLAIN ANALYZE attaches to each node measure how many rows the node actually produced and how many times it ran. Exactly what those two numbers count is explained in 1.5.1 along with the pull model. Once you get through this chapter, those numbers in EXPLAIN read not as abstract statistics but as traces of real execution.

1.4.10 Planner Hook: When It Fires, How to Use It

JoongHyuk Shin — Sun, 21 Jun 2026 09:10:21 +0000

Everything from 1.4.1 through 1.4.9 happened inside a single function, standard_planner(). Building paths, costing them, searching for a join order, estimating cardinality from statistics: all of it runs inside that one function. Yet PostgreSQL does not call standard_planner() directly. It puts another function, planner(), one step ahead of it, and has planner() call standard_planner(). And planner() can be made to call some other function instead of standard_planner().

That replacement is what the planner hook enables. When pg_stat_statements measures per-query planning time, or pg_hint_plan rewrites a plan according to hints, it all goes through this hook. Let's look at how PostgreSQL provides a way to observe or change planning behavior without touching a single line of the core, and how external code plugs into it.

All planner() does is check the hook

The body of planner() is essentially this.

if (planner_hook)
    result = (*planner_hook) (parse, query_string, cursorOptions, boundParams);
else
    result = standard_planner(parse, query_string, cursorOptions, boundParams);

planner_hook is a global function pointer. Its default value is NULL, in which case standard_planner() is called right away. A plain PostgreSQL build always takes this path: planner_hook is empty, so the incoming query goes straight to standard_planner().

The key here is the type of planner_hook.

typedef PlannedStmt *(*planner_hook_type) (Query *parse,
                                           const char *query_string,
                                           int cursorOptions,
                                           ParamListInfo boundParams);

This signature is identical, down to the character, to that of planner() and standard_planner(). It takes the same Query and returns the same PlannedStmt (the execution plan). So external code only has to write a planner function matching this type and store its address in planner_hook. Let's call this function, written by external code to register in planner_hook, a custom planner function. The moment its address is stored, every planning request enters this custom planner function first, instead of standard_planner().

Leaving a single function pointer empty is a simple device, but it is the core of PostgreSQL's extension model. Rather than editing the core source to insert new behavior, you connect an external function to a pointer the core left empty in advance. This is why you can change behavior while leaving the compiled PostgreSQL binary untouched.

A custom planner function delegates to standard_planner

So when and how does external code fill in planner_hook? When an extension is loaded, PostgreSQL calls its library's _PG_init() function once. Hook installation almost always happens here. The _PG_init() of pg_stat_statements ends in two lines.

prev_planner_hook = planner_hook;
planner_hook = pgss_planner;

The second line stores the address of the custom planner function pgss_planner into the global pointer. At a glance this looks like it fully replaces standard_planner(). But what pg_stat_statements wants to do is not to plan in place of PostgreSQL; it wants to measure the time PostgreSQL spends planning. The plan itself must still be built by PostgreSQL.

So pgss_planner is called first, but it hands the actual planning back to standard_planner(). The order is:

Pre-work: start a timer and record buffer usage.
Delegate: call standard_planner() to get the actual plan.
Post-work: stop the timer, compute the elapsed time, and store that figure in its own statistics table.
Return: hand the plan received in step 2 back to the caller unchanged.

It does not touch the plan itself; it only measures time around it. Having the custom planner function run first and then hand the real work to standard_planner() is the most common shape. If instead a function does not call standard_planner() and returns a plan it built itself, that amounts to replacing planning altogether. An extension that changes plans, like pg_hint_plan, is closer to this. That said, even hooks that change plans usually adjust the plan standard_planner() produced rather than building one from scratch; doing the latter is rare.

I once built dynamic row-level security (RLS) on top of planner_hook, going beyond what PostgreSQL's built-in RLS can do. Built-in RLS only accepts a fixed condition expression in a policy, so it cannot carry a condition that changes per query, like one a policy function produces at execution time. So I intercepted the Query with planner_hook right before planning and injected the dynamic condition there. For a SELECT, I adjusted the Query before delegating to gate the read path; for an INSERT/UPDATE, I reworked the plan returned after delegating to check the value on the write path. A single hook thus passed through both before and after delegation.

A custom planner function wrapping standard_planner() with pre-work and post-work, as above, is the common case, but not every function has all three parts. Some only touch the Query before delegating and return the resulting plan as is (pre-work only); some only adjust the plan after delegating (post-work only). What it does depends on the extension's purpose; only the skeleton, with delegation sitting in the middle, stays the same.

Why save prev_planner_hook

The first of those two lines has not been explained yet: prev_planner_hook = planner_hook. Before storing its own function, it saves the value already there. If only one extension is in use, this line is unnecessary, since the saved value would be NULL anyway. The line matters when several extensions are loaded.

Suppose shared_preload_libraries lists two extensions. Extension A, listed first, loads and stores its function in planner_hook. The prev A saved is NULL. Then extension B loads, and planner_hook currently holds A's function. When B runs prev_planner_hook = planner_hook, that captures A's function address, and then planner_hook gets B's function. If B had skipped this save and just stored its own function, the function A installed would be referenced by nothing and vanish. A's feature would die silently.

So each custom planner function, when delegating, does not call standard_planner() directly; it checks the saved prev first.

if (prev_planner_hook)
    result = prev_planner_hook(parse, query_string, cursorOptions, boundParams);
else
    result = standard_planner(parse, query_string, cursorOptions, boundParams);

If prev exists, it calls that; only when there is none does it call standard_planner(). This delegation happens at step 2 from the previous section, after the pre-work is done. Not after the extension has finished all its work, but with the delegation sitting between pre-work and post-work.

The result is that the extensions' functions form a chain.

planner()  →  extension B's function  →  extension A's function  →  standard_planner()

The call order is the reverse of the install order. Extension B, loaded last, is called first; B calls A through prev; A, whose prev is NULL, calls standard_planner().

Here, B calling A happens in the middle of B's function code (the delegation step). Likewise, A calls standard_planner() from the middle of its own code. So when standard_planner() finishes building the plan and returns its value, control comes back to A's function and the post-work code remaining in A runs; when A returns, control goes back to B's function and B's post-work runs. Calls go in the order B → A → standard_planner(), and returns come out in the reverse order, standard_planner() → A → B. When a function calls another function from the middle of its code, the rest of the outer function continues after the inner one finishes; this is just how ordinary function calls work. That structure is why the two extensions can each slot in their own pre-work and post-work without colliding.

When it fires

Looking at where planner() is called tells you when the hook fires. planner() is called by a thin wrapper, pg_plan_query(), and that wrapper runs whenever an optimizable query needs a plan. So the hook fires every time a query like SELECT/INSERT/UPDATE/DELETE enters planning, one query at a time.

EXPLAIN is included here too. EXPLAIN simply does not execute the query; it still builds the plan the same way, so it passes through the planner hook. This is also why pg_stat_statements captures the planning time of EXPLAIN-ed queries.

Prepared statements are an exception, though. While a plan that was built once stays in the plan cache and gets reused, no planning happens, so the hook does not fire either. The hook fires again only when the cache has no plan and one must be built anew, for instance when a generic plan (a plan built once and reused regardless of the parameter values) is first created or a custom plan (a plan rebuilt for each value) is built each time. When a tool that measures planning time via the hook shows "some executions have zero planning time" for a prepared statement, that is not a bug but the cache doing its job.

You can choose how deep to intervene

The planner hook intervenes at the very outside of the whole planning process. So it does not see what happens inside; it only receives the incoming Query and the outgoing PlannedStmt. But sometimes you want to step into just one intermediate stage of planning. For example, if you only want to add one more path to the scan path candidates of a particular table, taking over all of planning is excessive.

PostgreSQL therefore provides separate hooks at deeper stages. A hook that fires right after a base table's scan paths are all collected, a hook at the point where join paths are gathered, a hook that replaces the join-order search itself with your own algorithm (the stage where the DP and GEQO from 1.4.5 run), a hook that fires right after the planner reads table information from the catalog so you can add or remove index information, and so on.

The criterion for choosing is the depth of intervention. Work that only needs the input and output of the whole planning process (timing, plan post-processing) uses planner_hook; work that has to touch an intermediate result of a specific stage uses that stage's hook. The deeper the hook, the more of the planner's internal data structures you need to know, but you do not have to take responsibility for the whole of planning.

standard_planner mutates its input

There is a trap that those who write a hook directly can easily fall into. The source comment above planner() warns about it directly.

/* standard_planner() scribbles on its Query input, so you'd
 * better copy that data structure if you want to plan more than once. */

standard_planner() does not just read the Query tree it receives; it rewrites it in place. Once a plan has been built, the input Query is already mutated. If a hook tries to call standard_planner() twice on the same Query (say, to plan two ways and compare them), the second call receives already-mutated input. So a hook that needs to plan twice must copy the Query before handing it to standard_planner(). If you hit a symptom while writing a hook where "the plan is fine the first time but goes wrong from the second," it is usually a case of reusing the same input without knowing about this mutation.

What this means in practice

First, the more extensions use the planner hook, the more overhead is added per query. Turning pg_stat_statements on with track_planning = on measures planning time for each query, and that measurement is not free. The custom planner function being called first to turn the timer and buffer counters on and off is added to every planning operation. Usually this is negligible, but when you process tens of thousands of short queries per second, this overhead can show. Stacking several extensions that use the planner hook means their functions are called in a chain on every query, so keep this in mind on systems that plan frequently.

Second, the load order of extensions can affect behavior. Hooks are chained in reverse install order, so the order you list them in shared_preload_libraries determines the order they fire. Among extensions that only measure plans, the order is meaningless; but if you use an extension that modifies plans (like pg_hint_plan) together with one that measures, the order decides who touches the plan first and who measures the result. One thing to check when extensions do not mesh as expected is the listing order in shared_preload_libraries.

Third, the planner hook is an entry point you can use for debugging and diagnosis. Without touching the core, you can look right there at what Query a given query comes in as and what PlannedStmt it goes out as. With SQL you can only see the execution plan via EXPLAIN, but to inspect the structure of the Query tree itself, before and after it goes to the planner, the hook is just about the only avenue. A small extension that installs a planner hook and logs the incoming Query and outgoing PlannedStmt lets you observe the input and output of planning without recompiling the core.

When several extensions interleave, or rewrite stages stack up, the Query tree often ends up changed in ways I did not intend. For those cases I built a monitoring tool that dumps the Query tree before and after the planner and used it for debugging. Wiring that tool to a single GUC meant I could toggle monitoring by changing the GUC value through SQL alone, with no rebuild and no touching the server. Combining a hook with a GUC lets you observe internal behavior on a live server using nothing but SQL.

1.4.9 Planner Preprocessing: Subquery Pull-up, Predicate Pushdown, Equivalence Classes

JoongHyuk Shin — Sun, 21 Jun 2026 09:09:16 +0000

So far we have seen the planner build candidate paths and rank them by cost, choose join methods and join orders, and estimate those costs from statistics. But before any of that cost comparison begins, the planner does something else first. It takes the incoming Query tree and rewrites it once into a shape that is easier to optimize. That rewriting is preprocessing.

In processing order, preprocessing is the planner's very first step. Yet why it pays off only becomes clear once you understand cost and joins. Why it is cheap to shrink the rows entering a join is explained by join cost, and why it matters to manufacture a missing join condition to avoid a Cartesian product is explained by join order. That is why a step that runs first in processing is the last one we look at in this book.

Preprocessing rewrites the Query tree before cost comparison starts

The planner's entry point runs preprocessing once for each Query it receives. The function that gathers this work is subquery_planner. The name makes it sound like it only deals with subqueries, but it actually does everything that should happen exactly once per Query object. Only after that preparation finishes does the cost-based candidate comparison begin.

One thing is worth pinning down here. The rewriter from chapter 1.3 also rewrites the Query tree, but its purpose is different from this preprocessing. The rewriter expands views into their contents, injects RLS policies as conditions, and fills in default values for columns omitted from an INSERT. In other words, it settles what the query actually means. Preprocessing does not touch that settled meaning at all. It leaves the rows that will come out exactly as they are, and rearranges only the shape of the tree so the same result can be produced more cheaply. If the rewriter finishes deciding "what to do," preprocessing is the first trimming of "how to do it."

Why rewrite at all? The Query tree the rewriter hands over still carries the shape the SQL author wrote. People write for readability, not for the planner's convenience. For clarity they wrap parts of the query in subqueries and pile several conditions into one WHERE clause. Leave that shape alone, and the cost and join comparison never sees the better candidates. A condition that only needs to filter one table tags along all the way to the join and inflates the join's input, and with no join condition spelled out in the SQL, the only join order available between two tables is a Cartesian product. Preprocessing tidies up this shape so the cost comparison gets to see all its candidates. The five sections that follow look at that tidying one piece at a time.

Pull subqueries up into the parent query

The first thing it does is pull subqueries in the FROM clause up into the parent query. Merging a subquery into the parent as a single level is called flattening, or subquery pull-up, in the sense of lifting the subquery upward. It runs in the opposite direction from the predicate pushdown we will see later, which pushes conditions down. Take this query as an example.

SELECT *
FROM (SELECT * FROM customers WHERE status = 'active') c
JOIN orders o ON c.id = o.customer_id;

The author wrapped the part that selects only active customers in a subquery to keep it readable. If the planner leaves this subquery as is, selecting active customers and joining with orders split into two stages. The inner subquery first produces an intermediate result of "active customers," and the outer query joins that result with orders. The problem is that because these two stages are processed separately, the planner cannot put customers and orders together into its join-order search. customers is trapped inside the subquery while orders sits outside, so the two belong to different processing units.

Flattening removes this boundary. It pulls the subquery up into the parent query, conceptually turning the query above into this shape.

SELECT *
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.status = 'active';

The SQL above is not text that actually gets re-emitted; it just renders the result of the tree transformation in SQL form. Now customers and orders are two tables in the same query, and the planner can put both into its join-order search to compare which one to read first and how to join them. The WHERE status = 'active' condition also moves outward and becomes a target for the predicate pushdown we will see shortly.

Not every subquery can be merged. If the subquery contains aggregation (GROUP BY or aggregate functions), window functions, DISTINCT, LIMIT, ordering, or HAVING, it is not merged. These features need the subquery computed as one closed result for its meaning to be preserved. For instance, if the subquery computes per-customer order totals with GROUP BY, mixing and merging it with the outer table breaks the aggregation grouping. So the planner only pulls up simple subqueries that produce the same result either way, and plans the rest as closed units on their own.

IN and EXISTS subqueries that appear in a WHERE clause or a JOIN condition are handled similarly. Conditions like WHERE customer_id IN (SELECT ...) or WHERE EXISTS (SELECT ...) are converted into joins when possible. Converting them lets the planner pick an order and method like any other join and optimize it, instead of checking the subquery separately for each outer row. This internal form of an IN or EXISTS turned into a join is called a semi-join, and it is not SQL syntax. The author does not write SEMI JOIN; the planner turns the IN or EXISTS into a kind of join internally.

Compute the expressions that can be reduced to constants ahead of time

Next the planner scans the expressions in the query and computes ahead of time the parts that can be computed early. This is called constant folding.

WHERE amount > 2 + 2     -- as written
WHERE amount > 4         -- after constant folding

2 + 2 is a constant with no reason to be re-added for each row, so it is reduced to 4 ahead of time. There are cases where the result is determined even with non-constant parts mixed in. For example, if some view or condition expands into a form like WHERE is_vip OR true (is_vip being a boolean column for whether a customer is a VIP), it is always true whether is_vip is true or false, so it reduces to WHERE true and the condition itself disappears. Conversely, if AND false creeps in, that condition is always false and gets tidied away entirely.

That said, not every function is computed ahead of time. A function that can return a different value each time it is called must not be precomputed. nextval(), which advances a sequence every time it is called, is the classic example. PostgreSQL precomputes a function only when it is marked immutable (the property of always returning the same output for the same input); otherwise it reduces only the arguments as far as possible and leaves the function call itself for execution time.

This step has two effects. One is that the executor stops repeating the same computation for every row. Instead of adding 2 + 2 a million times across a million rows, it reduces it to 4 once when the plan is built. The other is that with expressions simplified, the transformations that follow recognize conditions more easily. For instance, the equivalence class we will see at the end of this section looks for "column = constant" conditions like region_id = 5 to spread to other tables, and if the expression sits unreduced as region_id = 2 + 3, it fails to recognize that as a constant comparison and misses the chance to spread it. Constant folding has to reduce 2 + 3 to 5 ahead of time for the next step to work.

Turn an outer join into a simpler inner join

An outer join costs more than an inner join. First, the difference between the two. An inner join produces only rows that have a match in both tables. An outer join, specifically a LEFT JOIN, keeps every row of the left table and, where there is no match on the right, manufactures a row with the right columns filled with NULL. This NULL-filling adds work and narrows the planner's freedom to reorder joins. Yet there are cases where a LEFT JOIN the author wrote produces exactly the same result as an inner join. The planner finds those cases and turns the outer join into a simpler inner join. This does not mean an inner join ranks above an outer join; it means simplifying toward the side without the extra work of NULL-filling.

The key to the decision is the WHERE condition applied to the finished join. In SQL, WHERE applies to the entire result after FROM and JOIN have been processed.

SELECT *
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 100;

The LEFT JOIN was used to keep every customer. But the join result has WHERE o.amount > 100 on it. A row where orders is NULL-filled because there is no matching order has o.amount as NULL, and NULL > 100 is not true, so that row is dropped by WHERE. In other words, the NULL-filled rows cannot survive into the final result anyway. So there is no reason to do the NULL-filling in the first place. The planner turns this LEFT JOIN into an inner join. Conceptually the query above becomes this.

SELECT *
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 100;

For this reasoning to hold, the WHERE condition has to be the kind that "can never be true once NULL comes in." o.amount > 100 is such a condition. If amount is NULL the comparison cannot be true, so the NULL-filled rows are guaranteed to be filtered out. (PostgreSQL calls an operator that cannot be true on NULL input a strict operator. Most comparison operators, =, >, <, and so on, qualify.) If the condition were o.customer_id IS NULL instead, it is the opposite. customer_id is the join key, so it is NULL only in rows that were NULL-filled for lack of a match. Letting only those rows through means "select only customers with no orders," which the planner recognizes as an anti-join. Like the semi-join, the anti-join is not SQL syntax but a kind of join internal to the planner.

The reason simplifying outer joins matters is that the fewer outer joins there are, the more freely the planner can shuffle the join order.

Push each condition down to the table it references

When you write several conditions joined by AND in a WHERE clause, they sit bundled together at the top of the query in the SQL text. But each of those conditions actually touches a different table. Some look at only one table, some at two together. The planner pushes each condition down toward the table it actually references, so it applies right where that table is read. This transformation is called predicate pushdown, and internally PostgreSQL calls it qual distribution. qual is short for qualification, PostgreSQL's shorthand for the filter conditions that go into a WHERE or ON.

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.region_id = 5;

c.region_id = 5 references only customers. Treat this condition literally as "a filter on the joined result," and it filters by region only after orders and customers are fully joined. The rows to be discarded were dragged all the way to the join. Instead the planner pushes this condition down to where customers is read. Conceptually it becomes this.

SELECT *
FROM orders o
JOIN (SELECT * FROM customers WHERE region_id = 5) c ON o.customer_id = c.id;

The "after" above does not mean the tree actually turns into a subquery; it renders in SQL form the fact that region_id = 5 is applied first where customers is read. customers narrows to only the customers in region 5 first, and only those few customers join with orders.

The difference is clear in numbers. If there are 100,000 customers nationwide and 5,000 in region 5, then without pushing the condition down, all 100,000 customers are joined with orders and then filtered. Push it down, and only 5,000 enter the join. A join costs less the smaller its input, so the same result comes at far less cost.

As a result of this distribution, each table's workspace (RelOptInfo) accumulates a list of conditions that apply only to that table. Each condition goes into the list wrapped in an object called a RestrictInfo, whose additional contents the earlier section on the path tree covered. A join condition that references two tables together is handled not at one table but at the join, and among those, an equality join condition like o.customer_id = c.id gets one more level of special handling. That handling is the very last transformation we will see next.

Pull out missing conditions by grouping equal values together

The last transformation fills in missing conditions by grouping equality conditions together. When several = conditions hang off a WHERE clause, the planner gathers them into a group of values known to be equal to each other. This group is called an equivalence class.

The principle is simple. If a = b and b = c, then a = c is also true. The planner can pull out this a = c, which is nowhere in the SQL, and use it as a new join condition.

Why this is useful shows up in join order.

SELECT *
FROM orders o, customers c, regions r
WHERE o.region_id = c.region_id
  AND c.region_id = r.id;
-- conditions written by the author: orders-customers, customers-regions
-- condition pulled out by the planner: o.region_id = r.id  (orders-regions)

The author wrote only the orders-customers condition and the customers-regions condition. There is no condition directly linking orders and regions. If the planner used only the written conditions, then trying to join orders and regions first, with no condition linking them, the only thing it could make is a Cartesian product. A Cartesian product is a join that produces every combination of rows from two tables, so 100,000 orders and 100 regions yield ten million rows. Almost always a disaster, so the planner discards that join order. But if the equivalence class pulls out o.region_id = r.id, a direct join between orders and regions becomes possible. A join order that would have been discarded comes back as a candidate.

Something stronger happens when a constant joins the group.

SELECT *
FROM orders o, customers c
WHERE o.region_id = c.region_id
  AND c.region_id = 5;
-- group: {o.region_id, c.region_id, 5}
-- pulled out: o.region_id = 5,  c.region_id = 5

Once the group becomes {o.region_id, c.region_id, 5}, the planner applies o.region_id = 5 and c.region_id = 5 right where each table is read. o.region_id = c.region_id was originally a condition you could only check by joining the two tables, but after the transitivity, each table is filtered by = 5 ahead of time. The predicate pushdown from the previous section ends up applying to both tables on its own. There is no longer any need to check o.region_id = c.region_id separately at the join.

This transitivity does not hold everywhere, though. Below an outer join, the NULL-filling makes a derivation like = 5 unsafe. So the planner applies transitivity only within a group of tables inner-joined to each other, and handles the region an outer join creates separately.

Beyond these five transformations, there are smaller tasks bundled into the same preprocessing step. Turning a MERGE command into a join form, inlining function-form FROM entries into the body where possible, and flattening a simple UNION ALL into one. They all share the same goal of producing a shape the planner can work with.

What this means in practice

First, "why is this condition I wrote here applied over there" is because of preprocessing. In EXPLAIN you sometimes see a condition you wrote in WHERE show up in an odd place. It is attached to a Filter or Index Cond on a table scan node rather than at a join, or a condition you never wrote appears. That is the result of predicate pushdown pulling the condition down to a table scan, or an equivalence class pulling out a condition that was not in the SQL. When you read the per-node Filter and Index Cond in EXPLAIN, knowing that the position is not where you wrote it but where the planner pushed or derived it makes the plan much easier to read.

Second, wrapping something in a subquery does not change performance. Whether you wrap for readability or write it flat, a simple subquery gets merged into the same query by flattening, so the plan is the same. However, a subquery with aggregation, DISTINCT, LIMIT, or ordering is not merged, creating a boundary across which the planner cannot weigh the inside and outside together. If a subquery boundary in a performance-critical query seems to have trapped the plan unintentionally, suspect something inside that blocks flattening. A DISTINCT habitually attached with no effect on the result, or an unnecessary LIMIT, is a common culprit.

Third, an equality join condition fills in by transitivity even if you write only one side. Writing just o.region_id = c.region_id AND c.region_id = r.id lets the planner pull out o.region_id = r.id, so you do not need to spell out a join condition for every pair of tables. But this is limited to equality (=) conditions. Range conditions (o.amount < c.credit) and inequalities are not propagated. If a join is not linked by equality and a Cartesian product creeps into the plan and slows the query down, that is a sign of a structure transitivity cannot fill. The fix is to link the missing join path directly with an equality condition.

1.4.8 Statistics: pg_statistic and Selectivity

JoongHyuk Shin — Sun, 21 Jun 2026 09:08:11 +0000

From 1.4.2 through 1.4.5, one number showed up every time the planner computed a cost. Weighing a sequential scan against an index scan, deciding which join method is cheaper, gauging intermediate result sizes while reordering joins: underneath all of it sits an estimate of "how many rows pass this condition." That number is called cardinality.

So how does the planner know it? It cannot actually count how many of 100,000 rows WHERE country = 'KR' keeps, not at planning time. Scanning the whole table just to build a plan would already be running the query once. So the planner consults statistics it prepared in advance. Where those statistics live, in what shape, and how cardinality comes out of them is the starting point of the whole cost calculation. If the start is off, every cost and join order downstream is off with it.

Selectivity Is the Surviving Fraction

The unit the planner works in is not an absolute row count but a fraction. Selectivity, the fraction of all rows that a WHERE clause keeps (between 0 and 1), is that unit. A selectivity of 0.5 means half the rows pass the condition; 0.001 means one row in a thousand survives.

Cardinality follows directly:

estimated rows = selectivity × input rows

The rows=... that EXPLAIN prints is exactly the result of this multiplication. On a 100,000-row table, a condition with selectivity 0.5 shows up as rows=50000. Every cost calculation the planner does ultimately takes this estimated row count as input. One wrong selectivity throws off the whole cost comparison.

That leaves one question. What does the planner look at to estimate selectivity?

The Planner's Source: pg_statistic

The answer is pg_statistic, the system catalog that holds per-column statistics. Statistics for each column of a table are stored here. The catalog itself is locked away from ordinary users, so you look at it through pg_stats, a view that exposes the statistics in human-readable form.

The statistics stored for one column fall into two kinds. One is a set of fixed fields present regardless of column type; the other is a set of slots filled according to the shape of the data. The fixed fields hold the fraction of NULLs, the number of distinct values, and the average byte width. The slots hold statistics of differing kinds, such as a list of common values or a histogram of the value distribution. The statistics for one column look like this:

pg_statistic (one column)
├─ fixed fields (every column)
│    null_frac     fraction of NULL rows
│    n_distinct    number of distinct values
│    avg_width     average byte width of a value
└─ slots (filled by data shape)
     MCV           common values and their frequencies
     histogram     boundaries that split the distribution
     correlation   value order vs physical storage order

Why a slot structure? Because different data types call for different statistics. A scalar value like an integer or a date benefits from a distribution histogram, but an array column needs something else, like its "most common elements." So PostgreSQL stores, in each slot, a kind code marking what sort of statistic lives there, and code reading the statistics searches by kind code rather than by slot number.

The Four Statistics Behind Selectivity Estimation

Four kinds of statistics do the actual work in selectivity estimation. Real data makes it click. I set up a 100,000-row table stat_demo. The country column has a skewed distribution (KR is half, US a fifth, JP a tenth, and the remaining 20% is spread over a few hundred rarely-used two-letter country codes); amount is a value spread evenly from 1 to 100,000; and note is half NULL. After running the statistics-collection command ANALYZE once, querying the country column's statistics from pg_stats gives this:

SELECT null_frac, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats WHERE tablename = 'stat_demo' AND attname = 'country';

null_frac         | 0
n_distinct        | 598
most_common_vals  | {KR, US, JP, FO, ...}
most_common_freqs | {0.5032, 0.1982, 0.1013, 0.00067, ...}

The four statistics divide up the work like this.

First, null_frac is the fraction of rows that are NULL. country has no NULLs, so it is 0; but the note column, half of which is NULL, would read about 0.5. The selectivity of WHERE note IS NULL is exactly that value, which on 100,000 rows estimates about 50,000 rows.

Second, n_distinct is how many distinct values there are. If an order-status column held only the three values 'paid', 'shipping', 'delivered', this would be 3. For country it came out as 598. This value serves as the denominator when estimating a value that is not common in an equality condition, because if values are spread evenly, one value takes up roughly 1/n_distinct.

This value is read differently depending on its sign. When positive, it is the count of distinct values, as just described. When negative, it means a multiple of the row count. Querying the amount column gives -0.53346, which means the number of distinct values is 0.53346 times the row count, that is, about 53,000 on 100,000 rows. The reason for also allowing a negative form is that a table's row count can be updated more often than the statistics are. Pinning the count as an absolute number would make it drift as rows grow, but storing it as a ratio lets the estimate move with the row count.

Third is most_common_vals (the most common values, MCV for short). For country the MCV came out as {KR, US, JP, FO, ...}. The leading KR, US, JP are the genuinely common values, taking up half, a fifth, and a tenth of the data. The FO at the end is different. It is one of the rarely-used codes that fill the remaining 20%; those rare codes all have a similar frequency (about 30 rows each), but in drawing the random sample FO happened to be picked most often among them and slipped onto the end of the MCV list. The fraction each value takes up sits right beside it in most_common_freqs, in the same order. KR is 0.5032, US is 0.1982. An equality condition on a common value uses this frequency directly.

Fourth, the histogram is a list of boundary values that split the value distribution into buckets. It does not appear on a categorical value like country; it appears on a column you can line up by magnitude, like amount. Querying amount gives these boundaries in histogram_bounds:

{7, 1049, 2049, 3114, 4167, 5139, ... , 99008, 99995}

With 101 boundaries there are 100 buckets between them. ANALYZE picks the boundaries so each bucket holds 1% of the rows (1,000 rows here). Because amount is spread evenly, the boundaries land at roughly 1,000 apart. A range condition like amount > 90000 follows this list of boundaries.

There is a reason MCV and the histogram are kept apart. A few values that are skewed and frequent behave differently from the rest, which are scattered at similar frequencies. The few frequent values are best remembered one frequency at a time, while the rest are best lumped into buckets. So a column can carry both an MCV and a histogram, and in that case the histogram describes only the distribution left over after the MCV values are removed.

Equality: Common Values vs Rare Values

The function that computes the selectivity of an equality condition col = constant (var_eq_const) treats common values and rare values differently. Same equality, but the estimation path forks.

If the constant is in the MCV list, the story is simple. ANALYZE already measured that value's frequency, so it is used as is. For WHERE country = 'KR':

country='KR'   -> rows=50323   (MCV frequency 0.5032 × 100000)
country='US'   -> rows=19820   (MCV frequency 0.1982 × 100000)
country='JP'   -> rows=10133   (MCV frequency 0.1013 × 100000)

The estimated row count is exactly proportional to the frequency. The more common the value, the more directly the statistics remember its frequency, so the estimate is most trustworthy.

If the constant is not in the MCV, the estimate takes one more step. How common it is, the planner does not know; but it does know the value sits among "everything that is not a common value." So it subtracts the sum of the common values' frequencies and the NULL fraction from the whole to get "the fraction the rest occupies," and divides that by the number of distinct values left after the common ones. It assumes the remaining values are all spread evenly at similar frequencies. For instance, if the common values take up 80% of the table and the remaining 20% is split among 100 distinct rare values, one rare value's selectivity is taken as 0.2 ÷ 100 = 0.002.

country='AAA' -> rows=33   (a value not in the MCV)

Tracing where this 33 comes from shows the logic of the estimate directly. The frequencies in the MCV add up to about 0.807. Most of it is taken by the common values KR, US, JP (0.5032 + 0.1982 + 0.1013 = 0.8027), with the rest of the rare MCV values adding a little more. There are no NULLs, so the remaining fraction is 1 - 0.807 ≈ 0.193. The number of distinct values left after the common ones is 598 total minus the 9 in the MCV, which is 589. So the selectivity is 0.193 ÷ 589 ≈ 0.00033, and multiplied by 100,000 it comes to about 33. That matches the 33 EXPLAIN printed.

Here an inherent limit of statistics-based estimation surfaces. AAA is in fact a value with not a single row in this table. Yet the estimate is 33. The planner does not know the value really is absent. It only has the information "not in the MCV," so it treats a value that does not exist and a value that exists rarely exactly the same, both at 33. Statistics are a summary drawn from a sample, so for a value the summary never captured, the planner can only fill in with the even-spread assumption.

When there are no statistics at all (a fresh table that has never been analyzed, say), the estimate gets cruder still. The default selectivity for equality is hardwired at 0.005, that is, one in 200. This is the same as assuming there are about 200 distinct values. It is the last-resort guess for when there is nothing to go on.

Ranges Use the Histogram, ANDs Multiply

A range condition like col > value or col < value uses the histogram seen earlier. It finds which bucket the constant falls into, and converts how far into that bucket it sits into a fraction. Since amount's boundaries were about 1,000 apart, for amount > 90000 the value 90000 sits just below the eleventh boundary from the end, leaving about 10 buckets above it. That is roughly the top 10%.

amount > 90000              -> rows=10007   (top ~10%)
amount BETWEEN 25000 AND 75000 -> rows=50071 (middle ~50%)

The condition asking for the top 10% estimates about 10,000 rows, and the one asking for the middle half about 50,000. With an even distribution, the fraction and the row count line up naturally.

When several conditions are joined with AND, the planner multiplies each one's selectivity. Putting country = 'KR' (0.5032) and amount > 90000 (about 0.10) together gives 0.5032 × 0.10 ≈ 0.050, about 5,000 rows.

country='KR' AND amount>90000 -> rows=5036   (actual 4938)

The estimate and reality nearly agree. But this multiplication hides an assumption: that the two conditions are independent. The example above was accurate because country and amount were built to be genuinely unrelated in the synthetic data. In real data, two columns are often entangled, and that is where this multiplication breaks down.

When Columns Are Correlated: Extended Statistics

Think of city and zip code. Once the zip code is fixed, the city is effectively determined. Put a condition like city = 'Seoul' AND zipcode = '06236', and the planner simply multiplies the two selectivities. But almost every row with zip code 06236 is already in Seoul, so city = 'Seoul' filters out essentially nothing beyond what the zip code condition already removed. The multiplication, treating the two as unrelated, shaves the fraction twice anyway. The result is a row count far below reality, an underestimate.

Every statistic seen so far is about a single column, so it cannot catch this entanglement. So PostgreSQL separately offers extended statistics, which collect the correlation between several columns as a group. With CREATE STATISTICS you name a group of columns, and ANALYZE measures that group's functional dependencies or its group-wise distinct count. The planner then knows city and zipcode are effectively one lump and applies the fraction only once.

CREATE STATISTICS addr_stat (dependencies) ON city, zipcode FROM addresses;
ANALYZE addresses;

Extended statistics objects have one interesting property. Unlike an index, they are designed as independent objects, not tied to a particular table.

This independence also shows up in ownership. An index, no matter who creates it, takes its owner from the table's owner; but the owner of a statistics object made with CREATE STATISTICS is whoever ran the CREATE statement. I saw this difference as an inconsistency, a bug, and proposed a patch to the PostgreSQL community to make a statistics object's owner follow the table's owner like an index does. My reasoning was this: unlike a view, which can be defined over several tables, statistics are built for a single table only, so like an index or a trigger they are strictly bound to that table, and ownership should follow the table. The proposal was not accepted. A statistics object having a different ownership model from an index is by design, and a PG core contributor pointed out the reason. There is a plan to eventually allow statistics defined across several tables, so they were deliberately made into independent objects not bound to any one table.

ANALYZE: Statistics Come from a Sample

Every statistic seen so far is filled in by ANALYZE. But ANALYZE does not read the whole table. It draws a random sample (some rows pulled at random from the table) and estimates the statistics from there.

The sample size is, by default, about 30,000 rows. Precisely, it is 300 times the setting that controls statistics precision (default_statistics_target, default 100). What is interesting is that this size is almost independent of the total table size. Whether the table has 10,000 rows or a billion, the sample is about 30,000 rows either way.

This can seem to clash with intuition. Shouldn't a bigger table call for looking at more? An opinion poll lines the intuition up. A national poll asks 1,000 to 2,000 people and produces a result with similar error whether the electorate is 10 million or 40 million. It does not scale the sample up in proportion to the population. The reliability of a sample is set by the size of the sample itself, not by what percentage of the whole it is. ANALYZE is the same. PostgreSQL's sample-size formula follows a 1998 paper (Chaudhuri, Motwani, Narasayya) on how large a sample histogram construction needs, and its conclusion too is that the sample size needed for a given precision barely grows no matter how large the table gets. So rather than growing the sample with the row count, a fixed absolute size gives enough precision.

That the statistics come from a sample also means they are always an approximation. And they are a snapshot from the moment ANALYZE ran. If the table changes a lot afterward, the statistics go stale. How stale statistics wreck an estimate is clearest seen directly. Starting from 100,000 rows (KR is half) and running ANALYZE, I then inserted 100,000 more KR rows and, without running ANALYZE again, asked the same condition:

After INSERT (before ANALYZE): country='KR' estimate = 99,573   (actual 150,030)
After ANALYZE                : country='KR' estimate = 150,060   (actual 150,030)

Right after the INSERT, the planner does roughly sense that the table grew and raises the total row count to near 200,000. But KR's frequency is still stale at 0.50. The real KR share is now 75% (150,030 / 200,000), yet multiplying by 0.50 underestimates it at about 100,000 rows. Only after ANALYZE remeasures the frequency does an estimate near 150,000 come out. The crux is that selectivity is a fraction: when the fraction is stale, the estimate misses even when the total row count is right.

Finally, back to why pg_statistic is locked away from ordinary users. The MCV slot holds actual data values (the most common country codes, the most common salary figures, and so on). The statistics themselves expose part of the data, so an unprivileged user reading the raw catalog leaks information. So the raw pg_statistic is blocked, and the human-facing pg_stats view is filtered to show only statistics for columns the reader is allowed to see.

What this means in practice

First, after a big data change, running ANALYZE yourself is the safe move. Right after a bulk load, a bulk delete, or a large update, the statistics are stale, and as the example above showed, selectivity being a fraction means the estimate misses even when the total row count is right. Autovacuum will eventually run ANALYZE, but there is a lag until it crosses its threshold. If you have to run a heavy query right after loading, slipping a manual ANALYZE in between is the better choice. When a query suddenly turns slow, checking whether the relevant column's statistics in pg_stats match reality is the starting point of diagnosis.

Second, a query whose estimate is badly off shows its cause when you line up EXPLAIN ANALYZE's estimated and actual row counts. If it says rows=33 but 50,000 rows actually came out, that node's selectivity estimate is wrong. And the error does not stop there. When one table's estimate is off, the join sitting on top of it is off by more, and it amplifies as join stages stack up. So finding the node where estimate and actual diverge by more than tenfold usually points at that column's statistics or a correlation between conditions, and the starting point of a slow query is often a single column with skewed statistics.

Third, when estimates are inaccurate on a very large table, you can raise statistics precision per column. With the sample fixed at about 30,000 rows, a large table with very many distinct values or an awkward distribution may have a sample that cannot capture the whole distribution. Raising that column's statistics target makes the sample size and the MCV and histogram sizes grow together, sharpening the estimate. For example, to raise country's target from the default 100 to 1000:

ALTER TABLE stat_demo ALTER COLUMN country SET STATISTICS 1000;
ANALYZE stat_demo;

You have to run ANALYZE again after changing the setting for the new target to take effect in the statistics. But ANALYZE cost and planning time grow with it, so it is right to apply this selectively, only to columns where the estimate is actually a problem.

1.4.7 Parallel Cost: When to Go Parallel and How Many Workers

JoongHyuk Shin — Sun, 21 Jun 2026 09:07:06 +0000

The planner weighs how to read a table, how to join, and how to aggregate by cost, and picks the cheapest candidate. Parallel query is one more decision layered on top of those candidates: whether one process handles a given scan or join alone, or several processes split the work, and if they split it, among how many. This decision is also made by the planner, not the executor. The planner costs a parallel candidate separately, puts it on the same scale as the serial one, and keeps whichever is cheaper.

So understanding parallelism comes down to three numbers. When does the planner consider parallel at all. If it does, how many workers does it assign. And how much does the cost drop once the work is split. These three numbers map directly onto the questions you hit in practice: "this table is huge, why isn't it going parallel," and "I added cores, why didn't it get that much faster." How the worker processes actually divide the pages and cooperate is the executor's job, covered in 1.5. Here we look at the numbers the planner uses to decide whether to go parallel in the first place.

Small tables are never even candidates

Parallelism is not free. Launching worker processes, setting up shared memory, and having the leader gather their results all carry a fixed cost. When a table is small, that fixed cost outweighs the gain from splitting the work, so reading it alone is actually faster. That is why the planner only considers parallelism once a table crosses a certain size.

That threshold is min_parallel_table_scan_size. The default is 8MB, which works out to 1024 pages (PG 18). The planner estimates how many pages a scan will read, and if that estimate falls below this value, it does not build a parallel path at all. This is usually the first reason you stare at an EXPLAIN plan and never see a Parallel Seq Scan. Index scans have their own threshold, min_parallel_index_scan_size (default 512KB, 64 pages). When a scan reads both the heap and an index, each threshold is checked on its own.

Write queries drop out of parallel consideration regardless of size. The SELECT inside an INSERT ... SELECT or an UPDATE never goes parallel, no matter how heavy that SELECT is. The parallel portion has to be strictly read-only, and the reason for that restriction is tangled up with how workers share state, which 1.5 covers. Here we just note the fact: a SELECT that comes with a write is not a candidate.

Worker count is set by table size

Once the planner decides to go parallel, how many workers does it launch? PostgreSQL sets this from the logarithm of the table size, with base 3 specifically. It starts at one worker and adds one more each time the page count reaches three times the threshold. Since the threshold itself starts at min_parallel_table_scan_size (8MB), the steps come out like this (PG 18 defaults).

8MB or more: 1 worker
24MB or more: 2 workers
72MB or more: 3 workers
216MB or more: 4 workers

Each time the table triples in size, the worker count goes up by one. It does not double as the size doubles; it grows much more slowly. A 1GB table does not get dozens of workers. The marginal gain from adding workers shrinks on larger tables, so the formula is conservative from the start.

There is one more cap on top of this: max_parallel_workers_per_gather, which defaults to 2. Even if the log formula yields 4, this cap of 2 trims the worker count to 2. So with no special configuration, scanning a large table normally gets at most two workers, and together with the leader, which pitches in directly, three processes split the same scan. To use more cores, you have to raise this value. Setting it to 0 turns parallelism off entirely.

Workers are not a per-query resource. The total number of parallel workers the whole server can run at once is bounded by another cap, max_parallel_workers (default 8), so when several queries go parallel at the same time, they share this pool. If the pool runs short, the planner may have planned for 2 workers but only 1 actually launches, or none do.

Parallelism divides only the CPU cost

Once you have decided to add workers and settled on a count, how is the parallel path's cost computed? The heart of it fits in one sentence. Parallelism divides only the CPU cost by the worker count; it does not divide the disk cost.

A scan cost breaks into two chunks: the cost of reading pages off disk, and the CPU cost of evaluating the WHERE conditions and processing each row that comes back. When going parallel, PostgreSQL divides only the CPU cost by the worker count. The disk cost stays at its full value.

There are two reasons the disk cost is not divided. First, no matter how many workers you attach, the total number of pages that must be read does not shrink. If a table is 10,000 pages, all 10,000 still have to come off disk no matter who reads them. Parallelism just lets several workers split those pages among themselves; it does not reduce the page count.

So would splitting the reads make the disk deliver pages faster? Counter to intuition, it does not. The disk is a single device the workers share, and there is a ceiling on how fast that device hands out pages. On top of that, when one process reads pages in order, the OS prefetches the next ones (read-ahead), so even a single reader already gets close to that device ceiling. Adding readers therefore does not make the disk deliver data N times faster. If anything, workers jumping between different regions can look like scattered access to the disk and cost a little.

PostgreSQL's cost model takes the conservative view here: it treats the disk cost as having nothing to amortize across workers and leaves it untouched. On fast SSDs parallel reads can give some benefit, but the model assumes that benefit is zero so as not to overvalue parallelism. So what parallelism actually cuts is the CPU side alone.

That said, the value the CPU cost is divided by is not exactly the worker count. The leader does not just launch workers and sit idle; it runs the same scan itself and produces rows, so the number of actual hands at work is the worker count plus the leader's share. The planner estimates that share as "the leader spends 30% of its time servicing each worker." So the divisor that actually scales the CPU cost comes out like this.

1 worker: 1 + (1 − 0.3) = 1.7
2 workers: 2 + (1 − 0.6) = 2.4
3 workers: 3 + (1 − 0.9) = 3.1
4 workers: 4 + 0 = 4.0

As workers pile up, the leader spends more time collecting their results and doing the post-processing on top, and less time on the scan itself. So the leader's share shrinks as workers grow, and by 4 workers it is treated as zero. This leader participation is toggled by parallel_leader_participation, which defaults to on.

So for parallelism to cut cost meaningfully, the CPU chunk of the scan has to be large. A scan that evaluates a heavy WHERE condition on every row, or runs a large aggregate, has a big CPU chunk, so splitting it pays off. A scan that just reads a lot of pages with light per-row work has most of its cost on the disk side, so attaching workers cuts little.

The fixed cost Gather adds

A parallel path does not only have costs that shrink; it also has costs that get added. The Gather node that collects the workers' results piles on two costs. One is the setup cost of launching workers and preparing shared memory; the other is the communication cost of handing each row a worker produces over to the leader.

The setup cost is parallel_setup_cost, which defaults to 1000. Recall that the cost of reading one page in earlier chapters was around 1, which makes 1000 a hefty fixed cost, on par with reading 1000 pages. The communication cost is parallel_tuple_cost, defaulting to 0.1 per row.

This setup cost is the decisive reason parallelism loses out on small queries. The fixed 1000 is laid down the same whether the table is small or large, but the CPU cost that gets cut is tiny when the table is small. On a small table the added 1000 outweighs the amount saved, so the parallel path ends up costing more than the serial one. The planner compares their total costs and keeps the cheaper one, so on a small query, even if a parallel candidate is built, it falls behind in the cost comparison. If the size threshold is the first gate, this cost comparison is the second gate above it.

When result order has to be preserved, GatherMerge is used instead of Gather. This happens when an ORDER BY means each worker's already-sorted output has to be merged without breaking the order. That merge carries the burden of continually comparing the front rows from each worker, so the planner charges GatherMerge 5% more for communication than Gather, plus the comparison cost of the merge sort itself. The price of keeping order is reflected directly in the cost.

Comparing serial and parallel cost in numbers

Let us put the rules so far onto actual numbers. A sequential scan cost is the sum of a disk term and a CPU term. The disk term multiplies the cost of reading one sequential page (seq_page_cost, default 1.0) by the page count, and the CPU term multiplies the cost of processing one tuple by the tuple count.

Take an accounts table with 1 million rows in 10,000 pages (about 80MB), scanned with WHERE balance > 9000. Since it is a simple comparison qual, the CPU cost per tuple is cpu_tuple_cost (0.01) plus the comparison cost cpu_operator_cost (0.0025), for 0.0125. The cost of the serial sequential scan comes out as:

disk_run_cost = 1.0 × 10000       = 10000
cpu_run_cost  = 0.0125 × 1000000  = 12500
total         ≈ 22500

Now attach 2 workers. 80MB clears the size threshold (8MB), so parallelism becomes a candidate; the log formula yields 3 workers but the default cap of 2 trims it. The divisor for 2 workers is 2.4. The disk term stays put and only the CPU term is divided by 2.4. On top of that, the Gather that collects the results adds setup of 1000 and a per-row communication cost. If 1000 rows pass the WHERE, the communication cost is parallel_tuple_cost 0.1 × 1000 = 100.

disk_run_cost = 1.0 × 10000       = 10000   (unchanged)
cpu_run_cost  = 12500 ÷ 2.4       ≈ 5208    (divided by workers)
Gather setup  = 1000
Gather comm   = 0.1 × 1000         = 100
total         ≈ 16308

22500 dropped to 16308, so the planner picks parallel. The roughly 6000 saved comes almost entirely from the CPU term being cut from 12500 to 5208. The disk term of 10000 holds equally on both sides, and the setup of 1000 is added only on the parallel side.

The numbers flip the other way when a table barely clears the size threshold. With small page and tuple counts, the CPU term is small too, so splitting it among workers only cuts a few hundred, while the setup of 1000 is still laid down in full. The parallel total then ends up larger, so the path clears the size gate but falls behind in the cost comparison. This is the setup cost acting as that second gate, expressed in numbers.

What this means in practice

First, when a large table won't go parallel, check the size threshold and the worker caps first. The check order is threefold. First, min_parallel_table_scan_size (default 8MB). If the table is smaller than this, the planner never even raises parallelism as a candidate. Next, max_parallel_workers_per_gather (default 2). If this is 0, parallelism is fully off, and to use more cores on an analytical query you raise it per session. Last, the overall pool, max_parallel_workers (default 8). In a high-concurrency environment where this pool runs short, the query runs with fewer workers than planned. When Workers Planned (the number the planner intended to launch) and Workers Launched (the number that actually started) differ in EXPLAIN ANALYZE, this pool shortage is exactly what happened.

Second, doubling the workers does not double the speed. Parallelism does not speed up in direct proportion to core count. As workers grow, the leader's share of collecting results and post-processing grows, so the effect of each additional worker tapers off, and on top of that there is the fixed setup cost. The planner's cost model itself reflects this diminishing return by shaving the leader's contribution as workers grow. So do not expect raising max_parallel_workers_per_gather to speed things up by that same ratio; it is better to vary the worker count and actually measure, finding the point where the effect flattens out.

1.4.6 Aggregate Cost: Choosing HashAgg vs GroupAgg

JoongHyuk Shin — Sun, 21 Jun 2026 09:06:01 +0000

A query like SELECT region, COUNT(*) FROM sales GROUP BY region folds many rows together, collapsing each group into a single value. This folding of many rows into one is aggregation, and COUNT, SUM, AVG are the familiar examples. PostgreSQL handles aggregation in the execution plan with one of two nodes: HashAggregate or GroupAggregate. Both do the same job, grouped aggregation, but they go about it differently, and the planner picks one or the other to nail into the plan tree for a given query. When you see HashAggregate in one EXPLAIN and GroupAggregate in another, that is the result of this choice. What decides it? How the two nodes actually run (how they accumulate state, fill the hash table, and split to disk when memory runs out) is covered in Chapter 1.5. This section looks at the step before that: how the planner compares their costs and picks one.

The two strategies have the same total; they differ at startup

The function that computes aggregate cost is cost_agg. Look inside it and one surprising fact shows up: the sort-based strategy (GroupAggregate) and the hash-based one (HashAggregate) have the same total cost. A source comment states it plainly.

in this cost model, AGG_SORTED and AGG_HASHED have
exactly the same total CPU cost, but AGG_SORTED has lower startup cost.

That makes sense. Both strategies pass every input row through the aggregate function exactly once and emit one result row per group. The total amount of work is the same, so the total cost is the same. So what is the planner comparing? The answer is the rest of that comment: startup cost.

As we saw earlier, in EXPLAIN's cost=A..B, the first number A is startup (the cost paid before the first result row appears) and the second number B is total (the cost through the last row). What earlier sections called "cost" and used for comparison was mostly the total, the second number. The two aggregate strategies diverge at the first number, startup.

GroupAggregate works on sorted input, spots where one group ends, and emits that group's result right away. The first row comes out the instant the first group closes, so the cost to the first result is small. In the cost model, GroupAggregate's startup inherits the input's startup cost almost unchanged. HashAggregate, by contrast, cannot declare any group complete until it has pulled in the entire input and finished building the hash table, so it cannot emit a single row before then. The cost model therefore loads HashAggregate's startup with the cost of reading the input to the end, that is, the input's full total cost.

This difference shows up in real EXPLAIN numbers. Suppose we group a 100,000-row table by one column, and that column happens to have an index, so sorted input arrives for free. Running the same aggregate query both ways gives these costs (about 1,000 groups).

GroupAggregate  (cost=0.29..4138.17 rows=1001)
  ->  Index Only Scan ...  (cost=0.29..3628.16 rows=100000)
HashAggregate   (cost=1943.00..1953.01 rows=1001)
  ->  Seq Scan ...         (cost=0.00..1443.00 rows=100000)

Look at the first number, startup. GroupAggregate is 0.29, near zero; HashAggregate is 1943. The index hands over the sort for free, so GroupAggregate can emit a result the moment the first group closes, and its cost to the first result is near zero. HashAggregate has to pull in all 100,000 rows and finish the hash table before the first row appears, so the cost of reading the input to the end, 1943, becomes its startup outright.

So where do we see that the totals are equal? Take each node's total and subtract the total of the input scan below it, leaving just what the aggregate node added. For GroupAggregate, 4138.17 - 3628.16 = 510.01; for HashAggregate, 1953.01 - 1443.00 = 510.01. The cost the aggregation itself adds is identical down to the penny. The total numbers (4138 and 1953) drift apart not because aggregation costs more in one case, but because GroupAggregate chose a more expensive index scan (3628) over the sequential scan (1443) to get sorted input. Strip away the input and the aggregate work is the same; what differs is when that work is paid, which is startup.

This difference actually decides the choice when there is a LIMIT on top, or a parent node that needs only some of the results. For a query like GROUP BY ... LIMIT 10 that needs only the first few rows, a low-startup GroupAggregate can build just ten groups and stop without processing the whole input. HashAggregate cannot use this advantage: whatever the LIMIT, its build phase must read the input to the end. Even when the totals are equal, the lower-startup side becomes the cheaper candidate in situations like this.

GroupAggregate's startup is low only when the sort is free

But GroupAggregate's low startup hides a precondition: "sorted input." To find group boundaries by comparing adjacent rows, rows of the same group must arrive back to back, which means the input has to be sorted by the grouping key.

When the input already arrives sorted, this precondition is met for free. If the grouping key has an index and the read goes through it, for instance, the sorted order follows at no extra cost. In that case GroupAggregate's startup really is low.

The problem is when there is no sorted input. Then the planner places a Sort node under GroupAggregate to sort the input first. Sorting means reading the whole input and lining it up, so its entire cost goes into startup: the first group cannot be emitted until the sort finishes, and the sort cannot finish until the whole input is read. The moment a fresh sort is required, GroupAggregate's startup advantage vanishes. It becomes just like HashAggregate in having to read the input to the end before the first row, with the extra work of sorting on top.

Take the same query as the sorted case above, but now without an index, so it must sort first. The costs change like this.

GroupAggregate  (cost=9747.82..10507.83 rows=1001)
  ->  Sort       (cost=9747.82..9997.82 rows=100000)
        ->  Seq Scan ...  (cost=0.00..1443.00 rows=100000)
HashAggregate   (cost=1943.00..1953.01 rows=1001)

GroupAggregate's startup jumped from 0.29 to 9747. That is the Sort's cost 9747.82 carried up wholesale into startup. The sort raises not just the startup but the total (10507) too, putting it more than five times above HashAggregate's total (1953). Unlike the sorted case where the two strategies' aggregate costs matched, the gap opens here because GroupAggregate had to buy a sort it did not have before and take on its full cost. For this query the planner picks HashAggregate without hesitation.

So the first condition that decides the choice is "is the input already sorted by the grouping key?" If it is, GroupAggregate gets its low startup without paying for a sort, which is favorable; if a fresh sort is needed, HashAggregate becomes attractive by exactly that cost.

HashAggregate's risk is the number of groups

HashAggregate skips the sort but takes on a different cost. It has to hold per-group accumulators in a hash table, and as the number of groups grows, that table presses on memory.

The memory a hash table may use is work_mem multiplied by hash_mem_multiplier. work_mem is how much memory one operation like a sort or a hash can use before spilling to disk, and hash_mem_multiplier is a factor that raises that limit by a multiple for hash-based operations only (default 2.0). The cost model estimates the number of groups and gauges whether the hash table fits within this limit. If it fits, it finishes in memory with no spill; if it overflows, a spill cost for processing in disk-split chunks is added.

The key input here is the group-count estimate. The planner estimates how many groups will come out from the grouping key's statistics (how that estimate is made is covered in Section 1.4.8). For a GROUP BY region with only a few regions, the group count is small, the hash table is light, and HashAggregate comes out cheap. For a SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id with millions of customers, the group count rivals the input size, the hash table overflows the memory limit, and a disk spill cost attaches.

When a spill happens, that much cost is added to HashAggregate's total, because the overflowed rows have to be written to disk and read back later, creating disk I/O. That I/O cost is proportional to the amount moved to and from disk, and the cost model charges twice what a sort would for the same amount of disk. Even moving the same amount, HashAggregate's disk access pattern is considered less efficient than a sort's on real hardware. The background is a difference in how the two operations use disk. HashAggregate's spill scatters the overflowed rows across several partition files by the hash of the grouping key, then reads them back partition by partition; a sort, by contrast, reads its sorted runs back in order and merges them. Going back and forth across scattered files tends to be slower on disk than reading in order, so the same page count is charged more heavily for HashAggregate. That said, why it is exactly twice is not a value derived from theory but an empirical fudge factor, and the source comment offers no precise rationale, calling it only a "generic penalty." In the end, for a query where many groups make a spill likely, this doubled disk cost lifts HashAggregate's total and tips the planner's scale toward the sort-based side.

So the second condition is "how many groups are there?" Few groups favor HashAggregate by saving the sort; a group count rivaling the input attaches a spill cost and makes the sort-based side safer. Since the group-count estimate drives this judgment, a wrong estimate throws off the choice along with it.

The planner builds both and picks by cost

The planner does not fix one of the two in advance and then cost it. At the point where grouping is handled, it builds a sort-based path and a hash-based path separately (add_paths_to_grouping_rel). The sort-based one is a path with GroupAggregate placed on sorted input (or on top of a Sort), and the hash-based one is a HashAggregate path. Once both are registered as candidates, the standard cost comparison from Section 1.4.1 picks the cheaper one. The startup difference, sort cost, and spill cost seen so far all enter this comparison as numbers and decide the outcome.

But before costs are even compared, candidate eligibility can already differ. If an aggregate function is not in a form a hash can handle, HashAggregate never makes it onto the candidate list at all. An aggregate written with WITHIN GROUP (ORDER BY ...), like percentile_cont for a median (an ordered-set aggregate), needs the values within a group sorted to produce an answer, but a hash table only gathers a group's values together without sorting them. Such aggregates leave the sort-based strategy as the only candidate.

Turning enable_hashagg off removes HashAggregate from the candidates. The interesting part is that, as we will see in Chapter 1.5, even after HashAggregate gained the ability to spill to disk, the planner treats this switch not as a soft condition ("use it only when it fits in memory") but as a hard off-switch. In memory or spilling to disk, off means off.

What this means in practice

First, when the aggregate strategy looks wrong, suspect statistics before indexes. The planner picks between HashAggregate and GroupAggregate on a single group-count estimate. If that estimate is far off, say it expects few groups when there are really millions, it may pick HashAggregate only to watch the hash table overflow memory and spill to disk. If an aggregate query suddenly slows down, compare the estimated row count (rows=) in EXPLAIN against the actual count from EXPLAIN ANALYZE, and if they diverge sharply, run ANALYZE to refresh the statistics so the planner can see the group count correctly and reconsider the strategy.

Second, one index on a frequently grouped column widens the choice of aggregate strategy. GroupAggregate's low startup holds only when the input arrives sorted by the grouping key. With an index on that column, the planner can get sorted input without a fresh sort and hold GroupAggregate as a cheap candidate. That sort is also reused by an ORDER BY above, so a query like GROUP BY region ORDER BY region finishes grouping and ordering in one go off a single index sort. Knowing which columns you frequently group and order by lets you bake into the design that an index on that column acts directly on aggregate cost.

Third, work_mem affects both which strategy the aggregate picks and how fast it finishes. The cost model charges HashAggregate's spill cost based on whether the hash table fits in work_mem (times hash_mem_multiplier). When this value is small, a spill cost is charged heavily even at the same group count, and the planner shies away from HashAggregate; when it is large enough, the planner assumes it finishes in memory without a spill and picks HashAggregate. If a heavy aggregate query is slow from spilling to disk, you can raise work_mem for that session. But work_mem is memory taken per connection, and per operation within a plan, so raising it globally by a lot is risky when many connections are active. Raising it per session for heavy aggregate queries only is the safe approach.