What is this issue?
PostgreSQL tracks how many times each index is scanned in the pg_stat_user_indexes system view.
When idx_scan = 0, the index has not been used by any query since the last statistics reset
(typically at server restart or manual reset via pg_stat_reset()).
This doesn't necessarily mean the index is useless - it could be:
- A recently created index that hasn't been needed yet
- An index for infrequent operations (monthly reports, seasonal features)
- An index for queries that haven't run since statistics were reset
Why it matters
Wasted Disk Space
Large indexes can consume gigabytes of storage that could be reclaimed
Slower Writes
Every INSERT, UPDATE, and DELETE must update all indexes on the table
Increased Backup Time
More data to backup, restore, and replicate
Memory Pressure
Index metadata and pages consume shared_buffers and OS cache
How PG Pilot detects it
```sql SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexrelname NOT LIKE 'pg_%'; ```
How to fix it
Check when statistics were last reset
Before removing any index, verify how long the statistics have been collecting:
SELECT stats_reset FROM pg_stat_bgwriter;If this was recent (within days), the index may simply not have been needed yet.
Review query patterns
Consider if queries that would use this index run periodically:
- Monthly or quarterly reports
- Seasonal features
- Batch jobs that run infrequently
Check pg_stat_statements to see if relevant queries exist.
Test in a non-production environment
If possible, drop the index in staging and run your test suite to verify
no queries regress.
Drop the index safely
Use CONCURRENTLY to avoid locking the table during the drop:
DROP INDEX CONCURRENTLY schema_name.index_name;Note: DROP INDEX CONCURRENTLY cannot run inside a transaction.
Prevention
- Review index necessity before creating - document why each index exists
- Monitor index usage regularly with PG Pilot
- Remove indexes that remain unused for extended periods
- Consider partial indexes for selective queries instead of full table indexes