When does PostgreSQL choose NOT to use an index even if one exists?

How each index type works and what it is for, composite and covering indexes, partial and expression indexes, the cost of indexing, and why the planner sometimes ignores an index.

Cracked Java

The planner uses an index only when it estimates the index is cheaper than the alternative — so "the index exists" and "the index is used" are different facts. Most "why isn't my index used?!" cases are the planner being correct, or being misled by something fixable. Here are the recurring causes.

1. Low selectivity — the predicate matches too many rows

If a query returns a large fraction of the table, an Index Scan means that many random heap fetches, which is more expensive than one sequential pass. The planner correctly prefers a Seq Scan. The rough flip point is a single-digit percentage of rows, tuned by random_page_cost (default 4.0, often too high for SSDs).

2. The table is small

A few hundred rows live in a handful of pages; scanning them sequentially beats descending an index. The planner ignores the index on purpose, and that's fine.

3. Stale or missing statistics

The planner costs plans from ANALYZE statistics. After a bulk load it may think a 10M-row table has 100 rows and mis-cost everything.

ANALYZE orders;                      -- refresh stats
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;  -- finer histogram

4. Non-sargable predicates

Wrapping the column in a function, casting it, or applying arithmetic hides it from a plain index:

WHERE lower(email) = 'a@b.com'        -- index on email unusable
WHERE created_at::date = '2024-01-01' -- cast defeats the index
WHERE price * 1.2 > 100               -- expression on the column
WHERE phone LIKE '%5555'              -- leading wildcard can't seek a B-tree

Fix with an expression index (CREATE INDEX ON t (lower(email))) or by rewriting as a sargable range (created_at >= '2024-01-01' AND created_at < '2024-01-02').

5. Type mismatch

Comparing a column to a differently-typed value can force an implicit cast on the column and skip the index — e.g. a text column compared to an integer literal.

6. Left-prefix / wrong column order

A query on a composite index that skips the leading column (WHERE b = … on an index (a, b)) can't seek it.

Confirm before you "fix"

Run EXPLAIN (ANALYZE, BUFFERS) and compare estimated vs actual rows. If they agree and the Seq Scan is genuinely cheap, leave it. If the estimate is wildly off, fix the estimate — don't reach for SET enable_seqscan = off, which is a diagnostic toy, not a production fix.