Scenario
A reporting query joining a 2-million-row transactions table to a recently-populated accounts table runs in 2ms on staging (10,000 accounts) but takes 45 minutes on production (2 million accounts). The planner chose Nested Loop based on stale statistics showing 10 rows in accounts — actual row count is 2 million.
How to Identify
Conditions:
EXPLAIN ANALYZE shows Nested Loop with inner table loops=N where N is the outer row count- Inner scan
actual rows >> rows estimate
- Total execution time = outer_rows × inner_scan_cost (multiplicative disaster)
- Statistics on inner table are stale (recent bulk load without ANALYZE)
pg_stat_user_tables.last_analyze on inner table predates the data growth
Analysis Steps
-- 1. Check loops count on inner scan — this is the multiplier
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT t.id, t.amount, a.name
FROM transactions t
JOIN accounts a ON a.id = t.account_id;
-- Look for: "loops=N" on inner scan node — N × inner_cost = total cost
-- 2. Check statistics freshness on both tables
SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname IN ('transactions', 'accounts');
-- 3. Compare estimated vs actual rows on inner table node
-- estimated rows=10, actual rows=2000000 → bad statistics
-- 4. Diagnose with enable_nestloop off (diagnostic only, not a fix)
SET enable_nestloop = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT t.id, t.amount, a.name
FROM transactions t
JOIN accounts a ON a.id = t.account_id;
RESET enable_nestloop;
-- If Hash Join is now chosen and is much faster → confirms the diagnosis
Pitfalls
- Never leave
SET enable_nestloop = off in production. It disables Nested Loop for ALL queries, degrading OLTP workloads where Nested Loop + index is the correct choice.
- Nested Loop is correct and fast for small inner relations. The problem is the planner choosing it for a large inner relation due to bad statistics.
- The fix is always statistics — not disabling join types.
- After
ANALYZE, re-run EXPLAIN to confirm the planner switches to Hash Join or Merge Join automatically.
pg_hint_plan forcing a join type is a temporary workaround, not a root fix.
Resolution Approach
Run ANALYZE on the inner table immediately — the planner will recalculate estimates and switch to Hash Join automatically. Add ANALYZE to the end of all ETL scripts that populate tables used in joins.