From sentence to structure

A bookstore report joins customers, orders, and payments, then keeps unpaid orders in one region. The analyzed query knows exactly which objects and operators those words denote, but it still resembles the author's sentence. If PostgreSQL treated that written order as an algorithm, SQL would gain little over application loops. Planning begins by exposing the relational structure so legal alternatives can be considered.

A logical plan is not yet a promise about physical algorithms. A join node does not necessarily mean nested loops, hash join, or merge join. A scan node does not necessarily mean a sequential scan or an index scan. Logical planning is about what must happen, not exactly how it will happen. This separation lets the database first reason about equivalence and then reason about cost.

The rewrite surface

Suppose a query joins customers, orders, and payments, then filters for unpaid orders in one region. The written SQL has a particular order, but the logical plan can expose a set of joins and predicates. Some filters can move closer to base relations. Some projections can remove unused columns early. Some joins can be reordered because inner joins are associative and commutative under suitable conditions. These transformations give the planner much of its leverage.

Trace a rewrite without changing the question

The initial structure may join customers to orders, join payments, and only then apply `orders.status = 'unpaid'`. Because the predicate refers only to orders, PostgreSQL can usually apply it at the orders input. It may also discard wide order columns once later operators no longer need them. Both rewrites reduce downstream work while producing the same observable relation.

That last clause is the invariant. A rewrite is valid because of semantics, not because the new tree looks cheaper. Moving a condition across an outer join might eliminate a null-extended row that the original query preserved. Duplicating a volatile function might call it twice. Moving work across a security barrier might reveal information. Logical optimization is therefore proof-guided rearrangement: preserve meaning first, expose cheaper choices second.

Equivalent is not always obvious

Two forms of the same request can deserve the same route. One query filters orders by status after joining customers. Another places the status condition in a subquery that selects unpaid orders first. If the forms are semantically equivalent, a good planner should be able to reach the same efficient route. The user should not have to encode every performance detail in SQL.

But not every rewrite is safe. Outer joins preserve unmatched rows, so pushing a predicate across them can change which null-extended rows survive. Volatile functions cannot be freely duplicated or reordered because calling them twice may not be the same as calling them once. Limits and ordering can make transformations visible to the user. The logical planner needs rules that understand semantics, not just tree shapes.

Boundaries that must stay boundaries

Selections are the easiest place to see the value. If a table has one million orders and only one thousand are unpaid, applying `status = 'unpaid'` before a join can shrink the work dramatically. Projection has a similar effect. If later stages need only `customer_id` and `amount`, carrying a wide description field through a hash table wastes memory. Logical plans create opportunities to reduce data before physical algorithms are chosen.

Join order is the classic planning problem. Joining three tables can be done in several orders, and joining ten can produce a huge search space. The logical planner represents the possible join relations and legal combinations. It does not need to materialize every possible tree in full detail at once, but it must keep enough alternatives alive for cost-based planning. A poor early choice can dominate execution time.

Subqueries and common table expressions add more cases to handle. Some subqueries can be flattened into the surrounding query, which exposes more optimization opportunities. Others must remain separate because of aggregation, limits, volatility, or semantic boundaries. A common table expression may behave like an optimization fence in some designs or be inlined in others. The planner's job is to recognize when a written boundary is essential and when it is merely a convenient way the user expressed a relation.

Set operations such as union, intersect, and except also become logical operators. They combine relations with rules about duplicates and compatibility. Grouping introduces aggregate operators that depend on grouping keys and aggregate functions. Window functions add computations over partitions without collapsing rows. Each feature expands the planner's vocabulary while keeping the same core aim: represent the desired relation as a composition of well-defined operators.

Logical planning also prepares for permissions and security barriers that cannot be ignored. A view or row-level policy may require certain filters to happen in a controlled order relative to user-provided predicates. Optimization is powerful, but it must not move information across security boundaries. The best plan is not valid if it changes who can infer what.

A rewrite checklist

Before transforming a plan, identify the equivalence law being used, the SQL features that can invalidate it, and the observable result that must remain fixed. Cost is deliberately absent from that checklist. A cheaper tree becomes a candidate only after semantic safety has been established; otherwise optimization has changed the program.