Postgres Wasn't Ignoring My Index. On My Machine It Used the Index Too Often.

The ticket is always the same. A query is slow, there is an index on the column in the WHERE clause, and EXPLAIN shows Seq Scan. The usual reaction is that Postgres is being stubborn, and the usual fix is SET enable_seqscan = off.

I built a table, put indexes on it, and timed every way Postgres could answer the same query. I expected a planner that is too timid about indexes. On this machine I found close to the opposite: across a wide range, the planner chose an index-driven plan and a plain sequential scan was faster, by as much as 1.85x.

How the planner decides

Postgres does not decide by rule. It prices every candidate plan in abstract cost units and runs the cheapest one. The prices come from three inputs: statistics it keeps about each column, a few cost constants, and the physical size of the table.

flowchart LR
  A[Column statistics<br/>pg_statistic] --> B[Estimated matching rows]
  B --> C1[Cost of sequential scan]
  B --> C2[Cost of index scan]
  B --> C3[Cost of bitmap scan]
  K[Cost constants<br/>and table size] --> C1
  K --> C2
  K --> C3
  C1 --> D{Pick cheapest}
  C2 --> D
  C3 --> D
  D --> E[Execute]

A few terms before going further. The heap is the table’s own storage, the pages that hold the actual rows. An index is a separate structure that maps column values to row locations in the heap. Selectivity is the fraction of rows a filter keeps, so val < 100000 on a million distinct values has a selectivity of 10%.

There are three ways to read matching rows. A sequential scan reads the whole heap in order and discards what doesn’t match. An index scan walks the index and fetches each matching row from the heap as it goes. A bitmap scan walks the index first, collects all the matching locations into a bitmap, and then visits the heap pages in physical order.¹ That last property is the point of it: the heap is read in one pass instead of jumping around.

The cost constants matter because they say how expensive each kind of page access is. By default a sequentially read page costs 1.0 and a randomly fetched page costs random_page_cost, which is 4.0.² The documentation is candid about the number. Random access to durable storage is “normally much more expensive than four times sequential access,” and 4.0 is used because most indexed reads are assumed to be in cache.² So the default is a deliberate bet on a warm cache, not a measurement of any disk.

The setup

One machine, one Postgres 17.6 server on Windows with default settings, and a 5-million-row table:

CREATE TABLE t (id int PRIMARY KEY, val int NOT NULL, seq int NOT NULL, pad text NOT NULL);
INSERT INTO t
SELECT g, (random()*1000000)::int, g, repeat('x', 80)
FROM generate_series(1, 5000000) g;
CREATE INDEX t_val ON t(val);
CREATE INDEX t_seq ON t(seq);
VACUUM ANALYZE t;

The heap is 601 MB and each index on a single int is 56 MB. Both indexed columns hold the same kind of values, but val is random while seq is the insertion order, so their physical layout differs completely. pg_stats reports a correlation of 0.007 for val and exactly 1 for seq. Correlation here means how closely the order of values in the column matches the order of rows on disk.

The query is SELECT count(pad) FROM t WHERE val < X. Using count(pad) rather than count(*) matters: it forces a visit to the heap, because an index-only scan could otherwise answer from the index alone and hide the effect I wanted to measure.

For each selectivity (full methodology in note ³) I forced each plan type with the enable_* settings, ran one warm-up, then took the median of five EXPLAIN (ANALYZE, TIMING OFF) runs. I also let the planner choose with nothing forced. Parallel query was switched off, so sequential scans were not given a multi-worker advantage. The whole table fits in the operating system’s file cache, so this is a warm-cache test. I did not measure cold reads.

Measurement 1: a column in random order

The shape is the textbook one, and so is the left side. Below about half a percent the index plans win by orders of magnitude: 0.3 ms against 327 ms at 100 rows. The sequential scan costs roughly the same wherever you stand, around 250 ms, because it always reads 601 MB.

The crossover is where it gets interesting. A plain index scan loses to the sequential scan at about 1%, and the bitmap scan holds on until somewhere between 1% and 2%. At 10% the bitmap scan took 493 ms to the sequential scan’s 255 ms. At 40% the plain index scan took over ten seconds, 33 times the sequential scan, because each matching row pulls a heap page in random order and the 128 MB of shared buffers cannot hold the working set.

What did the planner pick on its own?

The planner stuck with the bitmap scan from 0.002% to 20% and switched to a sequential scan only at 40%. Checking a few more points with plain EXPLAIN, the switch happens between 20% and 30%. The real crossover on this machine was between 1% and 2%, an order of magnitude earlier.

It was not a statistics problem. At 10% the plan reported an estimate of 499,336 rows and returned 500,372, which is within 0.2%. The planner knew exactly how many rows it was getting. It priced the bitmap scan at 122,209 units against 139,423 for the sequential scan, about 12% cheaper, and the real ratio was roughly 1.8 times the other way.

Part of the real cost is visible in the plan output. With the default work_mem of 4 MB, the bitmap could not hold exact row locations for every page, so 33,112 of its 76,834 heap pages went “lossy”: Postgres remembers only the page, then rechecks every row on it. That removed 1.9 million rows by recheck. Raising work_mem to 64 MB made all pages exact and brought the 10% case down to about 400 to 415 ms, an improvement but still well short of the 255 ms sequential scan. The lossy bitmap is one contributor, not the whole story.

Measurement 2: the same query on an ordered column

Now the same table and the same kind of range filter, on seq, the column whose order matches the heap.

At 10%, the ordered column answered in 46 ms with the index while the sequential scan still took 252 ms. The random column at the same 10% selectivity took 464 ms with the plan the planner chose, about ten times longer. Same table, same row count, same index type, same filter shape. At 40% the index still won here (300 ms against 386 ms), where on the random column it had lost by 33x.

So selectivity alone does not decide anything. The deciding factor is how many distinct heap pages the matching rows land on. In an ordered column, 10% of the rows live in 10% of the pages, and the index scan reads them in order. In a random column, 10% of the rows touch nearly every page, so you pay for the index lookups and still read the entire heap.

Postgres models this explicitly. In cost_index, it estimates a best-case and a worst-case I/O cost and interpolates between them using the square of the correlation: run_cost += max_IO_cost + csquared * (min_IO_cost - max_IO_cost).⁴ The source comment even flags the squared weighting with an “XXX is that appropriate?” note.⁴ A bitmap scan does something similar with page count: its cost per page slides from random_page_cost toward seq_page_cost as the fraction of the table fetched grows.⁴

You can see what the planner believes about your column with one query:

SELECT attname, correlation FROM pg_stats WHERE tablename = 't';

Measurement 3: stale statistics, and a plan that got faster

The other classic explanation for “wrong plan” is that the statistics are out of date. I tested it, and the result was not what I expected either.

I created a 3-million-row table where every row has status = 'done', ran ANALYZE, and then updated 5% of the rows to 'pending' without re-analyzing. Statistics are only refreshed by ANALYZE, whether run by hand or by the background auto-analyze, and are “always approximate even when freshly updated.”⁵ I turned autovacuum off for this table so the test would be deterministic. In production it would normally catch up, but the window before it does can be long on a busy table.

With the stale statistics, WHERE status = 'pending' was estimated at rows=1. The actual count was 150,000. After ANALYZE the estimate was 151,605. That is a 150,000x error, caused entirely by pending not appearing in the stored most-common-values list.

On the single-table query, nothing changed. Both the stale and the fresh plan were an index scan at 13 to 14 ms once warm. The updated rows were most likely written together at the end of the table, which would make the index scan the right answer regardless.

A join is where the estimate gets used more heavily. Joining those pending rows to t and filtering t.val < 500000:

The stale plan was faster. The nested loop did 150,000 index probes into t, which on a warm cache is cheap, and the fresh, more accurate plan decided to read all 5 million rows. The error pushed the planner toward the plan my warm-cache machine happened to prefer.

That does not make stale statistics harmless. A misestimate and the real cost of the plan it produces are separate things, and I observed only one direction. With cold storage, the same error could go the other way. I did not measure that, so I will not claim it.

Measurement 4: when the index cannot be used at all

Sometimes the planner is not choosing. The index simply does not apply to the query as written. I built a million-row table with an uppercase hash in a name column, and an ordinary B-tree index on it.

An index on name stores values of name, not values of lower(name). Once the column is wrapped in anything, even || '', the planner cannot use it. The documentation’s own example is exactly this one: the query can use an index “if one has been defined on the result of the lower(col1) function.”⁶ A leading-wildcard LIKE has the same problem, since a B-tree is ordered by prefix.

Two details stand out. Without the expression index, the planner guessed 5,000 rows for the lower(...) filter, exactly 0.5% of the table, when the true answer was 1, since it has no statistics on an expression nobody indexed. And lower() took 450 ms against 70 ms for || '', so the function call alone costs about 380 ms over a million rows.

The fix is the expression index, with the trade-off the documentation states plainly: it is “relatively expensive to maintain,” since the expression is computed on every insert and non-HOT update, but not recomputed during a search.⁶

What this does not show

Every number came from one Windows machine, one Postgres version and one warm cache. Cold storage was not measured, and it is the case random_page_cost = 4.0 was designed around, so I would not assume my crossover transfers to a table that does not fit in memory. Parallel sequential scans were disabled, which if anything makes the gap in Measurement 1 conservative. And it is one query shape: count(pad) over a range on a plain int. A query with ORDER BY ... LIMIT can let an index scan stop early and win big.

I also would not turn this into a settings change. In a random_page_cost sweep, a value of 12 made the planner’s choice flip between sequential and bitmap scans as selectivity rose, not move in one direction, which is the per-page cost interpolation from the source at work. Tuning one constant to fix one query is how you end up with a different one.

A diagnostic routine that survives all of this

When the plan looks wrong, in order:

1. Run EXPLAIN (ANALYZE, BUFFERS) and compare rows= with actual rows=. If they differ by more than about ten times, fix the statistics first (ANALYZE, or an expression index).
2. If the estimates are right, check correlation in pg_stats. A low value explains a mid-selectivity index plan that is slower than it looks.
3. Check the predicate lets the index apply: no function around the column, no leading wildcard, matching types.
4. Only then time the alternatives with SET enable_seqscan = off in one session, to see whether the planner’s choice was the slow one. It is a measuring tool, not a fix.

Takeaways

• A Seq Scan next to an index is not a bug report. On my 5M-row table, a sequential scan beat every index plan once more than 1 to 2% of a randomly ordered column matched.
• The planner knew the row count to within 0.2% and still chose a bitmap scan that ran up to 1.85x slower. Accurate estimates do not guarantee accurate costs.
• Physical order matters as much as selectivity. The same 10% filter took 46 ms on the ordered column and 464 ms on the random one. Check pg_stats.correlation.
• A badly wrong row estimate (1 against 150,000) did not hurt the plan here and even helped on a warm cache. Do not treat estimate error and plan quality as the same thing, but do not count on the luck either.
• If the predicate wraps the indexed column in a function, no tuning will bring the index back. Index the expression instead: 450 ms became 0.05 ms.

References