PostgreSQL Index Performance: Finding Unused, Duplicate, and Expensive Indexes

Indexes are one of the few PostgreSQL tools that can make a query 100 times faster and make every write a little slower at the same time. That tradeoff is easy to ignore when the index is new and the incident is hot.

Six months later, the schema has emergency indexes, ORM-generated indexes, migration leftovers, and a few duplicates with different names. Reads are not always faster, writes are definitely heavier, and nobody wants to drop anything because nobody knows what is safe.

Index performance work is not just adding indexes. It is owning the index set as production inventory.

Start with usage, but do not worship it

idx_scan tells you how often an index has been used since stats were reset. Low usage is a signal, not a verdict. A rarely used index may protect an important monthly job or an incident runbook.

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, indexrelname
LIMIT 50;

Look for indexes that tax hot writes

An unused index on a cold table is untidy. An unused index on a table receiving thousands of writes per second is a performance cost. Prioritize by table write rate and index size.

SELECT
  s.relname AS table_name,
  s.n_tup_ins + s.n_tup_upd + s.n_tup_del AS writes,
  i.indexrelname AS index_name,
  i.idx_scan,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM pg_stat_user_tables s
JOIN pg_stat_user_indexes i ON i.relid = s.relid
ORDER BY writes DESC, pg_relation_size(i.indexrelid) DESC
LIMIT 50;

Duplicate indexes hide in plain sight

A duplicate is not always byte-for-byte identical. One index can be a left-prefix of another. A unique index may also support a read path. Before dropping anything, inspect definitions and constraints.

SELECT
  tablename,
  indexname,
  indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexdef;

Bloat suspicion needs more than vibes

Indexes grow because data grows, values churn, pages split, and dead tuples wait for cleanup. If an index is much larger than expected or write performance degrades after churn, investigate bloat with the extensions and tools you allow in production.

The key is to connect size to behavior. A large index that serves the main feed may be justified. A large index with no scans on a high-churn table deserves a cleanup ticket.

Drop indexes like a production change

DROP INDEX CONCURRENTLY IF EXISTS old_unused_idx;

1. Confirm the index is not backing a constraint.
2. Check usage over a representative window.
3. Search application and migration code for explicit index assumptions.
4. Test plans for important queries before and after removing it in staging.
5. Use DROP INDEX CONCURRENTLY when appropriate.
6. Keep rollback SQL ready.

The production habit

The teams that get good at PostgreSQL performance do not chase every knob. They turn a vague complaint into a named failure mode, collect one clean measurement, make one change, and then compare the next measurement against the first. That rhythm is slower than guessing for the first hour and much faster by the end of the incident.