Skip to main content

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're being read end-to-end because a filtered or joined column has no index. A high seq_scan count alone isn't 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's 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;
PatternWhat it means
Large n_live_tup, high seq_scan, low/zero idx_scanThe table is read end-to-end — a filtered/joined column likely needs an index
High seq_tup_read per seq_scanEach scan wades through many rows — expensive at scale
Small table, high seq_scanFine — 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's 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're 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 doesn't change, the real index wouldn't have helped either — you just saved yourself a pointless build. Hypothetical indexes vanish on disconnect, and they're 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.

Don't just add — remove the dead weight​

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

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

idx_scan = 0 means the index hasn't 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 haven't fired in this window; and replica-only usage (each server tracks its own stats, so a hot standby index shows 0 on the primary). After that, drop the rest with DROP INDEX CONCURRENTLY.

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's not in the schema it reads as code, and it certainly can't see pg_stat_user_tables. So it writes the natural predicate and assumes it's 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, and replica-only usage.

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 isn't 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. Treat them as candidates — rule out constraint-backing indexes, rare/periodic queries, and replica-only usage — then DROP INDEX CONCURRENTLY what's 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 won't 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.