Why your index is not being used
The planner counted, and decided that reading the whole table in order is cheaper than jumping around it. That is usually right. The interesting question is where the line sits — and it sits lower than most people expect.
It is like fetching twenty things from a supermarket. For a handful, you walk to each shelf and back. Past that it is quicker to push a trolley down every aisle once, even though you pass a great deal you do not want.
You did not really want to know how indexes work. You wanted to know why yours is being ignored on this one query, today, on a table that used to be fast. Pull the slider below and you will find the crossover yourself — then the rest of this page explains why it sits where it does.
The two costs being compared#
A sequential scan ▬ reads every page of the table, in physical order, and the storage layer is extremely good at that: it reads ahead, pages arrive in batches, and the per-page cost is low. Its total cost barely depends on how many rows actually match.
An index scan ● reads a handful of index pages and then fetches each matching row from wherever it happens to live. Those fetches are scattered, each one is individually more expensive, and there is one per matching row. Its cost is roughly proportional to the size of the result.
Two costs with different slopes cross exactly once. The index scan starts cheap — the fixed-depth descent that makes a B-tree’s read cost predictable costs the same few pages whatever the query asks for — and then climbs, one scattered fetch per matching row. Everything the planner does around the crossing — statistics, histograms, correlation — is an attempt to guess which side of it your query is on before running it.
On the shape above, that crossing lands at about seventeen rows in twelve hundred — a shade over one percent. That is the number that surprises people: the index stops being worth it far earlier than “most of the table” suggests, and on an SSD-tuned random_page_cost it moves out to nearer five.
Follow one query down#
Five stages, and everything turns on the estimate made at stage three. Nothing moves until you press.
The four reasons you will actually hit#
In roughly the order I have run into them. The first two account for most of it.
The predicate is not sargable
You indexed the column and then queried a function of the column. The index is on the value, not on the result of calling something on it.
WHERE lower(email) = ?
The statistics are stale
The planner thinks the table has 900 rows because that is what it had when it was last analysed. It is guessing on last month’s shape.
ANALYZE orders;
The column is being cast, not the value
Postgres resolves a bare literal to the column’s type, so order_id = ‘9043’ on a bigint column is fine and still uses the index. The trap is the other direction: an id stored as text and compared against a number, which forces the cast onto the column — and you are back at reason one.
WHERE order_ref::bigint = 88214
The composite column order is wrong
A composite index serves a filter on its leading column, in the same key order an in-order walk reads out of a search tree. The same two columns in the other order do not help you at all.
INDEX (tenant, created_at)
Ask the database, not the internet#
Every guess above is settled by one command. Run it on the real query, on the real data, and compare the estimated row count against the actual one — the gap between those two numbers is the entire problem, made visible.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE tenant = 7;
Seq Scan on orders (cost=0.00..2891.00 rows=41 width=182)
(actual time=0.014..38.902 rows=8940 loops=1)
Filter: (tenant = 7)
Buffers: shared hit=1891The planner expected 41 rows and got 8,940. It did not choose badly; it was asked the wrong question. ANALYZE orders; and run it again.
Why forcing the index backfires#
The tempting fix is to force the index. It works on the test data and fails in production, because the test data has a uniform distribution and production has one tenant holding forty percent of the rows. The planner was not wrong about that query; it was right about the average query and wrong about yours.
Fix the statistics or the predicate, not the plan. A forced plan is a decision frozen at the moment you were most confused.
IF YOU REMEMBER ONE THING
An ignored index is almost never a bug in the planner. It is a disagreement between the statistics and reality, and only one of those two can be edited.
All of this assumes your query is the only one running. Once it is not, the planner’s estimate is no longer the only thing between you and a slow answer — the isolation level decides what your query is even allowed to see.
Questions people also ask
6 QUESTIONSHow do I force Postgres to use my index?
You can, with enable_seqscan = off around the query, and you almost certainly should not. It works on your test data and fails in production, where one tenant holds forty percent of the rows. Fix the statistics or the predicate instead, so the planner reaches the right conclusion on its own.
Why does the same query use the index on staging but not in production?
Different data distribution, and therefore different statistics. Staging is usually small and uniform, so a small result set is a realistic estimate. Production has skew, so the same predicate matches far more rows and the scan wins on cost.
Does an index on (a, b) work for a query on b alone?
No, not as a normal range scan. A composite index is sorted by a first, so a filter on b alone has nothing contiguous to seek to. Postgres may still choose a full index scan if the index is much smaller than the table, but that is a consolation prize rather than the thing you wanted.
What is a sargable predicate?
One the engine can turn into an index seek. Comparing the column itself is sargable; comparing a function of the column is not, because the index stores the column values and not the results of calling something on them. Index the expression if you need to query the expression.
How often should I run ANALYZE?
Autovacuum handles it for most tables. The exceptions worth watching are tables that grow or change shape quickly, and tables you have just bulk-loaded — those need an explicit ANALYZE before anyone runs a query you care about.
Is a sequential scan always bad?
No. On a small table, or a query matching a large fraction of the rows, it is the fastest available plan by a wide margin, because sequential reads are cheap and predictable. It is only a problem when the planner picks it based on an estimate that turned out to be wrong.