Подзапросы и CTE в SQL: читаемость и производительность

Прямой ответ

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

WITH user_totals AS (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
)
SELECT u.user_id, u.total
FROM user_totals u
WHERE u.total > 1000;

Блок CTE вычисляется один раз и используется внешним запросом, поэтому промежуточный результат не пересчитывается для каждой строки. Об агрегации — в статье про GROUP BY в SQL.

CTE и читаемость запроса

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

Именованный блок. Промежуточный результат получает имя, поэтому внешний запрос ссылается на него как на таблицу.

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

Повторное использование. Блок CTE может использоваться несколько раз во внешнем запросе, поэтому дублирование подзапроса устраняется.

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

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

Correlated-подзапрос и стоимость

Correlated-подзапрос ссылается на столбец внешнего запроса, поэтому выполняется для каждой строки внешнего запроса и замедляет выборку.

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

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

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

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

Материализация CTE и план запроса

Материализация фиксирует результат блока CTE до выполнения внешнего запроса, поэтому планировщик не может протолкнуть фильтр внутрь блока.

Материализованный блок. Результат CTE вычисляется полностью и сохраняется, поэтому фильтр внешнего запроса не сокращает объём вычисления блока.

Нематериализованный блок. Блок CTE подставляется во внешний запрос, поэтому планировщик применяет фильтр до вычисления.

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

Практическое следствие: материализованный блок CTE с большим промежуточным результатом вычисляется полностью даже при малом числе строк во внешнем запросе.

О оконных функциях как замене подзапросов — в статье про оконные функции SQL; о разборе плана — в статье про оптимизацию SQL-запросов.

Практика: применение подзапросов и CTE

1. Выносите промежуточные шаги в CTE. Запрос читается по шагам, поэтому изменение вносится с меньшим риском ошибки.

2. Заменяйте correlated-подзапрос соединением. Подзапрос выполняется один раз вместо числа строк внешнего запроса.

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

4. Используйте рекурсивный CTE для иерархий. Обход дерева выполняется запросом, поэтому цикл на стороне приложения не требуется.

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

6. Сопоставляйте объём промежуточного результата с числом строк внешнего запроса. Большой промежуточный результат при малом числе строк указывает на избыточное вычисление.

7. Фиксируйте длительность запроса в метриках. Рост длительности указывает на рост объёма данных либо на смену плана.

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

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

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

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

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

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

FAQ

Зачем использовать CTE вместо вложенных подзапросов? Именованные блоки делают запрос читаемым по шагам и позволяют повторно использовать промежуточный результат без дублирования подзапроса.

Чем опасен correlated-подзапрос? Он выполняется для каждой строки внешнего запроса, поэтому длительность растёт пропорционально числу строк.

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

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

Источники: PostgreSQL WITH, subquery expressions, USING EXPLAIN — ссылки в шапке.