Turning queries into logical plans

From sentence to structure

An analyzed query knows what every name and expression means, but it still resembles the user's sentence. Planning begins by moving from sentence shape to relational structure. The database wants a representation where filters, joins, projections, grouping, and ordering can be rearranged without changing the result. That representation is the logical plan, a tree of operations that describes the result in terms the optimizer can transform.

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.

Equivalent is not always obvious

Two forms of the same request can deserve the same route. One query filters orders by status in the `where` clause after joining customers. Another places the status condition in a subquery that selects unpaid orders first. If the two forms are semantically equivalent, a good planner should be able to reach the same efficient route. The user should not have to spell out every performance detail in SQL. Logical planning is what makes a declarative language practical.

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.