# 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](https://www.postgresql.org/docs/18/using-explain.html), read 2026-09-20. For anything that writes, wrap it in a transaction and roll back. ```sql 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](https://www.postgresql.org/docs/18/using-explain.html), 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](https://www.postgresql.org/docs/18/using-explain.html), 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. ```sql 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](https://www.postgresql.org/docs/18/planner-stats.html), 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`. ```sql 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](https://www.postgresql.org/docs/18/runtime-config-query.html), 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](https://www.postgresql.org/docs/18/runtime-config-query.html), 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](https://www.postgresql.org/docs/18/runtime-config-query.html), 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](https://www.postgresql.org/docs/18/runtime-config-query.html), 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: ```sql 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: ```sql 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: ```sql 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. ```sql 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](https://www.postgresql.org/docs/18/indexes-index-only-scans.html), 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. ```sql 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 ```sql 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](https://www.postgresql.org/docs/18/routine-vacuuming.html), 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: ```sql 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 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. [Get started free →](https://app.dbgorilla.com/signup) ## Related - [How to read a PostgreSQL EXPLAIN ANALYZE plan](https://www.dbgorilla.com/learn/postgres/how-to-read-a-postgres-explain-analyze-plan/) - [Which PostgreSQL index type should I use?](https://www.dbgorilla.com/learn/postgres/which-postgresql-index-type-should-i-use/) - [Why did my Postgres query suddenly get slow?](https://www.dbgorilla.com/learn/postgres/why-did-my-postgres-query-suddenly-get-slow/)