PostgreSQL index not used: separate planner choice from missing index

The symptoms

• •EXPLAIN shows Seq Scan or a different index than the one expected, even though the column has an index defined.
• •Query latency rises as the table grows, while the expected index exists and is valid per \d or pg_indexes.
• •pg_stat_user_tables shows seq_scan and seq_tup_read climbing for the table, with idx_scan flat or low for the index you expected.
• •EXPLAIN (ANALYZE) row estimates diverge sharply from actual rows (rows=1000 vs actual=900000) on the same node.
• •Application logs show slow-query warnings only for specific parameter values (e.g., a low-limit API path is fast, the full-scan path is slow).

Likely causes

• •Planner chose a sequential scan because the estimated row count exceeded the index scan cost threshold for that table's statistics.
• •Stale pg_stats after bulk load, mass UPDATE/DELETE, or changing data distribution since the last ANALYZE.
• •Predicate uses an expression, function, or implicit cast (e.g., WHERE created_at::text, WHERE LOWER(col)) that prevents index match on the indexed column.
• •Index is present but not the one you expected: a partial index whose WHERE clause excludes the row, an expression index not matching the query expression, or a multicolumn index with wrong leading column.
• •Planner cost parameters (random_page_cost, seq_page_cost, effective_cache_size) or work_mem interact with selectivity to favor seq scan on your hardware.
• •Data type mismatch between literal and column (e.g., text vs varchar, int vs bigint) causing an implicit cast that defeats the index.
• •Table is small enough that the planner reasonably picks seq scan; the "index not used" is correct behavior, not a failure.

First ten minutes

1. 01Confirm the index actually exists, is valid, and is ready: query pg_indexes and pg_index for the table and columns, checking indisvalid and indisready.
2. 02Capture the actual plan with EXPLAIN (ANALYZE, BUFFERS) for the exact query text the application issues; do not assume the ORM emits identical SQL.
3. 03Compare estimated vs actual rows at each node in the plan; the first large divergence is where the planner's input went wrong.
4. 04Read pg_stats for the indexed columns (most_common_vals, most_common_freqs, histogram_bounds, null_frac) to see what the planner actually believes about the data.
5. 05Check pg_stat_user_tables.seq_scan, seq_tup_read, idx_scan for the table to see whether the seq path is being chosen repeatedly in production traffic.
6. 06Check pg_stat_user_indexes.idx_scan for the candidate index to confirm whether it is used at all or only ignored for this query shape.
7. 07Decide: is the planner wrong, or is the index wrong for this predicate? Do not change anything until the evidence points to one side.

Evidence to collect

• •EXPLAIN (ANALYZE, BUFFERS) output for the failing query, including actual rows, planned rows, shared hit/read, and node types.
• •pg_stats rows for every column referenced in WHERE/ORDER BY/JOIN, plus correlation and null_frac.
• •pg_stat_user_tables seq_scan, seq_tup_read, idx_scan counters and their delta over a known interval.
• •pg_stat_user_indexes.idx_scan and idx_tup_read for the expected index and any competing indexes.
• •Index definition from pg_indexes including partial WHERE clause, expression, column order, and opclass.
• •Server-level planner parameters in effect (SHOW random_page_cost, seq_page_cost, effective_cache_size, default_statistics_target).

Where to look

• •pg_indexes and pg_index for index definition, validity (indisvalid), readiness (indisready), and partial-index predicate (indpred).
• •pg_stats and pg_stats_ext for column distributions the planner costed against; the monitoring-stats documentation defines these as the planner's view of the data.
• •EXPLAIN (ANALYZE, BUFFERS) output, where shared hit/read exposes whether the seq scan is at least cached, not just slow.
• •pg_stat_user_tables and pg_stat_user_indexes for cumulative usage counters that distinguish "never used" from "rarely used" from "used only by other queries."
• •pg_stat_statements (if present) to find the exact queryid, call count, and mean execution time, separating planner choice from frequency.
• •PostgreSQL logs for slow-query or auto_explain output around the failing request window.

Diagnostic steps

1. 01Run EXPLAIN (ANALYZE, BUFFERS) on the literal SQL the application runs; record planned vs actual rows at the top relation node.
2. 02Inspect the plan node type on the table of interest: Seq Scan, Index Scan, Index Only Scan, or Bitmap Index Scan. The node type is the decision, not a guess.
3. 03Compare estimated row count to actual: ratios above ~10x indicate stale or missing statistics on the predicates the planner used.
4. 04Read pg_stats for the filtered columns and check whether most_common_vals/histogram_bounds reflect current data; large null_frac or skew can make the index look expensive.
5. 05Verify predicate-index compatibility: column type, expression form, leading column order, and partial-index WHERE clause. Mismatch here is "wrong index," not "planner ignored it."
6. 06Cross-check pg_stat_user_indexes.idx_scan for the candidate index; if zero, decide between "unused everywhere" and "unused for this query shape" before changing anything.
7. 07Review planner parameters via SHOW: random_page_cost, seq_page_cost, effective_cache_size, and default_statistics_target; treat deviations from sane values as a hypothesis, not a default fix.
8. 08Use SET enable_seqscan = off; EXPLAIN (ANALYZE) as a diagnostic only, to see whether the index path is even physically usable; revert immediately after.

Common mistakes

• •Adding a redundant index without proving the planner is wrong; if estimates are stale, ANALYZE often restores index use without new indexes.
• •Assuming the ORM's SQL matches what you tested in psql; parameter types and casts from the driver frequently change the plan.
• •Tuning random_page_cost downward to force index use globally; this masks selectivity misestimates and can regress other queries.
• •Conflating "index not used for this query" with "index never used"; pg_stat_user_indexes is the only source that separates these.
• •Running ANALYZE on a table that is still changing (ongoing bulk load) and treating the result as ground truth; recheck after the load completes.
• •Forcing enable_seqscan = off in configuration files; it is a diagnostic switch, not a performance setting.

Safe fixes

• •If pg_stats is stale relative to current data, run ANALYZE on the table (or the specific columns via ALTER TABLE ... SET STATISTICS) and re-capture the plan; only proceed if the new plan uses the index.
• •If the predicate applies a function or cast to the indexed column, change the query to a sargable form or add a matching expression index; confirm with EXPLAIN before declaring success.
• •If the index is partial and the query falls outside its predicate, either widen the index's WHERE clause or add a non-partial index; pick based on measured query mix, not intuition.
• •If the multicolumn index has the wrong leading column for this query, add a new index with the correct leading column rather than dropping the existing one without evidence.
• •If type mismatch causes an implicit cast, align the literal/parameter type with the column type (e.g., cast to bigint for a bigint column) and recheck the plan.
• •If the table is genuinely small, accept the seq scan as correct behavior and record the evidence; do not invent work for the planner.
• •If the plan is still wrong after the above and the cost parameters are the only remaining cause, change parameters at the session level first, measure, then consider server-level changes.

Prove the fix

1. 01The plan for the exact application SQL now shows an Index Scan / Index Only Scan / Bitmap Index Scan on the expected index, captured via EXPLAIN (ANALYZE, BUFFERS).
2. 02Estimated rows at that node are within an order of magnitude of actual rows (typical target: ratio under ~3x for selective predicates).
3. 03pg_stat_user_tables.seq_scan and seq_tup_read stop rising for the affected table during a representative workload window, while idx_scan for the chosen index increases.
4. 04Query latency, as observed in pg_stat_statements or application slow-query logs, returns to the expected band for the workload mix.
5. 05A controlled regression check: capture the plan before and after the change in the same session parameters, and store both outputs so the decision is auditable.

Prevention and next steps

• •Schedule ANALYZE after known bulk loads, partition switches, or large UPDATE/DELETE batches rather than relying on autovacuum thresholds alone for statistics freshness.
• •Keep column types aligned end-to-end (ORM mapping, driver parameters, SQL literals) so predicates do not acquire implicit casts that defeat indexes.
• •Use EXPLAIN (ANALYZE) in code review for new query paths against tables whose row counts are expected to grow; treat the plan as part of the change.
• •Monitor pg_stat_user_tables.idx_scan vs seq_scan ratios and pg_stat_user_indexes.idx_scan trends as leading indicators before users report slowness.
• •Document the cost parameters (random_page_cost, seq_page_cost, effective_cache_size) used per environment so plan changes are attributable to data, not config drift.

Safe commands and checks

psql -h <host> -p <port> -U <user> -d <db> -c "SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' AND tablename = '<table>';"
psql -h <host> -p <port> -U <user> -d <db> -c "SELECT a.attname, s.n_distinct, s.null_frac, s.most_common_vals, s.most_common_freqs FROM pg_stats s JOIN pg_attribute a ON a.attrelid = s.attrelid AND a.attnum = s.attnum WHERE s.tablename = '<table>' AND s.attname IN (<column_list>);"
psql -h <host> -p <port> -U <user> -d <db> -c "EXPLAIN (ANALYZE, BUFFERS) <the exact application query>;"
psql -h <host> -p <port> -U <user> -d <db> -c "SELECT relname, seq_scan, seq_tup_read, idx_scan FROM pg_stat_user_tables WHERE relname = '<table>';"
psql -h <host> -p <port> -U <user> -d <db> -c "SELECT indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE relname = '<table>' ORDER BY idx_scan DESC;"
psql -h <host> -p <port> -U <user> -d <db> -c "SHOW random_page_cost; SHOW seq_page_cost; SHOW effective_cache_size; SHOW default_statistics_target;"
psql -h <host> -p <port> -U <user> -d <db> -c "SET enable_seqscan = off; EXPLAIN (ANALYZE) <the exact application query>; RESET enable_seqscan;"
psql -h <host> -p <port> -U <user> -d <db> -c "ANALYZE <table>;" -- run only after confirming the table is not under active bulk load