# Why Postgres Ignores Your Index (Sargability, Taught by a Kata)

> Your query is correct, your tests pass, and the index you carefully created is never touched. The usual culprit is one innocent-looking habit: wrapping the column in a function. Here is what sargability means, how to catch the problem with EXPLAIN, and how I built a coding kata that grades your query plan so the lesson actually sticks.

Published: 2026-07-10. Author: Milan Jovanović.

Canonical: https://milanjovanovic.tech/blog/sql-index-not-used-sargability

Postgres ignores your index when the predicate is not **sargable**: when the column side of the comparison is wrapped in a function, a cast, or arithmetic.
The index stores raw column values, so a wrapped column can never seek into it, and the only option left is scanning every row.
The fix is to transform the parameter, not the column, and the only tool that verifies it is `EXPLAIN`.

Your query returns the right rows.
Every test is green.
And the index you carefully created is never touched.

This failure mode is invisible precisely because nothing fails: the query is correct at 6 rows and a disaster at 60 million, and the difference never shows up in a unit test.
It is also, in my experience, the single most common database performance bug in application code, and it usually comes from one innocent-looking habit.

## What Makes a Query Non-Sargable?

Say a CI server stores one row per build in a `builds` table, with an index on `result`.
You want every build with a given result, case-insensitively, so you write the defensive classic:

```sql
SELECT id FROM builds
WHERE lower(result) = lower(@value)
ORDER BY id;
```

Correct. And the index on `result` is now useless.

A B-tree index stores the **raw column values** in sorted order.
Postgres can seek into it for `result = 'CANCELLED'`, but `lower(result)` is a different value that exists nowhere in the index, so the only option is to compute `lower()` for **every row in the table**.
That is a sequential scan, by construction.

The property has an old-fashioned name: **sargability** (from "Search ARGument-able").
A predicate is sargable when the column stands alone on its side of the comparison, and every function you wrap around the column takes the index off the table.
The same trap has many costumes:

- `lower(email) = lower(@email)` and friends
- `EXTRACT(YEAR FROM placed_at) = 2026`, or in EF Core LINQ, `o.PlacedAt.Year == year`, which translates to exactly that
- `CAST(id AS text) LIKE @pattern`
- Arithmetic on the column: `price * 1.2 > @limit`

The fix is always the same move: **transform the parameter, not the column**.
A year filter becomes a range over the raw column (`placed_at >= '2026-01-01' AND placed_at < '2027-01-01'`).
And the case-insensitive comparison? If the data is stored in one consistent case, compare the raw column and let the index seek; if you genuinely need case-insensitivity, put an expression index on `lower(result)` or use `citext`, and the wrapped form becomes sargable.

## Seeing It: EXPLAIN Is the Only Truth

You cannot detect this by reading the query, timing it, or counting green tests.
The only tool that tells the truth is the query plan:

```sql
EXPLAIN (FORMAT JSON)
SELECT id FROM builds
WHERE lower(result) = lower(@value)
ORDER BY id;
```

`EXPLAIN` without `ANALYZE` only plans, never executes, so it is safe anywhere.
If the plan tree contains a `Seq Scan` on your table where you expected an index node, you have your answer.

This mattered enough to me that I built it into a product.
[**Katabench**](https://katabench.com), my coding platform, has a database track where every kata runs your C# (raw SQL via [**Dapper**](https://milanjovanovic.tech/blog/dapper-dotnet-guide), or EF Core) against a **real, throwaway PostgreSQL instance** created for your submission and destroyed afterwards.
No in-memory fakes; the same philosophy as Testcontainers, because the planner is the thing being learned.

![A Katabench database kata: the task statement explains aggregating in SQL instead of pulling rows into C#, the editor holds a starter Solution.cs, and the test panel shows all five tests passing with per-test time budgets and memory](https://milanjovanovic.tech/blogs/articles/sql-index-not-used-sargability/database-kata.png)

Here is the kata built on exactly the `lower()` trap ("Builds by Result").
The starter code ships the defensive query, and the interactive Run shows you the SQL your code produced **and the plan it got**, right under the test:

![Running the naive lower(result) = lower(@value) query on Katabench: the test passes, but the captured SQL below it shows the query plan with a Seq Scan on builds flagged as a full table read](https://milanjovanovic.tech/blogs/articles/sql-index-not-used-sargability/naive-query-seq-scan.png)

Look at the bottom-right: the test **passes** (175 ms, well inside budget), and the plan underneath says `Seq Scan on builds, full table read`.
That pairing is the whole lesson in one screenshot: correct and slow-at-scale are compatible, and only the plan tells you.

## Grading the Plan, Not the Stopwatch

Here is the design problem that made this kata interesting to build: **you cannot teach this with a timing gate**.

On a small visible fixture, the sequential scan genuinely is the fastest plan (reading six rows beats bouncing through an index), so the naive query would pass any latency threshold you set.
Worse, on a tiny table Postgres will seq-scan even a perfectly sargable query, because the planner is right to.
Timing gates on small data teach nothing, and timing gates on huge data are flaky.

So the kata grades the **plan itself**, against a large hidden dataset (sixty thousand rows, seeded and then `ANALYZE`d so the planner has honest statistics).
After the timed runs, the harness re-issues your captured statements as `EXPLAIN (FORMAT JSON)` with the same bound parameters, and asserts rules over the plan tree: no `Seq Scan` on `builds`, the plan must use `ix_builds_result`, and the answer must be a single statement, one round-trip.

Rewrite the query to compare the raw column, and the verdict flips:

![The fixed query on Katabench: all four tests pass and all three query-plan rules hold, with the graded plan tree showing a Bitmap Index Scan using ix_builds_result instead of a sequential scan](https://milanjovanovic.tech/blogs/articles/sql-index-not-used-sargability/plan-gate-passing.png)

Same green tests, but now the graded plan shows `Bitmap Index Scan using ix_builds_result`, and all three plan rules hold.
The difference between the two screenshots is one `lower()` call.

## Summary

You do not need a kata platform to build the reflex; you need one habit:

**When you write a WHERE clause, look at which side of the operator the column is on.**
If the column is wrapped in anything (a function, a cast, arithmetic, a property extraction that your ORM turns into a function), the index is out of the game, and only `EXPLAIN` on realistic data will tell you.

And if you want the reflex drilled into your fingers rather than your bookmarks, the database track on [**Katabench**](https://katabench.com) will happily fail your query plan until it sticks.

## Frequently asked questions

### What makes a SQL query non-sargable?

Applying a function or expression to the column side of a predicate, for example lower(result) = lower(@value) or extracting the year from a timestamp column. The index stores raw column values, so once the column is wrapped in a function the database cannot seek into the index and falls back to scanning every row.

### Why does Postgres use a sequential scan instead of my index?

Either the predicate is non-sargable, so the index is unusable, or the planner correctly estimates that a sequential scan is cheaper, which is common on small tables where reading everything beats index round-trips. Check with EXPLAIN on a realistically sized dataset with fresh statistics (run ANALYZE) before concluding the index is broken.

### How do I check whether a query actually uses an index?

Prefix the query with EXPLAIN and read the plan tree: an Index Scan, Index Only Scan, or Bitmap Index Scan node naming your index means it is used; a Seq Scan on the table means it is not. EXPLAIN without ANALYZE only plans the query and never executes it, so it is safe to run against production.

### How do I make a case-insensitive comparison use an index?

Three options: store the data in one consistent case and compare the raw column, create an expression index on lower(column) so the wrapped form becomes indexable, or use the citext type which makes comparisons case-insensitive at the type level. Wrapping both sides in lower() at query time without an expression index scans the whole table.