Find Missing Indexes in PostgreSQL with SQL

calendar Apr 4, 2026 / · 3 min read · postgresql performance optimization indexing database  ·
Share on: linkedin copy

How to Find Missing Indexes in PostgreSQL

A missing index means PostgreSQL reads every row in a table to find what it needs. This is called a sequential scan. For small tables, that is fine. For large tables, it is slow and wastes CPU and I/O.

PostgreSQL tracks sequential scans in the pg_stat_all_tables view. You can query this view to find tables that are being scanned too often without an index. Once you find those tables, you can add the right indexes and speed up your queries.

Sample Code

1SELECT
2  relname                                               AS TableName,
3  TO_CHAR(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
4  TO_CHAR(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
5  TO_CHAR(n_live_tup, '999,999,999,999')                AS TableRows,
6  PG_SIZE_PRETTY(PG_RELATION_SIZE(relname::REGCLASS))   AS TableSize
7FROM pg_stat_all_tables
8WHERE schemaname = 'public'
9ORDER BY TotalSeqScan DESC;

Notes: Works on all supported PostgreSQL versions. Replace 'public' with your schema name if needed. Stats accumulate since the last pg_stat_reset() call or server restart.

A simpler version focused on row counts:

 1SELECT
 2    relname,
 3    idx_scan,
 4    seq_scan,
 5    n_live_tup
 6FROM
 7    pg_stat_user_tables
 8WHERE
 9    seq_scan > 0
10ORDER BY
11    n_live_tup DESC;

Code Breakdown

  • pg_stat_all_tables — A built-in view that tracks table access statistics, including how many times each table was scanned sequentially or via an index.
  • seq_scan — The total number of sequential scans on the table. A high number means PostgreSQL is reading the whole table often.
  • idx_scan — The total number of index scans. A high number here means indexes are being used well.
  • n_live_tup — The estimated number of live rows in the table. Large tables with high seq_scan counts are the biggest problem.
  • PG_RELATION_SIZE — Returns the size of the table in bytes. PG_SIZE_PRETTY formats it as KB, MB, or GB.
  • TO_CHAR — Formats numbers with commas for easier reading.
  • ORDER BY TotalSeqScan DESC — Puts the worst offenders at the top of the results.

Key Points

  • A table with a high seq_scan and a low idx_scan is a strong candidate for a new index.
  • A large table (many rows or large size) with many sequential scans will hurt performance the most.
  • If seq_scan is high but n_live_tup is very low (say, under 1,000 rows), an index may not help much. PostgreSQL often chooses sequential scans for small tables anyway.
  • Compare seq_scan to idx_scan. If sequential scans far outnumber index scans on a large table, an index is likely missing.

Insights and Explanations

When you run this query, focus on tables that meet all three of these conditions:

  1. High seq_scan count
  2. Low idx_scan count
  3. Large number of rows or large table size

Once you identify a table, look at the queries that run against it. Check the WHERE, JOIN, and ORDER BY columns. Those are the best candidates for new indexes.

You can use EXPLAIN ANALYZE to confirm that a query is doing a sequential scan and to test whether a new index helps:

1EXPLAIN ANALYZE SELECT * FROM your_table WHERE your_column = 'value';

Keep in mind that pg_stat_all_tables resets after a server restart or when SELECT pg_stat_reset() is run. If your server was recently restarted, the numbers may not reflect normal traffic patterns. Give it time to accumulate data before drawing conclusions.

Additional Considerations

  • Permissions: Any user with access to pg_stat_all_tables can run this query. Superusers see all tables; regular users see only tables in their schema.
  • Too many indexes: Adding an index speeds up reads but slows down writes. Only add an index if the query performance gain is worth the write overhead.
  • Partial indexes: If a table is large but most queries filter by a specific value, a partial index may be more efficient than a full index.

References

Posts in this series