Database Reference
In-Depth Information
How to do it...
PostgreSQL keeps track of each access against an index. We can view that information and
use it to see if 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 need to explain how it works.
How it works...
The PostgreSQL statistics accumulate various useful information. These statistics can be
reset to zero using an administrator function. Also, as the data accumulates over time, we
usually find that objects that have been there longer have higher apparent usage. So if we see
a low number for
idx_scan
, then it might be that the index was newly created (as was the
case in my preceding demonstration), or it might be that the index is only used by a part of the
application that runs only at certain times of day, week, month, and so on.
Another important consideration is that the index may be a unique constraint index that exists
specifically to safeguard against duplicate inserts. An
INSERT
does
not
show up as an
idx_
scan
, whereas an
UPDATE
or
DELETE
might, because they have to locate the row first. So, a
table that only has INSERTs against it will appear to have unused indexes.
Also, some indexes that show usage might be showing usage that was historical, and there is
no further usage. Or, it might be the case that some queries use an index where they could
just as easily and almost as cheaply use an alternative index. Those things are for you to
explore and understand before you take action.
See also
You may decide from this that you want to remove an index. If only there was a way to try
removing an index and then put it back again quickly if you cause problems! Rebuilding an
index might take hours on a big table, so these decisions can be a little scary. No worries,
just follow the next recipe,
Carefully
removing
unwanted
indexes
.
