Я сталкивался со следующими подходами по добавлению индексов:

  1. Что-то стало тормозить — добавляем индекс “на глаз”. По умолчанию добавляем на что-то очевидное.
  2. Мониторим slow query, анализируем запросы, добавляем индекс по анализу статистики (это обычно выделенные DBA).
  3. Добавляем индексы на все поля по умолчанию, а там авось что пригодится.

Первые два варианта не очень оперативны — индекс создается уже после того, как релиз на проде. Да, можно подстелить соломку и прогнать хорошие перф-тесты на копии базы прода, но уже очень хорошо, когда у вас есть хоть какие-то перф-тесты, а чтобы еще и актуализированы были сразу под релиз — это фантастика какая-то.

На прошлой работе написал велосипед, который извлекал все SQL-запросы из юнит-тестов и прогонял их через EXPLAIN ANALYZE на копии продовой базы. Это работало весьма неплохо, но доавтоматизировать это до CI-стадии я так и не успел.

Что-то похоже есть и в опенсорсе — эта утилита делает примерно то же самое, только еще и сама создает “гипотетические” индексы и проверяет, что они дадут ускорение запросов. Использовать ее на проекте не смог, т.к. расширение для этих гипотетических индексов нельзя поставить на AWS-овский Postgres :/