Skip to content
The Data EngineeringThe Data EngineeringBeta
About
Main content

Predicate simplification, constant folding and sargability

Which predicates reach storage at all?

The justification is one line and it is unusually clean. Evaluation cost is per row. Moving an evaluation from per-row to per-query saves a factor of n. This is the only rewrite whose benefit scales with the size of the data without needing to know the size of the data. That is exactly why it is applied unconditionally and never costed.

Example: WHERE amount * 1.18 > 100 * 2 can be folded to WHERE amount * 1.18 > 200. The 100 * 2 is computed once, not a billion times.

What can be folded

What is foldable is decided by the , not by the expression: a function can be folded only if it is deterministic and free of side effects. PostgreSQL therefore classifies functions as immutable (same arguments always give the same result), stable (same result within one statement) or volatile (may change at any call). now() is stable within a statement. It is evaluated once per execution but cannot be folded at plan time. That is why a cached plan containing now() must re-evaluate it, and why date-based against now() is a different mechanism from against a literal date.

Three-valued logic removes identities

removes a large part of the set of identities you would expect:

  • x <> x is , not FALSE (when x is NULL).
  • NOT (x > 5) is not x <= 5.
  • p OR NOT p is not TRUE.

So the simplifier works in a three-valued (Kleene) algebra, and systems track as an explicit derived property. One of the highest-value rewrites depends on it:

Example. orders LEFT JOIN couriers c ON ... WHERE c.region = 'West'. For an order with no courier, c.region is NULL, and NULL = 'West' is UNKNOWN, so those rows are removed by the filter. The outer join keeps rows that the filter will then remove, so it can be changed to an inner join with the same result. This matters because an inner join can be reordered and an outer join mostly cannot. This single rewrite hands an entire subtree back to the join-order search. Generated SQL from BI tools, ORMs and view stacks uses left joins constantly and then filters them, so this rewrite fires constantly.

Sargability: the gate to data reduction

Sargability (from "search ARGument ABLE") is where this section connects to everything in Data reduction. A predicate reaches an index, a zone map or a partition filter only if it has the form column op constant. WHERE year(ts) = 2024 is not in that form. WHERE ts >= '2024-01-01' AND ts < '2025-01-01' is.

Some perform this inversion for a few known functions. Most do not, for a sound reason: inverting an arbitrary function is generally impossible. The consequence is stark: the simplifier's coverage decides which predicates reach the storage layer at all. Pruning, skipping and pushdown all come after the question "did the predicate survive normalization in a matchable form?" A wrapped column is not a small inefficiency. It for that query.

In one sentence: folding turns per-row work into per-query work, so its benefit is n× and needs no ; three-valued logic deletes half the identities you would expect; and sargability is the gate that decides whether any data-reduction machinery gets to run.