Hash Join

A Hash Join reads its inner input once and builds a hash table in memory, keyed on the join column. Then it reads the outer input once and probes the table for each row. Each side is read once, which is why it wins when both are large. In the sample, after ANALYZE commerce.orders corrects the estimate from 612 to 206,880, the planner drops the Nested Loops and joins orders, order_items and customers with Hash Joins instead.

Why it matters

The hash table has to fit in work_mem. When it does not, PostgreSQL splits both inputs into batches and writes all but the current one to temp files. The Hash node prints Buckets: N Batches: M Memory Usage: NkB. Batches: 1 is the good case. Anything above 1 means the join went to disk. The sample runs at work_mem = 4MB, enough for 206,880 orders, not for 37,200,000 order_items. The planner hashes the smaller side, so a huge hash table is usually an estimate problem first.

What to do

1. If Batches is above 1, check which side was hashed. The Hash node's rows are the inner input. If that is the bigger table, the planner misjudged the sizes: ANALYZE both.
2. If the sizes are right, raise work_mem for that session, SET work_mem = '16MB', and run again. Not globally: every sort and hash in every connection can take that much.
3. A filter that cuts the inner side before the join shrinks the hash table too.

In the Plan tab

The Hash node is folded into its Hash Join, so one node carries both. A join that batched shows a spill label on the node, N MB disk, and the finding Hash Join spilled to disk with work_mem 4MB and a Retry · button. The inspector's Memory bar marks where work_mem sits against what the join used.