Кожен індекс уповільнює запис, займає пам'ять і диск, подовжує бекапи й вакуум. З роками в базі накопичуються індекси, створені «про всяк випадок» чи для запитів, яких уже немає.
1. Невикористані:
SELECT s.relname AS table, s.indexrelname AS index, 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;
2. Дублікати - однакові колонки в тому самому порядку:
SELECT indrelid::regclass AS table, array_agg(indexrelid::regclass) AS indexes
FROM pg_index
GROUP BY indrelid, indkey, indexprs::text, indpred::text
HAVING count(*) > 1;
3. Надлишкові за лівим префіксом. Індекс (user_id) зайвий, якщо є (user_id, created_at): другий обслуговує ті самі запити. Виняток - коли перший унікальний чи значно менший і використовується для гарячого запиту.
Перш ніж видаляти:
- Переконатися, що статистика збиралася достатньо довго (щомісячні звіти, сезонні задачі).
- Перевірити всі репліки - статистика окрема на кожному сервері.
- Перевірити, чи індекс не підтримує зовнішній ключ (каскадне видалення без нього стане повним переглядом таблиці).
- Видаляти через
DROP INDEX CONCURRENTLYі мати скрипт для швидкого відновлення.
Інструменти: запити з PostgreSQL Wiki, pgstattuple, звіти в pganalyze, Percona Monitoring. Аналіз варто повторювати регулярно, а не раз на кілька років після проблем зі швидкістю.
