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