Как решать проблему падения производительности запроса к базе данных после оптимизации и создания индексов

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

Умеете ли вы диагностировать по плану запроса, а не гадать, и знаете ли, что индекс может сделать хуже. Следом обычно просят прочитать EXPLAIN ANALYZE или объяснить, почему запрос быстрый в консоли и медленный из приложения.

Как отвечать

Начните с измерений: сравните планы до и после через EXPLAIN (ANALYZE, BUFFERS) в PostgreSQL, найдите тяжёлые запросы в pg_stat_statements или журнале медленных запросов. Главное, на что смотреть: расхождение оценки строк с фактическим числом. Дальше типичные причины:

• устаревшая статистика: после массовой загрузки планировщик думает, что строк мало, и выбирает не тот путь. Лечится ANALYZE, заодно проверьте, успевает ли autovacuum;
• неудачный индекс: низкая селективность, неверный порядок колонок в составном индексе, функция над колонкой в WHERE. Планировщик берёт новый индекс, а тот читает полтаблицы случайным доступом;
• обобщённый план: pgJDBC после пяти выполнений (prepareThreshold) переходит на серверный prepared statement, и PostgreSQL может закрепить generic plan, плохой для редких значений параметра. Отсюда «в psql быстро, из приложения медленно»;
• несовпадение типов: драйвер SQL Server по умолчанию шлёт строки как nvarchar, и сравнение с колонкой varchar отключает поиск по индексу. Лечится sendStringParametersAsUnicode=false;
• запись: каждый индекс замедляет INSERT и UPDATE, а раздувание после массовых правок требует VACUUM или REINDEX.

На стороне Java проверьте, что Hibernate генерирует тот SQL, который вы ожидаете (spring.jpa.show-sql или логирование через прокси источника данных), нет ли N+1, и не ждёт ли запрос соединения в HikariCP. Во втором случае медленный не запрос, а пул.

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

Добавляют ещё индексов. Проблема часто в статистике или плане, а лишний индекс только утяжеляет запись.

Замеряют один прогон. Первый запуск читает с диска, второй из кэша, и «ускорение» оказывается прогретым буфером.

Создают индекс на проде обычным CREATE INDEX, который блокирует запись в таблицу. Нужен CREATE INDEX CONCURRENTLY, а после его сбоя остаётся невалидный индекс, который надо найти и пересоздать.