Why your index is not being used: the reasons the planner ignores it

01Sometimes the scan is genuinely faster

An index scan reads the index and then fetches matching rows from the table, which is random I/O. A sequential scan reads pages in order. When a query returns a large fraction of the table, sequential reading wins, and the planner is right to choose it.

The threshold is lower than intuition suggests - often around five to ten percent of the table depending on row width and cache state. If your query matches a third of the rows, no index will help, and the fix is a more selective query rather than a better index.

02Composite index column order

A composite index is usable left to right. An index on (tenant_id, created_at) serves a query filtering by tenant and sorting by date. The same columns in the opposite order do not, because the leading column is not what you are filtering on.

The rule is equality conditions first, then range conditions, then the ordering column. This single ordering issue accounts for a large share of 'I added an index and nothing improved' cases, and it is easy to verify by reading the index definition against the query.

03Functions and type mismatches on the column

Wrapping a column in a function makes a plain index unusable, because the index stores the raw values rather than the function's output. Lowercasing a column for a case-insensitive comparison is the classic example, and the fix is an expression index on the same expression.

Type mismatches do this quietly. Comparing a bigint column to a value the driver sends as a different numeric type can force a cast on the column side, which has the same effect as a function. The plan shows it, but only if you read the filter condition carefully.

• Function on the column - use an expression index matching it exactly.
• Implicit cast on the column side - align the parameter type.
• Leading wildcard in LIKE - use a trigram index instead.
• OR across different columns - consider separate indexes and a bitmap scan.

04Stale statistics

The planner estimates row counts from statistics gathered by ANALYZE. If a table has changed substantially since the last run - a bulk load, a large deletion - the estimates can be badly wrong and the resulting plan choice will be too.

The diagnostic is in EXPLAIN ANALYZE: compare estimated rows against actual rows. A gap of orders of magnitude means the statistics are misleading the planner, and running ANALYZE often fixes the plan immediately.

05Partial indexes and their exact predicate

A partial index only covers rows matching its predicate, and the planner will only use it when it can prove the query falls inside that predicate. An index on active rows will not serve a query that does not filter on the same condition, even if every row it wants happens to be active.

This trips people up because the index looks smaller and faster and appears to be exactly what the query needs. The predicate has to match logically, not approximately.

06Too many indexes is its own problem

Every index is maintained on every write, so an over-indexed table pays on insert, update and delete, and consumes memory that would otherwise cache data. Adding indexes speculatively degrades write performance for no read benefit.

Postgres records index usage. Indexes with near-zero scans are pure cost, and dropping them is one of the few optimisations that improves write throughput and reclaims memory at the same time. Review that list periodically rather than only adding.

• Check index usage statistics before adding another.
• Drop indexes with no recorded scans - they only cost.
• Prefer one well-ordered composite over several single-column indexes.
• Remember every index slows every write to that table.