Postgres pg_stat_user_indexes: Finding Unused Indexes Before They Slow You Down

Subjectpg_stat_user_indexesAuthorVeraApplies toPostgres 9.4+RiskLow — read-only query, no side effects from the view itself

Every index you create adds overhead to every write. INSERT, UPDATE, DELETE, and VACUUM all maintain your indexes whether or not anyone reads them. pg_stat_user_indexes shows you which indexes have never once been used to answer a query since the last statistics reset. That's the list you start with when you want to reclaim throughput.

What the view shows

The key columns:

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;

• idx_scan: how many index scans have been initiated on this index. Zero means the planner has never used it.
• idx_tup_read: index entries returned by scans. Can be nonzero even for indexes with low idx_scan if those scans were broad.
• idx_tup_fetch: heap tuples fetched via this index. This is what actually reduces I/O. An index with high idx_tup_read but low idx_tup_fetch may be pointing at dead tuples or returning rows that are later filtered.

For the question "is this index unused," idx_scan = 0 is your signal.

The false-zero trap

Before you drop anything, check when statistics were last reset:

SELECT stats_reset FROM pg_stat_bgwriter;

If stats reset three days ago and your index supports a monthly billing batch job, idx_scan = 0 means nothing. The job hasn't run yet. You need observation periods that include at least one full cycle of every significant workload pattern: end-of-month runs, quarterly reports, annual archival jobs.

A conservative minimum is 30 days of continuous operation after the last pg_stat_reset() call. If you don't control when resets happen — someone ran one after a schema migration — your zero counts may be meaningless.

Indexes that should never be dropped regardless of scan count

Some indexes exist to enforce constraints, not to speed queries. The planner may never use them for scans and they'll still show idx_scan = 0.

Check before dropping:

-- Primary keys and unique constraints
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'your_table'
  AND (indexdef LIKE '%UNIQUE%' OR indexname IN (
    SELECT conindid::regclass::text
    FROM pg_constraint
    WHERE contype IN ('p', 'u')
  ));

-- Foreign key target indexes (often implicitly enforced)
-- If a column is referenced by FK in another table,
-- dropping its index can cause lock escalation on deletes
SELECT
  tc.table_name,
  kcu.column_name,
  ccu.table_name AS foreign_table,
  ccu.column_name AS foreign_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.referential_constraints rc
  ON tc.constraint_name = rc.constraint_name
JOIN information_schema.key_column_usage ccu
  ON rc.unique_constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

Expression and partial indexes require extra care

An expression index on lower(email) will only be used when the query includes WHERE lower(email) = $1. If your queries use WHERE email = $1 (exact, no function call), the index never fires and shows idx_scan = 0 — but it might be there intentionally for case-insensitive login, which just isn't being triggered in your current measurement window.

Partial indexes are similarly narrow. An index with WHERE status = 'pending' only appears in plans when the planner sees a matching predicate. If your monitoring queries don't use that filter, the index looks idle.

Check the index definition before drawing conclusions:

SELECT indexdef FROM pg_indexes WHERE indexname = 'your_index_name';

pg_stat_reset timing discipline

Reset statistics deliberately, not on a calendar. The right time to reset is after a significant schema change — after you've dropped unused indexes and want fresh counts for the survivors. Resetting before a schema change throws away your evidence.

Never reset statistics just to "start fresh." You lose months of data that could show you indexes used only by infrequent jobs.

What pg_stat_user_indexes does not show

• Index bloat: an index can have idx_scan > 0 and still be 5x its ideal size due to dead entries. Use pgstattuple or pg_repack for that.
• Predicate selectivity: a high idx_scan count doesn't mean the index is helping much if it returns 40% of the table on every scan.
• HOT update impact: indexes block HOT (Heap Only Tuple) updates. An unused index on a frequently-updated column has a hidden write cost even if scan count is zero.

pg_stat_user_indexes answers one question: "has this index ever been scanned?" That's enough to build a candidate drop list. Judgment about which candidates to actually drop still requires understanding the workload.

Building something and want to talk through your database architecture? builds.anethoth.com lists projects in progress — publicly, with proof of work.