Find the Indexes Nobody Uses Before You Add Another One
The database counts how often each index has been used: PostgreSQL in pg_stat_user_indexes.idx_scan, MySQL in sys.schema_unused_indexes. An index with zero scans since the counters were reset is pure write tax, and a duplicate index whose columns are the leading prefix of another is the same waste. Reading those counters before adding an index tells you whether the table is already carrying indexes you can drop.
Questions this Concept answers
- Why is an index whose columns are a leading prefix of another index usually waste?
PostgreSQL Index Performance: Unused and Expensive Indexes
Every index makes some reads faster and every write a little slower, so an index nobody uses is pure cost. PostgreSQL counts index use in pg_stat_user_indexes as idx_scan, which tells you how many ti…