PostgreSQL 11 Administration Cookbook

How to do it…

PostgreSQL keeps track of each access against an index. We can view that information and use it to see whether an index is unused, as follows:

postgres=# SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan; schemaname |       indexrelname       | idx_scan------------+--------------------------+---------- public     | pgbench_accounts_bid_idx |        0 public     | pgbench_branches_pkey    |    14575 public     | pgbench_tellers_pkey     |    15350 public     | pgbench_accounts_pkey    |   114400(4 rows)

As we can see in the preceding code, there is one index that is totally unused, alongside others that have some usage. You now need to decide whether unused means that you should remove the index. That is a more complex question, and ...