Indexes are the primary tools used to enhance database performance, allowing the server to retrieve specific rows much faster than scanning an entire table. However, every index comes with a “tax”—they consume disk space and add overhead to INSERT, UPDATE, and DELETE operations because the index must be updated alongside the table. Therefore, identifying and removing unused indexes is a critical part of database maintenance.
Unlike some other database systems, PostgreSQL provides built-in cumulative statistics that track every time an index is consulted by the query planner. This guide explains how to use system views and execution plans to verify if your indexes are pulling their weight or simply wasting resources.
Table of Contents
Key Takeaways for Index Auditing
- Query Planner → The engine that evaluates execution strategies and selects an index only if it is the lowest cost option.
- idx_scan counter → A value in the system catalogs that increments every time an index is used for a scan.
- last_idx_scan → A timestamp that reveals the exact time an index was last utilized for a query.
- EXPLAIN ANALYZE → The most reliable command to see if a specific query is using an index in real-time.
- Redundant Indexes → Seldom used or duplicate indexes should be removed to improve write performance and save space.
Method 1: Checking Global Statistics with pg_stat_user_indexes
The most efficient way to audit index usage across your entire database is to query the pg_stat_user_indexes system view. This view provides a running total of all activity since the statistics were last reset.
SQL Command:
SELECT
relname AS table_name,
indexrelname AS index_name,
idx_scan,
last_idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
Understanding the Output: The idx_scan column shows the total number of index scans initiated. If you see an index with an idx_scan of 0 and a last_idx_scan that is NULL, it means that index has not been used since the statistics were reset. This is a prime candidate for removal. For more on managing your relations, see Finding your tables in PostgreSQL.
Method 2: Using EXPLAIN ANALYZE for Specific Queries
If you have a specific query that feels slow, you can use the EXPLAIN command to see if PostgreSQL is choosing your index or falling back to a sequential scan.
SQL Command:
EXPLAIN ANALYZE SELECT * FROM your_table WHERE your_column = 'value';
Understanding the Output: Look for nodes labeled Index Scan or Index Only Scan in the results. If the plan shows Seq Scan, the planner has decided the index is not useful for this specific query. Note that EXPLAIN ANALYZE actually executes the query to provide real run-times and row counts. To learn more about optimizing these results, check out Troubleshooting slow queries in PostgreSQL: A step-by-step guide.
Method 3: Monitoring Index-Only Scans
PostgreSQL supports Index-Only Scans, which can retrieve data directly from the index without ever visiting the main table (the heap). This is significantly faster for I/O performance.
SQL Command:
See also: Mastering the Linux Command Line — Your Complete Free Training Guide
SELECT
indexrelname,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes;
Understanding the Output: The idx_tup_read column counts entries retrieved from the index, while idx_tup_fetch counts live rows fetched from the table. In an efficient Index-Only Scan, the fetch count will be much lower than the read count because the system found the data within the index itself.
Step-by-Step Process for Index Auditing
- Enable Statistics Tracking: Ensure the
track_countsparameter is set toonin yourpostgresql.conf; otherwise, the usage views will remain empty. - Run a Usage Audit: Execute a SELECT against
pg_stat_user_indexesto find indexes with zero or very low scan counts. - Cross-Reference with Server Uptime: Check
pg_postmaster_start_time()to see how long the server has been collecting stats. If the server just restarted, a zero scan count might just mean the app hasn’t run its weekly reports yet. - Test Queries with EXPLAIN: Before dropping an index, find the queries that should use it and run
EXPLAIN ANALYZEto confirm the planner’s logic. - Clean Up Redundant Indexes: Use the
DROP INDEXcommand for any objects that are proven to be unused.
Index Usage Summary
| Method | Best Use Case | Key Column/Metric |
|---|---|---|
pg_stat_user_indexes | Long-term server monitoring | idx_scan |
EXPLAIN ANALYZE | Debugging a specific slow query | Index Scan node |
pg_stat_database | High-level database health | tup_fetched |
last_idx_scan | Identifying “dead” or legacy indexes | Timestamp of last use |
FAQs
Why is my index scan count 0 even though I just ran a query? PostgreSQL statistics are collected locally and then updated to shared memory at intervals. Additionally, on very small tables that fit in a single disk page, the planner may choose a sequential scan because it is cheaper than using an index.
What is the difference between idx_tup_read and idx_tup_fetch? idx_tup_read represents the total number of entries the system looked at in the index. idx_tup_fetch is the number of rows it actually went into the table to get. A large gap usually indicates efficient Index-Only Scans or the presence of dead tuples.
Can I reset these counters without restarting the server? Yes, you can use the pg_stat_reset() function to clear statistics for the current database and start a fresh monitoring period.
