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:

SELECTrelnameAStable_name,indexrelnameASindex_name,idx_scanASindex_scans,idx_tup_readAStuples_read,idx_tup_fetchAStuples_fetchedFROMpg_stat_user_indexesJOINpg_indexONpg_stat_user_indexes.indexrelid=pg_index.indexrelidWHEREschemaname='public'ORDERBYidx_scanDESC;

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!