SQL Joins and Execution Plans: Nested Loop, Hash, and Merge
A join is a strategy decision, and the planner has three weapons. We execute the same join three ways — nested loop, hash join, merge join — and watch the memory and I/O behavior of each. Then we feed the same query through a real planner and read the execution plan to see which strategy it picked and why. We cover the conditions that force a strategy: index availability, sort order, memory limits, and the dreaded nested-loop-on-every-row. With the mechanics visible, you can look at a plan's node shapes and immediately diagnose the common join performance failures.
Topics covered:
- Nested loop, hash, and merge join mechanics
- Memory and I/O profiles per strategy
- Reading execution plans node by node
- Why planners choose — and mischoose — strategies
Related articles
How SQL Query Optimizers Think
Cost-based optimization, join ordering, and statistics — why your query plan changed overnight and what to do about it.
The Query Planner Cost Model
How PostgreSQL's optimizer picks execution plans, why EXPLAIN ANALYZE lies, and what cost parameters actually control.
Why Your Query Is Slow (And It's Not the Index)
Buffer pool misses, WAL contention, lock waits, and connection pooling — the non-index causes of database slowness.
More in Databases
B-Trees: The Shape of Databases
Why every major database is a tree shaped like a disk page — and how to read your index's health from its shape.
WatchPostgres Internals Tour: Processes, Buffer Pool, WAL, and MVCC
A guided tour of PostgreSQL internals — process model, buffer manager, WAL, and MVCC — the mechanisms that make Postgres behave the way it does.
DetailsConnection Pools, Database-Side: What a Connection Really Costs
What actually happens to your database when connections pile up — the connection lifecycle, pool sizing math, and why max_connections is not a tuning knob.
DetailsStorage Engines: LSM-Trees vs. B-Trees
LSM-trees vs. B-trees — how each storage engine writes, compacts, and reads, and what that means for write and read amplification in your workload.
DetailsSharding Strategies: Partition Keys, Distribution, and Rebalancing
How sharding actually works — partition keys, data placement, cross-shard queries, and the operational reality of splitting one database into many.
DetailsReplication Explained: WAL Shipping, Lag, and Failover
How database replication actually works — the transaction log, the lag, and the failure modes of synchronous and asynchronous replication in production.
DetailsTransactions and Isolation Levels: ACID, MVCC, and Anomalies
What transactions actually guarantee — ACID mechanics, MVCC, and the real behavior behind each isolation level, demonstrated with concrete anomalies.
DetailsDatabase Indexes Visualized: B-Trees, Covering Indexes, and Planner Decisions
How database indexes actually work — B-trees, hash indexes, covering indexes, and when the planner will or won't use the index you made.
DetailsQuery Optimizer Internals: From Parse Tree to Execution Plan
What happens inside a query optimizer — parse, rewrite, join ordering, cost models, and how the planner decides the plan your query gets.
DetailsDepth, delivered weekly
One technical dispatch a week — articles and episode notes before they go public.
One technical dispatch per week. No noise.