# How to find missing indexes in PostgreSQL

> **Quick answer:** Look in `pg_stat_user_tables` for large tables (high
> `n_live_tup`) where `seq_scan` **and** `seq_tup_read` are high relative to
> `idx_scan`.  They are being read end-to-end because a filtered or joined column
> has no index.  A high `seq_scan` count alone is not the signal; a small table or
> a "read most rows" query is *supposed* to scan.  Confirm with `EXPLAIN` (a
> `Seq Scan` returning few rows is the tell) and add the index.  Separately,
> treat indexes with `idx_scan = 0` as drop *candidates* to review, never as an
> automatic drop list.

## The signal: sequential scans on a big table

PostgreSQL tracks how each table is accessed.  A table that is constantly scanned
in full is usually missing an index:

```sql
SELECT
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    n_live_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;
```

| Pattern | What it means |
|---|---|
| Large `n_live_tup`, high `seq_scan`, low/zero `idx_scan` | The table is read end-to-end, a filtered/joined column likely needs an index |
| High `seq_tup_read` per `seq_scan` | Each scan wades through many rows, expensive at scale |
| Small table, high `seq_scan` | Fine, scanning a tiny table is cheaper than an index |

A sequential scan is not automatically bad (small tables and "read most rows"
queries should scan).  It is a problem when a **big** table is scanned to return a
**few** rows.

## Confirm it with EXPLAIN

`pg_stat_user_tables` points you at a table; `EXPLAIN` proves the query:

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;
```

A `Seq Scan on orders` that returns one row out of millions is a missing index on
`customer_id`. See [reading a plan](https://www.dbgorilla.com/learn/postgres/how-to-read-a-postgres-explain-analyze-plan/)
for the details.  Remember: **foreign-key columns are not indexed automatically**.  They are the most common miss.

## Test the index before you build it (hypopg)

Building an index on a big table is expensive, so validate first.  Install once
with `CREATE EXTENSION hypopg`, then create a **hypothetical** index visible
only to your current session:

```sql
SELECT hypopg_create_index('CREATE INDEX ON orders (customer_id)');
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;  -- does the plan switch to it?
```

If the plan does not change, the real index would not have helped either, you just
saved yourself a pointless build.  Hypothetical indexes vanish on disconnect, and
they are session-local, behind a transaction- or statement-mode pooler a
follow-up query may land on a different backend that never saw them.  When you do
create for real, use
[`CREATE INDEX CONCURRENTLY`](https://www.dbgorilla.com/learn/postgres/how-to-add-a-postgres-index-without-downtime/)
to avoid blocking writes.

## Do not just add, remove the dead weight

Unused indexes are pure cost: they slow every write and take disk, with no read
benefit.

```sql
SELECT s.relname, s.indexrelname, s.idx_scan,
       i.indisvalid, i.indisreplident, i.indisexclusion
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND i.indisvalid            -- exclude failed CREATE INDEX CONCURRENTLY leftovers
  AND NOT i.indisprimary
  AND NOT i.indisunique
  AND NOT i.indisexclusion
  AND NOT i.indisreplident
ORDER BY s.relname;
```

`idx_scan = 0` means the index has not been scanned since the last stats reset, a candidate, not a verdict.  Rule out indexes backing a primary key, unique, or
exclusion constraint; rare or periodic jobs that have not fired in this window;
and replica-only usage (each server tracks its own stats, so a hot standby index
shows `0` on the primary); invalid indexes left behind by a failed
`CREATE INDEX CONCURRENTLY` (`indisvalid = false`), which always read `0` because
the planner will not use them; and any index serving as a table's replica identity
(`indisreplident`).  After that, drop the rest with `DROP INDEX CONCURRENTLY`.

The replica-identity case is the one that bites hardest, because the damage is
deferred. `DROP INDEX` on a `REPLICA IDENTITY USING INDEX` index **succeeds with
no error or warning**, the table simply reverts to behaving as
`REPLICA IDENTITY NOTHING`. But a table in that state that belongs to a
publication replicating `UPDATE` or `DELETE` *cannot support those operations*:
PostgreSQL raises an error on the publisher.  So the drop looks clean during the
maintenance window, and writes to that table start failing later, with nothing
obviously connecting the two.  Check `relreplident` on the table before dropping
anything the query returns.

## Why does aI-generated code miss indexes?

Your AI coding agent (Claude Code, Cursor) writes the query, but it has no idea
which columns are indexed.  That is not in the schema it reads as code, and it
certainly cannot see `pg_stat_user_tables`. So it writes the natural predicate and
assumes it is fast.  Whether a column *needs* an index is a function of table size,
access pattern, and existing indexes, all runtime facts the model never sees.

## How do I keep on top of indexing?

- Review `seq_scan` vs `idx_scan` on your biggest tables periodically.
- Add an index for every foreign key you filter or join on.
- Validate candidate indexes with `hypopg` before building.
- Review `idx_scan = 0` indexes as drop *candidates*, after ruling out
  constraint-backing indexes, rare/periodic queries, replica-only usage, invalid
  indexes (`indisvalid = false`), and replica-identity indexes
  (`indisreplident`).

## How DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor)
the real access statistics, which tables are being sequentially scanned, which
foreign keys lack indexes, which indexes look unused, so it can recommend the
index a query actually needs and flag drop *candidates*. It surfaces and explains;
it does not touch production. [Get started free →](https://app.dbgorilla.com/signup)

## FAQ

**How do I know if a table is missing an index?**
Weigh it: a high `seq_scan` alone is not reliable (small tables and most-rows
queries should scan).  Look for a large table with high `seq_scan` *and*
`seq_tup_read` relative to `idx_scan`, then confirm with `EXPLAIN` showing a
`Seq Scan` that returns few rows.

**How do I find unused indexes?**
Query `pg_stat_user_indexes` for `idx_scan = 0`, joined to `pg_index` so you can
filter properly.  Treat them as candidates, rule out constraint-backing indexes,
rare/periodic queries, replica-only usage, invalid indexes left by a failed
`CREATE INDEX CONCURRENTLY` (`indisvalid = false`), and any index serving as the
table's replica identity (`indisreplident`), that last drop succeeds without an
error but leaves the table behaving as `REPLICA IDENTITY NOTHING`, so `UPDATE`
and `DELETE` on it then error on the publisher if it is published for those
actions.  Then `DROP INDEX CONCURRENTLY` what is left.

**Can I test an index before creating it?**
Yes.`CREATE EXTENSION hypopg`, then create a hypothetical, session-only index
so `EXPLAIN` shows whether the planner would use it.  They vanish on disconnect
and will not survive a transaction/statement-mode pooler hop.

**Does an index on a foreign key get created automatically?**
No.  PostgreSQL indexes primary keys and unique constraints, not foreign-key
columns, you add those yourself.

## Related

- [How to add a PostgreSQL index without downtime](https://www.dbgorilla.com/learn/postgres/how-to-add-a-postgres-index-without-downtime/)
- [Which PostgreSQL index type should I use?](https://www.dbgorilla.com/learn/postgres/which-postgresql-index-type-should-i-use/)
- [How to read a PostgreSQL EXPLAIN ANALYZE plan](https://www.dbgorilla.com/learn/postgres/how-to-read-a-postgres-explain-analyze-plan/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)