OptimizationIntermediate
What happens when you run SELECT * FROM users WHERE email = ?
“Trace the query from the client to the result.”
What this tests
- End-to-end engine understanding
Answers by level
Read the beginner answer first and notice what is missing.
The text arrives over a connection and is parsed into a tree; names are resolved against the catalog. The rewriter expands *. The planner enumerates access paths — sequential scan vs the email index — prices each from statistics, and picks the cheapest. The executor runs that plan, pulling rows; pages come from the buffer cache, a miss reads from disk. The rows are serialised back.
Green flags · Red flags
Strong green flag · Separates planning from execution cleanly.
Green flags
- Names parser/planner/executor
- Buffer cache and disk
- Index vs seq scan
Red flags
- "It just finds it"
- No mention of the planner or statistics
Follow-up questions
F1
Where would this query be slow?
Scenario
Explain why the same query is 3 ms one day and 400 ms after a bulk import (hint: statistics).