Unused Indexes
Check Frequency
Daily
Default Configuration
Detects indexes that are not in use by any queries within the last35 days and creates an issue with severity "info", one for each table (or table hierarchy in case of inheritance or partitioning). Resolves once all unused indexes on a table are dropped or start 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), primary keys, and unique indexes since they are necessary to enforce constraints.
This check is enabled by default. These parameters can be tuned in the Configure section of the Alerts & Check-Up page.
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 these indexes are 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've confirmed the indexes are safe to drop, you can clean them up by running DROP INDEX CONCURRENTLY "<index_name>";.
Note: expression indexes can influence the query planner even if the index is unused. When dropping an expression index, if you notice some queries are slower you may want to CREATE STATISTICS for the expression the index previously covered.
Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →