It becomes necessary to check for unused indexes because indexes end up consuming a significant chunk of disk space, and if not monitored closely, they can consume unnecessary CPU cycles, more so in the case of them becoming fragmented.
In order to be able to find unused indexes in PostgreSQL, we need to ensure that the track_activities and track_counts configuration parameters are enabled in the postgresql.conf file. It is only when statistics are collected that we will be able to identify the unused indexes.
We can use the following query to identify unused indexes in PostgreSQL:
SELECT
relid::regclass AS table,
indexrelid::regclass AS index,
pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size,
idx_tup_read,
idx_tup_fetch,
idx_scan
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
AND indisunique IS FALSE;
table | index | index_size | idx_tup_read...