Как исследовать и оптимизировать медленный запрос в базе данных?
Что проверяют
Есть ли у вас метод и начинаете ли вы с измерения.
Как отвечать
По шагам.
1. Найти проблемный запрос. Не гадать: pg_stat_statements показывает, кто съедает больше всего суммарного времени, лог медленных запросов ловит разовые всплески. Часто выясняется, что тормозит не тот запрос, на который думали.
2. Посмотреть план: EXPLAIN (ANALYZE, BUFFERS). Смотреть на самый дорогой узел, на расхождение оценки и факта, на последовательный просмотр большой таблицы и на сортировки, ушедшие на диск.
3. Определить причину. Типичные: нет подходящего индекса; индекс есть, но не применяется из-за функции над колонкой или несовпадения типов; выбирается слишком много строк; устаревшая статистика; неудачный порядок соединений.
4. Исправлять по возрастанию стоимости: переписать запрос, потом индекс (составной с верным порядком, частичный, покрывающий), потом предподсчёт (материализованное представление), и только потом настройки.
5. Проверить, а запрос ли виноват. Он может не выполняться, а ждать: блокировки, длинная транзакция рядом, исчерпанный пул соединений. Это видно в pg_stat_activity, а не в плане.
6. Замерить снова на боевом объёме.
Отдельно про N+1: тысяча быстрых запросов вместо одного лечится изменением выборки, а не оптимизацией каждого.
Где спотыкаются
Начинают с добавления индексов.
Не различают «медленно выполняется» и «долго ждёт».
Забывают, что функция над колонкой отключает индекс.
Оптимизируют на пустой тестовой базе.