Sargable WHERE: Writing Index-Friendly Predicates

A predicate is sargable (Search ARGument ABLE) when the planner can use an index on the column to jump straight to the matching rows. Wrap that column in a function — EXTRACT, lower(), date() — and the index goes dark: the database dutifully evaluates the expression for every row, i.e. a Seq Scan. Here is how to avoid that.

What disables the index

A plain B-tree indexes the column's values, not the values of a function over them. The moment the left side of your WHERE is f(column), the index on column is unusable.

-- NOT sargable: function on the column
SELECT id, email
FROM users
WHERE lower(email) = 'ann@shop.io';

-- NOT sargable: EXTRACT wraps created_at
SELECT id, amount
FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2024;

The tell is simple: if you cannot keep the column bare on one side of the comparison, the predicate is probably not sargable. The same trap covers created_at::date = '...', date(created_at), amount + 0 = 100, and coalesce(status, '') = 'paid'.

Gotcha: arithmetic on the column kills the index too. WHERE salary * 12 > 100000 is non-sargable, but WHERE salary > 100000 / 12 is fine. Push the math onto the constant side.

Rewriting to ranges

The main move is to replace a function over the column with a range on the column itself. A half-open interval >= ... AND < ... is exact (no last-day-of-time off-by-one) and uses the index.

-- good: WHERE ts >= '2024-01-01' AND ts < '2025-01-01'
SELECT id, amount
FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at <  '2025-01-01';

-- One specific day, half-open and index-friendly
SELECT id, amount
FROM orders
WHERE created_at >= '2024-03-15'
  AND created_at <  '2024-03-16';

For case-insensitive lookups, do not write lower(email) = ...; normalize the data up front or use the citext type. Then plain equality stays sargable:

-- Equality stays sargable; index on email is usable
SELECT id, email
FROM users
WHERE email = 'ann@shop.io';

LIKE and the leading wildcard

LIKE with an anchored prefix (text, then %) is sargable — a B-tree can seek by the start of the string. A leading % turns the search into a full scan.

-- Sargable: anchored prefix, uses a B-tree index
SELECT id, name
FROM users
WHERE name LIKE 'Ann%';

-- NOT sargable: leading wildcard forces a scan
SELECT id, name
FROM users
WHERE name LIKE '%nna%';

A PostgreSQL detail: prefix LIKE under a non-default locale needs an index built with text_pattern_ops. For substring search with a leading %, no plain B-tree helps — you need a trigram GIN index (pg_trgm).

Expression indexes — when you really need the function

Sometimes the function is genuinely part of the question. Then make the predicate sargable by indexing the expression itself. PostgreSQL supports this:

-- Index the expression, then this query is sargable again
CREATE INDEX idx_users_lower_email ON users (lower(email));

SELECT id, email
FROM users
WHERE lower(email) = 'ann@shop.io';

-- Functional index for a derived "year" lookup
CREATE INDEX idx_orders_created_year
ON orders (EXTRACT(YEAR FROM created_at));

The catch: the expression in the index and in the WHERE must match literally. An alternative is a generated column with an ordinary index on it. Do not pile up functional indexes where a range would do: an index on lower(email) costs space and slows writes.

MySQL and ClickHouse

In MySQL the rules are identical: WHERE DATE(created_at) = '2024-03-15' ignores the index, so rewrite to a range. Since 8.0.13 MySQL supports functional indexes (CREATE INDEX ... ((lower(email)))); before that, a generated column gave the same effect.

In ClickHouse the "index" is a sparse primary key plus sort order. A function over a key column defeats granule pruning in exactly the same way, so filter on the bare key column, preferably by range.

Bottom line: keep the column bare on one side of the comparison, swap functions for ranges, avoid the leading %, and where a function is truly required, index the expression.