Why Postgres uses a sequential scan despite an index

Quick answer: Usually the planner is right, and the choice is not between an index and a table scan. It is between four access paths, and the deciding cost follows heap pages, not rows. Read EXPLAIN (ANALYZE, BUFFERS) and compare the estimate with the actual count before concluding that the index was ignored in error.

What was measured, and where

Every number on this page was run in one container, so differences between the rows reflect the query rather than the machine.

server	PostgreSQL 18.6 in container dbg-pgseq, image postgres:18.6, default settings
host	Docker on an 8 vCPU host
table	orders, 2,000,000 rows, 16,667 heap pages, B-tree indexes on status and on created_at
second table	orders_clustered, the same 2,000,000 rows stored grouped by status, 16,704 heap pages
date	2026-09-20

Plan shape, buffer counts and timings depend on the machine, the cache, the settings and the statistics. What follows is one environment’s output, not the output you should expect from running the same SQL.

The status column is deliberately skewed, because a uniform column hides the subject. Measured on that PostgreSQL 18.6 dbg-pgseq container on 2026-09-20, with the server computing the percentage column in the same query:

status	rows	percent of table
refunded	2,003	0.10
pending	48,077	2.40
shipped	485,878	24.29
complete	1,464,042	73.20

What is actually happening?

An index is not a shortcut the database takes whenever one exists. It is one option with a cost, and the planner picks the cheapest plan it can estimate.

There are four paths, not two. The same query, table and indexes were tested on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, varying only the filtered value, with three warm runs each:

filtered value	plan chosen	estimated rows	actual rows	heap blocks	three warm runs (ms)
refunded	Index Scan	2,067	2,003	not reported	6.127, 6.358, 6.993
pending	Bitmap Heap Scan	44,133	48,077	15,773	52.988, 49.812, 56.748
shipped	Bitmap Heap Scan	490,000	485,878	16,667	207.388, 188.671, 183.568
complete	Seq Scan	1,463,800	1,464,042	not reported	438.233, 362.657, 384.149

That table shows three of the four paths because the fourth needs an index this bench did not initially have. With a covering index added, the same refunded query became an Index Only Scan with Heap Fetches: 0 in 0.459 to 0.897 ms across three warm runs, on that same PostgreSQL 18.6 dbg-pgseq container on 2026-09-20.

The bitmap heap scan is the middle option. It reads the index to build a list of tuple locations, sorts that list into physical order, and then visits the heap in one forward pass. PostgreSQL uses this path when a predicate matches more than a handful of rows but far fewer than the table. An explanation framed only as index versus sequential scan leaves it out.

The number that decides it is pages, not rows

The bench was built to isolate this. Measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, two tables hold exactly the same 2,000,000 rows and exactly the same 48,077 pending rows, 2.40 percent of the table. The only difference is physical: in orders the matching rows are scattered, while in orders_clustered they are stored next to each other. Both tables were measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, with three warm runs each:

table	status correlation	plan chosen	heap blocks touched	estimated total cost	three warm runs (ms)
orders	0.60	Bitmap Heap Scan	15,773 of 16,667	17,713.12	52.988, 49.812, 56.748
orders_clustered	1.00	Index Scan	not reported	1,318.43	14.579, 12.184, 11.087

The rows, count, fraction, server and day were all the same. Measured on that PostgreSQL 18.6 dbg-pgseq container on 2026-09-20, the scattered copy had to read 15,773 of the table’s 16,667 pages to return 2.40 percent of its rows, because a page must be read whether it holds one matching row or a hundred. The grouped copy did not, so the planner estimated an index scan at 1,318.43, well below the bitmap path at 17,713.12, and chose it.

The idea that an index stops paying above some fraction of the table is the wrong model, so no threshold based on that fraction will transfer. The planner estimates how many pages it will have to touch. The row fraction predicts that only when the matching rows are spread evenly.

The direction people usually state still holds, but for the page reason rather than the row reason: a predicate matching most of the rows must touch nearly every page, so a sequential scan wins. Measured on that PostgreSQL 18.6 dbg-pgseq container on 2026-09-20, shipped matched 24.29 percent of the rows and touched all 16,667 pages. The converse does not hold. A small fraction is not evidence of a small page count, which is what the two rows above measure.

How do I confirm it?

Get the plan with actual numbers. Before you run it on anything that matters, note what EXPLAIN ANALYZE does:

Keep in mind that because EXPLAIN ANALYZE actually runs the query, any side-effects will happen as usual, even though whatever results the query might output are discarded in favor of printing the EXPLAIN data.

That is from Using EXPLAIN in the PostgreSQL 18 documentation, read 2026-09-20. For anything that writes, wrap it in a transaction and roll back.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT id, total, created_at
FROM orders
WHERE status = 'pending';

Then read four things, in this order.

Estimated rows against actual rows, at the lowest node where they diverge. This is the first question, because every other decision is made from the estimate. Be precise about what the estimate is:

The rows value is a little tricky because it is not the number of rows processed or scanned by the plan node, but rather the number emitted by the node.

That is also from Using EXPLAIN in the PostgreSQL 18 documentation, read 2026-09-20.

loops, before you compare anything. On a node that ran more than once, the printed figures are per-execution, so comparing them to a total compares two different quantities:

In such cases, the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution.

That sentence is from Using EXPLAIN in the PostgreSQL 18 documentation, read 2026-09-20.

Index Cond against Filter. A condition under Index Cond narrowed the index scan. The same condition under Filter was applied to rows the scan had already produced, which means the index did not restrict it. This line separates “the index is being used” from “the index is being used for something else”.

Heap Blocks and Buffers. These show how much of the table the plan actually touched, which is the quantity discussed in the previous section.

Why the planner picks the table over the index

The query is not selective enough, in pages

This is the common case, and it is not a defect. Read Heap Blocks, not the row count.

The statistics are stale

The planner estimates from a sample collected by ANALYZE. After a bulk load, a large delete or a migration, that sample can describe a table that no longer exists.

SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

If last_analyze and last_autoanalyze are both old or null, fix that first with ANALYZE orders; and re-read the plan.

The columns vary together, and ANALYZE cannot fix that

Fresh statistics are not enough when the query filters two related columns, because of an assumption the planner makes by default:

The planner normally assumes that multiple conditions are independent of each other, an assumption that does not hold when column values are correlated.

That is from Statistics Used by the Planner in the PostgreSQL 18 documentation, read 2026-09-20.

Measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, on a 1,000,000 row shipments table whose country and city always move together, with statistics that had just been refreshed:

statistics available	estimated rows	actual rows
ordinary per-column only	10,214	100,000
plus CREATE STATISTICS (dependencies, mcv)	101,267	100,000

ANALYZE had just run in both cases. The sample was not stale; the model was wrong, and only the extended statistics object could tell the planner that city follows from country.

CREATE STATISTICS shipments_geo (dependencies, mcv)
  ON country, city FROM shipments;
ANALYZE shipments;

Use extended statistics when a jointly filtered pair is underestimated, and re-read the estimate afterwards to confirm it moved. They also correct the opposite error. On the same table, the pair country = 'GB' AND city = 'Boston' never occurs, yet ordinary per-column statistics estimated it at 90,932 rows; the extended statistics object estimated it at 1. Measured on that PostgreSQL 18.6 dbg-pgseq container on 2026-10-02.

The cost settings say random reads are expensive

The planner’s preference between an index path and a table scan depends on both configuration and measurement.

Sets the planner’s estimate of the cost of a non-sequentially-fetched disk page. The default is 4.0.

That is from Query Planning in the PostgreSQL 18 documentation, read 2026-09-20, describing random_page_cost.

Measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, the same status = 'shipped' query with nothing changed but that setting:

random_page_cost	plan chosen	estimated total cost
4.0, the default	Bitmap Heap Scan	28,273.93
1.1	Index Scan	22,218.60

Same data, same index, same statistics, different plan. On storage where random reads are not four times a sequential one, the same documentation says:

Also, in a heavily-cached database you should lower both values relative to the CPU parameters, since the cost of fetching a page already in RAM is much smaller than it would normally be.

That is from Query Planning in the PostgreSQL 18 documentation, read 2026-09-20.

Do not lower it after one slow query. The same page states the risk:

Unfortunately, there is no well-defined method for determining ideal values for the cost variables. They are best treated as averages over the entire mix of queries that a particular installation will receive. This means that changing them on the basis of just a few experiments is very risky.

That is from Query Planning in the PostgreSQL 18 documentation, read 2026-09-20.

One neighbouring setting is worth knowing because its name is misleading:

This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used only for estimation purposes.

That is effective_cache_size, from Query Planning in the PostgreSQL 18 documentation, read 2026-09-20. It changes plans without changing memory.

The plan was made without your parameter value

Applications bind parameters, and a reusable plan cannot know which value it will get. Measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, a prepared statement on status = $1 planned generically estimated 500,000 rows and chose a bitmap heap scan. The value the application passes, refunded, matches 2,003 rows, and a plan made for that value gets an index scan.

This is why a query can be fast in psql and slow from the application with the same SQL, the same index and the same data. Check it directly:

SET plan_cache_mode = force_generic_plan;

If the plan changes under that setting, the difference is the parameter, not the index.

The predicate is not sargable

A predicate is sargable when the planner can seek the index with it. A function around the indexed column usually prevents that because the index stores created_at, not the function’s result. Measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, the same single day of rows fetched three ways:

how the day was asked for	plan	three warm runs (ms)
date_trunc('day', created_at) = DATE '2025-03-01'	Parallel Seq Scan	146.170, 137.174, 144.205
created_at >= '2025-03-01' AND created_at < '2025-03-02'	Bitmap Heap Scan on orders_created_at	14.135, 16.929, 17.032
the date_trunc form, with an index on that expression	Bitmap Heap Scan on orders_day	15.698, 15.537, 14.991

All three returned the same 3,334 rows, measured on that PostgreSQL 18.6 dbg-pgseq container on 2026-09-20. The third row shows that an expression index makes the function form indexable and brings it to the same cost as the rewrite rather than a lower one. The rewrite and the expression index are two ways to the same result, and the rewrite requires no index.

That third row only worked because the bench column is timestamp. On a timestamptz column, which is what most created_at columns are, the same statement is refused:

CREATE INDEX orders_day ON orders (date_trunc('day', created_at));

ERROR:  42P17: functions in index expression must be marked IMMUTABLE

The reason is the function, not the index. Measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, pg_proc.provolatile reads i for date_trunc(text, timestamp without time zone) and s for date_trunc(text, timestamp with time zone). The second depends on the session time zone, so its result is not a fixed property of the row and cannot be stored in an index.

The three-argument form names the zone and is immutable, so it can be indexed:

CREATE INDEX orders_day ON orders (date_trunc('day', created_at, 'UTC'));

A query only uses that index if it writes the predicate the same way, including the zone argument. That is the cost of the expression-index route on a timestamptz column: every query must use one named time zone, so choose that deliberately.

An expression index is also maintained on every write, and it blocks heap-only updates on the column it is built over. Measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, a 200,000 row table with fillfactor = 70 and no expression index took 200,000 updates of its timestamp column, of which 86,850 were heap-only. An index on date_trunc('day', t) was then created and the same 200,000 updates were repeated: the heap-only count stayed at 86,850, so none of the second round qualified. The updates moved the timestamp by one second and did not change the value of date_trunc('day', t) at all, which is the relevant detail. PostgreSQL checks whether an indexed column changed, not whether the indexed expression changed. An expression index earns its place on a query you run often, not on one you ran today.

On the boundary, prefer a half-open range. Measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20 against three rows, one of which sat exactly at midnight, BETWEEN returned 2 and >= AND < returned 1. BETWEEN is inclusive at both ends, which is right for a closed range and wrong for a contiguous time bucket. Choose the form that matches the range.

A cast landed on the column

This is easy to miss because the query returns correct results. Measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, against a bigint primary key:

what was written	what the plan shows	plan	three warm runs (ms)
id = 42	Index Cond: (id = 42)	Index Only Scan	0.127, 0.094, 0.089
id = '42'	Index Cond: (id = '42'::bigint)	Index Only Scan	0.092, 0.094, 0.088
id = 42.0	Filter: ((id)::numeric = 42.0)	Parallel Seq Scan	134.724, 138.808, 150.689
a numeric bind parameter	Filter: ((orders.id)::numeric = '42'::numeric)	Parallel Seq Scan	169.306, 126.375, 118.748

A type difference does not by itself cost the index: the second row compares an untyped literal and PostgreSQL resolved it to bigint. What costs the index is the cast landing on the column, which turns every row into a computed value the index does not hold. (orders.id)::numeric on the left of the comparison is the tell, and EXPLAIN (VERBOSE) is where you see it.

What actually fixes it

An index shaped for the query

Measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, a partial index covering only the pending rows was 1,032,192 bytes against the full status index’s 14,057,472 bytes, and the ten-row lookup it serves ran in 0.119, 0.120 and 0.121 ms across three warm runs, on a plan whose scan node names that partial index.

CREATE INDEX orders_pending_created ON orders (created_at)
  WHERE status = 'pending';

It is usable only where the query’s predicate implies the index’s predicate, so it fits a stable subset queried constantly rather than an arbitrary filter.

A covering index, with its condition attached

An index-only scan avoids visiting the heap, but only under a condition people forget:

An index-only scan, after finding a candidate index entry, checks the visibility map bit for the corresponding heap page.

And when that bit is not set:

If it’s not set, the heap entry must be visited to find out whether it’s visible, so no performance advantage is gained over a standard index scan.

Both sentences are from Index-Only Scans and Covering Indexes in the PostgreSQL 18 documentation, read 2026-09-20.

Measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, a covering index on status including the three selected columns answered the 2,003 row query with Heap Fetches: 0 in 0.897, 0.459 and 0.476 ms across three warm runs. An UPDATE then touched one percent of the table, and the same plan on the same container and day reported Heap Fetches of 2,025, 2,003 and 2,003 and took 21.704, 4.857 and 4.476 ms, for the same 2,003 returned rows, because the pages holding those rows were no longer marked all-visible.

CREATE INDEX orders_cover ON orders (status) INCLUDE (id, total, created_at);

The counter you would reach for to check this can be stale. Measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, pg_class.relallvisible read 16,667 of 16,667 pages while that query reported 2,003 to 2,025 heap fetches across its three runs, because relallvisible is refreshed by vacuum and analyze rather than kept current. Read Heap Fetches in the plan, which is measured by the execution you are looking at.

Ordering and LIMIT change the answer

A plan that is right for all the rows can be wrong for the first ten. Measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, the same filter and the same ORDER BY created_at:

query	plan	three warm runs (ms)
all 48,077 rows	Bitmap Heap Scan then Sort	83.964, 77.663, 73.905
the same, with LIMIT 10	Index Scan on orders_created_at	0.711, 0.725, 0.693

With the LIMIT, walking the created_at index in order and stopping after ten matches beats building the whole result and sorting it. A query that “does not use the index” may start using it when the application adds the LIMIT, and benchmarking without that LIMIT measures a different plan.

enable_seqscan is a diagnostic, and it does not forbid anything

SET enable_seqscan = off;

If the query gets faster with it off, the cost estimate was wrong. Find out why, then turn it back on. Leaving it off in production removes the planner’s ability to pick the right plan for every other query.

It is also weaker than its name. Measured on PostgreSQL 18.6 in container dbg-pgseq on 2026-09-20, a table with no usable index still got a sequential scan with the setting off, and PostgreSQL 18 labelled the node Disabled: true rather than refusing to plan. The switch discourages a path; it does not remove it.

Why does AI-generated code cause this?

There are three common patterns.

The first is the date wrapper. Asked for “orders from March”, a model often writes WHERE date_trunc('month', created_at) = ... or WHERE to_char(created_at, 'YYYY-MM') = '2026-03', because those expressions read like the question. Both are correct, and against a plain index on the column both forfeit it. to_char also pins the month to the server’s idea of the calendar, which for a timestamptz column is a time zone decision.

The second is the confident index suggestion. Asked why a query is slow, a model proposes an index without seeing the plan, the data distribution or the physical arrangement of the rows. Sometimes that index is already there and correctly ignored, which is the subject here.

The third is benchmarking the wrong statement. Generated test code drops the LIMIT and the bind parameters, both of which change the plan, as the two tables above measure.

How do I stop it coming back?

Read Heap Blocks and Buffers, not the row fraction. The page count is what the planner estimates and what the storage actually does.

Keep statistics fresh on tables that change in bulk. Autovacuum’s analyze threshold scales with the table, which is why a large table can wait a long time for one:

analyze threshold = analyze base threshold + analyze scale factor * number of tuples

That is from Routine Vacuuming in the PostgreSQL 18 documentation, read 2026-09-20. On the PostgreSQL 18.6 dbg-pgseq container on 2026-09-20 those two parameters read 50 and 0.1. Put a 2,000,000 row table through that formula and the wait is long. Run ANALYZE at the end of a bulk load rather than hoping.

Raise the statistics target on a skewed column, which is the first fix for skew inside one column, before reaching for extended statistics:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

Add extended statistics only after proving an estimate error on a jointly-filtered pair, and re-read the estimate afterwards to confirm it moved.

Check the plan your application gets, not the one psql gets, because a bound parameter can be planned generically.

Treat random_page_cost as an instance-wide decision. It shifts every plan on the server, and the documentation quoted above says tuning it from a few experiments is risky.

How DBGorilla helps

DBGorilla can help

DBGorilla connects read-only and gives your AI coding agent the plan from your live database rather than a guess about it. It also collects the sequential-scan and index-scan counts per table over time and the last analyze time, so it can tell the difference between an index that is missing and an index the planner is correctly ignoring.

Start free

FAQ

• Why is Postgres not using my index?

  Usually because it costed another path lower, and there are four of them rather than two: sequential scan, index scan, bitmap heap scan and index-only scan. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgseq, one 2,000,000 row table answered the same query with an index scan, a bitmap heap scan and a sequential scan depending only on which value was filtered for. When the planner is genuinely wrong the causes worth checking are stale statistics, correlated columns, a predicate the index cannot be seeked on, a cast landing on the column, and a generic plan for a bound parameter.

• Is a sequential scan always bad in PostgreSQL?

  No. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgseq, a filter matching 1,464,042 of 2,000,000 rows was answered by a sequential scan in 362.657 to 438.233 ms across three warm runs, and that was the cheapest plan available. The signal worth chasing is a sequential scan that returns few rows, which shows up as a large Rows Removed by Filter next to a small actual row count.

• What fraction of a table makes an index stop paying?

  There is no such fraction, because the cost follows pages rather than rows. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgseq, the same 48,077 of 2,000,000 rows, 2.40 percent, cost 15,773 of the table's 16,667 heap blocks and a bitmap heap scan when those rows were scattered, and an index scan costing 1,318.43 when the identical rows were stored grouped together. Same fraction, same server, same day, different plan, because what changed was how many pages held the matches.

• Does ANALYZE fix a wrong row estimate?

  It fixes the ones caused by stale samples, and not the ones caused by columns that vary together. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgseq on freshly analyzed statistics, a filter on two columns that always move together was estimated at 10,214 rows against an actual 100,000, and adding CREATE STATISTICS (dependencies, mcv) took the estimate to 101,267. ANALYZE alone could not fix that, because the planner assumes the two conditions are independent.

• Does a type mismatch stop the index being used?

  Only when the cast lands on the column rather than the value. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgseq on a bigint primary key, comparing to 42.0 produced Filter: ((orders.id)::numeric = 42.0) and a parallel sequential scan of 134.724 to 150.689 ms across three runs, while comparing to '42' produced Index Cond: (orders.id = '42'::bigint) and an index-only scan of 0.088 to 0.094 ms. Read the plan for which side the cast is on.

Related

• How to read a PostgreSQL EXPLAIN ANALYZE plan
• Which PostgreSQL index type should I use?
• Why did my Postgres query suddenly get slow?