Что делать, если SELECT запрос выполняется слишком долго

Что проверяют

Умеете ли вы сначала измерить, а потом чинить, и понимаете ли, что в Java-приложении время запроса складывается не только из работы базы. Ответ, который сводится к «добавить индекс», засчитывается как угадывание.

Как отвечать

Разделите три места, где теряется время: сам запрос, слой доступа к данным, ожидание соединения.

• База: снять реальный план выполнения с фактическими строками (EXPLAIN ANALYZE в PostgreSQL, аналог в другой СУБД), посмотреть тип доступа, расхождение оценки и факта (обычно устаревшая статистика или плохая селективность), сортировки на диске.
• Слой доступа: включить логирование запросов и посчитать их число на одну операцию. Классика это N+1 из-за ленивых коллекций, лечится join fetch, отдельным запросом по списку идентификаторов или проекцией вместо сущности. Постраничная выборка не должна тянуть всё в память и резать страницу уже там.
• Пул соединений: если растёт ожидание соединения, медленный запрос вторичен, а первична длинная транзакция, которая держит соединение и после чтения.

Дальше уже адресные правки: индекс под условие вместе с сортировкой, отказ от функции над колонкой в условии, keyset-пагинация вместо глубокого OFFSET, транзакция только для чтения.

Где спотыкаются

Вешают индексы на все колонки из условий и соединений. На неселективной колонке планировщик индекс не возьмёт, а запись и обновление подорожают.

Второе: считают виноватым SELECT *. Лишние колонки бьют по объёму передачи и мешают покрывающему индексу, но медленной таблицу на миллионы строк делает не это, а условие без индекса или неудачный тип доступа.

Третье: меряют время в приложении и обвиняют базу. Если запрос в базе занял единицы миллисекунд, а метод сотни, то проблема в ожидании пула, в сети или в маппинге результата, и оптимизация SQL ничего не изменит.