Functions On Indexed Columns Kill Indexes

Problem

`WHERE LOWER(email) = 'a@x.com'` ignores the index on `email`. Explain why, and propose two fixes.

Input

Conceptual.

Output

Explanation + DDL

Constraints

—

Sample input

—

Sample output

Functions wrap the indexed column; the planner can no longer match the index keys to the predicate. Either index the expression, or store a normalised column.

Explanation

Indexes match exact column expressions. Wrap the column → no match.

optimisationfunction-index@Atlassian

Visible test cases

in: explain

out: ok

Your solution — run it, use AI if stuck

sql

Solution & hidden tests are Pro-only — upgrade

All problems Back to courses