Диагностика медленных запросов
Предпосылки: EXPLAIN, планировщик запросов (оценки и выбор плана), B-tree (индексы и типы сканов), память и spill, prepared statements (parameter sensitivity).
← Prepared statements | Репликация →
Дежурный инженер получает алерт: p99 latency эндпоинта /orders вырос с 50 мс до 3 секунд. В логах — один и тот же SELECT. Индекс на таблице есть, данных не стало больше, код не менялся. Первый инстинкт — «база тормозит», но база обрабатывает тысячи других запросов нормально. Проблема конкретна: этот запрос делает слишком много работы или делает нужную работу слишком дорогим способом. Чтобы ускорить его, сначала нужно понять, что именно в нём дорого и почему оптимизатор выбрал такой путь.
Шаг 1: получить факты, а не догадки
Для SELECT почти всегда достаточно:
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;ANALYZE выполняет запрос реально. Для UPDATE/DELETE/INSERT это значит реальные изменения. Если нужно посмотреть план записи, обычно выполняют EXPLAIN (ANALYZE, BUFFERS) в транзакции и откатывают её, либо проверяют на тестовой базе с похожими данными.
Шаг 2: найти узел, где «сгорает» время
В выводе EXPLAIN ANALYZE ищите:
- узел с большим
actual time, - узел с большим
actual rows, - узел с большим
loops(особенно внутриNested Loop).
Частый случай: «всё выглядит нормально, но Nested Loop повторяет внутренний скан тысячи раз». Тогда дорого не то, что вы видите на одном проходе, а число повторов.
Шаг 3: проверить ошибки оценок (rows vs actual rows)
Если планировщик сильно ошибся в rows, он почти наверняка выбрал неверную стратегию:
- вместо
Index ScanвыбралSeq Scan(или наоборот), - выбрал не тот join алгоритм,
- выделил не тот объём памяти под сортировку/хеш и ушёл в spill.
Когда rows расходится на порядок, следующий вопрос всегда один: почему оптимизатор думает, что строк мало/много?
Типичные причины:
- устаревшая статистика (после массовых изменений данных),
- коррелированные колонки (оптимизатор предполагает независимость),
- условие «прячет» колонку в выражении, и оценка селективности становится грубой,
- параметризованный запрос получил generic plan и перестал учитывать конкретное значение (см. prepared statements).
Шаг 4: понять, где упираемся — в диск или в CPU
BUFFERS помогает отличить «всё читаем с диска» от «много считаем»:
- большой
shared readна дорогом узле обычно означает I/O‑проблему (данные холодные или план вынуждает читать много страниц), - если
shared readпочти нет, а время большое — часто это CPU/память: сортировки, хеши, вычисления выражений, большое количество строк.
Шаг 5: выбрать действие по симптомам
1) Слишком много строк попадает в тяжёлый узел
Если в сортировку, join или агрегат приходит «миллион строк», первое, что стоит проверить — можно ли сократить ввод раньше:
- добавить/уточнить фильтр,
- перестроить join так, чтобы селективная часть применялась раньше,
- убрать
SELECT *, если он тащит ширину строк и раздувает сортировки/хеши.
Когда проблема в порядке соединения, помогает понимание join ordering и поведения CTE.
2) План выглядит правильным, но всё равно много shared read
Это обычно означает, что запрос читает много страниц таблицы или индекса. Дальше полезно различать два сценария:
- данные действительно нужны (например, отчёт по большой доле таблицы) — тогда seq scan может быть нормальным, а проблема скорее в том, что это тяжёлая операция по смыслу;
- данные нужны «точечно», но план всё равно читает много страниц — тогда чаще всего не хватает подходящего индекса или условие не может использовать индекс.
Про индексы под форму запроса — в B-tree и в заметках по типам индексов.
3) В плане появился spill (temp I/O)
Если Sort Method: external ... или у hash‑узла Batches > 1, запрос начал писать во временные файлы. Это почти всегда объясняет «внезапно стало медленно» и ведёт к двум вопросам:
- почему операция получила такой большой вход,
- можно ли избежать сортировки/хеша или уменьшить вход (часто это эффективнее, чем рассчитывать на память).
Подробности того, как это выглядит и почему так происходит — в память и spill.
4) «Тот же запрос», но скорость гуляет от параметров
Если разные значения параметра дают разную селективность, план, который хорош для одного значения, может быть катастрофой для другого. Это особенно заметно, когда запрос выполняется как prepared statement и переходит в generic plan.
Признак — один и тот же plan shape в приложении при принципиально разных входах. Разбор причин и терминов — в prepared statements.
Sources
- PostgreSQL Documentation (пример: v16): Using EXPLAIN. https://www.postgresql.org/docs/16/using-explain.html
- PostgreSQL Documentation (пример: v16):
EXPLAIN. https://www.postgresql.org/docs/16/sql-explain.html