1. Run this query
Read-only. Run it on the primary, and also on each replica that serves reads, because Postgres counts index use per server.
In psql: \copy ( …query… ) TO 'indexes.csv' WITH CSV HEADER. Any SQL client's "export as CSV" works.
2. Paste the result
Choose a file or paste CSV text, then click Audit.
Result
| Kind | Indexes | Disk |
|---|
Candidates to drop
| Kind | Table | Index | Size | Scans | Why |
|---|
Tables with the most flagged disk
| Table | Indexes | Index disk | Flagged | Flagged disk |
|---|
Statements to review
The rules, and what they leave out
- Invalid — Postgres marked the index invalid (usually a failed
CREATE INDEX CONCURRENTLY). It is written on every change and never read. - Duplicate — same method, columns, INCLUDE list, WHERE clause and uniqueness as another index on the table. One is kept: a constraint-backing one first, then the most scanned.
- Redundant — a plain btree whose columns are the leading columns of another btree with the same WHERE clause. If the longer index is itself unused, the shorter one is left alone.
- Unused — zero scans since the statistics were last reset, and not unique, primary, or behind a constraint.
- Reconciles. The kinds, the candidate list and the reclaimed total add up to the same numbers.
- Not modelled: foreign keys needing an index, replicas, statistics age, bloat, partial usage by rare jobs. Check each candidate before running anything.
Common questions
How do I find unused indexes in Postgres?
Run the query on this page in psql or your SQL client. It reads pg_stat_user_indexes, whose idx_scan column counts how many times each index was used since the statistics were last reset. Paste the output here and the tool lists every index that was never scanned, plus invalid, duplicate and prefix-redundant ones, with the disk each would free.
Is it safe to drop an index with zero scans?
Not automatically. idx_scan resets when statistics are reset or after a crash recovery, it is counted per server so an index used only on a read replica shows zero on the primary, and a monthly or quarterly job may not have run since the reset. Check how old your statistics are, check replicas, and keep a copy of each definition so you can recreate it. Primary keys, unique indexes and indexes behind constraints are never suggested.
What counts as a duplicate or redundant index?
A duplicate has the same method, columns, INCLUDE list and WHERE clause as another index on the same table. A redundant index is a plain btree whose columns are the leading columns of another btree with the same WHERE clause, so a query that could use the short one can use the longer one. Both still cost a write on every insert and delete.
Does the tool connect to my database?
No. It never sees a connection string. You run one read-only query yourself and paste the result; the text is parsed in your browser and not sent anywhere.
What does it not see?
Foreign keys that need a supporting index, queries that would use an index you are about to drop, replicas, how long the statistics have been collecting, and bloat. It reads only the file you give it, so treat the output as a list of candidates to check, not a decision.
Is this database advice?
No. It applies fixed rules to the text you provide and writes statements for a person to review. You are responsible for checking and for any change you make to your database.