Suboptimal Join Type (Hash vs Merge vs Nested Loop)

Scenario

A query joining a 5M-row orders table to a 200-row lookup table is using a Hash Join and spilling to disk (Batches: 4). Memory usage spikes during business hours when many similar queries run concurrently, causing OOM pressure on the server.

How to Identify

Conditions:

• EXPLAIN ANALYZE shows Hash Batches: N where N > 1 (hash join spilling to disk)
• Large memory usage in Hash node: Peak Memory Usage: XXXXXKB
• High buffers: temp read/written values in EXPLAIN output
• pg_stat_bgwriter showing elevated buffers_alloc
• Queries joining small lookup tables still using Hash Join when Nested Loop + index would be faster

Analysis Steps

-- 1. Identify the join type and spill
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, s.label
FROM   orders o
JOIN   order_status s ON s.id = o.status_id;

-- Look for:
--   Hash Batches: >1  → spilling to disk
--   Peak Memory Usage in the Hash node
--   Rows Removed by Filter in inner scans (Nested Loop)

-- 2. Check current work_mem
SHOW work_mem;

-- 3. Estimate memory needed for exact in-memory hash
-- Rule of thumb: ~24 bytes per row in hash table
-- For 5M rows: 5,000,000 * 24 bytes = ~120MB work_mem needed

-- 4. Check if both sides have usable indexes for Merge Join
SELECT indexname, indexdef
FROM   pg_indexes
WHERE  tablename IN ('orders', 'order_status');

Pitfalls

• Never disable join types globally with SET enable_hashjoin = off in production. This forces the planner away from Hash Join for ALL queries, degrading many unrelated queries.
• Hash Batches > 1 means spilling — it does not mean the join type is wrong. It means work_mem is too small for this query’s data volume.
• Merge Join requires sorted input — it is efficient only when both sides are already sorted or have indexes on the join columns.
• Nested Loop is correct for small inner relations (lookup tables); it becomes catastrophic for large inner tables due to repeated scans.
• Setting work_mem globally at a high value can cause OOM: 100 connections × multiple sort/hash nodes × large work_mem = exhausted RAM.

Resolution Approach

Identify which join type is being used and why it’s suboptimal: