Why PostgreSQL is not using your index
Use EXPLAIN to check why PostgreSQL chose a scan: inspect index conditions, row estimates and costs before changing the query or statistics.
PostgreSQL may skip an index because the query cannot use it or because another plan has a lower estimated cost. Inspect the actual plan, index conditions, row estimates and buffers before changing statistics, the predicate or cost settings.
Suppose one tenant owns almost every order. Reading the table once may beat following an index to most of its rows. For a tenant with only a few orders, the index may help. Check which case your query and data actually make.
The index exists, but this query still uses a sequential scan. Before changing a setting, check whether the predicate can use the index and what the planner thinks each available path will cost. The slider below shows one possible cost crossover; it is a teaching model, not a threshold for your PostgreSQL table.
In this constructed example, a table has 1,200 rows on 60 pages. The sequential scan costs 72 arbitrary units; the index scan costs 3 plus 4 for each matching row. The model switches to a scan at 18 matches. Those numbers were chosen to expose the trade-off, not measured from PostgreSQL. Move the slider to see what changing the match count does while the other assumptions stay fixed.
What the cost sketch can tell you#
A sequential scan ▬ visits the table and tests its rows. In the sketch it pays a fixed cost whether the predicate matches two rows or two hundred. A real plan also accounts for row processing, caching and possibly parallel work; its estimated cost is not elapsed time.
An index scan ● finds matching index entries and may fetch their table rows from scattered pages. The sketch charges once per match to make that work visible. PostgreSQL can also group heap fetches in a bitmap scan, use cached pages or sometimes answer from an index without a heap fetch. Its EXPLAIN guide shows these distinct plans; the slider models only two simplified paths.
These two drawn lines cross between 17 and 18 matches. PostgreSQL has more candidate plans and more costs than the sketch. Its statistics estimate how many rows a condition will return; the planner then costs the eligible plans. A query can also miss an index because its condition does not match the indexed column or expression. The B-tree comparison explains the storage path behind one of those candidates.
The sketch’s switch at 18 of 1,200 rows is 1.5% of this invented table. Do not use that percentage as an index rule or infer a different threshold by changing random_page_cost: the slider does not model that setting. On a real table, inspect the available plans and compare their estimated and measured work.
Follow one query down#
Step through a simplified five-stage planning story. The estimate matters, but an unusable index or another cheaper plan can decide the outcome too.
Four checks when an index is missing#
These are checks, not a ranked list of causes. Start with the predicate and plan, then test the relevant explanation.
The predicate is not sargable
A plain index on email does not store lower(email). If that expression is the actual search task, an expression index can support it; adding one also has write costs.
WHERE lower(email) = ?
The statistics are stale
After a bulk load or a distribution change, the row estimate can diverge from actual results. Compare both in EXPLAIN (ANALYZE, BUFFERS); update statistics when they no longer describe the data, then check the plan again.
ANALYZE orders;
The column is being cast, not the value
An explicit cast on the indexed column changes the expression being searched. A plain index on order_ref may not support the cast expression below. Inspect the plan’s Index Cond rather than assuming that any type conversion disables an index.
WHERE order_ref::bigint = 88214
The composite column order is wrong
Leading columns usually make a B-tree search most efficient. For a filter on created_at alone, an index on (tenant, created_at) may still be usable: PostgreSQL 18 can use skip scan when repeated searches over few leading values pay off. Check the actual plan.
INDEX (tenant, created_at)
Use EXPLAIN to test each guess#
The query below is illustrative SQL, not a captured production plan. Run the equivalent SELECT against representative data where you are authorised to inspect it. ANALYZE executes the statement; BUFFERS reports buffer activity. Compare the chosen scan, Index Cond or Filter, estimated versus actual rows, and buffer hits and reads. PostgreSQL’s EXPLAIN reference defines these fields.
If the plan estimates 41 rows but returns 8,940, the estimate needs investigation; that difference alone does not explain why a sequential scan was chosen. If the estimate is close and many rows match, a scan may be sensible. Check the predicate and index first, then run ANALYZE orders; if the statistics are stale. Compare the plan and elapsed work again rather than assuming the index must appear.
Use a forced plan only as a diagnostic#
Temporarily setting enable_seqscan = off can help reveal and time another eligible plan. It discourages sequential scans; it cannot remove them entirely or make a mismatched index applicable. Compare both plans with representative data, then restore the setting. PostgreSQL’s index-usage guide recommends this as a diagnostic and distinguishes poor statistics from a scan that is genuinely cheaper.
If the index cannot match the condition, change the query or index design deliberately. If estimates are wrong, investigate statistics. If estimates are sound but timings disagree with costs, inspect caching and cost assumptions. The plan choice is the result to explain, not a verdict that the planner or the index is broken.
IF YOU REMEMBER ONE THING
First ask whether the predicate can use the index. Then compare estimated rows with actual rows and costs with measured work. A sequential scan can be the right result.
To see why the heap fetches behind an ordinary index scan can matter, follow the row’s path through pages and tuples. The two-plan slider leaves that storage detail out. When a query runs beside writes, isolation levels explain which row versions it may see; that is a separate question from which scan plan it uses.
Questions people also ask
6 QUESTIONSHow do I force Postgres to use my index?
Setting enable_seqscan to off discourages sequential scans but cannot eliminate them or make an unusable index fit the predicate. Compare candidate plans on representative data, then restore the setting. Investigate the query, statistics and costs before keeping any change.
Why does the same query use the index on staging but not in production?
Table size, value distribution, statistics, cache and planner settings may differ. A scan on a small staging table can even be cheaper than an index lookup. Compare EXPLAIN output and representative data before deciding that either plan is wrong.
Does an index on (a, b) work for a query on b alone?
The leading column usually matters most, but it is not an absolute no. PostgreSQL 18 can use a B-tree skip scan on a later column when repeated searches over the few leading values are worthwhile. It may instead scan much of the index or choose a table scan; inspect the actual plan.
What is a sargable predicate?
It is a condition an available index can help evaluate. A plain index on email does not directly provide lower(email), but PostgreSQL can use an index on that expression. Whether it chooses the index still depends on the estimated cost.
How often should I run ANALYZE?
Autovacuum normally schedules ANALYZE, but after a bulk load or a large distribution change, check whether statistics represent the current table. Run ANALYZE when needed and compare the estimates again; it does not guarantee an index plan.
Is a sequential scan always bad?
No. It can be cheaper for a small table or a query returning many rows. Even a good row estimate does not prove the chosen plan is fastest, and a poor estimate does not alone explain why an index was skipped. Compare plans and elapsed work on representative data.