What is this issue?
A duplicate index is one where another index on the same table covers the same leading columns.
For example, if you have indexes on (user_id) and (user_id, created_at), the second index
can satisfy queries that would use the first.
Common causes of duplicate indexes:
- Adding an index without checking existing ones
- ORM/framework migrations creating indexes automatically
- Manual index creation after forgetting about existing indexes
- Composite indexes that make single-column indexes redundant
Why it matters
Double Storage Cost
Both indexes store essentially the same data, doubling storage requirements
Slower Writes
Every INSERT/UPDATE/DELETE must maintain both indexes
Planner Confusion
The query planner may choose suboptimally between duplicate indexes
Maintenance Overhead
Both indexes need vacuuming and statistics updates
How PG Pilot detects it
```sql SELECT a.indrelid::regclass AS table_name, a.indexrelid::regclass AS index1, b.indexrelid::regclass AS index2, pg_size_pretty(pg_relation_size(a.indexrelid)) AS index1_size FROM pg_index a JOIN pg_index b ON a.indrelid = b.indrelid AND a.indexrelid != b.indexrelid AND a.indkey::text = b.indkey::text WHERE a.indexrelid > b.indexrelid; ```
How to fix it
Identify which index to keep
Compare the duplicate indexes:
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE relname = 'your_table'
ORDER BY idx_scan DESC;Generally keep the index with more scans or the one that's part of a constraint.
Check for constraints
Don't drop indexes that enforce constraints:
SELECT conname, conindid::regclass
FROM pg_constraint
WHERE conindid = 'your_index'::regclass;Drop the redundant index
Use CONCURRENTLY to avoid table locks:
DROP INDEX CONCURRENTLY schema_name.redundant_index_name;Prevention
- Check existing indexes before creating new ones
- Use `\di+ tablename` in psql to list indexes
- Review ORM-generated migrations for duplicate index creation
- Document why each index exists