What this usually means
The planner estimates that a sequential scan is cheaper than using the index. This happens when statistics are stale, the query's WHERE clause doesn't match the index's columns or operators, or the index's selectivity is poor (e.g., low cardinality columns). It can also be caused by planner cost constants that are misconfigured or data type mismatches that prevent index matching.
The first ten minutes — establish facts before touching code.
- 1EXPLAIN (ANALYZE, BUFFERS) your_query and look for 'Seq Scan' on a large table with an index.
- 2Check if the index exists: SELECT * FROM pg_indexes WHERE tablename = 'your_table';
- 3Run ANALYZE your_table; then re-EXPLAIN to see if stats were outdated.
- 4Compare estimated vs actual rows in EXPLAIN output — big discrepancy means stale stats.
- 5Test with enable_seqscan = off; to force index usage and see if the index actually works.
- 6Check the query's WHERE clause for type mismatches or function calls that prevent index matching.
The specific files, logs, configs, and dashboards that usually own this bug.
- searchEXPLAIN (ANALYZE, BUFFERS) output — look for row estimates vs actuals, and filter conditions.
- searchpg_stat_user_tables and pg_stat_all_indexes for scan counts and last vacuum/analyze times.
- searchpg_class.reltuples and pg_class.relpages for table size estimates.
- searchpg_stats view for column statistics (n_distinct, most_common_vals, histogram_bounds).
- searchPostgreSQL log (log_min_duration_statement) for auto-explain of slow queries.
- searchpg_settings for planner cost constants: random_page_cost, seq_page_cost, effective_cache_size.
- searchTable DDL and index definitions — check for partial indexes, expression indexes, or collation mismatches.
Practical causes, not theory. These are the things you will actually find.
- warningOutdated table statistics (need ANALYZE) after bulk inserts, updates, or deletes.
- warningQuery condition uses a function or type cast that prevents index matching (e.g., WHERE date::text = '2023-01-01').
- warningLow selectivity: index on a column with few distinct values (e.g., boolean) where planner prefers seq scan.
- warningMisconfigured random_page_cost (too high) making index scans look expensive compared to seq scans.
- warningComposite index column order doesn't match the query's WHERE/ORDER BY (leftmost prefix rule).
- warningIndex is not used due to query using a different collation or operator class than the index.
- warningThe query has a LIMIT and ORDER BY that could use an index but the planner estimates a sort is cheaper.
Concrete fix directions. Pick the one that matches your root cause.
- buildRun ANALYZE (or VACUUM ANALYZE) on the table to refresh statistics.
- buildIncrease default_statistics_target for columns used in WHERE clauses to get better selectivity estimates.
- buildAdd a composite index that matches the query's filter and sort order (e.g., (col1, col2) for WHERE col1=... ORDER BY col2).
- buildIf selectivity is low and the index is still beneficial, reduce random_page_cost to match storage (e.g., 1.1 for SSD).
- buildRewrite query to avoid functions on indexed columns (e.g., use date range instead of date::text).
- buildFor partial indexes, ensure the query's WHERE clause matches the index predicate exactly.
- buildUse CREATE STATISTICS on correlated columns to help the planner estimate combined selectivity.
A fix you cannot prove is a guess. Close the loop.
- verifiedRe-run EXPLAIN (ANALYZE, BUFFERS) and confirm the planner now uses the index (Index Scan or Index Only Scan).
- verifiedCompare query execution time before and after the fix (should be significantly lower).
- verifiedCheck pg_stat_user_indexes: idx_scan should increment for the index after running the query.
- verifiedForce index with SET enable_seqscan=off; and verify the plan uses the index (to ensure the index works).
- verifiedRun VACUUM ANALYZE and re-check plans to ensure stability.
- verifiedTest with realistic data volumes and distributions to confirm the fix holds under load.
Things that make this bug worse or harder to find.
- warningDon't set enable_seqscan=off permanently—it can cause worse plans for other queries.
- warningDon't blindly increase default_statistics_target too high (e.g., 1000) — it bloats pg_stats and slows analyze.
- warningDon't forget to analyze after creating a new index — the planner needs stats to consider it.
- warningDon't assume a composite index will be used for queries that don't use the leftmost column.
- warningDon't ignore type mismatches: WHERE int_col = '5' may not use an index if the column is integer.
- warningDon't disable seq scans without understanding why the planner chose them — it's usually correct.
E-commerce Order Query Slows After Bulk Import
Timeline
- 09:00Bulk import of 500k orders completed (order_items table grows to 5M rows).
- 09:15Pager alerts: order API endpoint latency spikes from 50ms to 8s.
- 09:20EXPLAIN ANALYZE shows Seq Scan on order_items for query: SELECT * FROM order_items WHERE order_id = 12345 AND status = 'pending' ORDER BY created_at DESC LIMIT 10;
- 09:22Index exists: (order_id, status, created_at) — but planner ignores it.
- 09:25Run ANALYZE order_items; — no change in plan.
- 09:30Check pg_stats: order_id has 500k distinct values (good), but most_common_vals on status shows 'shipped' (90%) and 'pending' (5%) — selectivity for 'pending' is low.
- 09:35Test SET enable_seqscan=off; — query uses index and returns in 2ms. So index works, planner just chose wrong.
- 09:40Examine planner cost constants: random_page_cost = 4 (default for HDD) on SSD RDS instance. Reduced to 1.1.
- 09:42Re-run EXPLAIN: now uses Index Scan. API latency back to 50ms.
After a routine bulk import of 500k orders, our order API endpoint suddenly slowed from 50ms to 8 seconds. The query was simple: fetch the 10 most recent pending items for a given order. We had a composite index on (order_id, status, created_at) that should have been a perfect match. But EXPLAIN showed a sequential scan on the 5-million-row table.
I first ran ANALYZE thinking stats were stale, but the plan didn't change. Then I used SET enable_seqscan=off to force index usage — the query returned in 2ms, confirming the index worked. So the planner was making a bad cost estimate. I checked the planner cost constants and found random_page_cost was still set to 4, the default for spinning disks. Our RDS instance used SSDs, where random reads are much cheaper.
I reduced random_page_cost to 1.1, re-ran EXPLAIN, and the planner switched to an Index Scan. Latency dropped back to 50ms. The root cause was a misconfiguration from an old PostgreSQL version migration that kept the default HDD cost. The lesson: always tune planner cost constants to your storage hardware, especially after migrating to SSDs.
Root cause
random_page_cost was left at default 4 (spinning disk) while the database ran on SSDs, making index scans appear more expensive than sequential scans in the planner's cost model.
The fix
Set random_page_cost = 1.1 in postgresql.conf (or per-session) to reflect SSD performance. Then ANALYZE the table.
The lesson
Planner cost constants must match your hardware. Defaults are conservative for HDDs; SSDs need lower random_page_cost and higher effective_cache_size.
PostgreSQL's planner estimates the cost of each possible plan using a combination of constants and statistics. The key constants are seq_page_cost (cost of reading a page sequentially, default 1.0), random_page_cost (cost of a random page read, default 4.0), and cpu_tuple_cost (processing cost per row). On modern SSDs, random reads are nearly as fast as sequential reads, so random_page_cost should be lowered to around 1.0–1.5.
The planner also uses effective_cache_size to estimate how much of the working set fits in OS cache. Setting this too low makes index scans appear more expensive because the planner assumes many disk reads. On RDS or dedicated servers, set this to about 50–75% of available RAM.
The planner relies on pg_stats to estimate how many rows a condition will return. If stats are outdated (e.g., after bulk loads), the planner may overestimate the number of rows and choose a sequential scan. Running ANALYZE refreshes these stats. For columns with uneven distributions, increase default_statistics_target (default 100) to capture more detailed histograms.
Another common issue is low selectivity: if an index column has few distinct values (e.g., a boolean 'is_active'), the planner may decide a sequential scan is cheaper because it expects to read a large fraction of the table anyway. In such cases, a partial index (WHERE is_active = true) can help.
PostgreSQL will only use an index if the query's WHERE clause matches the index's columns and operator class exactly. A common pitfall is implicit type coercion: for example, indexing a timestamp column but querying with a string (WHERE ts = '2023-01-01') may not use the index if the planner decides to cast the column instead of the constant. Using explicit casts or proper parameter types avoids this.
Another issue is function calls on indexed columns: WHERE LOWER(name) = 'john' won't use a plain index on name unless you create an expression index on LOWER(name). Similarly, using LIKE with a leading wildcard ('%foo') cannot use a B-tree index.
For a composite index (col1, col2, col3), the planner can use it for queries that filter on col1, or col1 and col2, or all three, but not on col2 alone without col1. This is the leftmost prefix rule. If your query filters on col2 and col3, you need a separate index that starts with col2.
Additionally, if the query has an ORDER BY that matches the index's trailing columns, the planner might use an Index Scan to avoid sorting. But if the ORDER BY direction differs from the index's sort order (ASC vs DESC), the planner may choose a sort instead. Creating an index with matching sort orders can fix this.
When debugging, you can temporarily disable sequential scans with SET enable_seqscan = off; to see if the planner can use an index. If the query runs significantly faster, you know the index is usable and the planner is misestimating. However, don't leave this setting on in production — it can cause terrible plans for other queries.
You can also use pg_hint_plan (an extension) to embed index hints in queries, but this is a last resort. The proper fix is to correct the underlying statistics or cost configuration so the planner makes the right choice naturally.
Frequently asked questions
I just created an index, but EXPLAIN still shows a seq scan. Why?
The planner needs up-to-date statistics to consider the new index. Run ANALYZE table_name; and then re-run EXPLAIN. If it still shows a seq scan, the planner may estimate that the index scan is more expensive due to cost constants or low selectivity.
How do I check if my index is actually being used?
Query pg_stat_user_indexes for idx_scan counts: SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'your_table'; A low or zero idx_scan suggests the index is not being used. Also, run EXPLAIN (ANALYZE, BUFFERS) on a typical query to see the actual plan.
Should I set random_page_cost to 1.0 for SSDs?
1.0 is the same as seq_page_cost, meaning random and sequential reads cost the same. That can cause the planner to overuse index scans. A common recommendation is 1.1 to 1.5 for SSDs, giving a slight preference to sequential scans when the index would read many pages. Test with your workload.
My query uses a function on an indexed column. Can the index still be used?
Only if you create an expression index on that function, e.g., CREATE INDEX idx_lower_name ON users (LOWER(name)); Then the planner can use it for queries like WHERE LOWER(name) = 'john'. Otherwise, the function prevents index matching.
Why did my query stop using an index after a bulk insert?
After bulk inserts, the table's statistics become outdated. The planner may think the table is smaller than it actually is, or the distribution changed. Run ANALYZE to refresh stats. Also, if the bulk insert changed the distribution of values (e.g., introduced many new distinct values), the planner's selectivity estimates may be off until stats are updated.