While pg_stat_user_tables is important for spotting missing indexes, it is sometimes necessary to find indexes that should really not exist. Recently, I was on a business trip to Germany and discovered a system that contained mostly pointless indexes (74% of the total storage consumption). While this might not be a problem if your database is really small, it does make a difference in the case of large systems—having hundreds of gigabytes of pointless indexes can seriously harm your overall performance.

Fortunately, pg_stat_user_indexes can be inspected to find those pointless indexes:

test=# d pg_stat_user_indexes 
         View "pg_catalog.pg_stat_user_indexes"
    Column     |  Type  | Collation | Nullable | Default 
---------------+--------+-----------+----------+---------
 relid         | oid    |           |          | 
 indexrelid    | oid    |           |          | 
 schemaname    | name   |           |          | 
 relname       | name   |           |          | 
 indexrelname  | name   |           |          | 
 idx_scan      | bigint |           |          | 
 idx_tup_read  | bigint |           |          | 
 idx_tup_fetch | bigint |           |          | 

The view tells us how often every index on every table in every schema has been used (idx_scan). To enrich this view a bit, I suggest the following SQL:

SELECT schemaname, relname, indexrelname, idx_scan,   
       pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size,
       pg_size_pretty(sum(pg_relation_size(indexrelid)) 
                      OVER (ORDER BY idx_scan, indexrelid)) AS total 
FROM   pg_stat_user_indexes 
ORDER BY 6 ; 

The output of this statement is very useful. It doesn't only contain information about how often an index was used—it also tells us how much space has been wasted for each index. Finally, it adds up all the space consumption in column 6. You can now go through the table and rethink all of those indexes that have rarely been used. It is hard to come up with a general rule regarding when to drop an index, so some manual checking makes a lot of sense.

There is also a view called pg_statio_user_indexes that contains caching information about an index. Although it is interesting, it usually does not contain information leading to big leaps forward.