Databases & Storage · core
Query Execution & Optimization
Logical and physical plans, cardinality estimation, join ordering, indexes, vectorized execution, and spilling.
Mental model
A query optimizer searches physical plans using imperfect estimates. Execution quality depends on access paths, join order, memory budgets, and fallback under estimation error.
How to study Query Execution & Optimization
Begin by restating the mental model in your own words, then connect it to a concrete system you have built or operated. Name the mechanism, the constraint it addresses, and the trade-off it introduces. Use Architecture of a Database System to check details, but close the source before writing your explanation. Retrieval is the learning step; rereading is only preparation.
Next, compare Query Execution & Optimization with the neighboring concepts in its roadmap. Ask what changes in correctness, latency, resource use, operability, and failure recovery. Complete Design exercise: Query Execution & Optimization and preserve the command, input, output, and one failed attempt as evidence. Finish by explaining the idea without jargon to someone who has not studied the track.
Proof of understanding
- Explain the mechanism from first principles and identify the state it reads or changes.
- Give one situation where the concept is the right choice and one where it is not.
- Predict a realistic failure mode before running the drill, then compare the prediction with evidence.
- Connect the result to a roadmap or build artifact instead of treating the concept as isolated trivia.
Learn from primary sources
Practice and explain it back
Design exercise: Query Execution & Optimization
Logical and physical plans, cardinality estimation, join ordering, indexes, vectorized execution, and spilling. Implement designOutline() returning non-empty values for: logicalPlan, costModel, physicalExecution. Each value must name a concrete mechanism or decision.
Expected evidence: A design outline with logicalPlan, costModel, physicalExecution plus an explicit failure mode or trade-off.
Open the interactive drill →Review prompts
- The plan looks reasonable but the query is slow. What estimate is usually wrong, and why does it compound?
Build evidence
Object-storage-backed index
Store index segments in object storage with a hot in-memory cache.
- Index segments persisted as immutable objects
- A cache in front of object storage
- Cold-read latency measured and noted
Prerequisites
None assigned yet.
Related concepts
None assigned yet.