O'Reilly logo

Mastering PostgreSQL 9.6 by Hans-Jurgen Schonig

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Digging into indexes

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 | ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required