Move a filter only when the answer stays the same

The executor can run several plans for the same SQL request. Logical optimization changes the plan’s operations while preserving the result. Saving work is useful only after we establish that the new expression means the same thing.

Suppose we join orders to products and keep orders whose paid price exceeds 20. The condition uses only order fields. For this inner equality join and ordinary numeric comparison, we can filter the orders before joining them.

Compare the two paths

Assume 100 orders enter the plan and 10 satisfy the condition. Every order refers to one existing product.

Filter after joining
100 orders → 100 joined rows → 10 qualifying rows

Filter before joining
100 orders → 10 qualifying orders → 10 joined rows

Both plans return the same matching pairs. The second sends fewer orders into the join.

Moving a condition closer to its input is called predicate pushdown. A predicate is simply a condition that decides whether a row qualifies. The rewrite is justified here because removing a disqualified order before the join removes exactly the pairs the later filter would remove.

This argument assumes the condition has the same defined behavior whenever evaluated. Side effects, errors, and special expression semantics can prevent apparently harmless rearrangements.

A join that keeps unmatched rows changes the reasoning

An inner join returns matching pairs. A left outer join also retains each left-side row with no match, filling the missing right-side fields with NULL.

Consider a report that must list every customer and any order over 20. Customer A has an order for 8; customer B has one for 30. If the amount condition is part of the outer join’s matching rule, A remains with no qualifying order, and B appears with 30.

If we instead join all orders and then apply WHERE paid_price > 20, A’s row is removed. A NULL comparison also does not evaluate to true, so an unmatched customer’s row would be removed too. The supposedly equivalent rewrite changes the report.

Predict the unmatched case

Add customer C, who has no orders. Should the original “every customer and any order over 20” report include C?

Keep the preserved side of the join

Yes, with missing order fields. The report promises every customer. An outer-join matching condition can leave C unmatched while retaining C’s row. A later WHERE condition on the missing amount would reject that row unless the query explicitly accounts for it.

A useful optimizer therefore needs semantic rules, not just rules that reduce row counts. Once it has several legal plans, a cost model can choose among them.

PostgreSQL’s table-expression documentation explains the distinction between outer-join conditions and later filtering.

Extended reference

The extended chapter preserves the original technical coverage, additional examples, and citations. The explanation above supplies the concepts needed for this page; the reference develops the specialist cases.

Find a section from the original chapter