How to find missing indexes in PostgreSQL

Q: How do I know if a table is missing an index?

Check pg_stat_user_tables, but weigh it, a high seq_scan count alone is not a reliable signal, since sequential scans are the right plan for small tables or queries that read most rows. Look for a large table (high n_live_tup) with high seq_scan and high seq_tup_read relative to idx_scan: it is repeatedly read end-to-end, which usually means a column you filter or join on has no index. Confirm by running EXPLAIN on the query and looking for a Seq Scan that returns few rows.

Q: How do I find unused indexes in PostgreSQL?

Query pg_stat_user_indexes for indexes with idx_scan = 0. They have not been scanned since the last stats reset. Treat these as candidates for review, not automatic drops: idx_scan resets on pg_stat_reset(), the counter only reflects queries run in this stats window (a rare or periodic job may not have fired yet), an index backing a primary key, unique, or exclusion constraint still enforces integrity, and an index used only to serve reads on a replica shows idx_scan = 0 on the primary because each server tracks its own stats. Join to pg_index so you can also exclude 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 the table replica identity (indisreplident). That last one is the dangerous case: the DROP itself succeeds with no error, but PostgreSQL documents that once a REPLICA IDENTITY USING INDEX index is dropped the table behaves as REPLICA IDENTITY NOTHING, and a table in that state that is published for UPDATE or DELETE cannot support those operations, they error on the publisher. So the drop looks clean and writes to that table start failing afterwards. Rule out those cases before you DROP INDEX CONCURRENTLY.

Q: Can I test an index before creating it?

Yes. Install it once with CREATE EXTENSION hypopg, then hypopg creates hypothetical indexes that exist only in your current session, so you can run EXPLAIN (not EXPLAIN ANALYZE) and see whether the planner would use one, without building the real index. If the plan doesn't change, the index wouldn't have helped. The hypothetical indexes vanish on disconnect, and because they are session-local they won't work reliably behind a transaction- or statement-mode connection pooler, where a follow-up query may land on a different backend that never saw them.

Q: Does an index on a foreign key get created automatically?

No. PostgreSQL indexes primary keys and unique constraints automatically, but not foreign-key columns. A join or filter on a foreign key stays a sequential scan until you add the index yourself.

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:

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:

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 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:

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 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.

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 →

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.

The signal: sequential scans on a big table​

Confirm it with EXPLAIN​

Test the index before you build it (hypopg)​

Do not just add, remove the dead weight​

Why does aI-generated code miss indexes?​

How do I keep on top of indexing?​

How DBGorilla helps​

FAQ​

Related​