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

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:

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:

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, my coding platform, has a database track where every kata runs your C# (raw SQL via Dapper, 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.

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:

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 ANALYZEd 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:

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 will happily fail your query plan until it sticks.