Postgres: The cost of functions in WHERE clauses

A common mistake in SQL is applying a calculation to a column inside a WHERE or JOIN clause.

It looks like this:

-- The "Bad" Way
SELECT * FROM users
WHERE LOWER(email) = '[email protected]';

-- OR
SELECT * FROM orders
WHERE DATE_TRUNC('day', created_at) = '2023-12-01';

This logic seems fine, but it kills performance.

The Problem

Database indexes store your data exactly as it is.

When you wrap a column in a function (like LOWER or DATE_TRUNC), Postgres cannot match the data against the index anymore. It doesn’t know the result of that function until it runs it for every single row.

Instead of jumping directly to the right record, the database performs a Sequential Scan. It has to read the entire table from top to bottom.

Solution 1: Change the logic

Always try to keep the column “raw” on one side of the operator. Change the value you are comparing against instead.

For dates, use a range:

-- The "Good" Way
SELECT * FROM orders
WHERE created_at >= '2023-12-01 00:00:00'
  AND created_at <  '2023-12-02 00:00:00';

Now Postgres can use the index to jump straight to the data.

Solution 2: Functional Indexes

If you absolutely must use a function (like case-insensitive search), create an index specifically for that function:

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

Now Postgres has a saved list of the lowercase versions and can search them fast. The trade-off here is that their might be a performance penalty when inserting or updating rows as the index gets calculated each time.