Find (and Safely Drop) Unused Indexes in PostgreSQL

A quick query to find PostgreSQL indexes nobody uses, the four checks to run before dropping one, and how to drop it without blocking writes.

Illustration of a database cylinder with a row of index cards, several faded and dusty, one being carefully pulled out

Indexes speed up reads, but they aren’t free. Every INSERT, UPDATE and DELETE has to maintain them, they take up disk, and they compete for memory in shared buffers. On a database that’s been around a few years, it’s common to find indexes that nobody has used in months. Here’s how to find them, and what to check before you drop one.

The query

PostgreSQL tracks how often each index is scanned in pg_stat_user_indexes. This query lists indexes with zero scans. It skips primary keys, unique indexes, and indexes that back a constraint, because those enforce rules even if no query reads them.

SELECT s.schemaname,
       s.relname        AS table_name,
       s.indexrelname   AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT EXISTS (
        SELECT 1 FROM pg_constraint c
        WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

The biggest unused indexes come first, so the top of the list is where the payoff is.

Before you drop anything, check these four things

  1. When were stats last reset? A zero only means something if the counters have been collecting for a while. Run SELECT stats_reset FROM pg_stat_database WHERE datname = current_database(); and aim for at least a few weeks of history, ideally covering a month-end.
  2. Do you have read replicas? Scan counts are tracked per server. An index used only by reporting queries on a replica will show zero on the primary. Run the query on every replica too.
  3. Are there rare jobs? Quarterly reports and yearly archive jobs may depend on an index that looks idle most of the time.
  4. Is the index new? A recently created index will naturally show low counts.

On PostgreSQL 16 and later, pg_stat_user_indexes also has a last_idx_scan column, which tells you when the index was last used, not just how many times.

Dropping safely

First save the definition, so you can recreate the index if something slows down:

SELECT pg_get_indexdef('schema_name.index_name'::regclass);

Then drop it without blocking writes to the table:

DROP INDEX CONCURRENTLY schema_name.index_name;

CONCURRENTLY can’t run inside a transaction block. If it fails partway through, check for a leftover invalid index and drop it again.

Wrap-up

Run the query once a quarter, verify across all replicas, and keep the saved definitions somewhere handy. Removing dead indexes is one of the cheapest write-performance wins you’ll find.

Leave a Reply