Logical vs physical query plans and the optimizer

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.

LIKELY FOLLOW-UPS 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.

ONE 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.