Logical vs physical query plans and the optimizer
query-planning stages.
logical plan says what (relational algebra, no algorithms); physical plan says how (specific operators); cost-based optimizer enumerates physical options and picks the cheapest using statistics.
Why the distinction exists
A SQL query says what result you want, not how to get it. Separating planning into logical and physical stages lets the engine first reason about correct relational transformations, then independently choose efficient execution mechanisms.
Logical plan
The logical plan is a tree of relational-algebra operators, joins, selections, projections, aggregations, expressing what to compute and in what relational order, but saying nothing about algorithms, indexes, or access paths. Multiple logical plans can be equivalent; for example a filter can be pushed below a join, or join order can change, without altering the result. These equivalences are the search space for rewrites.
Physical plan
The physical plan commits to how each operation runs: the join method (nested-loop, hash join, or sort-merge), the access method (sequential scan versus index scan), the aggregation strategy (hash versus sort), and the concrete order of execution. It is what the executor actually runs.
Role of the cost-based optimizer
The optimizer transforms logical into physical by exploring the space of equivalent logical forms and their possible physical implementations. For each candidate it estimates cost, expected rows, IO, CPU, and memory, using statistics like table sizes, column cardinalities, and histograms fed into a cost model. It prunes the search and selects the plan with the lowest estimated cost. Because estimates rely on statistics that can be stale or inaccurate, the chosen plan is the cheapest the optimizer believes exists, not provably the global optimum.
What usually comes next
Why do stale statistics cause bad plans, what is the difference between rule-based and cost-based optimization, and how does join-order search explode combinatorially.
A concrete example
For a two-table join filtered on one table, the logical plan is join then filter; the optimizer rewrites it to push the filter down, then for the join picks a hash join if both sides are large or an index nested-loop if one side is tiny and the other indexed, choosing whichever has lower estimated cost.
Interview question
What best captures the difference between a logical and a physical query plan?
- a.Logical plans are cost-estimated; physical plans are not
- b.Logical plans run on one node; physical plans run distributed
- c.Logical plans use indexes; physical plans use full scans
- d.Logical plans describe what to compute in relational algebra; physical plans specify how, including algorithms and access pathsCorrect
Why? this is the answer
The logical plan is the algorithm-agnostic relational-algebra description of what to compute, while the physical plan chooses concrete operators, join methods, and access paths. The cost-based optimizer estimates costs to pick among physical alternatives, so it is the physical side that is cost-driven.
Just read this? Test yourself on what you have been reading.
- #query-optimizer
- #query-plan
- #cost-based-optimization
- #statistics
- #databases
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles