Why your index is not being used

Article.

You added the index. The query is still slow. This comes up in interviews more than any other indexing question, because it separates people who know indexes exist from people who have had one ignored at three in the morning.

There are five reasons, and they are worth checking in this order — the first two are your bug, the third is a design mistake, and the last two are the planner being right while you assume it is wrong.

First, confirm it is actually unused

Everything below is a waste of time if the index is being used and something else is slow.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE email = 'a@b.com';

Seq Scan on users means unused. Index Scan using idx_users_email means used and your problem is elsewhere — see Reading a query plan.

One caveat before you trust it: on a small table a sequential scan is genuinely faster, and the planner knows. Test on production-like volume, or you will spend an afternoon fixing a query that was never broken.

1. The condition is not sargable

“Sargable” — Search ARGument ABLE — means the planner can use the index directly. Wrapping the column in anything breaks it.

-- Not sargable. The index is on created_at, not on a function of it.
WHERE EXTRACT(year FROM created_at) = 2026
WHERE created_at + interval '1 day' > now()
WHERE lower(email) = 'a@b.com'
WHERE name LIKE '%alice%'          -- leading wildcard

The mechanism is simple once stated: a B-tree stores the column’s values in sorted order. EXTRACT(year FROM created_at) is a different value that appears nowhere in the index, so there is nothing to search.

Two fixes. Move the work off the column:

-- Sargable: a range over the stored values themselves
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

Or index the expression you actually query:

CREATE INDEX idx_users_email_lower ON users (lower(email));
-- now WHERE lower(email) = $1 has an index whose keys match

The expression in the index must match the query exactly. An index on lower(email) does nothing for WHERE upper(email) = ..., and nothing for WHERE email = ... either.

The leading-wildcard case has no B-tree fix at all — LIKE '%alice%' cannot be a range scan, because a B-tree is ordered by prefix. That one needs a trigram index (pg_trgm) or full-text search.

2. A type mismatch you cannot see

The nastiest one, because the query looks correct and the index looks correct.

-- users.id is bigint. The application sends a string.
SELECT * FROM users WHERE id = '42';

Postgres resolves that by casting, and which side gets cast decides everything. Here the untyped literal is cast to bigint and the index works fine. The column gets cast when the parameter’s type is wider than the column’s, and the common case is a Python Decimal or float sent against an integer column:

-- id is int; the driver sends a numeric (Python Decimal("42"))
EXPLAIN SELECT * FROM dec WHERE id = 42.0;
--  Seq Scan on dec
--    Filter: ((id)::numeric = 42.0)     <- the column was cast

There is an int = numeric comparison only by promoting the integer, so every row’s id is converted and the index on id is not a candidate. Pass an int, or cast the parameter (id = %s::int).

A text column compared with an integer does not silently cast at all in PostgreSQL 18: WHERE external_ref = 42 on a varchar column fails with operator does not exist: character varying = integer. MySQL does convert the column in that case and loses the index, which is where the “digits stored as varchar” folklore comes from.

The Filter: line is the tell. If it shows a cast wrapped around your column, the index was never a candidate.

3. Composite index, wrong column order

An index on (a, b, c) is not three indexes. It is one, sorted by a, then by b within equal a, then by c.

(tenant_id, status, created_at)

tenant 1 | active   | 2026-01-02
tenant 1 | active   | 2026-03-11
tenant 1 | pending  | 2026-01-08
tenant 2 | active   | 2026-02-20

Reading that ordering tells you exactly what it can serve. A query filtering on tenant_id alone reads a contiguous run. tenant_id and status reads a shorter run. status alone is useless — those rows are scattered through the whole index, because sorting by status only happens within a tenant_id.

That is the left-prefix rule, and it is not a rule so much as a consequence of the sort order.

The design guidance that follows: equality columns first, then the range column, and put the most selective equality first. A range condition stops the index being useful for anything to its right, because once you are scanning a range of created_at, the values beyond it are no longer in sorted order for your purposes.

PostgreSQL 18 relaxes this with skip scan: an index on (a, b) can serve WHERE b = ... by descending once per distinct value of a, shown as Index Searches: N in EXPLAIN ANALYZE. It pays off only when a has few distinct values (Index types); with many, the column order still decides.

4. It is not selective enough

Here the planner is right and you are wrong.

An index scan reads the index, then fetches each matching row from the heap — a random I/O per row. A sequential scan reads pages in order. Past roughly 5-10% of the table, sequential wins outright, and the planner switches.

-- 60% of rows are 'active'. An index here is dead weight.
SELECT * FROM subscription WHERE status = 'active';

Low-cardinality columns — booleans, a status with four values, soft-delete flags — rarely deserve a plain index for this reason. What they deserve is a partial index, which indexes only the rows you actually query:

CREATE INDEX idx_subscription_pending ON subscription (created_at)
    WHERE status = 'pending';

If 2% of rows are pending, that index is a fiftieth of the size, stays in cache, and is a genuine win. The predicate has to appear in the query for the planner to use it.

5. The planner has bad information

If the condition is sargable, the types match, the column order is right and the predicate is selective — then the planner is choosing badly, and it is choosing from bad inputs.

Stale statistics are the first suspect. Compare estimated against actual rows in the plan; if they diverge by orders of magnitude, run ANALYZE.

random_page_cost is the second. It defaults to 4.0, a number chosen when random reads on spinning disks really were four times the cost of sequential ones. On SSDs that is wrong, and it biases every decision against index scans:

ALTER DATABASE app SET random_page_cost = 1.1;   -- or per tablespace / globally

This is one of the highest-value settings on a modern server, and it is still at its 1990s default on a great many of them.

The index that is used and still hurts

Two production failures that do not show up as “index not used”:

Bloat. Postgres does not remove index entries for dead tuples immediately. A table with heavy update churn accumulates an index far larger than its live data, so each lookup traverses more pages. REINDEX CONCURRENTLY rebuilds it without blocking writes.

Write amplification. Every index must be updated on every insert, and on every update touching its columns. Eight indexes on a hot table means eight B-tree updates per write, plus the WAL for all of them. The classic version of this is a team that adds an index for each slow query over a year and cannot work out why writes got slow.

That is the trade not taken whenever someone proposes another index: reads get faster, writes get slower and storage grows, forever. An index you added for a report that ran twice is pure cost.

Find the ones nobody uses:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Zero scans since the last statistics reset. Check the reset time before dropping anything, and remember a unique index may exist to enforce a constraint rather than to serve a query.

Related

• How a B-tree index works - the B-tree model and indexing fundamentals
• Index types - B-tree, GIN, GiST, BRIN and when each fits
• Reading a query plan - reading the plan that tells you which of these it is