Postgres index audit with pg_stat: find slow and unused indexes

What a Postgres index audit is (and what it is not)

A Postgres index audit is a reality check based on what your database is actually doing in production. It answers practical questions: which queries are slow, which indexes get used, which indexes never get touched, and where the time goes.

The goal isn’t to collect “nice to have” indexes. It’s to speed up the busiest paths and reduce avoidable cost.

Indexes often look fine in dev because dev traffic is tidy: small tables, predictable queries, and a few happy paths. Production is messier. Tables grow, filters and sorts vary, edge cases show up, and many users hit the same endpoints at once. A query that feels “fast enough” on 10,000 rows can fall over at 10 million if it starts doing full table scans or large sorts.

A good audit helps you:

• Identify specific slow query patterns under real load
• Confirm which indexes are helping today
• Find indexes that cost storage and slow writes without paying you back
• Decide what to add or adjust, and how you’ll prove it helped

“Real query stats” means measured behavior from your live workload: how often a query runs, how long it takes, how many rows it returns, and whether it spikes. Guessing from schema alone is risky. A schema can suggest what might be indexed, but it can’t tell you which filters users actually use or which queries dominate database time.

It also helps to keep the tradeoffs clear. Indexes are not free:

• They speed up reads (SELECT) when they match your filters, joins, and sort order.
• They can slow down writes (INSERT/UPDATE/DELETE) because every change must update indexes.
• They increase storage and maintenance work (vacuuming, bloat, backups).

So an audit is not “add indexes everywhere.” It’s “add the right ones, remove the waste, and verify the results with stats.” This shows up a lot in AI-generated codebases (for example from Cursor, Replit, or v0), where queries often ship without careful indexing. The app works in a demo, then production traffic finds the slow paths quickly.

Before you start: make sure your stats are trustworthy

An index audit is only as good as the stats behind it. If your database restarted, failed over, or had stats reset, you can end up adding the wrong index or dropping a useful one.

First, confirm you can read the views you need. Most of the signals come from system views like pg_stat_*. On managed Postgres you may not be a superuser, but you should usually be able to read common pg_stat views with a monitoring-friendly role.

Before pulling numbers, do a few quick checks:

• Make sure you can query pg_stat_database, pg_stat_user_tables, and pg_stat_user_indexes without errors.
• Verify whether stats were recently reset (after a restart, failover, or a manual reset).
• Confirm your app’s queries aren’t mostly bypassing Postgres (a caching layer can hide real database load).
• Run the audit on the same database that serves real traffic.

If you can use it, pg_stat_statements makes the audit far more reliable because it aggregates real query behavior over time (calls, total time, mean time). It must be installed and enabled ahead of time, and it only tracks queries after it starts collecting.

Pick an observation window that matches how the app is used. A good default is a few real business cycles, including peak hours.

Avoid using a distorted window unless the distortion is the problem you’re trying to fix. Don’t do an audit right after:

• A major deploy that changed query patterns
• A backfill or migration that flooded the database
• A bulk import that isn’t normal traffic
• An incident where the system was degraded

One common trap: a newly shipped feature accidentally triggers an N+1 query loop. Your stats will then scream for indexes, even though the real fix is to stop the query explosion.

The pg_stat views you will rely on (quick map)

A practical audit starts with one principle: trust what your database has observed under real traffic.

These views cover most of what you need:

• pg_stat_statements: A scoreboard of normalized queries with totals and averages for time, calls, and rows. This is where you find which query patterns burn the most latency.
• pg_stat_user_tables: How each table is accessed. The key signal is whether it’s mostly hit via indexes or via sequential (full) scans.
• pg_stat_user_indexes: How often each index is used. Helpful for spotting indexes that look important but have near-zero usage.
• pg_statio_user_tables: Whether table reads come from cache or disk. High disk reads often correlate with user-visible slowness.
• pg_statio_user_indexes: The same cache vs disk view, but for index pages.

A common misunderstanding: usage counts and impact are not the same thing.

• Usage counts (like “how many times an index was scanned”) show popularity, not time saved.
• Time and I/O signals (from pg_stat_statements and the pg_statio_* views) show pain.

A good workflow is: find expensive query shapes in pg_stat_statements, then use table and index stats to understand why those queries are slow (table scans, disk reads, poor selectivity, and so on).

Also remember that stats aren’t permanent. They reset on restart, and they can be reset manually. “Unused” sometimes only means “unused since the last reset,” so make sure your window covers normal traffic.

Step-by-step: run a simple index audit in 30-60 minutes

This is a quick audit based on real traffic signals. If you inherited an app where pages randomly crawl or time out, these snapshots help you focus on what users actually hit.

Step 1: Pull your slowest queries (total time and average time).

-- Requires pg_stat_statements
-- Postgres 13+ uses *_exec_time columns
SELECT
  queryid,
  calls,
  total_exec_time AS total_ms,
  mean_exec_time  AS mean_ms,
  rows,
  shared_blks_read,
  shared_blks_hit,
  left(query, 120) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

SELECT
  queryid,
  calls,
  total_exec_time AS total_ms,
  mean_exec_time  AS mean_ms,
  rows,
  shared_blks_read,
  shared_blks_hit,
  left(query, 120) AS query_sample
FROM pg_stat_statements
WHERE calls >= 20
ORDER BY mean_exec_time DESC
LIMIT 20;

Use the first query to find what burns the most total time across the day. Use the second to find slow statements that might spike user latency.

Step 2: Find tables doing lots of sequential reads. These are common “missing index or wrong query shape” candidates.

SELECT
  schemaname,
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  n_live_tup,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 20;

Step 3: List indexes that are unused or barely used. Large indexes with low idx_scan are prime suspects.

SELECT
  schemaname,
  relname      AS table_name,
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 30;

Step 4: Spot heavy-write tables where extra indexes can slow you down. Every extra index makes inserts and updates do more work.

SELECT
  schemaname,
  relname,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_tup_hot_upd,
  (n_tup_ins + n_tup_upd + n_tup_del) AS total_writes
FROM pg_stat_user_tables
ORDER BY total_writes DESC
LIMIT 20;

Step 5: Save snapshots with timestamps so you can compare before vs after. Store results somewhere consistent (CSV or a small table) and take notes about what changed.

CREATE SCHEMA IF NOT EXISTS audit;
CREATE TABLE IF NOT EXISTS audit.slow_queries_snap (
  captured_at timestamptz NOT NULL,
  queryid     bigint,
  calls       bigint,
  total_ms    double precision,
  mean_ms     double precision,
  query_sample text
);

INSERT INTO audit.slow_queries_snap
SELECT
  now(), queryid, calls,
  total_exec_time, mean_exec_time,
  left(query, 200)
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 50;

How to spot missing indexes using real traffic signals

A missing index usually shows up as two things at once:

• A large table getting scanned repeatedly
• A small set of query patterns repeatedly showing up as expensive

Start with table-level signals. In pg_stat_user_tables, a high seq_scan (especially paired with high seq_tup_read) on a large table is a strong hint that queries are filtering or joining without an index. It’s not proof, but it tells you where to look.

Then connect it to query patterns in pg_stat_statements. You’re looking for repeated access patterns:

• Common WHERE filters (for example, user_id, created_at, tenant_id)
• JOIN keys (foreign keys are a frequent culprit)
• ORDER BY columns that force sorting on large result sets
• LIMIT queries that still read a lot because they can’t use an index effectively

A quick rule of thumb: index selective columns first. A selective column has many distinct values, so it can narrow the search quickly. WHERE user_id = 42 is usually selective. WHERE status = 'active' often isn’t.

Multi-column indexes can be the right fix, but order matters because Postgres uses the leftmost part of a btree index most effectively. If your common query is:

SELECT * FROM orders
WHERE user_id = $1 AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

An index like (user_id, created_at) matches that pattern. The reverse (created_at, user_id) often helps less because it doesn’t narrow to a single user early.

How to find unused indexes (and when not to drop them)

Unused indexes are sneaky: they add write cost, take disk, and make vacuum and backups heavier. The goal isn’t to delete indexes aggressively. It’s to remove the ones that never help your real workload.

Start with pg_stat_user_indexes. A basic first pass is:

• idx_scan = 0 since the last stats reset (candidate)
• Very low scans compared to how much the table is written to (candidate)

Then sanity-check with context. Some indexes exist for correctness, not speed:

• Indexes backing PRIMARY KEY or UNIQUE constraints
• Indexes that are critical for foreign key checks
• Indexes used to enforce application rules (for example, “one active subscription per user”)
• Indexes needed for rare but important jobs (reports, exports, forensics)

A classic mistake: you see idx_scan = 0 for orders_created_at_idx, drop it, and later discover month-end reconciliation relies on it. If you only look at a week of stats, you can make the wrong call.

A cautious process that works well:

1. Make a short candidate list (table, index, why you think it’s unused).
2. Re-check after a full business cycle (often 2-4 weeks).
3. If it still looks unused, drop it during a planned window and watch latency and write times.
4. Keep the index definition so you can recreate it quickly.

Find the worst latency offenders by query stats

If you want the biggest win quickly, rank real queries by how much time they burn in total. pg_stat_statements helps you see what’s costly across the day, not just what’s slow once.

Two patterns to compare:

• One very slow query (2 seconds) that runs twice a day
• A “medium” query (40 ms) that runs 10,000 times a day

The second one usually hurts more.

Here’s a simple starting point. Column names vary by Postgres version (you may see total_time/mean_time instead of total_exec_time/mean_exec_time):

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  shared_blks_hit,
  shared_blks_read,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Then re-run ordering by mean time to spot “one-off” slow statements:

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  shared_blks_hit,
  shared_blks_read,
  query
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 20;

For each candidate query, capture a few facts before changing indexes:

• Query text and queryid
• Calls, total time, mean time
• Rows returned (is it returning far more than the app needs?)
• shared_blks_hit vs shared_blks_read (mostly cached, or doing lots of disk reads?)
• A quick EXPLAIN (ANALYZE, BUFFERS) in a safe environment

Indexes aren’t always the answer. High latency also comes from bad joins that multiply rows, missing pagination, fetching giant JSON blobs, or doing too much work inside SQL.

Validate and design the right index (without guesswork)

After you flag a slow query, don’t create an index immediately. First confirm what the database is actually doing for the production query shape. The fastest way is to test the exact query (with realistic parameters) using EXPLAIN (ANALYZE, BUFFERS).

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 42
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

Read it like a story:

• Seq Scan means Postgres is reading a lot of the table and filtering after. That’s fine on small tables, but often slow on big ones.
• Index Scan or Index Only Scan means Postgres can jump to the rows it needs.
• BUFFERS shows how much work happened in memory vs disk, which often explains why a query is fast sometimes and slow at peak.

When you design an index, match it to how the query filters and sorts:

• Single-column index when one column does most of the filtering (for example user_id).
• Composite index when you filter on multiple columns together (for example (user_id, status)). Column order matters.
• Partial index when you always query a subset (for example only status = 'open'). Smaller index, less write cost.
• Including the sort column when it avoids a large sort (for example (user_id, status, created_at)).

Keep changes small and reversible:

1. Add one index targeting one high-latency query.
2. Re-run EXPLAIN (ANALYZE, BUFFERS) to confirm the plan improved.
3. Measure again over a comparable window.
4. If write time or lock time gets worse, roll it back.

If you inherited AI-generated code, the query itself is often the real issue (missing filters, fetching too many rows, sorting huge result sets). Fix the query first when that’s the case.

Common mistakes that make index audits backfire

Index audits go wrong when you treat one stat as “the truth” and move too fast.

Common mistakes:

• Dropping an index that protects correctness. Indexes backing PRIMARY KEY or UNIQUE constraints matter even if they look “unused.” Some indexes are also critical for foreign key checks.
• Chasing idx_scan without looking at time and rows. A low scan count doesn’t automatically mean “safe to drop,” and a high scan count doesn’t automatically mean “must keep.”
• Adding overlapping indexes for every slightly different query. This leads to multiple near-duplicates, higher write cost, and more maintenance.
• Blaming indexes when the real issue is bloat or stale stats. If autovacuum and analyze are behind, plans can degrade and scans can get slower.
• Testing during quiet hours and assuming peak improves. Cache warmth, concurrency, and write pressure change how plans behave.

Make one change at a time, measure before and after under similar load, and keep a rollback plan.

Quick checklist and next steps

An audit only pays off if you can repeat it. Keep one simple report you can run today, then run again after each change to prove the win (or catch a regression).

Your 30-minute repeatable report

• Capture top queries by total time and by average time (keep it small, like 20 each).
• Note large tables with high sequential reads.
• Keep a short review list of indexes that look unused, with a one-line reason.
• Propose up to three changes (add, remove, or adjust) and tie each one to a specific query or table.
• Re-run the same report after deployment and compare time, calls, rows, and scans.

Before dropping anything, do a quick exception check: does it back a primary key or unique constraint, support a foreign key, or exist for a rare but important job?

When you need a second set of eyes

If your app was generated by tools like Replit, Cursor, or v0, performance issues often come from a mix of missing indexes, inefficient queries, and unsafe patterns. FixMyMess (fixmymess.ai) focuses on diagnosing and repairing AI-generated codebases, including query and schema problems that only show up under production load. If you’re stuck, a targeted audit can help you choose the smallest set of changes that actually moves latency.