Explain constant folding, OR-to-IN normalization and transitive predicate derivation as rewrite-phase transformations, and why writing a filter as YEAR(created_at) = 2024 keeps the rewriter from helping you.

middleshould knowfreq 45%

answer

fold constants once at plan time
OR on one column -> IN list -> index range
a.k = b.k plus a.k = 5 derives b.k = 5
derivation unsound across outer-join null side
function/cast around column = no range, no propagation

basics

~20 s

Constant folding evaluates constant expressions once at planning time. OR-to-IN normalizes repeated equalities on one column into a single list so it can drive an index. Transitive derivation copies a predicate across an equality join, e.g. a.k = b.k with a.k = 5 also gives b.k = 5. Wrapping the column in a function leaves nothing to fold or match, so no index range is derivable.

solid answer

~1 min

These are all normalization rewrites that run before costing, and they exist to turn hand-written predicates into a canonical form the optimizer can reason about.

Constant folding: total > 100 * 1.2 becomes total > 120.0 once at planning time instead of per row. Folding also collapses expressions like 1 = 0 into FALSE, which can prune whole branches, and normalizes literal types so an index comparison is possible.
OR-to-IN normalization: k = 1 OR k = 2 OR k = 3 becomes k IN (1,2,3). A disjunction is hard to turn into an access path; a list of equalities on one column becomes an index range scan, a partition-elimination list, or a bitmap OR.
Transitive predicate derivation (predicate transitive closure): given the equality join a.k = b.k and a filter a.k = 5, the optimizer derives b.k = 5 and pushes it into b's scan. This can eliminate partitions and enable an index on b that was otherwise unreachable, and it dramatically changes join-order estimates.

All three need the column visible as a bare operand. YEAR(created_at) = 2024 hides the column inside a function: there is nothing constant to fold, no equality on the column to normalize or propagate, and no derivable index range. Rewriting it as a half-open range created_at >= DATE '2024-01-01' AND created_at < DATE '2025-01-01' restores all of it.

These are all **normalization** rewrites that run before costing, and they exist to turn hand-written predicates into a canonical form the optimizer can reason about. - **Constant folding**: `total > 100 * 1.2` becomes `total > 120.0` once at planning time instead of per row. Folding also collapses expressions like `1 = 0` into FALSE, which can prune whole branches, and normalizes literal types so an index comparison is possible. - **OR-to-IN normalization**: `k = 1 OR k = 2 OR k = 3` becomes `k IN (1,2,3)`. A disjunction is hard to turn into an access path; a list of equalities on one column becomes an index range scan, a partition-elimination list, or a bitmap OR. - **Transitive predicate derivation** (predicate transitive closure): given the equality join `a.k = b.k` and a filter `a.k = 5`, the optimizer derives `b.k = 5` and pushes it into b's scan. This can eliminate partitions and enable an index on b that was otherwise unreachable, and it dramatically changes join-order estimates. All three need the *column* visible as a bare operand. `YEAR(created_at) = 2024` hides the column inside a function: there is nothing constant to fold, no equality on the column to normalize or propagate, and no derivable index range. Rewriting it as a half-open range `created_at >= DATE '2024-01-01' AND created_at < DATE '2025-01-01'` restores all of it.

code

sql · 10 lines

sql

-- blocks index range + propagation: column hidden inside a function
WHERE YEAR(created_at) = 2024

-- rewriter-friendly half-open range on the bare column
WHERE created_at >= DATE '2024-01-01'
  AND created_at <  DATE '2025-01-01'

-- OR on one column normalizes to an IN list
WHERE status = 'NEW' OR status = 'OPEN' OR status = 'HELD'
-- becomes: WHERE status IN ('NEW','OPEN','HELD')

go deeper

for a junior

Know that constants are pre-computed, OR-ed equalities on one column become an IN list, and that wrapping a column in a function stops index usage.

for a middle

Explain all three rewrites with a concrete example each and give the half-open-range repair for the function-wrapped predicate.

for a senior

Add why transitive derivation is high value (partition elimination, unreachable indexes, better estimates), its outer-join restriction, and the expression-index alternative.

for a principal

Turn it into standards: predicate style rules in review, type alignment across joined columns to avoid implicit casts, and when a generated column beats query rewriting at scale.

Where these rewrites sit

After parsing and semantic analysis, and before cost-based plan search, the engine normalizes the predicate expressions. The goal is a canonical form: cheap constants pre-evaluated, disjunctions collapsed where possible, and every fact about a column made explicit so access-path selection and cardinality estimation can use it.

Constant folding

Any sub-expression whose operands are all constants (or planning-time-known parameters) is evaluated once during planning. 100 * 1.2 becomes 120.0; 'a' || 'b' becomes 'ab'. Benefits:

CPU: the arithmetic disappears from the per-row loop.
Access paths: an index comparison usually requires column op constant. Folding produces that shape and normalizes the literal's data type so no implicit conversion wraps the column.
Branch pruning: folding can produce a contradiction (WHERE 1 = 0) that lets the optimizer replace an entire subtree with an empty result, or a tautology it can drop.

What cannot be folded: anything non-deterministic or evaluated per row (a random or per-row timestamp function, a volatile user function). Engines classify functions by volatility precisely to decide what may be folded, cached, or hoisted.

OR-to-IN normalization

Disjunctions are the enemy of simple access paths, because each branch may need a different path. When several OR branches are equalities on the same column, the rewriter collapses them into a single IN list. Now the optimizer can treat it as a set of equality probes: an index range scan per value, a bitmap OR, a partition list, or a hash lookup against the values.

If the branches touch different columns, no collapse is possible; the optimizer's remaining option is a union-style plan (evaluate each branch, combine, de-duplicate) or a full scan with a filter. That is why an OR across two columns is frequently the reason a query ignores both indexes, and why splitting it into a UNION ALL of disjoint branches sometimes helps.

Transitive predicate derivation

Equality is transitive, and optimizers exploit it. From a.k = b.k (an equijoin) and a.k = 5, it follows that b.k = 5 for every row that survives the join. Deriving and pushing the second predicate is one of the highest-value rewrites available:

It filters b at its scan instead of after the join, shrinking the join input.
It can trigger partition elimination on b, skipping most of the table.
It makes an index on b.k usable when no user-written predicate mentioned b.k.
It improves estimates on both sides, so the join order chosen is a better one.

Derivation also works with range predicates in many engines (a.k > 100 plus a.k = b.k gives b.k > 100). Two caveats: it is unsound across the null-supplying side of an outer join (the derived predicate would remove rows that must be preserved), and it can be counter-productive if the derived predicate is expensive and non-selective - cost models weigh that.

Why function-wrapped columns block everything

WHERE YEAR(created_at) = 2024 compares a computed value, not the column:

There is no constant to fold on the column side; the function must be applied per row.
There is no column = constant equality, so nothing propagates transitively and nothing normalizes into an IN list on the column.
No index on created_at can be used to derive a range: the index orders raw values, and the engine cannot in general invert an arbitrary function to compute which raw values map to 2024. It must read every row and evaluate the function - a full scan plus per-row CPU.

The repair is to state the predicate as a range over the raw column: created_at >= DATE '2024-01-01' AND created_at < DATE '2025-01-01'. This is a half-open interval, so it is correct regardless of the column's time precision, and it is exactly the shape an index range scan and partition elimination want. Implicit type conversions cause the same damage silently - comparing a string column to a number can wrap the column in a cast and disable everything above.

The alternative, when the expression is genuinely required, is to make the expression itself indexable: an expression/function-based index or a persisted computed column, so the engine has a stored value to match against. That is a schema decision, not a rewrite the optimizer can perform for you.

Reviewing predicates

A useful review habit: for each predicate, ask whether the column appears bare on one side. If it does not, either rewrite to a range, or accept that this predicate will be evaluated per row after the data is read.

## Where these rewrites sit After parsing and semantic analysis, and before cost-based plan search, the engine normalizes the predicate expressions. The goal is a canonical form: cheap constants pre-evaluated, disjunctions collapsed where possible, and every fact about a column made explicit so access-path selection and cardinality estimation can use it. ## Constant folding Any sub-expression whose operands are all constants (or planning-time-known parameters) is evaluated once during planning. `100 * 1.2` becomes `120.0`; `'a' || 'b'` becomes `'ab'`. Benefits: 1. **CPU**: the arithmetic disappears from the per-row loop. 2. **Access paths**: an index comparison usually requires `column op constant`. Folding produces that shape and normalizes the literal's data type so no implicit conversion wraps the column. 3. **Branch pruning**: folding can produce a contradiction (`WHERE 1 = 0`) that lets the optimizer replace an entire subtree with an empty result, or a tautology it can drop. What cannot be folded: anything non-deterministic or evaluated per row (a random or per-row timestamp function, a volatile user function). Engines classify functions by volatility precisely to decide what may be folded, cached, or hoisted. ## OR-to-IN normalization Disjunctions are the enemy of simple access paths, because each branch may need a different path. When several `OR` branches are equalities on the *same* column, the rewriter collapses them into a single `IN` list. Now the optimizer can treat it as a set of equality probes: an index range scan per value, a bitmap OR, a partition list, or a hash lookup against the values. If the branches touch *different* columns, no collapse is possible; the optimizer's remaining option is a union-style plan (evaluate each branch, combine, de-duplicate) or a full scan with a filter. That is why an `OR` across two columns is frequently the reason a query ignores both indexes, and why splitting it into a `UNION ALL` of disjoint branches sometimes helps. ## Transitive predicate derivation Equality is transitive, and optimizers exploit it. From `a.k = b.k` (an equijoin) and `a.k = 5`, it follows that `b.k = 5` for every row that survives the join. Deriving and pushing the second predicate is one of the highest-value rewrites available: - It filters b at its scan instead of after the join, shrinking the join input. - It can trigger partition elimination on b, skipping most of the table. - It makes an index on `b.k` usable when no user-written predicate mentioned `b.k`. - It improves estimates on both sides, so the join order chosen is a better one. Derivation also works with range predicates in many engines (`a.k > 100` plus `a.k = b.k` gives `b.k > 100`). Two caveats: it is unsound across the null-supplying side of an outer join (the derived predicate would remove rows that must be preserved), and it can be counter-productive if the derived predicate is expensive and non-selective - cost models weigh that. ## Why function-wrapped columns block everything `WHERE YEAR(created_at) = 2024` compares a *computed* value, not the column: - There is no constant to fold on the column side; the function must be applied per row. - There is no `column = constant` equality, so nothing propagates transitively and nothing normalizes into an `IN` list on the column. - No index on `created_at` can be used to derive a range: the index orders raw values, and the engine cannot in general invert an arbitrary function to compute which raw values map to 2024. It must read every row and evaluate the function - a full scan plus per-row CPU. The repair is to state the predicate as a range over the raw column: `created_at >= DATE '2024-01-01' AND created_at < DATE '2025-01-01'`. This is a half-open interval, so it is correct regardless of the column's time precision, and it is exactly the shape an index range scan and partition elimination want. Implicit type conversions cause the same damage silently - comparing a string column to a number can wrap the column in a cast and disable everything above. The alternative, when the expression is genuinely required, is to make the expression itself indexable: an expression/function-based index or a persisted computed column, so the engine has a stored value to match against. That is a schema decision, not a rewrite the optimizer can perform for you. ## Reviewing predicates A useful review habit: for each predicate, ask whether the column appears bare on one side. If it does not, either rewrite to a range, or accept that this predicate will be evaluated per row after the data is read.

When is transitive predicate derivation not allowed?
Across the null-supplying side of an outer join: deriving a filter onto that side would remove rows that must survive NULL-extended, changing the result. It is also skipped when the join predicate is not an equality, and optimizers may decline when the derived predicate is expensive to evaluate and not selective enough to pay for itself.
If a report genuinely needs to filter by a computed expression, what are the options besides rewriting to a range?
Create an expression-based (function-based) index on exactly that expression, or add a persisted computed/generated column and index it. Both give the engine a stored, ordered representation of the computed value so an index range becomes possible. The trade-off is extra write cost and storage, and the query must use the identical expression for the index to match.

saying these in an interview costs you the question

Believing the optimizer can invert an arbitrary function to derive an index range
Thinking constant folding happens per row rather than once at planning
Assuming any OR can be turned into an IN list, even across different columns
Ignoring implicit type conversions that silently wrap a column in a cast
Expecting transitive derivation to apply across an outer join's null-supplying side

Explain constant folding, OR-to-IN normalization and transitive predicate derivation as rewrite-phase transformations, and why writing a filter as YEAR(created_at) = 2024 keeps the rewriter from helping you.

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Where these rewrites sit

Constant folding

OR-to-IN normalization

Transitive predicate derivation

Why function-wrapped columns block everything

Reviewing predicates

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions5 questions

▾related topics3 topics

Where these rewrites sit

Constant folding

OR-to-IN normalization

Transitive predicate derivation

Why function-wrapped columns block everything

Reviewing predicates

answer

go deeper

context