Postgres Tells You Your Query Was Slow, Not Which Index Is Wasted

Developers often add indexes "just in case" — like carrying a spare umbrella for years even though it rains once every five years. Postgres honestly reports that a query was slow, but won't tell you which index is sitting idle. Yet every extra index means wasted gigabytes and slower inserts.

The author built a three-file analyzer that does just that. On the first run, it found 20 out of 51 indexes were never used — 78% of index disk space was pure waste. A familiar pain: thinking "this index will speed everything up," but it just takes up space.

The analyzer uses nothing but pg_stat_user_indexes and a bit of SQL. It shows which indexes actually work and which can be dropped without guilt. Perfect for cleaning up your database without hiring a DBA.

METABYTE studio comment: We love clean databases too — every unused index is like a dead dependency in package.json: doesn't hurt until the project grows into a spaceship.