Finding Unused Indexes in Postgres

Database indexes are incredibly important in production systems. Single column ones are obvious, but multi-column (composite) indexes are game-changers for speeding up commonly used queries by several orders of magnitude.

But sometimes, we (I) create a few different indexes over time, and it's not always obvious when to remove old indexes. Indexes take up space and slow down writes, so it's important to monitor and clean them up periodically.

Here is a postgres query to give you some insight:

SELECT 
    relname AS table_name, 
    indexrelname AS index_name, 
    idx_scan AS index_scans, 
    idx_tup_read AS tuples_read, 
    idx_tup_fetch AS tuples_fetched
FROM 
    pg_stat_user_indexes
JOIN 
    pg_index ON pg_stat_user_indexes.indexrelid = pg_index.indexrelid
WHERE 
    schemaname = 'public'
ORDER BY 
    idx_scan DESC;

The output will be the table name, index name, and some data about each index including how many times the index was used in a query, how many tuples (index rows in this case) were read from the index, and how many tuples were actually fetched after all filtering was complete.

The output will contain data since the server was last restarted or the statistics were last reset. Here is some example output from a production server. I removed a bunch of rows to show heavily used indexes and some that aren't used at all:

 table_name    |          index_name         | index_scans | tuples_read | tuples_fetched 
---------------+-----------------------------+-------------+-------------+----------------
 items         | items_pkey                  | 17566068467 | 22444742841 | 21762928697
 routes        | routes_item_id_key          |  4046022477 |  2541792837 | 2521785009
 items         | items_url_idx               |  1520426292 |  7556543480 | 1518612148
 authors       | authors_pkey                |   211481111 |    45577051 |   42726045
 logs          | logs_type_coord_uniq        |     6437114 |     1462603 |     1392484
 spatial_ref   | spatial_ref_pkey            |     2060726 |    13792886 |     2056566
 users         | users_pkey                  |     1872578 |     2214935 |     1872578
 ...
 rate_limits   | rate_limit_key_like         |           0 |           0 |           0
 blocks        | blocks_pkey                 |           0 |           0 |           0
 blocks        | blocks_uniq                 |           0 |           0 |           0

As you can see, some of these indexes are never used! I have some work ahead of me now: manually review these indexes and potentially remove unused ones to save on space and improve write performance. Eventually, I'd like to set up automated alerts to let me know that some indexes aren't being used at all.

Let me know if this is helpful for you or if you've gone further and automated anything like this!