Оптимизация SQL-запросов: читаем план и убираем лишнюю работу

Прямой ответ

Оптимизация запроса начинается не с индекса, а с понимания, какую работу выполняет база. Инструмент — EXPLAIN ANALYZE: он показывает выбранный план и фактическое время каждого узла.

Порядок действий:

1. Замерить. Найти медленный запрос через журнал медленных запросов или статистику, а не по ощущениям.
2. Прочитать план. Определить, где тратится время: сканирование, соединение, сортировка, агрегация.
3. Убрать лишнюю работу. Часто дешевле изменить запрос, чем добавить индекс.
4. Добавить индекс под конкретный узел плана. Индекс без привязки к плану — угадывание.
5. Перепроверить. Сравнить фактическое время и число прочитанных блоков до и после.

Что искать в плане:

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name, sum(i.price)
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= now() - INTERVAL '30 days'
GROUP BY o.id, c.name;

Оценка разницы между фактическим и предполагаемым числом строк — самый информативный признак. Если планировщик ожидает 10 строк, а получает 100 000, он выберет вложенный цикл вместо хеш-соединения. Лечение — обновление статистики (ANALYZE) или увеличение точности оценки для колонки. Подробно о чтении планов — в статье про планы запросов PostgreSQL.

Практика: типичные причины медленных запросов

1. N+1 в слое ORM. Один запрос получает список, затем по запросу на каждую запись. На 500 записях это 501 обращение. Лечение — жадная загрузка связей или явное соединение; диагностика — в статье про проблему N+1.

2. Выборка лишних столбцов. SELECT * читает всю ширину строки. В таблицах с большими текстовыми полями это основная стоимость.

3. Отсутствие ограничения по времени. Запрос без условия на дату читает всю историю, хотя нужен последний месяц. Индекс по дате не помогает, если условие не задано.

4. Функция над колонкой в условии.

-- индекс не используется
WHERE date_trunc('day', created_at) = '2026-10-01'
-- используется индекс по created_at
WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02'

5. Сортировка без индекса. Если сортировка совпадает с порядком индекса, база читает данные уже упорядоченными. Иначе нужна отдельная операция, которая при большом объёме уходит на диск.

6. Неэффективный составной индекс. Порядок колонок в индексе имеет значение: сначала равенство, затем диапазон. Индекс по (status, created_at) служит запросу с фильтром по статусу и диапазону дат; обратный порядок работает хуже. О выборе индексов — в статье про индексы PostgreSQL.

7. Устаревшая статистика. После массовой загрузки или удаления распределение значений меняется, и планировщик принимает неверные решения. Регулярный ANALYZE для таких таблиц настраивают отдельно.

Рекомендации:

1. Оптимизируйте по измерению. Запрос, который «чувствуется медленным», может занимать 30 мс, а реальный узел — другой запрос, выполняющийся тысячи раз в минуту.
2. Считайте общее время, а не время одного вызова. Запрос за 5 мс, выполняемый 20 000 раз в минуту, дороже запроса за 2 секунды раз в час.
3. Проверяйте число прочитанных блоков. Параметр BUFFERS показывает фактический объём чтения; он информативнее времени, которое зависит от кэша.
4. Не добавляйте индексы «на всякий случай». Каждый индекс замедляет запись и занимает место. Индекс должен соответствовать конкретному узлу плана.
5. Разделяйте операционные и аналитические запросы. Тяжёлая агрегация на основной базе влияет на все операции — о различии нагрузок в статье про OLTP и OLAP.

Практический сценарий: экран отчёта открывался 22 секунды

Контекст: отчёт по заказам за период выполнялся 22 секунды при объёме 3 млн строк. Индексы по датам были.

1. Чтение плана. Планировщик оценивал выборку за 30 дней в 40 строк, фактически возвращалось 210 000. Из-за этой оценки было выбрано вложенное соединение, выполненное 210 000 раз.
2. Причина 1. Статистика по таблице не обновлялась после массовой загрузки архивных данных; доля недавних записей оценивалась неверно.
3. Причина 2. Соединение с таблицей позиций выполнялось до агрегации, поэтому объём промежуточных строк составлял 1,8 млн.
4. Действия. (а) Выполнили ANALYZE и повысили точность статистики для колонки даты; (б) агрегацию по позициям перенесли в подзапрос, чтобы соединять уже агрегированные данные; (в) добавили составной индекс (created_at, customer_id), покрывающий фильтр и соединение; (г) убрали SELECT * в пользу трёх нужных колонок.
5. Результат. Время снизилось до 1,1 секунды, число прочитанных блоков — в 14 раз. Дальнейшее ускорение потребовало бы переноса отчёта в хранилище, что для текущей нагрузки не понадобилось.

Вывод: большинство медленных запросов замедлены не отсутствием индекса, а неверной оценкой объёма данных или лишней работой в плане. Чтение плана даёт ответ быстрее, чем перебор индексов.

FAQ

Когда Seq Scan — это нормально? Когда читается значительная часть таблицы. Обращение к индексу для каждой строки дороже последовательного чтения, и планировщик правильно выбирает полный просмотр.

Почему добавленный индекс не используется? Возможные причины: условие не совпадает с порядком колонок индекса, над колонкой применяется функция, доля отбираемых строк велика, статистика устарела. Ответ виден в плане и в оценке стоимости.

Что делать, если план хороший, но запрос всё равно медленный? Проверить объём чтения и попадание в кэш, блокировки и ожидание других транзакций, а также нагрузку на устройство. Время выполнения включает ожидание, которое план не показывает.

Стоит ли использовать подсказки планировщику? В PostgreSQL механизм подсказок отсутствует по проектированию: считается, что корректная статистика даёт лучший результат. Практика — влиять на план через изменение запроса и обновление статистики.

Источники: PostgreSQL docs (using EXPLAIN, index types, query settings) — ссылки в шапке.