Indexes are crucial for query performance, but poorly managed indexes can hurt
your database more than they help. PG Pilot's Index Health feature provides
deep visibility into your index usage and health.
Every index has a cost: disk space, memory for caching, and write overhead for
maintenance. An unused or duplicate index provides zero benefit while incurring
all these costs.
PG Pilot analyzes your indexes to find:
- Unused indexes: Never scanned since statistics were reset
- Duplicate indexes: Multiple indexes covering the same columns
- Invalid indexes: Failed concurrent index builds
- Bloated indexes: Indexes with excessive dead space
- Missing indexes: Foreign keys without supporting indexes
What it monitors
- Index usage statistics (scan counts)
- Index size and growth trends
- Index validity status
- Duplicate index detection
- Foreign key indexing
- Primary key data type analysis
How to use
Navigate to the Indexes section from the main navigation. You'll see:
1. All Indexes: Sortable list of all indexes with size and usage metrics
2. Health Issues: Dedicated tab showing problematic indexes
3. Index Details: Click any index to see detailed statistics
The Health tab categorizes issues by type:
- Unused: Indexes with zero scans
- Duplicates: Indexes covering the same columns
- Invalid: Failed index builds
- Integer PKs: Tables with integer primary keys at risk of exhaustion
Each issue includes:
- The affected index/table
- Size and usage metrics
- Recommended action
- Link to detailed documentation