# Why Postgres isn't using your index: seven causes, with EXPLAIN output you can reproduce

You created the index and EXPLAIN still shows a sequential scan. Seven reasons PostgreSQL ignores an index (functions, casts, wildcards, selectivity, stale statistics, column order, tiny tables), each with a reproduction and a fix.

**You ran `CREATE INDEX`, you ran the query, and `EXPLAIN` still says `Seq Scan`. Before you blame the planner, know that in most cases it is right about what you asked for and wrong only about what you meant. This post covers the seven reasons we see most often for PostgreSQL ignoring an index: a function on the column, a type mismatch, a leading wildcard, a filter that matches most of the table, stale statistics, a multicolumn index in the wrong order, and a table that is simply too small. Every case comes with a reproduction on PostgreSQL 17.11, the exact plan it produced, and the fix. Paste the setup into `psql` and, on default settings, you will get the same plans.**

## How do I tell whether Postgres is using my index?

Put `EXPLAIN` in front of the query. A plan node called `Index Scan`, `Index Only Scan` or `Bitmap Index Scan` means an index is in use. `Seq Scan` (or `Parallel Seq Scan`) means the planner reads the whole table. `EXPLAIN` only plans the query; `EXPLAIN ANALYZE` also runs it and shows actual row counts, which you need for the stale statistics case below.

Two habits make plans easier to read and compare:

- `EXPLAIN (COSTS OFF)` removes the cost numbers, which change with table size and settings.
- `EXPLAIN (ANALYZE, BUFFERS)` shows how many pages a query really touched, not just how many the planner expected.

## The setup

All seven cases use one table of 200,000 users. The data is generated, so there is nothing to download:

```sql
SET TimeZone = 'UTC';
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE users (
  id         bigint PRIMARY KEY,
  email      text NOT NULL,
  status     text NOT NULL,
  org_id     int  NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO users
SELECT g,
       'User' || g || '@Example.com',
       CASE WHEN g % 100 = 0 THEN 'banned' ELSE 'active' END,
       (g % 500) + 1,
       timestamptz '2026-01-01' + (g || ' seconds')::interval
FROM generate_series(1, 200000) g;

CREATE INDEX users_email_idx ON users (email);
ANALYZE users;
```

As a baseline, an exact match on the indexed column uses the index:

```sql
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email = 'User42@Example.com';
```

```text
 Index Scan using users_email_idx on users
   Index Cond: (email = 'User42@Example.com'::text)
```

Everything below is a way of changing that query slightly and losing the index.

## 1. You wrapped the column in a function

An index on `email` stores the values of `email`. A condition on `lower(email)` is a question about different values, so the index cannot answer it:

```sql
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE lower(email) = 'user42@example.com';
```

```text
 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: (lower(email) = 'user42@example.com'::text)
```

The plan is parallel, but it is still a sequential scan. The fix is an **expression index**, an index on the same expression the query uses:

```sql
CREATE INDEX users_lower_email_idx ON users (lower(email));
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE lower(email) = 'user42@example.com';
```

```text
 Bitmap Heap Scan on users
   Recheck Cond: (lower(email) = 'user42@example.com'::text)
   ->  Bitmap Index Scan on users_lower_email_idx
         Index Cond: (lower(email) = 'user42@example.com'::text)
```

The expression in the query has to match the expression in the index. `lower(email)` and `lower(trim(email))` are different expressions. The same rule covers `coalesce(col, 0)`, arithmetic such as `price * 100`, and date functions. Date functions have one more catch: on a `timestamptz` column, `date_trunc('day', created_at)` and `created_at::date` depend on the session time zone, so Postgres will not let you index them directly (`functions in index expression must be marked IMMUTABLE`). Index an expression that fixes the zone instead, such as `((created_at AT TIME ZONE 'UTC')::date)`, and use the same expression in the query.

When you can, rewrite the condition instead. `created_at >= '2026-01-02' AND created_at < '2026-01-03'` can use a plain index on `created_at`, while `created_at::date = '2026-01-02'` cannot.

## 2. The types do not match

When the two sides of a comparison have different types, Postgres has to convert one of them. If it converts the column, an ordinary index on the column no longer applies. Here `id` is a `bigint`, and the literal `42.0` is a `numeric`:

```sql
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id = 42;
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id = 42.0;
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id::text = '42';
```

```text
 Index Scan using users_pkey on users
   Index Cond: (id = 42)

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((id)::numeric = 42.0)

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((id)::text = '42'::text)
```

Look at the `Filter` line: `(id)::numeric` is the column being cast. That cast is the giveaway, and it is the thing to hunt for whenever a plan shows `Seq Scan` on an indexed column.

The fix is to make the literal or the parameter match the column. In application code the usual source is a driver that sends a number as `numeric`, or a value that arrives as a string. Cast the parameter in the query (`WHERE id = $1::bigint`) or fix the type at the source. If the column itself has the wrong type, such as an identifier stored as `text` and compared to numbers, changing the column type is the real fix.

## 3. The pattern starts with a wildcard

A B-tree index is sorted, so it can find everything that starts with `User4` quickly. It cannot find everything that ends with `1234@Example.com`, because the values are not sorted by their endings:

```sql
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email LIKE '%1234@Example.com';
```

```text
 Seq Scan on users
   Filter: (email ~~ '%1234@Example.com'::text)
```

The `pg_trgm` extension indexes three-character fragments, which lets Postgres use an index for patterns with a leading wildcard, for `ILIKE`, and for `%` similarity searches. `pg_trgm` is among the extensions available on Relyt One. The index is a GIN index with the `gin_trgm_ops` operator class:

```sql
CREATE INDEX users_email_trgm_idx ON users USING gin (email gin_trgm_ops);
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email LIKE '%1234@Example.com';
```

```text
 Bitmap Heap Scan on users
   Recheck Cond: (email ~~ '%1234@Example.com'::text)
   ->  Bitmap Index Scan on users_email_trgm_idx
         Index Cond: (email ~~ '%1234@Example.com'::text)
```

Trigram indexes are larger than B-tree indexes and slower to update, and they help most when the search string is long enough to contain at least one full trigram. A pattern with fewer than three characters gives them little to work with. If your real need is searching words in text, use full-text search (`tsvector`) rather than `LIKE`.

## 4. The filter matches most of the table

An index pays off when it lets the database skip most of the table. If a condition matches 99% of the rows, reading the whole table in order is cheaper than jumping through the index 198,000 times, and the planner knows it:

```sql
CREATE INDEX users_status_idx ON users (status);
ANALYZE users;

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE status = 'active';
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE status = 'banned';
```

```text
 Seq Scan on users
   Filter: (status = 'active'::text)

 Index Scan using users_status_idx on users
   Index Cond: (status = 'banned'::text)
```

This is the index working as designed: the same index is used for the 1% of rows and skipped for the 99%. Nothing needs fixing in the planner. If the query for the common value matters, the question is whether it should return that many rows at all (add a `LIMIT`, an extra filter or an aggregate).

The rare value is where you can improve things. A **partial index** covers only the rows you look up, so it is small and cheap to maintain:

```sql
DROP INDEX users_status_idx;
CREATE INDEX users_banned_idx ON users (id) WHERE status = 'banned';
EXPLAIN (COSTS OFF) SELECT id FROM users WHERE status = 'banned';
```

```text
 Index Only Scan using users_banned_idx on users
```

The query's `WHERE` clause has to imply the index's `WHERE` clause. A query that asks for `status = 'banned'` can use this index. A query for another status cannot.

## 5. The statistics are stale

The planner does not look at your data when it plans a query. It looks at statistics collected by `ANALYZE` and estimates from those. When the table changes a lot after the last `ANALYZE`, the estimates stop matching reality and the plan can be wrong in either direction, including picking an index when the index is a bad idea.

Autovacuum normally refreshes statistics once about 10% of a table has changed (`autovacuum_analyze_scale_factor = 0.1`, plus a base of 50 rows). To show the effect deterministically we turn it off for one table, analyze it when it holds five rows, and then bulk-load 300,000:

```sql
CREATE TABLE events (id bigint, kind text, created_at timestamptz);
ALTER TABLE events SET (autovacuum_enabled = false);
CREATE INDEX events_kind_idx ON events (kind);

INSERT INTO events SELECT g, 'click', now() FROM generate_series(1, 5) g;
ANALYZE events;
INSERT INTO events SELECT g, 'view', now() FROM generate_series(1, 300000) g;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE kind = 'view';
```

```text
 Aggregate  (cost=4.31..4.32 rows=1 width=8) (actual rows=1 loops=1)
   ->  Index Only Scan using events_kind_idx on events  (cost=0.29..4.30 rows=1 width=0) (actual rows=300000 loops=1)
         Index Cond: (kind = 'view'::text)
         Heap Fetches: 300000
```

The planner expected one row (`rows=1`) and got 300,000 (`actual rows=300000`). It chose an index for a lookup that reads the whole table, and `Heap Fetches: 300000` means every row needed a visit to the table as well. After `ANALYZE`, the same query changes plan:

```sql
ANALYZE events;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE kind = 'view';
```

```text
 Finalize Aggregate  (cost=5558.22..5558.23 rows=1 width=8) (actual rows=1 loops=1)
   ->  Gather  (cost=5558.11..5558.22 rows=1 width=8) (actual rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate  (cost=4558.10..4558.11 rows=1 width=8) (actual rows=1 loops=2)
               ->  Parallel Seq Scan on events  (cost=0.00..4116.92 rows=176474 width=0) (actual rows=150000 loops=2)
                     Filter: (kind = 'view'::text)
                     Rows Removed by Filter: 2
```

The exact cost numbers vary a little from run to run, so do not compare them digit by digit. The lesson is about how to read a plan. **Compare `rows=` with `actual rows=` on every node.** When they differ by orders of magnitude, the problem is the estimate, not the index. Run `ANALYZE` after a bulk load or a large backfill, and check `last_autoanalyze` in `pg_stat_user_tables` if you suspect autovacuum is falling behind. For a column whose distribution is skewed, raising the statistics target (`ALTER TABLE … ALTER COLUMN … SET STATISTICS 500`) gives the planner a finer picture than the default of 100.

## 6. The multicolumn index is in the wrong order

A multicolumn B-tree index is sorted by its first column, then by the second within each value of the first, and so on. A query that filters on the leading column can use it well. A query that skips the leading column cannot:

```sql
CREATE INDEX users_org_created_idx ON users (org_id, created_at);
ANALYZE users;

-- leading column constrained: the index is used
EXPLAIN (COSTS OFF)
SELECT * FROM users WHERE org_id = 7 AND created_at >= '2026-01-02';

-- leading column missing: it is not
EXPLAIN (COSTS OFF)
SELECT * FROM users
WHERE created_at >= '2026-01-02 00:00:00' AND created_at < '2026-01-02 00:05:00';
```

```text
 Bitmap Heap Scan on users
   Recheck Cond: ((org_id = 7) AND (created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone))
   ->  Bitmap Index Scan on users_org_created_idx

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone) AND (created_at < ...
```

If you need to filter on `created_at` alone, you need an index that leads with it. Putting the column with equality conditions first, and the range column last, is the usual rule for indexes that serve both kinds of query:

```sql
CREATE INDEX users_created_org_idx ON users (created_at, org_id);
EXPLAIN (COSTS OFF)
SELECT * FROM users
WHERE created_at >= '2026-01-02 00:00:00' AND created_at < '2026-01-02 00:05:00';
```

```text
 Index Scan using users_created_org_idx on users
   Index Cond: ((created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone) AND (created_at < ...
```

**This one depends on your version.** PostgreSQL 18 added skip scans for B-tree indexes, which let a query that omits the leading column use the index in some cases. We ran the same table and the same `(org_id, created_at)` index on PostgreSQL 18.6, and the range query on `created_at` alone used the `(org_id, created_at)` index there. On PostgreSQL 17, which Relyt One runs, it does not. A skip scan is only worth it when the planner expects to skip few distinct values of the leading column or the later condition is selective, as it was here, so an index designed for the query is still the more reliable choice.

Every index also costs something on every write. Before you add one, check that an existing index does not already cover the query.

## 7. The table is small, or the index is not worth it

A twenty-row table fits in one page. Reading that page is cheaper than reading an index page and then a table page, so the planner scans the table:

```sql
CREATE TABLE plans (id int PRIMARY KEY, name text);
INSERT INTO plans SELECT g, 'plan' || g FROM generate_series(1, 20) g;
ANALYZE plans;

EXPLAIN (COSTS OFF) SELECT * FROM plans WHERE id = 7;
```

```text
 Seq Scan on plans
   Filter: (id = 7)
```

That is correct behavior, and it is a trap when you test on a small development database and expect the plan you will get in production, or the other way around. Test index decisions on data that looks like production, in size and in distribution.

There is also a diagnostic switch for the moments when you cannot tell whether the index is *possible* or just *not chosen*. Turn off sequential scans for one transaction and look at the plan:

```sql
BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT * FROM plans WHERE id = 7;
ROLLBACK;
```

```text
 Index Scan using plans_pkey on plans
   Index Cond: (id = 7)
```

If the index appears, it was usable and the planner judged the table scan cheaper, so look at statistics, selectivity and table size. If it still does not appear, the index cannot serve the query as written, so go back to cases 1, 2, 3 and 6. Use this switch for diagnosis only. Do not leave it on, and do not put it in application code.

## Which indexes does my database never use?

The opposite problem is indexes that nothing uses. They slow down every insert and update and take space. `pg_stat_user_indexes` counts index scans since the statistics were last reset:

```sql
SELECT count(*) FROM users WHERE email = 'User42@Example.com';
SELECT count(*) FROM users WHERE email = 'User43@Example.com';

SELECT pg_sleep(1.5);  -- statistics are reported with a short delay

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'users'
ORDER BY idx_scan DESC, indexrelname;
```

After running the exact-match query on `email` twice on the test table, and waiting a moment for the statistics to be reported, the output was:

```text
     indexrelname      | idx_scan
-----------------------+----------
 users_email_idx       |        2
 users_banned_idx      |        0
 users_created_org_idx |        0
 users_email_trgm_idx  |        0
 users_lower_email_idx |        0
 users_org_created_idx |        0
 users_pkey            |        0
```

Two cautions. `EXPLAIN` alone does not count as a scan, so only real executions show up. And an index with zero scans is a candidate for removal, not proof: it may exist to enforce a unique constraint, or serve a monthly report that has not run since the statistics reset.

## A checklist for the next slow query

1. Run `EXPLAIN (ANALYZE, BUFFERS)` and find the node that dominates.
2. In its `Filter` line, look for a function or a cast applied to the column (cases 1 and 2).
3. If the pattern starts with `%`, consider a trigram index (case 3).
4. Compare `rows=` with `actual rows=`. A big gap means `ANALYZE` (case 5).
5. Check how many rows the condition matches. If it is most of the table, the scan is correct (case 4).
6. For a multicolumn index, check whether the query constrains the leading column (case 6).
7. Test on realistic data, and use `SET LOCAL enable_seqscan = off` to tell "unusable" from "not chosen" (case 7).

## Test index changes without touching production

On a big table, building an index is the expensive part: a plain `CREATE INDEX` blocks writes while it runs, and `CREATE INDEX CONCURRENTLY` avoids that at the cost of a slower build. On Relyt One every branch has its own compute and copy-on-write storage, so you can create a branch of your database, build the index there, run `EXPLAIN (ANALYZE)` against your real data, and throw the branch away if the plan does not improve. The main branch never builds the index and never sees the experiment. Compare plans and row estimates between the branch and production, not durations: the branch's compute may be sized differently and starts with a cold cache.

[Start for free](/auth/signin?ref=blog-postgres-not-using-index)