Check for unused indexes

[isaiasanchez]

Is it possible to add a check for unused indexes?

Josh Berkus had a nice blog post about that in:
http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Probably also a check for sequencial scans on a table since last execution of check.

[rjuju]

Hello, sorry for late answer.

I like the idea. What kind of message, perfdata, long message and threshold would you want ?

I guess we could detect an index as unused as warning if it didn't have more than X usage since last call, but id could be problematic for application that doesn't run 24/7.

[isaiasanchez]

Well the idea is that we only warning those indexes never scanned but the table have been scan, so if an application never scan the table, so its indexes shouldn't be a problem.

We can use a percentage about table scan against a particular index scan, so it can give an idea about what index can be removed.

if we use limits like: warning 10% and critical 3% (higher values means the index is very used)

And if we extract that information.
Messages:
OK: The indexes are used in 20% of table scans.

Perfdata can be: index_size, table_size, index_scan for the lowest scanned indexes.

Also to be able to include, exclude tables as other checks for those tables a developer doesn't want to be checked.

[gleu]

2016 is quite a long time ago but since the issue is still there and a basic patch is quite easy, I've been working on it. Branch issue32 adds a new unused_indexes service that does exactly this: tells you how many indexes aren't used. For example:

$ ./check_pgactivity -s unused_indexes --dbinclude demo -F human
Service        : POSTGRES_UNUSED_INDEXES
Returns        : 1 (WARNING)
Message        : 1/1 index(es) unused
Long message   : Unused index = demo.public.titi_id_idx ;
Perfdata       : unused index in demo=1

You can use --dbinclude and --dbexclude options. You can use also --exclude to exclude some indexes.

If you're still there 10 years after creating this issue, can you test this branch and let us know?

Thanks.

[Krysztophe]

I'm really looking forward to test this on my favorite customer

[gleu]

Should be available on the May, the 18th. Will you be able to wait for two weeks? :)