skip to content

When a predicate like UPPER(email) = ? cannot be range-rewritten, how do you keep it index-friendly?

level: seniorimportance: should knowfreq 42%

answer

  1. some functions have no inverse to move
  2. check whether it is secretly a range
  3. fix the data, not the comparison
  4. transform the parameter, never the column
  5. last resort lives in the schema

basics

~20 s

Stop transforming the column: normalize the values on write so the stored form is already canonical, then compare the bare column to a normalized parameter. If the stored data cannot be normalized, the fix moves into the schema — an indexed expression or a persisted generated column — not the WHERE clause.

solid answer

~60 s

`UPPER`, `TRIM`, `COALESCE` and hashing are not invertible, so there is no constant boundary to move across the comparison the way you would with `price * 1.2` or a date truncation. Three options remain, in the order I would try them: 1. **Normalize on write.** Store the email lowercased, then write `WHERE email = LOWER(?)` — the function moves to the *parameter*, which is evaluated once. Note `WHERE LOWER(email) = LOWER(?)` does not help; the column is still wrapped. 2. **Let a case-insensitive collation do the comparison**, so the query compares bare columns. Support and syntax differ sharply between engines, so verify on yours. 3. **Move the fix into the schema** — index the expression itself, or persist it as a generated column and filter on that. The query stays as written; the definition of what is indexed changes. A few shapes only *look* unrewritable: `SUBSTRING(code FROM 1 FOR 3) = 'ABC'` is really a prefix test and becomes `code LIKE 'ABC%'`, and `COALESCE(deleted_at, DATE '9999-12-31') > CURRENT_DATE` becomes `deleted_at IS NULL OR deleted_at > CURRENT_DATE`. Check for those before reaching for schema changes.

code

sql · 5 lines
sql
-- column wrapped: no index range possible
SELECT id FROM users WHERE UPPER(email) = UPPER(?);

-- emails normalized to lowercase on write: column stays bare
SELECT id FROM users WHERE email = LOWER(?);

go deeper

for a junior

Know that putting a function on both sides of the comparison fixes nothing, and that the goal is always to get the column standing alone. Recognising the shape is enough at this level.

for a middle

Distinguish invertible transformations, which you move across the comparison, from ones like UPPER or a hash, which you cannot. Be able to rewrite the two disguised cases: a SUBSTRING prefix and a COALESCE default.

for a senior

Weigh the three real options out loud — normalize on write, a case-insensitive collation, or indexing the expression — and name the migration, backfill and invariant work that the first one actually implies.

for a principal

Own the policy: where canonical forms are defined, who enforces them, and when a schema-side workaround is acceptable for queries the organisation does not control. Keep engine-specific levers deliberate rather than habitual.

## The class of predicate this is about Sargability repairs come in two kinds. For invertible transformations — arithmetic with a constant, date truncation — you move the operation to the other side of the comparison and the column comes out bare. For everything else there is nothing to move: `UPPER(email)`, `TRIM(code)`, `COALESCE(col, x)`, a hash of a token, or `col % 10` cannot be undone with constants, so no amount of algebra in the WHERE clause produces a range over the stored values. Recognising which kind you are holding is the actual skill. Attempting an inverse that does not exist is how a slow query becomes a wrong one. ## First: check whether it is secretly rewritable Several common shapes look opaque and are not. **Prefix extraction is a range.** `SUBSTRING(code FROM 1 FOR 3) = 'ABC'` asks for values whose first three characters are `ABC`, which is exactly `code LIKE 'ABC%'` — a bare-column predicate. (Whether a given engine can then use an index for a left-anchored pattern depends on the column's collation, so confirm on yours.) **COALESCE on the column is a disjunction.** `COALESCE(deleted_at, DATE '9999-12-31') > CURRENT_DATE` means "never deleted, or deleted at some future date", which is `deleted_at IS NULL OR deleted_at > CURRENT_DATE`. Both branches now name the bare column. How well an engine handles the two branches varies, but you have at least given it a shape it can reason about. **A function applied to both sides is the worst of both.** `WHERE LOWER(email) = LOWER(?)` is written constantly and helps nothing: the parameter side was already cheap, and the column side is still wrapped. If it must be there, put it only on the parameter. ## Option 1: normalize on write The most durable fix is to make the stored value already canonical, so no transformation is needed at read time. Store emails lowercased and trimmed at insert, then query `WHERE email = LOWER(?)`. The column is bare; the single `LOWER` call on the parameter runs once, before the scan. This is a data decision, not a query decision, and it has consequences worth stating in an interview: you must decide whether the canonical form replaces the original or sits beside it (`email` plus `email_normalized`), and you must backfill and then enforce the invariant so it cannot drift. In exchange, every query against that column — not just the one you were tuning — becomes sargable for free, and the equality semantics become explicit in the schema rather than repeated in every WHERE clause. ## Option 2: let the comparison itself be case-insensitive If the engine supports a case-insensitive collation on the column, `WHERE email = ?` compares bare columns and does the case folding as part of comparison. That keeps queries clean and needs no application discipline. The caveats are real: collation support, naming, and the rules for which operations preserve it differ substantially between engines, and changing a column's collation means rewriting the data and the indexes over it. Verify behaviour on your engine before promising it, and never assert portable support. ## Option 3: move the fix into the schema When the query text cannot change — a vendor application, a reporting tool, thousands of call sites — the transformation can be indexed instead of avoided. Most major engines let you index an expression, or let you declare a generated column holding the derived value and index that; you would then filter on the generated column directly. Syntax varies by engine, and there are conditions on determinism and on how exactly the query's expression must match the indexed one, so treat this as an engine-specific lever rather than a portable answer. The judgement to voice: this option keeps the query untouched and pays for it in write cost and in a schema object whose purpose is invisible from the query. That is a fine trade for one hot predicate and a bad one as a habit. ## What not to do Do not add a second index on the raw column and hope. Do not "help" the comparison by casting the column. And do not change matching semantics while claiming to change only performance: if the data has mixed case and your fix normalizes the parameter alone, you have altered which rows come back. Whichever option you take, state explicitly whether the result set stays identical — normalizing on write does change what is stored, and that is a migration question, not a tuning one. ## How to close the loop Whatever you choose, verify afterwards that the predicate is now attached to the index access rather than applied as a leftover per-row filter, and compare row counts from the old and new queries on real data. A sargability fix that silently changes matching is far more expensive than the scan it replaced.

  • Why does WHERE LOWER(email) = LOWER(?) not help at all?
    Because the column is still wrapped. Only the parameter side was ever cheap — it is evaluated once regardless. Putting `LOWER` on both sides gives the appearance of symmetry while leaving the index unusable. If the stored data is known-normalized, apply the function to the parameter alone.
  • What is the risk of normalizing on write purely as a performance fix?
    It changes what is stored, so it is a migration with a backfill and an invariant to enforce, not a query tweak. It can also change matching semantics if the original casing carried meaning. Decide whether the canonical form replaces the original or sits beside it, and say so explicitly in review.
  • When would you accept the expression in the query and change the schema instead?
    When the query text is not yours to change — a vendor product, a BI tool, or hundreds of call sites — and the predicate is hot enough to justify the extra write cost. It is a good trade for one specific predicate and a poor default, because the reason the schema object exists is invisible from the query.

saying these in an interview costs you the question

  • Writes LOWER on both sides and believes it restores index use
  • Assumes every engine transparently matches an indexed expression
  • Normalizes only the parameter when stored data has mixed case
  • Adds another index on the raw column and expects the function to use it
  • Misses that SUBSTRING prefix tests rewrite to a LIKE prefix

context

resolving links…