Hash Join in PostgreSQL EXPLAIN

What it does

A Hash Join works in two phases:

1. Build. It runs its second child, always a Hash node, which reads one input (the inner side) and puts every row into a hash table keyed on the join columns.
2. Probe. It reads its first child (the outer side) row by row, hashes each row’s join value and looks it up in the table.

Each input is read once, so the cost grows roughly with the size of both inputs rather than their product. The build phase has to finish before the first row comes out, which is why a hash join’s startup cost is high.

It only works for equality conditions (Hash Cond: (o.customer_id = c.id)). Variants include Hash Left Join, Hash Right Join, Hash Full Join, Hash Semi Join (for EXISTS / IN) and Hash Anti Join (for NOT EXISTS). PostgreSQL 16 added Hash Right Anti Join and 18 added Hash Right Semi Join, which let the planner hash whichever side is smaller for those joins.

When the planner picks it

• An equality join where both sides have many rows, too many for a Nested Loop of index lookups.
• The inner side fits (or nearly fits) in work_mem × hash_mem_multiplier: 4 MB × 2 = 8 MB with PostgreSQL 15+ defaults (hash_mem_multiplier was 1.0 in 13 and 14).
• The output doesn’t need to be in join-key order. If it does, a Merge Join on sorted inputs can win.

In parallel plans you’ll see either a Hash Join whose hash table is built separately in every process (the inner side runs once per process), or a Parallel Hash Join above a Parallel Hash, where the processes build one shared table together.

Reading its numbers

Hash Join  (cost=1493.42..3289.17 rows=42640 width=3) (actual time=8.014..26.665 rows=43200.00 loops=1)
  Hash Cond: (o.customer_id = c.id)
  Buffers: shared hit=850
  ->  Index Scan using orders_created_at_idx on orders o  (… rows=43200.00 loops=1)
  ->  Hash  (cost=868.00..868.00 rows=50000 width=7) (actual time=7.988..7.988 rows=50000.00 loops=1)
        Buckets: 65536  Batches: 1  Memory Usage: 2466kB

• Hash Cond: the equality used for lookups.
• Join Filter / Rows Removed by Join Filter: any other join conditions, checked after a match is found.
• actual time=8.014..: the first row appeared after 8 ms, once the Hash child had finished building. Compare with the Hash node’s own time.
• The Hash child’s Buckets, Batches, Memory Usage: the hash table’s size. Batches: 1 means it fitted in memory. More than one means both inputs were split and partly written to temporary files. See Hash.
• Buffers: … temp read=… written=… on the Hash Join: temporary file traffic from those batches.

The side that’s hashed should be the smaller one. If the Hash child has millions of rows and the other side a few hundred, the planner was probably misled by an estimate.

When it’s a problem

• Batches above 1. The hash table outgrew work_mem × hash_mem_multiplier and spilled to disk. Raise work_mem for the session or the one query (SET LOCAL work_mem = '64MB' inside a transaction), or select fewer columns from the hashed side: every column you select is stored in the hash table. Raising work_mem server-wide is risky, because every hash and sort in every session can use that much.
• The bigger table is hashed. Usually an estimate problem: a filter the planner can’t estimate (functions on columns, correlated conditions). Rewrite the filter so it uses plain columns, run ANALYZE, or add extended statistics (CREATE STATISTICS).
• It hashes a big table to return a few rows. If the query returns a handful of rows, a nested loop with an index on the join column would read far less. Check that the index exists and that the outer row estimate is right.
• A non-parallel hash in a parallel plan. loops=3 on the Hash child means three processes each built the same table. For large inner sides a Parallel Hash Join is cheaper; it needs enable_parallel_hash (on by default) and an inner side the planner can scan in parallel.

Example

PostgreSQL 18.6, default settings (work_mem 4 MB, hash_mem_multiplier 2):

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, counted by customer country:

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.country, count(*)
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2024-06-01' AND o.created_at < '2024-07-01'
GROUP BY c.country;

HashAggregate  (cost=3502.37..3502.57 rows=20 width=11) (actual time=31.896..31.900 rows=20.00 loops=1)
  Group Key: c.country
  Batches: 1  Memory Usage: 32kB
  Buffers: shared hit=850
  ->  Hash Join  (cost=1493.42..3289.17 rows=42640 width=3) (actual time=8.014..26.665 rows=43200.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=4) (actual time=0.013..4.370 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=868.00..868.00 rows=50000 width=7) (actual time=7.988..7.988 rows=50000.00 loops=1)
              Buckets: 65536  Batches: 1  Memory Usage: 2466kB
              Buffers: shared hit=368
              ->  Seq Scan on customers c  (cost=0.00..868.00 rows=50000 width=7) (actual time=0.007..2.805 rows=50000.00 loops=1)
                    Buffers: shared hit=368
Planning:
  Buffers: shared hit=14
Planning Time: 0.159 ms
Execution Time: 31.938 ms

All 50,000 customers went into a 2.4 MB hash table in 8 ms; then 43,200 orders were looked up in it. Hashing the customers is cheaper than 43,200 index lookups would be.

With work_mem lowered to 1 MB (a 2 MB hash limit), the same hash table no longer fits:

->  Hash Join  (cost=1689.42..4015.17 rows=42640 width=3) (actual time=9.861..25.345 rows=43200.00 loops=1)
      Hash Cond: (o.customer_id = c.id)
      Buffers: shared hit=850, temp read=150 written=150
      …
      ->  Hash  (cost=868.00..868.00 rows=50000 width=7) (actual time=9.758..9.759 rows=50000.00 loops=1)
            Buckets: 65536  Batches: 2  Memory Usage: 1491kB
            Buffers: shared hit=368, temp written=85

Two batches: half the customers and the matching half of the orders were written to temporary files and joined in a second pass. Here that’s 150 pages and barely slower; with a hash side of millions of rows and dozens of batches, it’s where the time goes.

A parallel example. Add an order_lines table, three lines for each of the first 300,000 orders:

CREATE TABLE order_lines (
  order_id   bigint NOT NULL,
  line_no    int NOT NULL,
  product_id int NOT NULL,
  qty        int NOT NULL,
  PRIMARY KEY (order_id, line_no)
);
INSERT INTO order_lines
SELECT o, l, 1 + ((o * 31 + l * 17) % 2000) * 50, 1 + (o + l) % 5
FROM generate_series(1, 300000) AS o, generate_series(1, 3) AS l;
VACUUM ANALYZE order_lines;

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, l.product_id, l.qty
FROM orders o JOIN order_lines l ON l.order_id = o.id
WHERE o.id <= 20000;

The planner kept a plain Hash Join under a Gather, so each of the three processes built its own copy of the hash table (loops=3 on the Hash):

Gather  (cost=1975.01..14205.08 rows=17627 width=22) (actual time=6.703..49.514 rows=60000.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=5202 read=1205 written=292
  ->  Hash Join  (cost=975.00..11442.38 rows=7345 width=22) (actual time=5.146..44.086 rows=20000.00 loops=3)
        Hash Cond: (l.order_id = o.id)
        Buffers: shared hit=5202 read=1205 written=292
        ->  Parallel Seq Scan on order_lines l  (cost=0.00..9483.00 rows=375000 width=16) (actual time=0.101..15.618 rows=300000.00 loops=3)
              Buffers: shared hit=4695 read=1038 written=126
        ->  Hash  (cost=730.18..730.18 rows=19586 width=14) (actual time=5.019..5.020 rows=20000.00 loops=3)
              Buckets: 32768  Batches: 1  Memory Usage: 1174kB
              Buffers: shared hit=507 read=167 written=166
              ->  Index Scan using orders_pkey on orders o  (cost=0.42..730.18 rows=19586 width=14) (actual time=0.026..2.863 rows=20000.00 loops=3)
                    Index Cond: (id <= 20000)
                    Index Searches: 3
                    Buffers: shared hit=507 read=167 written=166
Planning:
  Buffers: shared hit=16 read=7 written=7
Planning Time: 0.255 ms
Execution Time: 51.342 ms

For a 20,000-row inner side that’s cheap (5 ms in each process), and the planner judged it cheaper to repeat than to share.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts, so a hash built on the wrong side stands out by its row numbers.