Hash in PostgreSQL EXPLAIN

What it does

Hash only ever appears as the second child of a Hash Join (or Parallel Hash under a Parallel Hash Join). It reads every row from its own child, the join’s inner side, and stores them in a hash table keyed on the join columns. When it’s finished, the Hash Join reads its other input and probes the table.

The table has a memory limit: work_mem × hash_mem_multiplier. With defaults that’s 4 MB × 2 = 8 MB on PostgreSQL 15 and later; on 13 and 14 the multiplier defaulted to 1.0, so 4 MB. If the rows don’t fit, the Hash splits them into batches by hash value: one batch stays in memory, the rest go to temporary files, and the join processes them one at a time, writing the matching rows from the other side to temporary files too.

When the planner picks it

Whenever it picks a Hash Join; the two always come together. The planner chooses which input to hash (usually the smaller one, by its estimates) and sizes the table for the estimated row count.

Reading its numbers

->  Hash  (cost=1243.00..1243.00 rows=1000 width=18) (actual time=13.329..13.330 rows=10000.00 loops=1)
      Buckets: 16384 (originally 1024)  Batches: 2 (originally 1)  Memory Usage: 417kB
      Buffers: shared hit=368, temp written=24

• rows: rows put into the hash table. Compare with the estimate: here 1,000 expected, 10,000 arrived.
• Buckets: slots in the hash table. PostgreSQL sizes it from the estimate and enlarges it at run time if far more rows arrive. (originally 1024) means it was sized for the estimate and grew.
• Batches: how many parts the table was split into. 1 means it all fitted in memory. Anything higher means rows were written to temporary files. (originally 1) means the planner expected it to fit and it didn’t; the split was decided during execution.
• Memory Usage: peak memory of the in-memory part. With more than one batch, it stays under the limit (here 417 kB under a 512 kB limit).
• Buffers: temp written=… on the Hash, and temp read/written on the Hash Join above: the temporary file traffic. The PostgreSQL docs note that disk usage for batches isn’t otherwise shown.
• actual time: build time. The Hash Join can’t return anything until this finishes.

When an “originally” appears, an estimate was wrong. That’s worth fixing even when the query is fast, because the same wrong estimate shapes the rest of the plan.

When it’s a problem

1. Many batches. Each extra batch means writing and rereading part of both inputs. Fixes:
   • Raise work_mem for the query or session: SET LOCAL work_mem = '64MB' in a transaction. Avoid large server-wide values: every hash and sort in every session can use that much.
   • Or raise hash_mem_multiplier (default 2.0) to give hash tables more room without enlarging sorts.
   • Select fewer columns from the hashed side; every selected column is stored in the table.
2. “originally” on Buckets or Batches. The estimate was low. Look at the filter on the hashed side: functions on columns (lower(country) IN (…) in the example) get a default selectivity guess. Rewrite to use the plain column where possible, create an expression index (its statistics are collected by ANALYZE), or run ANALYZE after bulk changes.
3. The wrong side is hashed. If the Hash holds millions of rows and the other side is small, the planner misjudged sizes; same fixes as above.
4. A huge build for a small result. Hashing a whole table to find a few matches suggests a nested loop with an index would be cheaper; check the index exists and the estimates are right.

Example

PostgreSQL 18.6. customers has 50,000 rows, a fifth of them in the four countries asked for; orders has 1,000,000 rows:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE customers (
  id         int PRIMARY KEY,
  name       text NOT NULL,
  country    text NOT NULL,
  created_at timestamptz NOT NULL
);
INSERT INTO customers
SELECT i, 'Customer ' || i,
       (ARRAY['GB','US','DE','FR','NL','IE','ES','IT','SE','NO',
              'DK','FI','PL','PT','BE','AT','CH','CA','AU','NZ'])[1 + (i * 7) % 20],
       timestamptz '2023-01-01' + i * interval '20 minutes'
FROM generate_series(1, 50000) AS i;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers (id),
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);
VACUUM ANALYZE customers, orders;

June’s orders from customers in four countries, with the country written in lower case:

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE lower(c.country) IN ('nz', 'gb', 'de', 'fr')
  AND o.created_at >= '2024-06-01' AND o.created_at < '2024-07-01';

With default settings:

Hash Join  (cost=1255.92..3051.67 rows=853 width=22) (actual time=15.644..24.308 rows=8640.00 loops=1)
  Hash Cond: (o.customer_id = c.id)
  Buffers: shared hit=850
  ->  Index Scan using orders_created_at_idx on orders o  (cost=0.42..1684.23 rows=42640 width=12) (actual time=0.015..4.100 rows=43200.00 loops=1)
        Index Cond: ((created_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-07-01 00:00:00+00'::timestamp with time zone))
        Index Searches: 1
        Buffers: shared hit=482
  ->  Hash  (cost=1243.00..1243.00 rows=1000 width=18) (actual time=15.614..15.615 rows=10000.00 loops=1)
        Buckets: 16384 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 636kB
        Buffers: shared hit=368
        ->  Seq Scan on customers c  (cost=0.00..1243.00 rows=1000 width=18) (actual time=0.015..14.180 rows=10000.00 loops=1)
              Filter: (lower(country) = ANY ('{nz,gb,de,fr}'::text[]))
              Rows Removed by Filter: 40000
              Buffers: shared hit=368
Planning:
  Buffers: shared hit=14
Planning Time: 0.158 ms
Execution Time: 24.622 ms

The planner can’t use the country statistics through lower(), so it guessed 1,000 customers and sized the table with 1,024 buckets. 10,000 arrived; the table grew to 16,384 buckets and still fitted in memory (636 kB).

With SET work_mem = '256kB' (a 512 kB hash limit), the same table doesn’t fit:

Hash Join  (cost=1255.92..3051.67 rows=853 width=22) (actual time=13.380..25.748 rows=8640.00 loops=1)
  Hash Cond: (o.customer_id = c.id)
  Buffers: shared hit=850, temp read=183 written=183
  …
  ->  Hash  (cost=1243.00..1243.00 rows=1000 width=18) (actual time=13.329..13.330 rows=10000.00 loops=1)
        Buckets: 16384 (originally 1024)  Batches: 2 (originally 1)  Memory Usage: 417kB
        Buffers: shared hit=368, temp written=24
        …

Batches: 2 (originally 1): the planner thought 1,000 rows would fit easily, and the hash split in two during the build. PostgreSQL 14 printed the same Buckets line for the default run, with whole-number row counts.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts, such as the 1,000 against 10,000 on this Hash.