How to Find Missing Indexes in PostgreSQL: 5 Proven Methods

If you have ever watched a query jump from 10 ms to 10 seconds as a table grew, there is a good chance you were looking at a missing index. Knowing how to find missing indexes in PostgreSQL is one of the highest-leverage skills for backend developers and DBAs, because an unindexed sequential scan is the single most common root cause of slow queries in production PostgreSQL systems.

PostgreSQL will not flag missing indexes for you — unlike SQL Server, it ships no official "missing index recommendations" feature. The good news: the built-in statistics views and planner tooling give you everything you need to diagnose the problem yourself. This article walks through a proven, repeatable workflow.

Before hunting for missing indexes, you need a mental model of why PostgreSQL skips them. Otherwise you will add indexes that the planner ignores.

The query planner and cost model in 60 seconds

PostgreSQL uses a cost-based optimizer. For every query, the planner estimates the cost of candidate plans — a sequential scan reading every heap page, an index scan doing random heap fetches, a bitmap heap scan mixing both — and picks the cheapest estimate. Its estimates are driven by statistics collected by ANALYZE: row counts (reltuples), value distributions, and correlation. If those statistics are stale, the planner can make bad choices, which is why ANALYZE is part of every tuning conversation.

Why a valid index can still be ignored

A btree index on the right column can still be skipped for legitimate reasons:

• Small tables. Reading a 200-row table sequentially is cheaper than the overhead of an index scan. This is fine and expected.
• Low selectivity. If WHERE status = 'active' matches 80% of rows, a sequential scan wins.
• Wildcard or expression predicates. WHERE lower(email) = ... or LIKE '%term%' cannot use a plain btree index on email.
• Function wrapping. WHERE date(created_at) = '2024-06-01' defeats an index on created_at unless you use a range predicate or an expression index.
• Stale or absent statistics on a brand-new table or after a bulk load.

Seq Scan vs Index Scan: the same query, two very different plans

When adding an index is NOT the right fix

Not every slow query needs an index. Before you create one, rule out:

• Bad query structure — SELECT * pulling hundreds of columns, or functions applied to indexed columns in the WHERE clause.
• Stale statistics — run ANALYZE tablename; and re-check the plan first.
• Configuration issues — an undersized work_mem or shared_buffers can make plans that look index-unfriendly actually be memory-related.
• Lock contention or bloat — a bloated table inflates scan cost in ways an index will not fix.

The cumulative statistics in pg_stat_user_tables and pg_stat_user_indexes are your fastest PostgreSQL missing index detection signal. pg_stat_user_tables tracks seq_scan (sequential scans per table) and n_live_tup, while pg_stat_user_indexes tracks idx_scan per index. Compare them to find large tables being read without index support.

The missing index investigation workflow: from statistics to verified fix

Finding tables with high seq_scan and low idx_scan counts

This query surfaces big tables where sequential scans dominate:

SELECT
  relname            AS table_name,
  seq_scan,
  idx_scan,
  CASE WHEN seq_scan + idx_scan > 0
       THEN round(seq_scan::numeric / (seq_scan + idx_scan), 2)
       ELSE NULL
  END                AS seq_scan_ratio,
  n_live_tup
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY seq_scan DESC
LIMIT 20;

Interpretation guide:

• High seq_scan + high n_live_tup — prime suspect. The table is big and being read the hard way.
• seq_scan_ratio near 1.0 — almost no index usage at all.
• High seq_scan but tiny n_live_tup — normal; small tables are scanned sequentially by design.

Next, check what indexes already exist on the suspect tables using the pg_indexes view:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

Calculating the scan ratio per table

The seq_scan_ratio column above is a quick health metric. Treat it as a triage signal, not proof: cumulative counters include full-table scans from analytics jobs, migrations, and maintenance queries. A ratio is only meaningful alongside n_live_tup and the actual queries hitting the table.

Resetting statistics with pg_stat_reset() for clean baselines

Because counters accumulate since the last statistics reset (often server start), a long-lived table's numbers may reflect months of history. For a clean measurement window:

SELECT pg_stat_reset();

Then run your normal workload for an hour or a day and re-run the triage query. Use this deliberately — it wipes cluster-wide statistics. pg_stat_reset_single_table_counters('tablename') (PostgreSQL 12+) resets one table if you prefer surgical scope.

Once you know which tables look suspicious, find which queries are hurting them. pg_stat_statements is the standard extension for that.

Enabling the pg_stat_statements extension

Add the extension to shared_preload_libraries in postgresql.conf, restart PostgreSQL, then create the extension:

CREATE EXTENSION pg_stat_statements;

Isolating queries with high total_exec_time and mean_exec_time

SELECT
  query,
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 1)  AS mean_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Prioritize queries with high total_exec_time (aggregate load) and high mean_exec_time (per-call pain). Note: the columns total_exec_time and mean_exec_time exist in PostgreSQL 13+; on older versions they are named total_time and mean_time.

Cross-referencing query predicates against existing indexes

For each slow query, extract the columns used in WHERE clauses, JOIN keys, and ORDER BY, then compare them against the indexdef output from pg_indexes. Any filtering or joining column with no leading position in an existing index is a candidate. This cross-reference step is what turns "that table looks scan-heavy" into "this specific predicate is unindexed."

Statistics suggest; the execution plan proves. Run the suspect query with actual execution data:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 12345
  AND status = 'pending';

Look for three things in the output:

1. Seq Scan on orders — the planner is reading the whole table despite a filter.
2. Rows Removed by Filter: ... — a huge number here means most rows read were discarded, the classic signature of an EXPLAIN ANALYZE missing index case.
3. Buffers: shared read=... — high read counts mean real disk I/O, not just cheap memory scans.

A healthy plan instead shows an Index Scan or Bitmap Heap Scan with Rows Removed by Filter close to zero. For a deeper walkthrough of plan reading.

The first three methods catch problems you know to look for. The fourth catches what you cannot watch in real time — intermittent queries that only go slow under production data volumes.

Configuring auto_explain with log_nested_statements

auto_explain logs execution plans automatically for queries exceeding a duration threshold:

# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on

log_nested_statements = on is essential for ORMs and stored procedures — it captures plans for the inner statements, not just the outer call.

Setting log_min_duration_statement thresholds

Independently of auto_explain, set log_min_duration_statement = '500ms' (tune the value to your SLO) to log the text of every slow query. That gives you the what, while auto_explain gives you the why.

Parsing logs at scale with pgBadger

On busy servers, raw logs are unusable by eye. pgBadger parses PostgreSQL logs into HTML reports with top-slowest queries and aggregated plan data, turning weeks of log noise into a ranked worklist. Run it on a schedule and you have continuous, hands-off PostgreSQL query performance tuning intelligence.

Diagnosis done — now build the fix. Follow PostgreSQL CREATE INDEX best practices so the index actually gets used:

• Order composite index columns by selectivity and usage. Put equality-filtered columns first, range/sort columns last. An index on (customer_id, status) serves WHERE customer_id = ? AND status = ? and WHERE customer_id = ?, but not a query filtering only on status.
• Match join keys, not just filters. Unindexed foreign-key columns used in joins are frequently missed.
• Use a partial index for skewed predicates. CREATE INDEX ... ON orders (created_at) WHERE status = 'pending'; is tiny and fast when only one status value is hot.
• Use INCLUDE for covering indexes. An index on (customer_id) INCLUDE (status) can satisfy queries via index-only scans, avoiding heap fetches — provided the pages are in the buffer cache or visibility map.
• Use expression indexes for function predicates. If you query lower(email), index lower(email), not email.
• Respect write-amplification. Every index slows INSERT, UPDATE, and DELETE, consumes cache, and can bloat over time. Create indexes CONCURRENTLY in production to avoid locking writes, and drop indexes with near-zero idx_scan to make room.
• Validate before and after. Capture the EXPLAIN (ANALYZE, BUFFERS) plan before creating the index, create it, run ANALYZE on the table, then re-run the identical plan comparison. Expect the Seq Scan node to become an Index Scan and buffer reads to drop sharply.