Join Strategies

Force the planner's hand between Nested Loop and Hash Join, and measure exactly what the wrong choice actually costs

PostgreSQL has three fundamentally different ways to execute a join, and picks between them based on estimated row counts, available memory, and whether a useful index or sort order already exists. Nested Loop re-scans (or index-probes) the inner table once per outer row — cheap when the outer side is small, catastrophic when it is not. Hash Join builds an in-memory hash table from one side and probes it once per row from the other — usually the right choice for two large, unsorted tables. Merge Join walks both sides in sorted order simultaneously — excellent when that order already exists, wasteful when it has to be created just for this.\n\nFor the specific join this lab builds, the planner already makes the right call on its own: Hash Join, by default, with no hints needed. That default choice is exactly what makes it worth deliberately overriding — enable_hashjoin and enable_mergejoin can be switched off for one session, forcing Nested Loop instead, so the "wrong" choice can be measured directly against the right one rather than just described.

Reading the Default Plan

EXPLAIN SELECT * FROM big_a JOIN big_b ON big_a.id = big_b.a_id;

Hash Join  (cost=1347.00..2224.26 rows=50000 width=20)
  Hash Cond: (big_b.a_id = big_a.id)
  ->  Seq Scan on big_b  ...
  ->  Hash  (cost=722.00..722.00 rows=50000 width=8)
        ->  Seq Scan on big_a  ...

Two 50,000-row tables, no useful index for the join column, no existing sort order — a textbook Hash Join situation, and the planner picks it without being told to.

Forcing the Alternative

SET enable_hashjoin = off;
SET enable_mergejoin = off;
EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM big_a JOIN big_b ON big_a.id = big_b.a_id;

Nested Loop  (cost=0.29..17456.96 rows=50000 width=0) (actual rows=50000 loops=1)
  Buffers: shared hit=150246
  ->  Seq Scan on big_b  ...
  ->  Index Only Scan using big_a_pkey on big_a  (actual rows=1 loops=50000)

With both alternatives disabled, the only strategy left is Nested Loop — an index probe into big_a, run once for every one of big_b's 50,000 rows.

The Real Cost, Measured

| Strategy | Buffers | Execution Time | |----------|---------|-----------------| | Hash Join (default) | shared hit=468 | 1729 ms | | Nested Loop (forced) | shared hit=150,246 | 4760 ms |

Over 320 times more buffer accesses, and nearly three times the wall-clock time — for the identical result set, on the identical data. Nested Loop is not a bad strategy in general; it is the wrong strategy for two large, equally-sized tables joined on a condition with no additional filter narrowing either side down first.

Nested Loop

For every row produced by the outer side, re-executes a scan (ideally an index scan) against the inner side to find matches. Its total cost scales with outer-row-count × inner-lookup-cost, which is cheap when the outer side is small and the inner lookup is fast, and expensive when the outer side is large — even with a fast per-lookup index scan, tens of thousands of lookups add up.

Hash Join

Builds an in-memory hash table from one input (ideally the smaller one) keyed on the join column, then makes a single pass over the other input, probing the hash table once per row. Total cost scales roughly with the combined size of both inputs rather than their product, which is why it usually wins for two large, unsorted tables with no helpful index on the join condition.

👀 Read the Planner's Own Default Choice

Run the join with no hints at all, and see which strategy the planner picks on its own.

psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM big_a JOIN big_b ON big_a.id = big_b.a_id;"

student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM big_a JOIN big_b ON big_a.id = big_b.a_id;" SET QUERY PLAN ----------------------------------------------------------------------- Hash Join (cost=1347.00..2224.26 rows=50000 width=20) Hash Cond: (big_b.a_id = big_a.id) -> Seq Scan on big_b (cost=0.00..746.00 rows=50000 width=12) -> Hash (cost=722.00..722.00 rows=50000 width=8) -> Seq Scan on big_a (cost=0.00..722.00 rows=50000 width=8) (5 rows)

🔧 Force Nested Loop and Measure It

Disable both alternatives to Nested Loop, then measure its real cost with ANALYZE.

psql -U postgres -d beer_db -c "SET enable_hashjoin = off;" -c "SET enable_mergejoin = off;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM big_a JOIN big_b ON big_a.id = big_b.a_id;"

student@lab:~$ psql -U postgres -d beer_db -c "SET enable_hashjoin = off;" -c "SET enable_mergejoin = off;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM big_a JOIN big_b ON big_a.id = big_b.a_id;" SET SET SET QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=17581.96..17581.97 rows=1 width=8) (actual rows=1.00 loops=1) Buffers: shared hit=150246 -> Nested Loop (cost=0.29..17456.96 rows=50000 width=0) (actual rows=50000.00 loops=1) Buffers: shared hit=150246 -> Seq Scan on big_b (cost=0.00..746.00 rows=50000 width=4) (actual rows=50000.00 loops=1) Buffers: shared hit=246 -> Index Only Scan using big_a_pkey on big_a (cost=0.29..0.33 rows=1 width=4) (actual rows=1.00 loops=50000) Index Cond: (id = big_b.a_id) Heap Fetches: 50000 Index Searches: 50000 Buffers: shared hit=150000 Planning: Buffers: shared hit=168 Planning Time: 25.203 ms Execution Time: 4760.565 ms (15 rows)

📊 Compare Against the Default, Directly

Re-enable the alternatives and run the identical measured query again to get the Hash Join's own real numbers for a fair comparison.

psql -U postgres -d beer_db -c "RESET enable_hashjoin;" -c "RESET enable_mergejoin;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM big_a JOIN big_b ON big_a.id = big_b.a_id;"

student@lab:~$ psql -U postgres -d beer_db -c "RESET enable_hashjoin;" -c "RESET enable_mergejoin;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM big_a JOIN big_b ON big_a.id = big_b.a_id;" SET SET SET QUERY PLAN ------------------------------------------------------------------------------------------------------------ Aggregate (cost=2349.26..2349.27 rows=1 width=8) (actual rows=1.00 loops=1) Buffers: shared hit=468 -> Hash Join (cost=1347.00..2224.26 rows=50000 width=0) (actual rows=50000.00 loops=1) Hash Cond: (big_b.a_id = big_a.id) Buffers: shared hit=468 -> Seq Scan on big_b (cost=0.00..746.00 rows=50000 width=4) (actual rows=50000.00 loops=1) Buffers: shared hit=246 -> Hash (cost=722.00..722.00 rows=50000 width=4) (actual rows=50000.00 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 1428kB Buffers: shared hit=222 -> Seq Scan on big_a (cost=0.00..722.00 rows=50000 width=4) (actual rows=50000.00 loops=1) Buffers: shared hit=222 Planning: Buffers: shared hit=184 read=7 Planning Time: 46.244 ms Execution Time: 1729.037 ms (16 rows)

Lab 3.1.3 complete. A join strategy's real cost, measured directly instead of assumed:\n\n\n Default plan read : ✅ Hash Join, chosen correctly with no hints\n Nested Loop forced and measured : ✅ 150,246 buffer hits, 4760ms\n Hash Join measured for compare : ✅ 468 buffer hits, 1729ms\n

Enable JavaScript to run the live terminal and track your progress.