Why Postgres Ignores Your Index

Postgres ships with a thumb on the scale in your index's favour. When the planner has no statistics for an equality predicate it falls back to a selectivity of 0.005, and the comment above that constant in selfuncs.h says the number was chosen deliberately — small enough "to ensure that indexscans will be used if available, for typical table densities of ~100 tuples/page". Missing statistics is the first explanation to rule out, not the last.

The interesting failures are the other ones. The planner has the numbers, does the arithmetic, and still picks a sequential scan. What it lacks there is not data about your rows. It lacks any way to represent what it doesn't know.

The usual mental model says a planner compares the cost of two access paths and takes the cheaper one, so a bad plan means bad statistics, and the fix is ANALYZE. That model is useful and it is how the code is structured. It also predicts the wrong things often enough that three companies have written public postmortems about it.

The cost model counts pages, not milliseconds

Cost is not time. It is a dimensionless number anchored to a convention: seq_page_cost is 1.0, and everything else is priced relative to it.

The same values are still there in the PostgreSQL 19 beta. Those first two numbers carry most of the weight in the decision you are asking about, and 4.0 has been the default since roughly 2000.

Assumed. Not measured. The cost model has no idea what is actually in shared_buffers or in the operating system's page cache, and that gap is large enough that Google rebuilt the costing in AlloyDB around it.

The direction of the error is not fixed, which is what makes tuning it by rule of thumb hopeless. In an October 2025 pgsql-hackers thread, Tomas Vondra measured the ratio on real hardware with fio at 8kB blocks and iodepth 1, and derived a "correct" random_page_cost from it:

His summary: "These are reasonably good SSDs, and yet the 'correct' random_page cost comes out about 5-10x of our default." So the default may be too low for cold data while remaining too high for hot data. It is a single scalar standing in for two different worlds — and Freund's measurement above shows that part of what it is modelling is CPU cache locality and sheer buffer-lookup count, not the disk at all.

Correlation is where this gets concrete for a specific index. cost_index interpolates between a best case, where the index order matches physical row order, and a worst case where it doesn't:

csquared = indexCorrelation * indexCorrelation;
run_cost += max_IO_cost + csquared * (min_IO_cost - max_IO_cost);

The comment directly above that line ends with "(XXX is that appropriate?)". Two more details in the same neighbourhood decide more index fates than most people realise. For a multi-column B-tree, btcost_correlation() takes the correlation of the first key column only and multiplies it by a hardcoded 0.75. And effective_cache_size is divided among the tables in the query, proportionally to their size. An index that looks cheap in a single-table SELECT can be priced out of a five-table join for no reason connected to the index itself.

Why fresh statistics made three companies fall over

1. January 2020

   Figma

   2h 18m degraded

   A routine ANALYZE changed a plan on their roles table. The planner estimated over 20 million rows where the real answer was 3.

2. March 2021

   GitLab

   6 incidents in 11 days

   The 15 March one ran 2 hours 6 minutes and ended with the primary being restarted. Running ANALYZE helped temporarily and did not prevent the next recurrence.

3. 19 February 2026

   Clerk

   ~90 min, 95% of traffic on HTTP 429

   An automatic ANALYZE convinced the planner that a nullable column was 100% NULL. The real figure was 99.9996%. That rounding error turned a query that should have touched nothing into one walking more than 17,000 rows.

Six years apart, different versions, one shape: the statistics were not stale. They were fresh, and fresh made it worse. Sampling is why.

ANALYZE reads 300 * statistics_target rows, so 30,000 at the default target of 100. The multiplier traces back to Chaudhuri, Motwani and Narasayya (SIGMOD 1998), and it is a defensible number for building a histogram. It is a much weaker number for deciding that the last 0.0004% of a column doesn't exist.

Worse, it doesn't sample rows. It samples blocks, then applies Vitter's algorithm inside them, and analyze.c is candid about the consequence:

* not every possible sample has an equal chance of being selected.
* For large relations the number of different blocks represented by
* the sample tends to be too small.  We can live with that for now.
* Improvements are welcome.

Since correlation is computed from the physical ordering of that sample, the bigger the table, the less you should trust the very number that decides whether an index scan looks cheap.

Then the estimates get combined, and independence is assumed. From clausesel.c: "that's only right if they have independent probabilities, and in reality they are often NOT independent even if they only refer to a single column." The documentation's own example is two perfectly correlated columns where WHERE a = 1 AND b = 1 estimates 1 row and returns 100.

CREATE STATISTICS fixes exactly that example. It does not fix the general case, because extended statistics are, in the words of the PostgreSQL 18 documentation, "not currently used by the planner for selectivity estimations made for table joins" — and joins are where estimation errors actually compound. Leis and co-authors measured how badly in How Good Are Query Optimizers, Really?:

Same query, same data, four answers spanning two orders of magnitude — and none of them close. Their 2025 retrospective in PVLDB 18(12) reports the finding still holds.

Robert Haas, who has committed to this planner for fifteen years, has said in public that the project has not come meaningfully closer to an optimizer that never errs, and that it is unclear whether such a thing is achievable. That is not a confession of a bug. It is a description of a model that optimises expected cost and has no term for risk.

A sequential scan is an answer, not a failure

The canonical demonstration is four lines of the official documentation. Same table, same index, one number changed:

-- WHERE unique1 < 100   (1% of rows)
Bitmap Heap Scan on tenk1  (cost=5.06..224.98 rows=100)
-- WHERE unique1 < 7000  (70% of rows)
Seq Scan on tenk1  (cost=0.00..470.00 rows=7000)

Nothing broke between those two plans. An index scan pays random-access cost per heap visit; once you are touching most of the pages anyway, you are paying that premium to reach the same data a sequential read would have streamed. The documentation on partial indexes puts a working threshold on it: a query searching for a value "that accounts for more than a few percent of all the table rows will not use the index anyway".

That last sentence is the practical one. If status = 'active' matches 80% of your table, an index on status is not being ignored. It is being declined, correctly, and the fix is a partial index on the rare values or no index at all.

Some predicates never reach the index at all

Four patterns account for most of the rest, and they share a mechanism: the B-tree stores values of the raw column, and the predicate asks about something else.

A function on the column is the obvious case. WHERE lower(email) = $1 cannot use a B-tree on email, because lower(email) is not what is stored. An expression index makes it storable, at which point the planner sees "just WHERE indexedcolumn = 'constant'". The constraint is that the expression must be IMMUTABLE, which is why date_trunc('day', created_at) on a timestamptz is rejected: its result depends on the TimeZone setting. Tom Lane reclassified those functions back in October 2004. Pinning the zone with created_at AT TIME ZONE 'UTC' makes it indexable again.

Implicit casts are subtler, and half of what circulates about them is folklore. The rule is that sargability survives when the constant is cast and dies when the column is cast, and what decides that is whether a cross-type operator exists in the same B-tree operator family. integer_ops contains them, so a bigint column compared to an int4 parameter is fine — the advice to add explicit casts there is a fossil from before PostgreSQL 8.0. numeric shares no family with int8, so this happens instead:

EXPLAIN SELECT id FROM a WHERE id = 9223372036854775808;
-- Parallel Seq Scan on a
--   Filter: ((id)::numeric = '9223372036854775808'::numeric)

The literal is one past the int8 range, so it parses as numeric, so the column gets promoted. An unvalidated numeric field on a public API is enough to force that plan on demand.

The layer that most often does this to you is not your SQL. Prisma issue #25807, confirmed and open since December 2024, generates WHERE "status" = CAST('ACTIVE'::text AS "public"."subscription_status") for enum comparisons. With the cast, a sequential scan; without it, a bitmap index scan on the same partial index.

Prefix LIKE depends on collation for a reason worth understanding. The planner rewrites LIKE 'foo%' into a range, col >= 'foo' AND col < 'fop', which is only sound if ordering is byte-wise. Under any locale-aware collation it isn't, so you need text_pattern_ops, which compares "strictly character by character rather than according to the locale-specific collation rules".

Making ICU the default collation in PostgreSQL 15 changed nothing about the first of those, because ICU is locale-aware too.

OR across different columns is the all-or-nothing one. The best available plan is a BitmapOr, and it costs you two things the documentation states plainly: any ordering from the underlying indexes is lost, so an ORDER BY ... LIMIT now needs a sort, and every branch needs an index or the whole predicate degrades to a sequential scan.

That ORDER BY ... LIMIT figure above deserves its own warning, because it is the one that fails silently and catastrophically. A 2011 pgsql-performance report documents it, and Tom Lane's assessment of a 2022 recurrence was that Postgres has no statistics capable of detecting the situation.

Read the plan as a record of belief

EXPLAIN output is not a report of what happened. It is the planner's reasoning, annotated with what happened, and the two are worth separating as you read.

Start with Index Cond versus Filter, because it is the difference between an index that narrowed the work and one that merely appeared in the plan. Adding a non-indexed condition to the documentation's example drops the row estimate from 100 to 1 while the cost goes from 224.98 to 225.20 — the documentation's own gloss is that it "reduces the output row count estimate, but not the cost because we still have to visit the same set of rows".

There is a trap one level below that. As Markus Winand documents, Postgres prints access predicates and index filter predicates identically, both as Index Cond, even though only the first narrows the range being walked. An index filter predicate gives what he calls a false sense of safety: the plan looks right and degrades as the table grows. Comparing the index definition against the condition, or watching buffers across two plans, is the only way to tell them apart.

Which brings up the field that ends most arguments. BUFFERS counts 8kB blocks touched, and unlike timing it doesn't move when the machine is busy or the cache is warm.

As of PostgreSQL 18, EXPLAIN (ANALYZE) enables BUFFERS by default. Note that auto_explain was not changed, so log_buffers still has to be switched on there.

One structural detail causes more misreadings than any other. Within a single node, some numbers are per-loop averages and some are cumulative totals:

You can read both behaviours off explain.c in PostgreSQL 18: show_instrumentation_count() divides, while Heap Fetches prints ntuples2 raw. An actual time of 0.003 ms under loops=100000 is 300 ms of real work.

Two more habits pay for themselves. Chase the deepest misestimate rather than the slowest node, because a child's error propagates into every parent estimate above it. And if your application uses prepared statements, the plan you get from EXPLAIN with literals may not be the plan that runs: after five executions Postgres may switch to a generic plan built without knowledge of your parameter values. BUG #17540 on 14.4 documents a generic plan with a lower estimated cost executing over 6500 times slower, and Tom Lane's reply calls the comparison itself "not really logically valid, because those estimates are founded on different statistics". EXPLAIN (GENERIC_PLAN), added in PostgreSQL 16, is how you see the other one.

Postgres 18 struck two items off this list

Two entries in every older version of this list stopped being true in September 2025, and it is worth knowing which.

Skip scan, commit 92fe23d93 by Peter Geoghegan, lets a multi-column B-tree serve WHERE b = 5 on an index over (a, b) by generating a skip array and running the query as though it were a = ANY(<every distinct a>) AND b = 5. The leading-column rule is softened, not repealed: the documentation notes that with many distinct values of a the whole index has to be scanned and a sequential scan usually wins anyway.

The OR-to-ANY transformation is the one to get right, because plenty of secondary sources place it in the wrong release. It was committed on 8 April 2024 and reverted two days later after Tom Lane's objections. It landed in PostgreSQL 18 instead, in a different form — applied during index matching rather than as a preprocessing rewrite — and it applies to OR on a single column. OR across different columns is still a bitmap or a sequential scan.

One diagnostic habit changed too. enable_seqscan = off no longer adds a disable_cost of 1e10; PostgreSQL 18 counts disabled nodes as a separate metric compared ahead of cost, and prints Disabled: true. If you learned to read the inflated number, it isn't there any more.

Debug the belief, not the index

Three things follow for day-to-day work.

Treat a rejected index as a claim to verify, not a bug to route around. The planner is asserting that the row estimate, the correlation and the cache assumption together make the index scan more expensive. Each of those is checkable — pg_stats for the first two, EXPLAIN (ANALYZE, BUFFERS) for what the scan really costs — and which one is wrong tells you what to fix. enable_seqscan = off is a fine way to ask what the alternative would have cost, and a bad thing to leave on.

Read buffers before you read time, and multiply by loops before you believe a number. These two habits catch the misreadings that send people off optimising a node that was never the problem. In PostgreSQL 18 the first one costs nothing extra.

Distrust the tuning advice that travels without conditions. If you change random_page_cost, change it because a specific query's estimated and actual costs disagree in a specific direction, and check the next few plans it moves.