Unused Indexes

Check Frequency

Daily

Default Configuration

Detects indexes that are not in use by any queries within the last 14 days and creates an issue with severity "info". Resolves once the index is dropped or starts being used. Note that if you have gaps in your collector reporting, this check may miss usage during un-reported periods.

Ignores small indexes (less than 32kB), as well as primary keys, unique indexes, and expression indexes since they are necessary to enforce constraints or useful as query planning hints.

This check is enabled by default.

Guidance

Impact

Postgres has to keep maintaining unused indexes for inserts, updates, and deletes to the table, slowing down writes. They also take up memory, competing with actively used indexes and table data. Larger unused indexes can also cause unnecessary disk space usage.

Solution

First, make sure this index is not in use in another database. You may need to check other environments that share the same schema, or verify that the index is not used on a read replica. If this server is a staging, QA or dev environment you may want to turn off the index unused check and only run it on production. You can also check usage of the associated table on the Schema Statistics page for the table. Once you have confirmed the index is safe to drop, you can clean up this unused index by running DROP INDEX CONCURRENTLY "<index_name>";.


Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →