Mastering PostgreSQL 12 - Third Edition

Digging into indexes

While pg_stat_user_tables is important for spotting missing indexes, it is sometimes necessary to find indexes that shouldn't really 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 may 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 ---------------+--------+-----------+----------+--------- ...