--- title: "Why an Index Is Not Used: the Estimate, Not the Index" description: "An index the planner ignores is usually a row-estimate problem. The fix starts with what the optimizer expected, not with the index definition." url: https://sqlpress.com/blog/index-not-used-estimate-not-index/ category: PostgreSQL tags: ["query-optimization", "indexing", "execution-plans", "statistics"] author: "Elias Rowe" published: 2026-08-23T00:00:00.000Z measured: false --- # Why an Index Is Not Used: the Estimate, Not the Index ## Key takeaways - The planner rejects an index when it expects the scan to return enough rows that random heap access costs more than reading the table sequentially. - That decision is made from statistics, not from the data, so a wrong estimate produces a wrong plan even when the index is perfect. - Check the estimated row count before changing the index. If the estimate is wrong, the index was never the problem. ## Test environment - System: PostgreSQL 18 - Workload: Illustrative (examples verified, nothing timed) - Notes: Cost constants quoted are PostgreSQL defaults: seq_page_cost 1.0 and random_page_cost 4.0. An index that the planner refuses to use is almost never a problem with the index. It is a problem with the number the planner expected the index to return. ## How does PostgreSQL decide whether to use an index? Before choosing a path, PostgreSQL does not sample the table. It reads the statistics collected by `ANALYZE` — a most-common-values list, a histogram, and a null fraction per column — and multiplies its way to an estimated row count. Every cost that follows is derived from that number. Once the estimate exists, the cost model compares access paths. Reading pages in physical order is charged `seq_page_cost`, which defaults to `1.0`. Reading a page at a random offset is charged `random_page_cost`, which defaults to `4.0`. An index scan pays that higher rate for each heap page it has to visit, because the index gives it row locations in index order, not in table order. So the arithmetic is straightforward. Below some number of rows, the index wins. Above it, the sequential scan wins, because touching most of the table in random order costs more than reading all of it in physical order. ## The estimate is what decides That means an index is skipped in two quite different situations, and they need different responses. The first: the estimate is correct and the index genuinely is the wrong choice. A query returning 40% of a table should not use an index, and forcing it to will make things slower. This case is the planner working. The second: the estimate is wrong. The planner expected 800,000 rows, the query returns 12, and the sequential scan it chose reads the whole table to find them. The index was fine the entire time. You can tell the two apart in one step, because EXPLAIN ANALYZE reports the actuals alongside the estimates: ```sql title="check-the-estimate.sql" EXPLAIN ANALYZE SELECT id FROM orders WHERE status = 'refunded'; ``` ```text {1-2} Seq Scan on orders (cost=0.00..48221.00 rows=812443 width=8) (actual time=0.031..612.884 rows=12 loops=1) Filter: (status = 'refunded'::text) Rows Removed by Filter: 1198788 ``` `rows=812443` against `rows=12` is the whole diagnosis. Nothing about the index definition needed to be examined. Comparing estimated rows against actual rows at every node, starting from the leaves, localizes most plan problems the same way. ## What makes the estimate better? Three things, in roughly this order of practical impact. **Statistics freshness.** A summary describing last month's distribution answers last month's question. A table that recently gained a large batch of rows with a new value distribution will mislead the planner until `ANALYZE` runs again. **Resolution.** `default_statistics_target` controls how many buckets the histogram gets. Raising it for a specific column costs more `ANALYZE` time and buys a better estimate on skewed data. **Correlated predicates.** The planner assumes independence between columns. `WHERE country = 'JP' AND city = 'Osaka'` multiplies two selectivities as if the second were unrelated to the first, and lands far below reality. Extended statistics exist for this case. ## Limits The cost constants above are defaults, and defaults describe hardware assumptions rather than facts. `random_page_cost = 4.0` encodes a seek penalty that does not apply to NVMe storage, which is why lowering it on modern hardware often makes the planner willing to use indexes it previously rejected. That is a real fix, but it is a change to a global assumption, so it belongs in a considered configuration review rather than in the middle of debugging one query. This article also describes only the choice between an index scan and a sequential scan. Bitmap heap scans sit between the two — collecting row locations before reading the heap in physical order — and the point at which the planner switches to one is governed by the same estimate. ## FAQ ### Why is my PostgreSQL index not being used? Usually because the planner expects the scan to return enough rows that visiting heap pages in random order costs more than reading the table sequentially. That decision is made from the statistics ANALYZE collected, not from the data itself, so a wrong row estimate produces a wrong plan even when the index is perfect. Check the estimated row count before changing the index definition. ### How do I tell whether PostgreSQL was right to skip the index? Run EXPLAIN ANALYZE and compare the estimated row count with the actual one. If they agree and the query genuinely returns a large fraction of the table, the sequential scan is correct and forcing an index will make things slower. If the planner expected hundreds of thousands of rows and the query returned twelve, the estimate is the problem and the index was fine all along. ### What is random_page_cost in PostgreSQL and should I lower it? It is the planner's charge for reading a page at a random offset, defaulting to 4.0 against 1.0 for a sequential page. That ratio encodes a seek penalty that does not apply to NVMe storage, so lowering it often makes the planner willing to use indexes it previously rejected. It is a global assumption, which puts it in a considered configuration review rather than in the middle of debugging one query. ### Why does PostgreSQL underestimate rows when two columns are related? The planner assumes columns are independent and multiplies their selectivities. A predicate on country and city is costed as if living in Osaka were unrelated to living in Japan, so the estimate lands far below reality. Extended statistics exist for exactly this case. ## Sources - [PostgreSQL 18 documentation — Using EXPLAIN](https://www.postgresql.org/docs/18/using-explain.html) (docs) - [PostgreSQL 18 documentation — How the Planner Uses Statistics](https://www.postgresql.org/docs/18/planner-stats.html) (docs) - [PostgreSQL 18 documentation — Planner Cost Constants](https://www.postgresql.org/docs/18/runtime-config-query.html) (docs) — Defines seq_page_cost, random_page_cost and the CPU cost constants referenced here.