Why your index isn't being used

The index exists. You can see it in pg_indexes. You created it on exactly the column in the WHERE clause. The query still takes forty seconds, and EXPLAIN says Seq Scan.

This is the single most common performance conversation in analytics engineering, and it almost never ends with “the database is broken.” It ends with a realisation about what an index actually is: a structure sorted by the value stored in the column.

Not by anything derived from that value. The moment your predicate asks a question about something other than the stored value, the sort order stops being useful, and the planner has no choice but to read every row.

There is a word for a predicate the index can serve: sargable, a contraction of Search ARGument ABLE. It is old vocabulary from the IBM System R days, and it is still the most useful single concept in query tuning. Sargable predicates seek. Non-sargable predicates scan.

Shape 1: a function on the column

This is the one everybody writes, usually while trying to be tidy:

WHERE DATE(created_at) = '2026-08-01'
WHERE UPPER(email) = 'ADA@EXAMPLE.COM'
WHERE amount * 1.1 > 100
WHERE EXTRACT(YEAR FROM signed_at) = 2026

Every one of these is unsargable against a plain index on the bare column. The index holds created_at values in sorted order; it holds no information whatsoever about the sorted order of DATE(created_at).

The engine must materialise the function’s output row by row. That means reading every row.

The fix is almost always to move the transformation to the other side of the comparison, where it is applied once to a constant instead of once per row:

WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'
WHERE amount > 100 / 1.1

Notice that the date rewrite is a half-open range: >= the start, < the next day. It is not BETWEEN two timestamps.

BETWEEN '2026-08-01' AND '2026-08-01 23:59:59' drops anything in the final second. That is exactly the sort of off-by-a-microsecond bug that survives code review.

Be careful with the algebra. Dividing both sides by a negative constant flips the inequality. Planners will generally not rearrange arithmetic on your behalf — constant folding evaluates 100 / 1.1 for you, but nobody is going to turn amount * 1.1 > 100 into a range on amount.

When the transformation is genuinely part of the data model — case-insensitive email lookup, say — the answer is not to contort the query. Index the expression itself instead.

PostgreSQL and SQLite support expression indexes directly. Other engines offer generated or computed columns that you then index:

CREATE INDEX orders_lower_email_idx ON orders (lower(email));

The predicate must then match the indexed expression exactly. An index on lower(email) will not serve WHERE upper(email) = ….

Shape 2: the leading wildcard

WHERE name LIKE 'smith%' can use a B-tree. WHERE name LIKE '%smith' cannot. The reason is the same as before: a B-tree is ordered by prefix, so a known prefix is a place to seek to and an unknown prefix is not.

There is a wrinkle specific to PostgreSQL that catches people who read the above and then find their prefix search still scanning. In PostgreSQL, a plain B-tree index can only serve a LIKE 'prefix%' pattern when the database uses the C collation.

Pattern matching in other collations does not follow the index’s sort order. The escape hatch is an operator class:

CREATE INDEX name_pattern_idx ON people (name text_pattern_ops);

For true substring search — the %smith% case — no B-tree in any engine will help you. The honest answer is a different index type.

Options include:

• PostgreSQL’s pg_trgm extension, which provides trigram GIN indexes that genuinely accelerate arbitrary substrings.
• Full-text search indexes, which handle word-level matching.
• At some scale, a dedicated search engine, which is the right tool.

Reaching for one of those is a design decision, not a tuning trick.

Shape 3: the implicit cast

This one is invisible in the query text, which is what makes it vicious.

Suppose account_number is a VARCHAR column — because account numbers have leading zeros — and somebody writes:

WHERE account_number = 4815162342

The comparison has a string on one side and a number on the other, so the engine must coerce one of them.

MySQL’s documented rule is that when a string column is compared with a number, the column is converted to a number. That is precisely the function-on-the-column problem from Shape 1 wearing a disguise.

The index is dead, and no function appears anywhere in your SQL.

The same trap appears across type families that feel interchangeable but are not:

• timestamp versus timestamptz
• int versus bigint in some engines
• char(n) versus varchar
• An application driver that binds every parameter as text against a typed column

The fix is boring and total: compare like with like. Quote the string literal, cast the parameter rather than the column, and make your ORM’s parameter types match your schema.

When a query mysteriously ignores an index, run EXPLAIN and look for a cast that you did not write.

Shape 4: OR across different columns

An index is a single sorted structure over a single set of columns. A predicate like:

WHERE email = 'ada@example.com' OR phone = '+91-555-0100'

asks two unrelated questions, and a row qualifies if either is true. An index on email can find the first set. It can tell you nothing about the second, so it cannot be used alone to prove a row does not qualify.

What happens next depends on the engine and on what indexes exist:

• If both columns are indexed, PostgreSQL can build a bitmap from each index and combine them with a BitmapOr; MySQL has an analogous index_merge union.
• If only one is indexed, you get a full scan.

The common failure is a schema where email is indexed and phone is not, along with a query that appears to have a perfectly good index available.

When index merging is unavailable or the planner declines it, the reliable rewrite is to split the query so each branch has a sargable predicate:

SELECT * FROM users WHERE email = 'ada@example.com'
UNION
SELECT * FROM users WHERE phone = '+91-555-0100';

UNION rather than UNION ALL, because a user with both values matching would otherwise appear twice. That deduplication is not free, so measure it.

OR on the same column is a different animal entirely and is perfectly sargable: WHERE status = 'a' OR status = 'b' is just WHERE status IN ('a', 'b'), which the planner treats as a set of index seeks.

Shape 5: the planner is right and you are wrong

The four shapes above are things you did to the query. This one is not.

If the planner estimates that your predicate will match a large fraction of the table, it will deliberately choose a sequential scan. It will usually be correct to do so.

Using an index means walking the index and then fetching each matching row from the heap — random I/O, one page at a time, in index order rather than storage order. A sequential scan reads the table in physical order at full streaming bandwidth.

Somewhere between roughly five and thirty percent selectivity, depending on the engine, the storage, and the correlation between index order and physical order, the scan wins.

So WHERE country = 'IN' on a table where most rows are Indian will ignore your index no matter how many times you rebuild it. The fixes here are structural, not syntactic:

• A partial index that stores only the rare rows: CREATE INDEX ... WHERE status = 'pending' on a table where two percent of rows are pending.
• A composite index that adds a discriminating column, remembering the leftmost-prefix rule — an index on (a, b, c) serves predicates on a, (a, b), and (a, b, c), but not on b alone.
• A covering index that includes the selected columns so the engine never touches the heap at all, turning random row fetches into a pure index scan.

There is also the case where the planner is wrong because its statistics are stale. If EXPLAIN ANALYZE shows an estimated row count that is off from the actual count by orders of magnitude, run ANALYZE (or your engine’s equivalent) before you change a single line of SQL.

Diagnosing it in ninety seconds

Stop guessing and read the plan. The workflow is short:

Run EXPLAIN ANALYZE on the real query with real parameter values, not a simplified version. Look at the top-level access method for the table you care about.

Seq Scan or Full Table Scan means no index was used. Index Scan, Index Only Scan, or Bitmap Index Scan means one was.

If you see a scan, compare the planner’s estimated rows against the actual rows. A close match means the planner chose the scan on purpose. You are in Shape 5, and the answer is a better index or a more selective predicate.

A wild mismatch means stale statistics.

If the estimate is fine, the predicate is highly selective, and you still got a scan, you are in Shapes 1 through 4. Read your WHERE clause looking specifically for:

• A function wrapping the column
• A % at the start of a pattern
• A literal whose type differs from the column’s
• An OR spanning two columns

One of those four is there. It always is.

The intuition to carry forward

An index is a promise about ordering, and a predicate either respects that ordering or it does not. col >= x respects it. f(col) = x does not, because the ordering of f(col) is unrelated to the ordering of col.

The exception is when f happens to be monotonic and the planner happens to know it — which, for almost every function you will use, it does not.

That single sentence generalises to every index type you will meet:

• Prefix order in a B-tree
• Hash buckets in a hash index
• Trigram sets in a GIN index
• Min/max ranges in a columnar file footer

Each one indexes something specific about the stored value. Ask a question about a derived value and you have opted out of the structure.

Write the predicate the index can answer, and you will spend far less time reading query plans.

Learn it as a system

Follow the sequence:

• Start with Filtering with WHERE, where predicate shape gets introduced properly.
• Move to File organisation and indexing for how B-trees and hash indexes are actually built and why the sort order is the whole game.
• Finish with Columnar storage and Parquet to see how the same idea reappears as min/max statistics and partition pruning in analytical engines.