While pg_stat_user_tables is important to spotting missing indexes, it is sometimes necessary to find indexes which 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 case of large systems--having hundreds of gigabytes of pointless indexes can seriously harm your overall performance.
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 | Modifiers ---------------+--------+----------- relid | oid | ...
