Right, let’s talk about cleaning up your own mess. You’ve been creating indexes like a kid with a free pass to an all-you-can-eat buffet. Some of them were brilliant ideas that save milliseconds on every query. Others… well, you were probably just guessing and hoping for the best. Every single one of those indexes, even the unused ones, is a tax you pay on every INSERT, UPDATE, and DELETE. They take up space, they slow down writes, and they make the vacuum worker hate you. It’s time to find the dead weight and cut it loose. Our best friend for this job is the pg_stat_user_indexes view.

Think of this view as the reality TV show where your indexes have to prove they’re still worth keeping around. It tracks how many times each index has been scanned. The key column here is idx_scan. An index that has a big, fat zero in that column is an index that has never been used. It’s just sitting there, consuming resources, offering nothing in return. It’s the most obvious candidate for deletion.

But before you go on a deletion spree, let’s look at the data. Here’s the basic query to see what’s what.

SELECT schemaname,
       relname AS table_name,
       indexrelname AS index_name,
       idx_scan AS index_scans
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

This will list your indexes, ordered from least-used to most-used. You’ll likely see a few zeros at the top. Those are your prime suspects.

The “Almost Useless” Index

Don’t stop at just the zeros. An index with only 10 scans on a billion-row table that’s updated constantly is also a terrible investment. You need to consider the ratio of scans to writes. There’s no perfect view for this, but you can cross-reference with pg_stat_all_tables to get a sense of table activity. An index with 100 scans on a table that has had 100,000 writes is probably still a net negative.

The Reindexing Pitfall & The Stats Reset

Here’s the first “gotcha” that will make you curse the PostgreSQL designers (lovingly, of course). These statistics are stored in memory and only written to disk every stats_temp_directory interval or when the server shuts down cleanly. But the bigger issue: these stats are reset if you run the REINDEX command on an index.

Yes, you read that right. If you have an old, unused index that you finally decide to reindex to reclaim bloat, you will also wipe out all its historical usage data. It will start back at zero, making it look brand new and unused. It’s a brilliantly frustrating catch-22. The lesson? Always check your idx_scan values before you consider any maintenance operation.

The Primary Key Exception

You’ll probably see your primary key indexes have very high scan counts. That’s expected. But what about a PK with a low scan count? That seems weird, right? Well, it’s probably not. Remember, idx_scan only counts index scans, not every use of the index. A primary key is often used for foreign key constraints and updates, which might use an index lookup (a form of index scan), but if you’re doing large sequential scans on the table, the PK’s idx_scan count might be low relative to the table’s size. Don’t immediately assume a low-scan PK is useless; it’s probably just being used for its intended purpose: guaranteeing uniqueness and facilitating joins.

A More Nuanced Query

So let’s be smarter. Let’s write a query that also tells us the size of the index. Why should I care about a 16KB unused index? Maybe I don’t. But a 4GB unused index? That’s an emergency.

SELECT schemaname,
       relname AS table_name,
       indexrelname AS index_name,
       idx_scan AS index_scans,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
       pg_stat_user_indexes.indexrelid
FROM pg_stat_user_indexes
JOIN pg_index i ON pg_stat_user_indexes.indexrelid = i.indexrelid
WHERE idx_scan < 50 -- Adjust this threshold as needed
ORDER BY pg_relation_size(pg_stat_user_indexes.indexrelid) DESC;

This orders your least-used indexes by their size on disk, so the biggest wastes of space float to the top.

The Final Check: Don’t Be a Cowboy

Before you DROP INDEX CONCURRENTLY that 50GB behemoth, you have one last crucial step. You must account for the possibility that the index is used by a very rare—but critical—query. Maybe it’s the quarterly financial report that runs for three hours and uses this one index to make it only take ten minutes.

  1. Check for Dependencies: Is it used by a constraint? (Unlikely for an unused index, but check).
  2. Temporarily Disable It: The nuclear option is to rename it. This is safer than dropping it outright. If nothing breaks after a week (or your important business cycle), then you can drop it for real.
-- Instead of dropping it, neuter it first.
ALTER INDEX IF EXISTS public.my_gigantic_useless_index RENAME TO my_gigantic_useless_index_old;
-- Wait. Watch. Pray.
-- If all is well, then and only then:
DROP INDEX CONCURRENTLY my_gigantic_useless_index_old;

This renaming trick is the equivalent of taking the bullets out of the gun before you clean it. It’s just good practice. Finding unused indexes is one of the easiest performance wins in PostgreSQL. It’s free real estate. Go claim it.