Function on column in conditions

Severity: Warning

Wrapping a column in a function call inside a WHERE clause or JOIN … ON condition 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 (or, for joins, a hash/merge join instead of an index-driven nested loop).

Examples

-- Function in WHERE — prevents index scan on email
SELECT * FROM users WHERE lower(email) = '[email protected]';

-- Function in JOIN ON — prevents nested-loop index lookup on source.some_data
SELECT *
FROM source AS s
INNER JOIN lookup AS l
  ON l.id = some_function(s.some_data);

Even if an index exists on email or some_data, PostgreSQL can’t use it because the function output 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 CAST(column AS type) or 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) = '[email protected]' 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 = '[email protected]';

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 CAST(serial AS numeric) BETWEEN 100 AND 200	Yes — a cast is a function
WHERE column::text = 'value'	Yes — a cast is a function
JOIN t ON t.id = coalesce(s.fk, 0)	Yes — function on column in JOIN condition

Detection

Query Doctor flags this nudge when it finds a function call, COALESCE expression, or type cast (CAST(col AS type) / col::type) in a WHERE clause or JOIN … ON condition whose arguments include a column reference. The nudge marker points to the function call or cast.