Cookbook recipe

Choosing the right join: nested loop, hash, or merge

Applies to PostgreSQL 13–17 Last reviewed May 2026 Grounded in source

What This Is

A production experience capsule based on real incident patterns.

Why It Matters

Builds production judgment under pressure.

Next Step

Run the decision point → · Enter full simulator →

Estimated investigation4 min

Scenario

The planner picked a nested loop that explodes at runtime. Understanding when each join wins helps you fix the inputs so the planner chooses well. Diagnose it See the join type and its row estimate: EXPLAIN…

Investigation Path

The planner picked a nested loop that explodes at runtime. Understanding when each join wins helps you fix the inputs so the planner chooses well.

Diagnose it

See the join type and its row estimate:

EXPLAIN (ANALYZE) SELECT ... FROM a JOIN b ON a.k = b.k WHERE ...;

Why it happens

Nested loop is great for small outer + indexed inner; hash join wins for large unsorted sets that fit in memory; merge join wins when both inputs are already sorted. A bad row estimate makes the planner choose the wrong one.

This is a Pro lesson

Get every Learning Pathway and cookbook recipe — grounded in PostgreSQL source code, with diagnostics, fixes, and prevention for each topic.

Continue this lesson to learn:

  • How to fix it
  • Prevent it next time
  • Related & next steps
  • All 36 Learning Pathway lessons
  • 170+ cookbook recipes
  • Source-grounded diagnostics & fixes
Unlock everything — $24.99/month $199/year — save 35%

Secure checkout Cancel anytime Source-grounded

Career Impact

This scenario builds production judgment and operational confidence under pressure.

Open Career Dashboard →

Keep going

Related & next steps

More like this

Connection slots nearly exhausted pg_toast Table Bloat Write-Ahead Logging: How WAL Guarantees Durability Track table and index bloat How MVCC decides which row version you see

Concepts on this page

Extended statistics Query planner Index
Browse the cookbook Step-by-step recipes Follow a pathway Learn it in order Open the glossary Every concept, plainly
Was this helpful?

← All cookbook recipes