Hash Join Skew and Hot-Key Batches #

The Hash node reports Batches: 256 (originally 16) and Memory Usage: 1804220kB — nearly 2 GB — on a server where work_mem × hash_mem_multiplier is 128 MB. Batching was supposed to cap memory, and here it multiplied the batch count sixteen-fold and still overshot fourteen times. That pattern has one cause: many build-side rows share the same join key.

The normal spill case, where more batches do reduce memory, is covered in diagnosing hash join batch spills. This page covers the case where they cannot.

The Algorithmic Condition #

A hash join builds a table from its inner input and assigns each row to a batch using bits of the row’s hash value. When the in-memory batch exceeds the budget, the executor doubles the number of batches and moves roughly half the rows of the current batch to later ones — using one more bit of the hash.

That works when hash values are spread out. It fails when many rows share one key, because identical keys produce identical hash values, and identical hash values always land in the same batch no matter how many bits are used. After a doubling, the batch holding the hot key is exactly as large as before. PostgreSQL detects this — a split that moves no rows or all rows — and disables further growth for that join, then lets the batch exceed the budget.

The hash budget is work_mem × hash_mem_multiplier (default multiplier 2.0 from PostgreSQL 15). The planner sizes the initial batch count from estimated rows and width; it does not model skew, so a join estimated at 16 batches can be planned as healthy and executed as a runaway.

There is a partial built-in defence. When the outer (probe) side is a plain column with MCV statistics, the executor sets aside about 2% of the hash budget as a skew table for the most common outer-side values, so frequent probe values find their matches without being batched. It helps when the probe side is skewed and the build side is not. It does not help when the build side itself contains a hot key with millions of duplicates, and it is not used by parallel hash joins. The general algorithm and its eligibility rules are in hash join mechanics.

Why doubling batches cannot split a hot key Top row: four batches before doubling, with one batch overfull because it holds a hot key. Bottom row: eight batches after doubling. Ordinary keys spread across the new batches and each batch shrinks, but the hot key's rows all remain together in a single batch that is just as large as before. 4 batches holds hot key 'acme' — over budget double: use one more hash bit 8 batches 'acme' still together every copy of a key has the same hash, so it always follows the same bit

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT e.id, a.plan_tier
FROM events e
JOIN account_members a ON a.account_id = e.account_id
WHERE e.occurred_at >= '2026-09-01';
Hash Join  (actual time=5120.4..48210.9 rows=91224100 loops=1)
  Hash Cond: (e.account_id = a.account_id)
  ->  Index Scan using events_occurred_at_idx on events e  (actual rows=2104000 loops=1)
  ->  Hash  (actual time=5102.1..5102.1 rows=14800000 loops=1)
        Buckets: 262144 (originally 262144)  Batches: 256 (originally 16)  Memory Usage: 1804220kB
        --  Batches grew 16 → 256        : the executor kept splitting
        --  Memory Usage 1.8 GB          : far above work_mem × hash_mem_multiplier (128 MB)
        --  both together                : a batch that could not be split — a hot key
        Buffers: shared hit=12044 read=148220, temp read=402110 written=402110
        ->  Seq Scan on account_members a  (actual rows=14800000 loops=1)

The hot key is visible in the data, not the plan:

SELECT account_id, count(*) FROM account_members
GROUP BY account_id ORDER BY count(*) DESC LIMIT 5;
 account_id |  count
------------+---------
          1 | 9120400      -- the 'system' account: 62% of all rows
       4412 |   18204
       ...
Reading the Hash node for skew Three lines of the Hash node are annotated. Batches 256 originally 16 shows the executor kept splitting. Memory Usage of 1.8 gigabytes against a 128 megabyte budget shows splitting did not work. Temp written blocks show the spill volume that accompanied it. Batches: 256 (originally 16) executor kept doubling batches Memory Usage: 1804220kB 14× the hash budget: unsplittable temp written=402110 spill I/O on top of the overshoot growth plus overshoot together is the skew signature

Step-by-Step Resolution #

  1. Confirm the pattern. Batches above originally, and Memory Usage well above work_mem × hash_mem_multiplier:

    SHOW work_mem; SHOW hash_mem_multiplier;

  2. Find the hot build-side keys with a GROUP BY on the join column of the relation under the Hash node, as above.

  3. Remove the hot key from the join when the query does not need it. Often it is a sentinel — a system account, an “unknown” category, a default tenant:

    SELECT e.id, a.plan_tier
FROM events e
JOIN account_members a ON a.account_id = e.account_id
WHERE e.occurred_at >= '2026-09-01'
  AND a.account_id <> 1;

  4. Pre-aggregate the build side when you need only one row per key. Joining to a distinct set removes the duplicates that cause the skew:

    WITH tiers AS (
  SELECT DISTINCT ON (account_id) account_id, plan_tier
  FROM account_members ORDER BY account_id, joined_at DESC
)
SELECT e.id, t.plan_tier FROM events e JOIN tiers t USING (account_id)
WHERE e.occurred_at >= '2026-09-01';

  5. Let the planner hash the other side or use a merge join when neither rewrite applies. Test alternatives in a rolled-back transaction:

    BEGIN;
SET LOCAL enable_hashjoin = off;
EXPLAIN (ANALYZE, BUFFERS) <query>;
ROLLBACK;

    A merge join handles duplicates by sorted streaming and does not need the hot key’s rows in memory at once.

  6. Re-run and verify. Batches should equal originally, and Memory Usage should sit below budget.

Memory against the hash budget A dashed vertical line marks the 128 megabyte hash budget. Before the fix, the memory bar extends to 1.8 gigabytes, far beyond the line. After excluding the hot key, the bar reaches 96 megabytes, inside the budget. before 1,804 MB · Batches 256 (originally 16) after 96 MB · Batches 16 (originally 16) dashed line: work_mem 64MB × hash_mem_multiplier 2.0 = 128 MB

Before and After #

-- BEFORE: hot key 1 on the build side
Hash  Batches: 256 (originally 16)  Memory Usage: 1804220kB   temp written=402110     Execution Time: 48402.3 ms

-- AFTER: account_id <> 1 (sentinel excluded)
Hash  Batches: 16 (originally 16)   Memory Usage: 98304kB     temp written=18840      Execution Time: 6210.8 ms

Common Pitfalls #

Raising work_mem until the spill disappears. The hot batch needs whatever the hot key’s rows occupy; the setting only moves the ceiling. Diagnostic signal: memory usage tracking the new setting upward on each attempt. Fix: address the duplicates rather than the budget.

Reading Batches growth as a statistics problem. An underestimate also grows batches, but memory then stays near budget. Diagnostic signal: batches grew and memory stayed close to the limit. Fix: that is ordinary underestimation — follow diagnosing hash join batch spills.

Expecting the skew table to help. It targets frequent probe-side values, not build-side duplicates. Diagnostic signal: the hot key is on the relation under Hash. Fix: reduce the build side’s duplicates.

Missing concurrent memory pressure. A 1.8 GB batch in each of several concurrent sessions can exhaust the server. Diagnostic signal: memory alerts that coincide with the query’s schedule. Fix: treat skewed joins as a capacity risk, as in pool size and the total work_mem budget.

Frequently Asked Questions #

Why does increasing batches not reduce hash join memory for skewed keys? Batches split rows by bits of the hash value, and every copy of a key has the same hash. Doubling moves all copies together, so the hot batch never shrinks; the executor stops splitting and lets it exceed the limit.

Does PostgreSQL have any built-in skew handling for hash joins? A limited one: for probe-side columns with MCV statistics it keeps a small skew table for the most frequent values, using about 2% of the budget. It does not help with build-side duplicates and is not used by parallel hash joins.

Is raising work_mem the right fix for a skewed hash join? Only when the hot key’s rows fit in the new budget. Filtering, pre-aggregating, or a merge join are usually more reliable.

Up: Hash Join Mechanics