Identifying Unused Indexes in PostgreSQL: Optimizing Storage and Performance

In PostgreSQL, indexes act as shortcuts, accelerating data retrieval within tables. However, unused indexes can become a burden, consuming storage space and potentially impacting performance. This article explores a PostgreSQL code snippet designed to identify unused indexes and explains its significance for database administrators and developers.

Sample Code from Command Line

 1SELECT
 2  relname AS table,
 3  indexrelname AS index,
 4  idx_scan,
 5  idx_tup_read,
 6  idx_tup_fetch,
 7  pg_size_pretty(pg_relation_size(indexrelname::regclass))
 8FROM
 9  pg_stat_all_indexes
10WHERE
11  schemaname = 'public'
12    AND
13  idx_scan = 0
14    AND
15  idx_tup_read = 0
16    AND
17  idx_tup_fetch = 0
18ORDER BY
19  pg_relation_size(indexrelname::regclass) DESC;

Notes PostgreSQL unused indexes. Tested on PostgreSQL 8.4, 9.x, 10.x, 11.x, 12.x, 13.0.

Code Breakdown:

• pg_stat_all_indexes: This built-in PostgreSQL view provides comprehensive statistics on all indexes in the database.
• relname: This column represents the name of the table the index belongs to.
• indexrelname: This column represents the name of the index itself.
• idx_scan: This column indicates the number of times the index has been scanned by the query planner.
• idx_tup_read: This column indicates the number of index entries retrieved and used by the PostgreSQL executor.
• idx_tup_fetch: This column indicates the number of table rows fetched using the index.
• pg_size_pretty(pg_relation_size(indexrelname::regclass)): This complex function calculates and displays the size of the index in a human-readable format.
• schemaname = 'public': This clause filters the results to only show indexes in the public schema (you can adjust this to your specific schema name).
• idx_scan = 0, idx_tup_read = 0, idx_tup_fetch = 0: These conditions identify indexes with zero scans, reads, or fetches, suggesting potential disuse.
• ORDER BY pg_relation_size(indexrelname::regclass) DESC: This clause sorts the results by index size (largest first) to prioritize potentially large unused indexes for review.

Key Points:

• The query results highlight indexes with zero idx_scan, idx_tup_read, and idx_tup_fetch values, potentially indicating unused indexes.
• The size column helps prioritize larger unused indexes that might be consuming significant storage space.

Insights and Explanations:

• Unused indexes can arise due to various reasons, such as:
  • Changes in application logic or data access patterns that render the index irrelevant.
  • Poorly designed indexes that don't effectively support queries.
• Identifying and dropping unused indexes can free up storage space and potentially improve query performance by reducing the number of indexes the PostgreSQL planner needs to consider.

Important Considerations:

• While the query provides valuable insights, it's crucial to analyze the context before dropping any indexes.
• Unused indexes might still be necessary for specific queries or future application changes.
• Consider the trade-off between storage space and potential performance gains when deciding to drop an index.

Conclusion:

By routinely identifying and managing unused indexes, you can optimize your PostgreSQL database for efficient storage utilization and optimal query performance.

References:

PostgreSQL Documentation - pg_stat_all_indexes: https://www.postgresql.org/docs/current/monitoring-stats.html

Finding Unused Indexes In PostgreSQL: https://dmitry-naumenko.medium.com/how-to-define-unused-indexes-in-postgresql-471da6f6f33f

How to identify unused indexes in PostgreSQL: https://dmitry-naumenko.medium.com/how-to-define-unused-indexes-in-postgresql-471da6f6f33f

Source:

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_indexes_unused.sql