Expression on column in WHERE

Severity: Warning

Using an arithmetic expression on a column in a WHERE clause prevents PostgreSQL from using a plain B-tree index on that column. The planner sees the computed result as an opaque expression and falls back to a sequential scan.

Example

SELECT * FROM order_lines WHERE ordered - served > 0;

Even if indexes exist on ordered and served, PostgreSQL can’t use them here because ordered - served is not the same expression any index was built on.

Why it happens

B-tree indexes store raw column values in sorted order. When you wrap a column in an arithmetic expression, the comparison is against the expression’s return value, not the stored column value. PostgreSQL would have to evaluate the expression on every row, defeating the purpose of the index.

This applies to any arithmetic operator: +, -, *, /, %.

For a deeper look at why indexes aren’t used through expressions, see depesz — Why is my index not being used? and Use The Index, Luke — Obfuscated Conditions.

How to fix it

Option 1: Create an expression index

Index the exact expression used in the query:

CREATE INDEX idx_order_lines_diff ON order_lines ((ordered - served));

Now WHERE ordered - served > 0 matches the index expression and PostgreSQL can use it directly.

Option 2: Rewrite the query to isolate the column

If the expression involves a single column and a constant, you can sometimes rewrite the condition algebraically:

-- Instead of:  WHERE price * 1.1 > 100
-- Rewrite to:  WHERE price > 100 / 1.1
SELECT * FROM items WHERE price > 90.91;

This only works when one side of the expression is a constant and the algebra is straightforward. Multi-column expressions like ordered - served > 0 can’t be simplified this way.

Common variations

Pattern	Same problem?
WHERE ordered - served > 0	Yes — arithmetic on columns
WHERE price * quantity > 1000	Yes — arithmetic on columns
WHERE col + 2 < 5	Yes — but can be rewritten to WHERE col < 3
WHERE id % 2 = 0	Yes — modulo on column
WHERE total / count > 10	Yes — division on column
WHERE lower(name) = 'bob'	Similar — covered by the function on column nudge

Detection

Query Doctor flags this nudge when it finds a comparison operator (=, <, >, <=, >=, <>, !=) in a WHERE clause where one side is an arithmetic expression (+, -, *, /, %) containing a column reference. The nudge marker points to the arithmetic operator.