Join Optimization

Introduction

Joins are where queries live or die. Understanding the three physical join types — nested loop, hash join, merge join — and when each is fast lets you write SQL that scales.

Understanding the topic

Core concepts to understand:

• Nested loop: good for small × large with index on large.
• Hash join: best for large × large equality joins.
• Merge join: best when both inputs are pre-sorted.
• Indexes on join columns enable nested loop & merge.
• EXPLAIN ANALYZE shows which the optimizer picked.

Syntax reference

Visual workflow / architecture:

Plan
└─ Hash Join  (cost=...)
    ├─ Hash       on orders.user_id
    │    └─ Seq Scan on orders
    └─ Index Scan on users using users_pkey

Real-world use

When a 50ms query suddenly becomes 5 seconds, it's almost always because the optimizer flipped from index nested-loop to a sequential hash join. Stats and indexes save the day.

Best practices

• Keep statistics fresh (ANALYZE).
• Index every FK + join column.
• Read EXPLAIN ANALYZE plans regularly.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Three physical join algorithms.
• Q2. When does the planner pick each?
• Q3. Why are fresh statistics critical?
• Q4. Reading an EXPLAIN ANALYZE.
• Q5. Hint mechanisms (and why most DBs avoid them).