Làm sao tìm ra index nào đang thừa hoặc không được dùng để xoá bớt?

PostgreSQL đếm số lần mỗi index được dùng trong pg_stat_user_indexes.

SELECT s.relname   AS table_name,
       s.indexrelname AS index_name,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;

Ba nhóm index nên xem xét bỏ:
1. idx_scan = 0 — chưa ai dùng kể từ lần reset thống kê gần nhất.
2. Trùng lặp (redundant prefix) — có (a) trong khi đã có (a, b). Index sau phục vụ luôn mọi truy vấn của index trước.
3. Trùng hoàn toàn — cùng tập cột, cùng thứ tự, chỉ khác tên. Thường do nhiều migration cùng tạo.

Những cạm bẫy phải kiểm trước khi xoá:
- Thống kê tính từ lúc nào? Kiểm tra stats_reset trong pg_stat_database. Nếu vừa restart hoặc vừa pg_stat_reset() cách đây một tuần thì số liệu chưa đủ đại diện — cần ít nhất một chu kỳ nghiệp vụ đầy đủ (bao gồm job cuối tháng, báo cáo quý).
- Thống kê là theo từng node. Trên replica, index phục vụ truy vấn đọc nhưng idx_scan ở primary vẫn bằng 0. Phải cộng gộp từ mọi node.
- Unique index không được xoá theo tiêu chí này — nó đang thực thi ràng buộc dữ liệu dù không phục vụ truy vấn nào.
- Index hỗ trợ foreign key cũng có thể idx_scan thấp nhưng cần thiết để ON DELETE không phải quét toàn bảng con.

Cách xoá an toàn: chạy DROP INDEX CONCURRENTLY trong khung giờ thấp điểm và giữ sẵn câu CREATE INDEX CONCURRENTLY tương ứng để khôi phục ngay nếu latency tăng.