Индексы нужны, но не всегда и не всем.
При разработке вы действительно точно не можете спрогнозировать нагрузку на БД. Но точно и не нужно. Как правило разработчик знает свое приложение, и какие данные из БД ему нужны. Соответственно знает предикаты (блок WHERE), которые будут в тех или иных запросах. Далее, входе разработки, когда будет накоплена некоторая статистика по наиболее востребованным предикатам, можно будет уже продумать какие индексы нужны.
В ходе реальной эксплуатации приложения, даже просто у разных клиентов может возникнуть ситуация, когда у одного все хорошо, а у другого тормозит. Все зависит от профиля нагрузки и наполнения таблиц. Бывает, что таблица DML-нагружена (много изменений) и имеет много индексов - будут тормоза, связанные с дополнительной работой по поддержания индексов в актуальном состоянии. А если ещё и много ограничений целостности...
Второй вариант, когда у одного клиента таблица на 1к строк и ему хорошо без индексов, даже если таблица "горячая" по чтениям. БД просто держит её всегда в кэше.
У другого клиента эта же таблица содержит 100к записей и ему ну очень нужны какие то индексы.
Ситуация когда данные в таблице часто меняются и часто и много запрашиваются, как правило связанны с не верной архитектурой приложения. Тут нужно уже переделывать.
