May 2018
Intermediate to advanced
576 pages
30h 25m
English
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 you should remove the index. That is a more complex question, and we first ...