# Postgres Not Using Index — Why the Planner Picks a Seq Scan, and the Fix for Each Cause

Canonical: https://commareports.com/fix/postgres-not-using-index
Published: 2026-11-10

> PostgreSQL ignores your index and runs a Seq Scan. Match what EXPLAIN shows to the cause — a function on the column, a type cast, a leading wildcard, the wrong column order, stale statistics, an INVALID index, a generic plan — and the fix for each.

# Postgres isn't using the index

You added an index, the query is still slow, and `EXPLAIN` says
`Seq Scan`. There are only two possibilities, and the fix depends on
which one you have:

1. **The index can't answer the query as written.** The `WHERE` clause
   doesn't match what the index stores — a function, a cast, a wildcard
   in front, the wrong column first.
2. **The planner could use it, and decided not to.** It estimated a
   sequential scan as cheaper — sometimes correctly, sometimes from bad
   statistics or bad cost settings.

Telling them apart takes one session-local switch:

```sql
BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT … ;   -- your query
ROLLBACK;
```

- The plan **switches to an Index Scan** → the index works; the planner
  priced it higher. Compare the actual times of both plans: if the index
  plan really is faster, it is a statistics or cost-setting problem (the
  bottom half of this page).
- It **still says Seq Scan** → the index can't serve this query (the top
  half).

`enable_seqscan = off` doesn't forbid seq scans, it makes them look very
expensive, so you'll see them anyway when there is no alternative. It is
a diagnostic switch — never put it in `postgresql.conf`.

## Match the symptom

| What the query or plan shows                                  | Cause                                      |
| ------------------------------------------------------------- | ------------------------------------------ |
| `WHERE lower(col) = …`, `date(created_at) = …`, `col + 1 = …` | Function or expression on the column       |
| `Filter: ((id)::numeric = …)`                                 | Column cast to the parameter's type        |
| `LIKE '%abc'`, `LIKE '%abc%'`, `ILIKE`                        | Leading wildcard — B-tree can't seek       |
| `LIKE 'abc%'` still a Seq Scan                                | Non-C collation without `text_pattern_ops` |
| Index on `(a, b)`, query filters only on `b`                  | Not the leading column                     |
| Small table, or many rows returned                            | Seq scan genuinely cheaper                 |
| `rows=` estimate 10× or more off the actual                   | Stale or missing statistics                |
| `\d table` shows the index as `INVALID`                       | Failed `CREATE INDEX CONCURRENTLY`         |
| Fast in psql, slow from the app                               | Generic plan for a prepared statement      |
| Index only matches some rows (`WHERE deleted_at IS NULL`)     | Partial index predicate not implied        |
| `Index Only Scan` with a large `Heap Fetches`                 | Visibility map stale — needs `VACUUM`      |

## The index can't answer the query

### A function or expression on the column

An index on `email` stores `email` values. `WHERE lower(email) = $1`
asks about a different value, so there is nothing to look up. Index the
expression itself — it must match the query exactly:

```sql
CREATE INDEX CONCURRENTLY users_lower_email_idx ON users (lower(email));
ANALYZE users;   -- statistics for the new expression
```

The same goes for dates. `WHERE date(created_at) = '2026-11-10'` can't
use an index on `created_at`; rewrite it as a range, which can:

```sql
WHERE created_at >= '2026-11-10' AND created_at < '2026-11-11'
```

And arithmetic: `WHERE price * 1.2 > 100` becomes
`WHERE price > 100 / 1.2`. Keep the indexed column alone on one side.

### A type cast on the column

When a parameter's type doesn't match the column, PostgreSQL may cast
the **column**, and an index on the column no longer applies. The plan
gives it away: `Filter: ((id)::numeric = '42'::numeric)`. Typical
source is an ORM or driver sending a `numeric` (or a Java `BigDecimal`)
for a `bigint` column. Fix the parameter
type in the application (or cast the parameter, `$1::bigint`), not the
column.

### A leading wildcard, or `LIKE` under a non-C collation

A B-tree index is sorted, so it can find everything starting with `abc`
— but not everything _ending_ with it. Two separate problems:

- **`LIKE 'abc%'`** works only if the database collation is `C`, or the
  index uses the pattern operator class:
  `CREATE INDEX ON products (sku text_pattern_ops);`
- **`LIKE '%abc%'` and `ILIKE`** can't use a B-tree at all. Use trigram
  matching:

```sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY products_name_trgm ON products USING gin (name gin_trgm_ops);
```

Trigram indexes need at least three characters of pattern to be useful.

### The column isn't first in a multi-column index

An index on `(tenant_id, created_at)` is sorted by `tenant_id` first.
A query on `created_at` alone has to read the whole index — before
PostgreSQL 18 the planner usually prefers the table. PostgreSQL 18 added
B-tree **skip scan**, which helps when the leading column has few
distinct values, but the dependable fix is still an index whose leading
column is the one you filter by with equality. Order: equality columns
first, then the range or sort column.

### A partial index the query doesn't match

`CREATE INDEX … WHERE deleted_at IS NULL` is only used when the planner
can prove the query's `WHERE` implies that predicate. Add the same
condition to the query, literally. With a parameter (`WHERE status = $1`
against an index `WHERE status = 'open'`) a generic plan can't prove it
— see prepared statements below.

### An INVALID index

`CREATE INDEX CONCURRENTLY` that fails (a deadlock, a duplicate under a
unique index, a cancelled session) leaves an index behind that is
maintained on writes but never used for reads. `\d table` prints
`INVALID` next to it. Find them all:

```sql
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
```

Drop it and build it again, or `REINDEX INDEX CONCURRENTLY` it.

## The planner chose not to use it

### The seq scan really is cheaper

Fetching a row through an index costs a random read of the table page.
When the query returns more than a few percent of the table, or the
table is a few pages, reading it straight through wins — and the planner
is right. A Seq Scan is only a problem when the plan shows a large
`Rows Removed by Filter` next to a small row count: it read everything
to keep a handful. If you need those rows faster, a covering index
(`INCLUDE (…)`) that allows an Index Only Scan is the usual next step.

### Stale statistics

The planner estimates row counts from `pg_statistic`, which autovacuum's
ANALYZE refreshes. After a bulk load, a big delete or a new expression
index, the numbers are wrong until it runs again. The plan shows it as
`rows=` estimates far from the actual rows. Run it yourself:

```sql
ANALYZE orders;
```

For skewed columns, raise the sample —
`ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;` then
`ANALYZE` — and for correlated columns (`city` and `zip`) add extended
statistics with `CREATE STATISTICS … (dependencies) ON city, zip FROM …`.

### Cost settings tuned for spinning disks

`random_page_cost` defaults to `4.0`, four times the cost of a
sequential page read — a 1990s disk ratio. On SSDs and most managed
databases, `1.1` describes reality better and makes index scans look as
cheap as they are. Also check `effective_cache_size`: set too low, the
planner assumes index pages won't be cached. Try both per session before
changing them globally.

### Prepared statements and generic plans

Your query is fast in `psql` and slow from the application. Drivers
prepare statements; after five executions PostgreSQL may switch to a
**generic plan** that ignores the parameter values, and for skewed data
(a status that is 0.1% `'open'` and 99.9% `'closed'`) the generic plan
can be a Seq Scan. Reproduce it with `PREPARE` + `EXPLAIN EXECUTE` six
times, then pin custom plans for that role or session:

```sql
SET plan_cache_mode = force_custom_plan;
```

### Heap Fetches on an Index Only Scan

An Index Only Scan still visits the table for every page not marked
all-visible in the visibility map. A large `Heap Fetches` count means
autovacuum hasn't caught up; `VACUUM orders;` restores the cheap path.

## Finding which query to look at

If you don't yet know which query is slow, `pg_stat_statements` ranks
every normalised query by total time — see
[pg_stat_statements](/glossary/pg-stat-statements) for the setup and the
query that lists the top offenders. To catch the plan of a slow query in
production, the `auto_explain` module logs it whenever a query exceeds
`auto_explain.log_min_duration`.

## Read the plan before and after

A plan in a terminal is hard to scan, and once pasted into Slack it
loses the indentation that holds it together. Paste it into Comma's free
[Postgres EXPLAIN visualizer](/tools/postgres-explain-viewer): it builds
the node tree with each node's self time, ranks where the time goes, and
flags seq scans that discard most of what they read, 10× row
misestimates, sorts that spilled to disk and heap fetches — all in your
browser, nothing uploaded.

When the index needs someone else's approval — the table owner, the DBA,
whoever reviews migrations — publish the plan as a link they can comment
on, node by node. Publish it again after the fix and the report keeps
both as revisions, so the before and after sit side by side.

**[Open the EXPLAIN visualizer →](/tools/postgres-explain-viewer)**

### Related

- [What EXPLAIN ANALYZE shows](/glossary/explain-analyze) — cost, rows, loops and self time
- [pg_stat_statements](/glossary/pg-stat-statements) — finding the queries worth explaining
- [Postgres EXPLAIN visualizer](/tools/postgres-explain-viewer) — paste a plan, get the report
- [Flame graph](/glossary/flamegraph) — the other way to see where time goes
- [Troubleshooting](/fix) — every fix page