A query filters on `lower(email)` and there is an index on `email`. The query is slow and the plan shows a sequential scan. Why, and what are the fixes?
Show the full answer Hide the answer
What is being tested
Whether you read query plans and understand that an index matches an expression, not a column name.
The mechanism
The index stores email values in their original form. The query asks for rows where
lower(email) equals something. The engine cannot use the index because it has no ordering of
lower(email) — it would have to apply the function to every stored value to find out, which is
exactly the scan it was trying to avoid.
This class of problem — a non-sargable predicate — appears in many forms and is one of the most common causes of a mysteriously unused index:
WHERE lower(email) = ...WHERE date_trunc('day', created_at) = ...WHERE id::text = ...(an implicit type cast)WHERE amount + fee > 100WHERE column LIKE '%term'(leading wildcard)
The fixes, best first
1. Normalise on write. Store the email lowercased in the first place, with a unique index on it. The query becomes a plain equality and the ambiguity disappears permanently. This is usually the right answer because the underlying issue is that the data has two representations.
2. Expression index. CREATE INDEX ON users (lower(email)). Works immediately and requires no
data change. Cost: another index to maintain on every write, and the query must match the expression
exactly.
3. Generated column plus index. A stored column computed from the source, indexed normally. More explicit than an expression index and visible in the schema.
4. A case-insensitive collation or type, where the engine supports it, which removes the need for the function entirely.
Why the other options are wrong
A corrupted index would produce errors or wrong results, not a planner choice. Stale statistics are a genuinely common cause of bad plans and worth checking — but they cause the planner to make a wrong choice between viable options, not to be unable to use an index at all. A wrong column type would fail at index creation.
What a strong answer adds
Checking whether this query should exist. If the application is doing case-insensitive lookups because it never decided on a canonical representation for email addresses, the index is a patch over a modelling problem, and there is probably also a duplicate-account bug waiting.