Оптимизация запросов БД: от плана к изменению запроса

Прямой ответ

Оптимизация запроса начинается с плана выполнения, поэтому изменение делается против измеренного узкого места, а не против предположения о медленном месте.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at > now() - interval '7 days';

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

Избыточные выборки и объём данных

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

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

Избыточные строки. Отсутствие ограничения по количеству возвращает весь набор, поэтому приложение обрабатывает данные, которые не показывает.

Постраничная выборка. Ограничение числа строк на странице сокращает объём передачи, поэтому время ответа определяется размером страницы, а не размером таблицы.

Практическое следствие: запрос без ограничения возвращает объём, растущий вместе с таблицей, поэтому время ответа растёт без изменения самого запроса.

Отдельный аспект — выборка связанных данных отдельными запросами. Обращение к базе для каждой строки результата умножает число запросов на число строк, поэтому время ответа растёт пропорционально объёму выборки. Объединение связанных данных в один запрос либо в соединение устраняет умножение.

Соединения, фильтрация и порядок операций

Порядок фильтрации и соединения определяет объём промежуточных данных, поэтому доля работы приходится на обработку лишнего объёма.

Ранняя фильтрация. Условие, применённое до соединения, сокращает объём строк, участвующих в соединении, поэтому работа соединения уменьшается.

Поздняя фильтрация. Условие, применённое после соединения, обрабатывает полный объём, поэтому работа соединения не сокращается.

Порядок соединения. Планировщик выбирает порядок по статистике, поэтому устаревшая статистика приводит к выбору неоптимального порядка.

Практическое следствие: устаревшая статистика таблицы приводит к оценке объёма, отличающейся от фактического, поэтому план выбирается против неверной оценки.

О создании индексов под фильтр — в статье про индексирование БД; о практике оптимизации SQL — в статье про оптимизацию SQL-запросов.

Проверка результата после изменения

Изменение запроса проверяется повторным планом, поэтому эффект фиксируется по измерению, а не по впечатлению.

Повторный план. План после изменения сравнивается с планом до изменения, поэтому смена операции видна явно.

Фактическое время. Время выполнения после изменения сравнивается с временем до изменения, поэтому сокращение подтверждается.

Поведение на объёме. Проверка на данных, близких к рабочему объёму, показывает, сохраняется ли эффект при росте таблицы.

Практическое следствие: изменение, проверенное на малом объёме данных, может не дать эффекта на рабочем объёме, поэтому проверка на малой выборке не подтверждает результат.

Практика: оптимизация запроса

1. Снимайте план с фактическим временем. План без фактического времени показывает оценку, а не измерение.

2. Находите операцию с основной долей времени. Доля времени указывает на узкое место, поэтому изменение делается против него.

3. Убирайте избыточные столбцы и строки. Объём передачи сокращается, поэтому время ответа не растёт с таблицей.

4. Применяйте фильтр до соединения. Объём строк в соединении сокращается, поэтому работа соединения уменьшается.

5. Обновляйте статистику таблицы. Оценка объёма соответствует факту, поэтому план выбирается против верной оценки.

6. Объединяйте выборку связанных данных. Число запросов не умножается на число строк, поэтому время ответа не растёт пропорционально.

7. Проверяйте результат на рабочем объёме. Эффект подтверждается на данных, близких к рабочему объёму.

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

1. Не изменяйте запрос без плана. Изменение против предположения может не затронуть узкое место.
2. Учитывайте объём при проверке. Проверка на малой выборке не подтверждает эффект на рабочем объёме.
3. Сопоставляйте план с фактическим временем. Оценка планировщика отличается от факта при устаревшей статистике.
4. Фиксируйте время запроса в метриках. Рост времени указывает на изменение объёма данных либо на деградацию плана.
5. Пересматриваем запрос при изменении схемы. Новая схема меняет доступные индексы и порядок операций.

Практический сценарий: запрос замедлился без изменения кода

Контекст: время выполнения запроса выросло за несколько недель, при этом текст запроса не менялся.

1. Диагностика. План с фактическим временем показал последовательное чтение таблицы вместо чтения по индексу. Оценка числа строк в плане отличалась от фактического в десятки раз, поэтому планировщик выбрал последовательное чтение.
2. Причина. Статистика таблицы устарела, поэтому оценка объёма не соответствовала фактическому и план выбирался против неверной оценки.
3. Действия. (а) Обновили статистику таблицы, чтобы оценка соответствовала фактическому объёму; (б) сняли повторный план и убедились в выборе чтения по индексу; (в) сравнили фактическое время до и после обновления и зафиксировали сокращение; (г) убрали избыточные столбцы из выборки, чтобы объём передачи сократился; (д) настроили метрику времени запроса, чтобы деградация обнаруживалась до обращения пользователя.
4. Результат. Запрос выполняется по индексу, поэтому время ответа сократилось и не зависит от роста таблицы.

Вывод: устаревшая статистика приводит к выбору плана против неверной оценки объёма, поэтому запрос замедляется без изменения текста. Обновление статистики и проверка повторного плана возвращают выполнение к чтению по индексу.

FAQ

С чего начинать оптимизацию запроса? С плана выполнения с фактическим временем. План показывает операцию с основной долей времени, поэтому изменение делается против измеренного узкого места.

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

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

Что даёт отказ от избыточных столбцов? Объём передачи сокращается, поэтому время ответа не растёт вместе с таблицей и не зависит от числа неиспользуемых полей.

Источники: PostgreSQL EXPLAIN, PostgreSQL planner statistics, Use The Index Luke — ссылки в шапке.