Query Optimizer Internals: From Parse Tree to Execution Plan
The optimizer is the part of the database you talk to most and see least. We follow a query from parser to execution: parse tree, rewrite, logical plan, then the cost-based search over join orders and access paths. We visualize how the planner estimates row counts and why estimates are the whole game. Using real EXPLAIN output, we watch the optimizer's decisions — seq scan vs. index scan, nested loop vs. hash join, join order flips — and what happens when statistics are stale or skewed. The video ends with the practical skill: reading a plan to understand what the optimizer believed about your data.
Topics covered:
- Parse, rewrite, planning, and execution phases
- Cardinality estimation and the cost model
- Join ordering and access path selection
- Reading EXPLAIN to see the optimizer's assumptions
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.
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.
ACID Isolation Levels Explained
From read uncommitted to serializable — understanding phantom reads, dirty reads, and how databases enforce transaction isolation.
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.
DetailsSQL Joins and Execution Plans: Nested Loop, Hash, and Merge
How the database executes joins — nested loop, hash join, and merge join — and how to read execution plans to see which strategy your query gets.
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.
DetailsDepth, delivered weekly
One technical dispatch a week — articles and episode notes before they go public.
One technical dispatch per week. No noise.