How to find all un-used indexes?

tinygipxy · January 27, 2018, 12:58am

By times we created a lot of index and I think some indexes are not in used anymore. Are then anyway to findout them ?

Thank you

avsej · January 27, 2018, 5:47pm

you can run all your queries with EXPLAIN and remove all indexes, which are not reported there.

tinygipxy · May 19, 2018, 12:03pm

Normally developer create Queries, so as IT we dont’ know all of queries. If we can have report to by time for all report usage (the last use of an index), it would help much

Naveen_Nisad · June 2, 2020, 7:52am

have you found any usage report query for indexes?

amit.kulkarni · June 2, 2020, 8:07am

Hi @Naveen_Nisad,

You can check for “last_known_scan_time” in “/api/v1/stats” REST call to indexer process. Please check the documentation. Note that the value of “last_known_scan_time” will NOT reset itself across the process restart. This stat was added in Couchbase Server version 6.5.0.

There is another stat “num_requests”, which will tell you number of requests served by that index - since the process comes up. But this value will be lost after the process restarts.

Also note that these values are correct for one instance (or replica) of the index. So, in case of multiple replicas, you will need to separately check with all the indexer nodes in your cluster.

Hope this help.

Naveen_Nisad · June 4, 2020, 7:20am

thanks Amit,
unfortunately I’m using 6.03 version and upgrade is not in recent plan.

Naveen_Nisad · June 5, 2020, 6:54am

I have achieved it from different way -
can someone confirm, is this good?

select array_agg(distinct statement ) as a from system:completed_requests where users = ‘username’ (I got around 2 months record)
Now we have list of all queries completed in system (I gathered this data from production)
and evaluated explain on all queries, and I got list of all used indexes

vsr1 · June 5, 2020, 1:05pm

This is not a reliable options and you can’t depend on system:completed_requests for all executed statements.

system:completed_requests only has entries that took more than 1sec
system:completed_requests are transient (any restart of query service will not persist)

Naveen_Nisad · June 18, 2020, 7:25pm

thanks, realized same after executing same in production,
But I found one performance testing env where last 2 months record was available and results with more than 1 sec response time only