Определение неиспользуемых индексов в PostgreSQL

Индексы не всегда бывают полезными. При добавлении индекса в таблицу, ускоряется чтение данных, но есть побочный эффект: при изменении данных в этой таблицы, одновременно будет происходить обновление индекса, т.е. все вставки, изменения или удаления строк будут происходить медленнее, чем это было до добавления индекса. По сути, приходится жертвовать производительностью в одном месте ради ускорения в другом.

Бывает, что индексы по какой-то причине были добавлены, но не используются и не будут использоваться в будущем. Полезно избавляться от таких индексов, чтобы освободить место на жестком диске, и ускорить изменение таблиц. Следующий запрос в этом поможет:

Он отображает список всех индексов, отсортированных по количеству сканирований (3 колонка). Если у каких-либо индексов число сканирований (index_scans_count) равно нулю, то стоит подумать об их удалении. Но, подождите…

Часто, в интернете можно встретить другие версии этого запроса. В некоторых из них выбираются только те индексы, где сканирований никогда не было, и содержатся только данные о наименовании индекса и о названии таблицы, для которой он применен. Этого мало.

При рассмотрении кандидатов на удаление, нужно проверить:

• Может быть таблица вообще не используется или планировщик избегает индекс по какой-то другой причине?
• Сколько места занимает индекс? Может быть он вообще не стоит внимания в данный момент?
• Какое количество последовательных чтений по таблице? Возможно нужно добавить дополнительные поля в индекс чтобы исключить последовательные чтения.

Сброс статистики.

Бывает так, что индекс когда-то использовался, но нет уверенности, что индекс используется до сих пор. Тогда нужно заново собрать статистику. Очищаем статистику в базе:

Теперь, если снова запустить запрос для определения использования индексов, там останутся названия таблиц, но все данные о сканированиях обнулятся. Можно продолжать работу — база данных снова будет собирать статистику и через какое-то время можно будет снова определить неиспользуемые индексы.