PostgreSQL

Postgres Index Not Being Used on LOWER or UPPER Query

A standard index on a text column doesn't get used when you wrap that column in LOWER() or UPPER() in a query, because a regular index is built on the raw column values — Postgres needs an index built specifically on the transformed expression to be able to use it for a query filtering on that same transformation.

The Problem

You have an index on an email column, expecting case-insensitive lookups to use it, but EXPLAIN shows a full sequential scan instead:

CREATE INDEX idx_users_email ON users(email);

EXPLAIN SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
Seq Scan on users  (cost=0.00..1834.00 rows=1 width=120)
  Filter: (lower(email) = 'user@example.com'::text)

The index clearly exists and is defined on the right column, yet the query planner ignores it entirely for this specific query.

Why It Happens

A standard B-tree index stores values exactly as they appear in the column — it has no knowledge of what LOWER(email) would produce for each row, since that's a transformation applied at query time, not something reflected in the index's own stored structure. Because of this, the index simply isn't a match for a query filtering on the transformed value, and Postgres correctly falls back to scanning the whole table and applying the function to each row instead. This is expected planner behavior, not a bug or a missed optimization opportunity Postgres failed to notice.

The Fix

Create an expression index that matches the exact transformation used in your queries, rather than a plain index on the raw column:

CREATE INDEX idx_users_email_lower ON users(LOWER(email));

With this index in place, a query using the exact same expression can now use it:

EXPLAIN SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
Index Scan using idx_users_email_lower on users  (cost=0.29..8.31 rows=1 width=120)
  Index Cond: (lower(email) = 'user@example.com'::text)

The key requirement is that the query's expression must match the index's expression exactly — an index on LOWER(email) won't help a query using UPPER(email), since these are considered entirely different expressions from the planner's perspective, even though they serve a conceptually similar case-insensitive purpose.

For consistent case-insensitive matching across your application, pick one direction (lowercase is the more common convention) and use it uniformly everywhere email or similar text is queried, both in your indexes and in your application's query-building code:

-- Consistent everywhere in the codebase
WHERE LOWER(email) = LOWER($1)

As an alternative to expression indexes and manual case normalization at query time, consider using PostgreSQL's citext extension, which provides a case-insensitive text type that handles comparisons transparently without needing LOWER()/UPPER() in every query, and can be indexed normally with a standard B-tree index:

CREATE EXTENSION IF NOT EXISTS citext;
ALTER TABLE users ALTER COLUMN email TYPE citext;
CREATE INDEX idx_users_email ON users(email); -- standard index, works for case-insensitive matches

This shifts case-insensitivity to be a property of the column's type itself, removing the need to remember to wrap every query in LOWER() consistently, though it does require adjusting the column's type, which is a more involved migration than simply adding an expression index.

Still Not Working?

If you've added the expression index but EXPLAIN still shows a sequential scan, check whether table statistics are stale, since the planner's decision to use an index also depends on its cost estimate, not just whether a matching index technically exists:

ANALYZE users;

It's also worth confirming the query genuinely uses the exact same function and argument types as the index definition — a subtle mismatch, like the index being created on LOWER(email) but the query comparing against LOWER(email::text) with an explicit cast that isn't present in the index expression, can be enough of a difference for the planner to no longer recognize them as the same expression.

You might also like