Why Isn't PostgreSQL Using My Index? Common Causes and Fixes

You created an index, the query still runs slowly, and EXPLAIN shows a sequential scan. Every case reduces to one of two situations: the planner thinks an index is slower than the alternative, or the index physically cannot answer the query as written. The first is a cost-estimation decision you tune with statistics and configuration; the second is a structural mismatch you fix by rewriting the query or building a different index. Telling them apart with EXPLAIN ANALYZE is the whole job.

The Planner Thinks the Index Is Slower

PostgreSQL's planner assigns a cost to every access path and picks the cheapest. When it picks a sequential scan over your index, it is predicting the index path costs more. That prediction can be correct, or it can be wrong because the inputs to the cost model are off.

1. The table is small, so a sequential scan is cheaper. On a table of a few hundred rows, reading every page sequentially beats the random I/O of an index lookup plus heap fetch. Confirm by checking the cost= values in EXPLAIN - if the Seq Scan cost is lower, the planner is right. To force the comparison, run SET enable_seqscan = off; and re-run EXPLAIN ANALYZE. If the index path is actually slower, leave it alone; the index will pay off as the table grows.

2. The query is not selective enough. An index helps when it eliminates most rows. A predicate like WHERE status = 'active' that matches 80% of the table is faster as a sequential scan, because the index path would visit nearly every heap page anyway, in random order. Confirm with the estimated rows= in the plan versus the table's total row count. A partial index (WHERE status = 'pending') can make a low-cardinality filter selective again.

3. A LIMIT changes the math. LIMIT lets the planner assume it can stop early, which sometimes makes a sequential scan look cheaper than an ordered index walk - especially when matching rows are common. Confirm by removing the LIMIT and watching the plan switch to an index scan. If the limited query is slow in practice, an index whose order matches the ORDER BY lets Postgres satisfy the LIMIT without scanning.

4. Statistics are stale. The planner estimates selectivity from pg_statistic, populated by ANALYZE. After a bulk load, a large DELETE, or a data-distribution shift, those estimates go stale and the planner mis-costs both paths. Compare estimated rows= against actual rows in EXPLAIN ANALYZE; a large gap points here. Run ANALYZE your_table; and re-check. For correlated columns, CREATE STATISTICS adds multivariate estimates the single-column histograms miss.

5. random_page_cost is wrong for your hardware. This planner constant estimates the cost of a random page read; it defaults to 4.0, against seq_page_cost of 1.0 - a ratio tuned for spinning disks. On SSD and NVMe storage, random reads are far cheaper than 4x sequential, so the default biases the planner away from index scans. Lower it (SET random_page_cost = 1.1; is common for SSD) and re-test. Measure before committing the change in postgresql.conf.

6. A different index already covers the query. If the chosen plan uses another index - often a covering index with INCLUDE, or a composite that satisfies the predicate as a prefix - your index is redundant for that query, not broken. Read the index name in the plan node. Sometimes both indexes have equal estimated cost and the planner picks either one; that is fine.

PostgreSQL Cannot Use the Index

In these cases no amount of cost tuning helps, because the B-tree physically cannot answer the query as written. The fix is to match the query to the index, or build an index that matches the query.

1. The sort order does not match. A B-tree stores keys in a defined direction. An index on (id, name, last_name) (all ascending) cannot directly serve ORDER BY id, name DESC, last_name - the mixed direction forces a sort, and the planner often falls back to a sequential scan plus sort. Build the index with matching direction (CREATE INDEX ... ON t (id, name DESC, last_name)) if that ordering is hot.

2. The leading columns do not match. A composite index is only usable when the query constrains a left-anchored prefix of its columns. An index on (a, b, c) serves predicates on a, on a, b, or a, b, c, but not a predicate on b alone. Reorder the index columns to put the most-filtered column first, or add a separate index.

3. A function wraps the indexed column. WHERE abs(id) = 123 cannot use a plain index on id, because the index stores raw id values, not abs(id). The planner will not prove the function is a no-op. Either drop the function from the predicate, or build an expression index: CREATE INDEX ON people (abs(id));. The same applies to lower(email), date_trunc('day', ts), and similar wrapped columns.

4. A data-type mismatch forces an implicit cast. WHERE id = 123::numeric against an integer column casts the column to numeric for every row, which the integer index cannot serve. Confirm by reading the Filter: line - a cast on the column side ((id)::numeric = ...) is the tell. Fix it by matching the literal's type to the column (123 or 123::int), not the other way around.

5. The operator is unsupported by the index type. A standard B-tree supports =, <, >, BETWEEN, and left-anchored LIKE 'foo%', but not ILIKE, LIKE '%foo%', or regex. Those need a different index: a text_pattern_ops B-tree for case-sensitive prefix matching, or a GIN index with pg_trgm for ILIKE and substring search. See LIKE and ILIKE operators for the trigram approach.

6. Collation mismatch. An index built under one collation cannot serve a query that requests a different one, and a non-C collation disables the index for LIKE prefix matching unless you add a text_pattern_ops operator class. If a prefix query ignores an index that looks correct, check the column and index collation with \d+ tablename.

How to Check and Test Indexes

EXPLAIN ANALYZE is the primary tool. It runs the query and prints the chosen plan with estimated and actual row counts, so you see both the decision and whether the estimate was accurate:

-- Shows the chosen plan plus actual rows and timing per node
EXPLAIN ANALYZE
SELECT * FROM people WHERE id = 2;

If the plan shows Seq Scan and you expected an index, set SET enable_seqscan = off; for the session and re-run. This does not delete the option globally - it makes sequential scans expensive enough that the planner reveals the next-best path and its real cost. Compare the two Execution Time figures. If the index path is genuinely slower, the planner was right. Reset with RESET enable_seqscan; when done; never leave it off in production.

To test whether a new index would help without paying to build it, use the HypoPG extension. It creates a hypothetical index the planner can consider, but that occupies no storage and is invisible to other sessions:

-- Create a hypothetical index, then check if the planner would use it
SELECT * FROM hypopg_create_index('CREATE INDEX ON people (id)');
EXPLAIN SELECT * FROM people WHERE id = 2;  -- note: EXPLAIN, not EXPLAIN ANALYZE

Use plain EXPLAIN, not EXPLAIN ANALYZE - the index does not physically exist, so it cannot actually be scanned. If the estimated plan switches to the hypothetical index with a lower cost, building it for real is worth it.

Finding which queries silently fall back to sequential scans across a busy database is harder than checking one statement by hand. Pulse continuously analyzes plans from pg_stat_statements, flags queries that regressed to sequential scans after a stats drift or a data-type change, and traces each back to the root cause - a stale ANALYZE, a wrapped column, a redundant index - then recommends the specific fix for a human to approve. That turns the manual EXPLAIN-and-compare loop into a continuous check.

Frequently Asked Questions

Q: Why does PostgreSQL use a sequential scan instead of my index?
A: PostgreSQL uses a sequential scan when its cost model estimates the scan is cheaper than the index path, which is common on small tables or low-selectivity predicates that match a large fraction of rows. Compare the cost= values in EXPLAIN; if the Seq Scan cost is lower, the planner is correct and the index would not help that query.

Q: How do I force PostgreSQL to use an index?
A: PostgreSQL has no per-query index hint. Use SET enable_seqscan = off; only to test whether an index path exists and what it costs, never as a permanent fix. If the planner avoids a useful index in production, fix the underlying cause - run ANALYZE, lower random_page_cost on SSD, or build an index that matches the query's filter and sort order.

Q: Why is my index ignored when I use a function in the WHERE clause?
A: A plain index stores the raw column value, so WHERE lower(email) = '...' or WHERE abs(id) = 123 cannot use an index on the bare column. Either remove the function from the predicate, or create an expression index such as CREATE INDEX ON users (lower(email)); that stores the computed value.

Q: Does a data type mismatch stop PostgreSQL from using an index?
A: Yes. Comparing an integer column to a numeric literal makes PostgreSQL cast the column for every row, which the integer index cannot serve. Match the literal's type to the column - write id = 123 rather than id = 123::numeric - so the index applies.

Q: Why does adding LIMIT change whether the index is used?
A: LIMIT lets the planner assume it can stop after finding enough rows, which can make a sequential scan look cheaper than an ordered index walk when matching rows are common. If the limited query is slow, build an index whose column order matches the ORDER BY so Postgres satisfies the LIMIT directly from the index.

Q: How do I lower random_page_cost for SSD storage?
A: Set random_page_cost = 1.1 in postgresql.conf (default is 4.0, calibrated for spinning disks) and reload. On SSD and NVMe, random reads are nearly as cheap as sequential, so the default biases the planner away from index scans. Measure query latency before and after rather than changing it blindly.

Q: Can I test if a new index helps before creating it?
A: Yes, with the HypoPG extension. hypopg_create_index('CREATE INDEX ...') registers a hypothetical index the planner can consider with no storage cost, then plain EXPLAIN (not EXPLAIN ANALYZE) shows whether the planner would choose it. If the estimated plan and cost improve, build the index for real.

Related Reading

• Creating Indexes in PostgreSQL: Index types, partial and expression indexes, and covering indexes with INCLUDE.
• How to Use PostgreSQL EXPLAIN ANALYZE: Read plan nodes, cost notation, and row-estimate errors that explain index choices.
• Finding and Fixing Slow PostgreSQL Queries: Locate the queries doing sequential scans with pg_stat_statements.
• PostgreSQL Performance Tuning: Configuration levers including random_page_cost, statistics, and autovacuum.
• LIKE and ILIKE Operators in PostgreSQL: Pattern matching and the trigram indexes that make ILIKE indexable.
• Debugging Low Cache Hit Ratio in PostgreSQL: When sequential scans evict hot pages and inflate disk reads.