The plan, running

The planner’s side of a query: statistics in, one chosen plan out. This lesson starts where that board stops, at the moment the chosen plan begins to run and needs memory to do it.

Before you start. The Query Planner showed the planner: the part of Postgres that reads a summary of your data and bets on one way to answer a query. Database 07: The Query Layer built the other half by hand: a plan is a tree of small steps, and each step pulls rows from the one below it, one row at a time. This lesson is about what those steps do when the rows they must hold do not fit in the memory they were given.

The idea in 30 seconds

A plan is a tree of steps that pull rows from the steps below. Most steps stream rows through with almost no memory; a sort, a hash aggregate and the hash side of a hash join must hold their input first.
work_mem is how much memory one such step may use. When its rows do not fit, a sort writes sorted runs to temporary files and merges them; a hash aggregate writes partitions and processes them as later batches.
Before Postgres 13 (24 September 2020), a hash aggregate the planner had misjudged could grow in memory without limit. Jeff Davis first proposed bounding it in August 2014.
work_mem is per step, per session, at once, and hash steps get it times hash_mem_multiplier (2 since Postgres 15). Raising it for everyone trades disk for the risk of running the machine out of memory.
Parallel workers split a scan under a Gather step, and each one brings its own work_mem.

Read the story · Skip to the numbers