How to find missing indexes in PostgreSQL
Quick answer: Look in
pg_stat_user_tablesfor large tables (highn_live_tup) whereseq_scanandseq_tup_readare high relative toidx_scan. They are being read end-to-end because a filtered or joined column has no index. A highseq_scancount alone is not the signal; a small table or a “read most rows” query is supposed to scan. Confirm withEXPLAIN(aSeq Scanreturning few rows is the tell) and add the index. Separately, treat indexes withidx_scan = 0as drop candidates to review, never as an automatic drop list.
The signal: sequential scans on a big table
Section titled “The signal: sequential scans on a big table”PostgreSQL tracks how each table is accessed. A table scanned in full may be missing an index:
SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tupFROM pg_stat_user_tablesORDER 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 reads many rows, which is expensive at scale |
Small table, high seq_scan | This is 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 becomes a problem when a big table is scanned to return a few rows.
Confirm it with EXPLAIN
Section titled “Confirm it with EXPLAIN”pg_stat_user_tables points you to a table; EXPLAIN shows the query plan:
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 details. Foreign-key columns are not indexed automatically, and they are the
most common miss.
Test the index before you build it (hypopg)
Section titled “Test the index before you build it (hypopg)”Building an index on a big table is expensive, so validate it first. Install once
with CREATE EXTENSION hypopg, then create a hypothetical index that applies
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, and you
avoid an unnecessary build. Hypothetical indexes vanish on disconnect. They are
also session-local, so 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 the index for real, use
CREATE INDEX CONCURRENTLY
to avoid blocking writes.
Do not just add, remove the dead weight
Section titled “Do not just add, remove the dead weight”Unused indexes slow writes and take disk space without helping reads.
SELECT s.relname, s.indexrelname, s.idx_scan, i.indisvalid, i.indisreplident, i.indisexclusionFROM pg_stat_user_indexes sJOIN pg_index i ON i.indexrelid = s.indexrelidWHERE 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.indisreplidentORDER BY s.relname;idx_scan = 0 means the index has not been scanned since the last stats reset.
That makes it 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; 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.
DROP INDEX on a REPLICA IDENTITY USING INDEX index succeeds with no error or
warning, and the table 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. The drop may look clean during the maintenance window, while writes
to that table start failing later with no obvious connection between the two.
Check relreplident on the table before dropping anything the query returns.
Why does AI-generated code miss indexes?
Section titled “Why does AI-generated code miss indexes?”Your AI coding agent writes the query, but it has no idea which columns are
indexed. That information is not in the schema it reads as code, and it cannot
see pg_stat_user_tables. It writes the natural predicate and assumes it is
fast. Whether a column needs an index depends on table size, access pattern,
and existing indexes, all runtime facts the model never sees.
How do I keep on top of indexing?
Section titled “How do I keep on top of indexing?”- Review
seq_scanvsidx_scanon your biggest tables periodically. - Add an index for every foreign key you filter or join on.
- Validate candidate indexes with
hypopgbefore building. - Review
idx_scan = 0indexes 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 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.
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.
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.
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.