Function on column in WHERE

Severity: Warning

Wrapping a column in a function call inside a WHERE clause prevents PostgreSQL from using a plain B-tree index on that column. The planner sees the function output as an opaque expression — it can’t reason about its relationship to the stored index values — so it falls back to a sequential scan.

Example

SELECT * FROM users WHERE lower(email) = 'alice@example.com';

Even if an index exists on email, PostgreSQL can’t use it here because lower(email) is not the same expression the index was built on.

Why it happens

B-tree indexes store values in sorted order and support fast lookups by comparing the indexed column directly against a search term. When you wrap the column in a function, the comparison is no longer against the raw column value — it’s against the function’s return value. PostgreSQL would have to call the function on every row to evaluate the predicate, which defeats the purpose of the index.

This applies to any function: lower(), upper(), trim(), date(), coalesce(), casts like column::text, and more.

How to fix it

Option 1: Create an expression index

Index the exact expression used in the query:

CREATE INDEX idx_users_lower_email ON users (lower(email));

Now WHERE lower(email) = 'alice@example.com' matches the index expression and PostgreSQL can use it directly.

Option 2: Rewrite the query to avoid the function

If you control the data, normalize values at write time so the query doesn’t need the function:

-- Store emails already lowercased, then query directly
SELECT * FROM users WHERE email = 'alice@example.com';

Option 3: Use a case-insensitive collation or operator

For case-insensitive text matching specifically, PostgreSQL’s citext extension or the ILIKE operator may be alternatives — though each has its own trade-offs with index support.

Common variations

Pattern	Same problem?
WHERE lower(name) = 'bob'	Yes — function on column
WHERE date(created_at) = '2024-01-01'	Yes — use a range instead: WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
WHERE coalesce(deleted_at, '1970-01-01') = '1970-01-01'	Yes — use WHERE deleted_at IS NULL instead
WHERE column::text = 'value'	Yes — a cast is a function

Detection

Query Doctor flags this nudge when it finds a function call in a WHERE clause whose arguments include a column reference. The nudge marker in the formatted SQL view points to the function call.