阅读中文版本

Audit PostgreSQL Unused Indexes

Use pg_stat_user_indexes to find indexes that are never scanned, then drop them safely after confirming they are not unique constraints.

SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

SELECT indexrelid::regclass, indisunique, indisprimary
FROM pg_index WHERE indexrelid = 'public.idx_orders_legacy'::regclass;

What this recipe does

Identify write-amplifying unused indexes from statistics and remove them with a reversible plan.

Insert throughput degrades over time as indexes accumulate, and the team suspects many are never used by queries.

Prerequisites

• PostgreSQL 13+ with statistics collection enabled
• A recent statistics reset or long-running instance
• Permission to drop indexes

Procedure

1. List indexes with zero scans and their size.

SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Expected result: Never-scanned indexes are listed with sizes, exposing the biggest write-amplification targets.

2. Confirm an index is not a unique constraint before dropping.

SELECT indexrelid::regclass, indisunique, indisprimary
FROM pg_index WHERE indexrelid = 'public.idx_orders_legacy'::regclass;

Expected result: indisunique=false and indisprimary=false means it is safe to consider removing.

How to confirm it worked

Run the commands above and confirm every expected result in order. If a step does not produce the stated output, use the troubleshooting notes before continuing. A passing run shows dropping the flagged indexes reduces write latency while query plans for known hot queries stay unchanged.

Troubleshooting

Statistics recently reset — Wait for a full workload cycle before trusting idx_scan; short windows mislead.

An index is used only at month end — Check reporting cycles or compare a longer window before dropping.

Limitations

• Statistics can miss rarely used indexes.
• Dropping indexes may slow some reports; review EXPLAIN plans.
• Does not cover indexes maintained by constraints.