In a relational database, what is the difference between a logical plan and a physical plan for the same query, which component produces each, and why does the distinction exist at all?

middlemust knowfreq 55%

answer

logical = what (algebra), physical = how (algorithms)
binder+rewriter emit logical; optimizer emits physical
one logical plan, many valid physical plans
physical adds access path, join algorithm, join order, costs
wrong results = logical problem; slow results = physical problem

basics

~20 s

A logical plan says what result is wanted - relational operators like scan, filter, join, aggregate - with no algorithms. A physical plan says how: chosen access methods, join algorithms and order, with costs. Binding and rewriting produce the logical plan; the cost-based optimizer produces the physical one.

solid answer

~60 s

The logical plan is relational algebra: a tree of Filter, Join, Project, Aggregate, Sort over named relations. It expresses the result, not the method - it says "join orders to customers on customer_id", never "hash join". It comes out of binding and rewriting, and many textually different statements normalize to the same logical plan.

The physical plan is executable: each logical operator is replaced by a concrete algorithm with concrete inputs - an index range scan instead of a table scan, a hash join instead of a nested loop, a specific join order, an explicit sort or a sort avoided by exploiting index order - annotated with estimated rows and cost. The cost-based optimizer produces it by enumerating candidates and costing them against catalog statistics.

The distinction exists because it separates correctness from performance. Any physical plan for a logical plan must return the same rows, so the optimizer is free to choose among them, and can choose differently tomorrow. It also means a logical plan is one-to-many: one meaning, many executions, and picking badly among them is what query tuning is about.

The **logical plan** is relational algebra: a tree of `Filter`, `Join`, `Project`, `Aggregate`, `Sort` over named relations. It expresses the *result*, not the method - it says "join orders to customers on customer_id", never "hash join". It comes out of binding and rewriting, and many textually different statements normalize to the same logical plan. The **physical plan** is executable: each logical operator is replaced by a concrete algorithm with concrete inputs - an index range scan instead of a table scan, a hash join instead of a nested loop, a specific join order, an explicit sort or a sort avoided by exploiting index order - annotated with estimated rows and cost. The **cost-based optimizer** produces it by enumerating candidates and costing them against catalog statistics. The distinction exists because it separates *correctness* from *performance*. Any physical plan for a logical plan must return the same rows, so the optimizer is free to choose among them, and can choose differently tomorrow. It also means a logical plan is one-to-many: one meaning, many executions, and picking badly among them is what query tuning is about.

code

text · 15 lines

text

Logical:
  Project[o.id, c.name]
    Join[o.customer_id = c.id]
      Relation[orders o]
      Filter[c.country = 'FR'] -> Relation[customers c]

Physical A (selective filter):
  NestedLoop
    IndexScan customers_country ('FR')      est rows 120
    IndexScan orders_customer_id (probe)    est rows 6 per outer

Physical B (unselective filter):
  HashJoin (build: customers)
    SeqScan customers  filter country='FR'  est rows 900000
    SeqScan orders                          est rows 8000000

go deeper

for a junior

Say logical = what (filters, joins, projections) and physical = how (index vs scan, hash vs nested loop), and that the optimizer turns one into the other.

for a middle

List the concrete choices the physical layer adds - access path, join algorithm, join order, sort versus hash aggregation - and note that plan choice depends on statistics.

for a senior

Use the split diagnostically: wrong rows means fix the SQL, slow-but-correct means fix statistics, indexes or estimates, and explain plan instability as a physical-layer property.

for a principal

Discuss the invariant that any physical plan must preserve the logical plan's semantics, why that makes the optimizer's search space well-defined, and the operational cost of that freedom - non-deterministic plan choice needing guardrails.

Two descriptions of the same query

After binding, a database holds a validated, typed tree describing your query. That tree is the logical plan. Before rows can be produced, that tree must be turned into something a machine can actually run: the physical plan. Understanding what each layer is allowed to say is the core mental model behind query optimization.

The logical plan: what, not how

A logical plan is expressed in relational algebra, roughly:

Relation - a named table or view reference.
Filter (selection) - keep rows satisfying a predicate.
Project - keep or compute a set of columns.
Join - combine two inputs on a condition (inner, left outer, semi, anti).
Aggregate - group and compute aggregates.
Sort / Limit / Distinct / Set operations.

What a logical plan deliberately does not say: whether the relation is read via an index or a full scan, which side of a join is probed, whether grouping is done by sorting or by hashing, whether anything is parallel. It has no cost and no row estimates in the physical sense; it is a specification of the answer.

A useful property: many statement texts normalize to the same logical plan. A subquery in the WHERE clause and an equivalent join may normalize to the same algebra, which is why rewriting them by hand often changes nothing. Conversely, if two statements produce different logical plans, they mean different things, and the optimizer is required to keep those differences.

The physical plan: how

The physical plan replaces each logical operator with an implementation and fixes every choice the logical plan left open:

Access path. A logical Relation + Filter becomes a full table scan, an index range scan followed by row fetches, an index-only read when the index covers every needed column, or a bitmap-style combination of several indexes.
Join algorithm. A logical Join becomes a nested loop (great when the outer side is tiny and the inner side has a selective index), a hash join (great for large unsorted equi-joins with enough memory), or a merge join (great when both inputs are already ordered on the key).
Join order. With three or more relations, the optimizer chooses the shape and sequence of joins - often the single largest source of plan quality.
Grouping and ordering strategy. Aggregation by hash table or by sorting; an explicit sort operator, or no sort at all because an index already returns the required order.
Physical extras. Materialization of a reused subtree, parallel degree, memory grants and spill behaviour.

Every node also carries the optimizer's estimates - expected row count and cost - which is what makes plan output diagnosable: comparing estimated to actual rows tells you whether the optimizer was misled.

Who produces which

Parsing produces a syntax tree.
Binding resolves it against the catalog and yields the initial logical plan.
Rewriting transforms the logical plan into an equivalent logical plan.
The cost-based optimizer enumerates physical implementations of that logical plan, prices each with a cost model fed by catalog statistics (row counts, distinct values, histograms), and emits the cheapest as the physical plan.
The executor runs the physical plan.

Why the separation is valuable

Correctness is settled once. The logical plan pins the semantics. Any physical plan the optimizer builds for it must return the same rows in the same required order. That invariant is what makes it safe for the database to change its mind between executions.

Search becomes tractable. Optimization is a search over physical alternatives for a fixed logical target. Without a stable logical layer there is no well-defined space to search or compare within.

It explains plan instability. A logical plan is a function of text plus schema; a physical plan is a function of logical plan plus statistics plus available structures plus resources. Identical text can yield a new physical plan after a bulk load, a new index, or changed memory limits - and this is the source of most "nothing changed but it got slow" incidents.

It sets the tuning agenda. If the query returns wrong results, the logical layer is wrong: your SQL means something other than you intended, and no plan change will fix it. If the results are right but slow, the logical layer is fine and you argue with the physical layer - statistics, indexes, or how you feed the cost model.

A concrete contrast

For a query joining orders to customers and filtering on a customer's country, the logical plan is a filter over customers joined to orders projecting a few columns. The physical plan might be: index range scan on a country index, then nested loop into an index on orders.customer_id. Or: full scan of customers with the filter applied, hash-joined to a full scan of orders. Same rows, wildly different cost profiles depending on how many customers match.

## Two descriptions of the same query After binding, a database holds a validated, typed tree describing your query. That tree is the **logical plan**. Before rows can be produced, that tree must be turned into something a machine can actually run: the **physical plan**. Understanding what each layer is allowed to say is the core mental model behind query optimization. ## The logical plan: what, not how A logical plan is expressed in relational algebra, roughly: - **Relation** - a named table or view reference. - **Filter (selection)** - keep rows satisfying a predicate. - **Project** - keep or compute a set of columns. - **Join** - combine two inputs on a condition (inner, left outer, semi, anti). - **Aggregate** - group and compute aggregates. - **Sort / Limit / Distinct / Set operations**. What a logical plan deliberately does *not* say: whether the relation is read via an index or a full scan, which side of a join is probed, whether grouping is done by sorting or by hashing, whether anything is parallel. It has no cost and no row estimates in the physical sense; it is a specification of the answer. A useful property: **many statement texts normalize to the same logical plan**. A subquery in the `WHERE` clause and an equivalent join may normalize to the same algebra, which is why rewriting them by hand often changes nothing. Conversely, if two statements produce different logical plans, they mean different things, and the optimizer is *required* to keep those differences. ## The physical plan: how The physical plan replaces each logical operator with an implementation and fixes every choice the logical plan left open: - **Access path.** A logical `Relation + Filter` becomes a full table scan, an index range scan followed by row fetches, an index-only read when the index covers every needed column, or a bitmap-style combination of several indexes. - **Join algorithm.** A logical `Join` becomes a nested loop (great when the outer side is tiny and the inner side has a selective index), a hash join (great for large unsorted equi-joins with enough memory), or a merge join (great when both inputs are already ordered on the key). - **Join order.** With three or more relations, the optimizer chooses the shape and sequence of joins - often the single largest source of plan quality. - **Grouping and ordering strategy.** Aggregation by hash table or by sorting; an explicit sort operator, or no sort at all because an index already returns the required order. - **Physical extras.** Materialization of a reused subtree, parallel degree, memory grants and spill behaviour. Every node also carries the optimizer's **estimates** - expected row count and cost - which is what makes plan output diagnosable: comparing estimated to actual rows tells you whether the optimizer was misled. ## Who produces which - Parsing produces a syntax tree. - Binding resolves it against the catalog and yields the initial **logical plan**. - Rewriting transforms the logical plan into an equivalent logical plan. - The **cost-based optimizer** enumerates physical implementations of that logical plan, prices each with a cost model fed by catalog statistics (row counts, distinct values, histograms), and emits the cheapest as the **physical plan**. - The executor runs the physical plan. ## Why the separation is valuable **Correctness is settled once.** The logical plan pins the semantics. Any physical plan the optimizer builds for it must return the same rows in the same required order. That invariant is what makes it safe for the database to change its mind between executions. **Search becomes tractable.** Optimization is a search over physical alternatives for a fixed logical target. Without a stable logical layer there is no well-defined space to search or compare within. **It explains plan instability.** A logical plan is a function of text plus schema; a physical plan is a function of logical plan plus statistics plus available structures plus resources. Identical text can yield a new physical plan after a bulk load, a new index, or changed memory limits - and this is the source of most "nothing changed but it got slow" incidents. **It sets the tuning agenda.** If the query returns wrong results, the logical layer is wrong: your SQL means something other than you intended, and no plan change will fix it. If the results are right but slow, the logical layer is fine and you argue with the physical layer - statistics, indexes, or how you feed the cost model. ## A concrete contrast For a query joining orders to customers and filtering on a customer's country, the logical plan is a filter over customers joined to orders projecting a few columns. The physical plan might be: index range scan on a country index, then nested loop into an index on `orders.customer_id`. Or: full scan of customers with the filter applied, hash-joined to a full scan of orders. Same rows, wildly different cost profiles depending on how many customers match.

If two developers write the same query differently - one with a subquery, one with a join - will they get the same plan?
Often yes: normalization and rewriting can reduce both to the same logical plan, after which the optimizer costs the same physical alternatives and picks the same physical plan. But not always - if the two forms are not semantically identical, for instance because of NULL handling or duplicate rows, the logical plans differ and must stay different. The reliable check is to compare the produced plans rather than assume.
Which layer would you change to fix a query that returns the wrong number of rows?
The logical layer, meaning the SQL itself: wrong join type, a missing predicate, or an unintended row multiplication. Row counts are a semantic property fixed by the logical plan, so no index, statistic refresh, or optimizer hint can correct them. Physical tuning only ever changes how fast the same rows arrive.

A logical plan is the recipe's finished dish description; the physical plan is the specific pans, order of operations and burner settings a cook chooses to get there.

saying these in an interview costs you the question

Describing the logical plan as 'the plan before indexes exist' rather than as algebra with no algorithm choices.
Believing a hint or an index can change which rows a query returns.
Assuming there is one 'correct' physical plan per query rather than a cost-ranked set.
Saying join order is part of the logical plan.
Treating estimated row counts as measured facts rather than statistics-driven predictions.

In a relational database, what is the difference between a logical plan and a physical plan for the same query, which component produces each, and why does the distinction exist at all?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Two descriptions of the same query

The logical plan: what, not how

The physical plan: how

Who produces which

Why the separation is valuable

A concrete contrast

▾common follow-ups2 questions

▾analogy~10 s

▾red flags5 flags

§ context

▾related questions8 questions

▾related topics4 topics

Two descriptions of the same query

The logical plan: what, not how

The physical plan: how

Who produces which

Why the separation is valuable

A concrete contrast

answer

go deeper

context