CTE как структура запроса

Code review SQL-запросов в командах обычно идёт хуже, чем review кода. Причина проста: SQL-запрос — это дерево, а мы читаем глазами слева направо, сверху вниз. На больших запросах эта диссонанс становится больно.

CTE — это инструмент, который помогает спрятать дерево в линейную последовательность шагов. Если правильно его использовать, запрос превращается в читаемый код, а не в загадку.

В этом уроке — практические паттерны, которые я лично использую в production-системах, и антипаттерны, которые встречал и просил переписать.

Паттерн 1: «один шаг — один CTE»

Каждый CTE должен решать ровно одну логическую задачу. Если в названии шага есть «и» — это два шага.

Пример хорошей декомпозиции:

Каждый из трёх CTE можно прочитать и понять отдельно. Финальный SELECT — это последний шаг сборки. На code review такой запрос читается за минуту.

Паттерн 2: имена с глагольным префиксом

Хороший CTE-name отвечает на вопрос «что это» существительным, отражающим бизнес-смысл. Иногда — глагольное имя для фильтра. Что точно НЕ хорошо: tmp, t1, sub1, data, result.

Полезные конвенции:

• Существительное в множественном числе для CTE, возвращающих много строк: paid_orders, vip_customers, top_products.
• Глагольная форма для шагов-фильтров: filter_to_active, narrow_to_eu.
• _with_<dimension> для CTE, добавляющих информацию: orders_with_revenue, customers_with_country_name.
• final_-prefix для последнего шага, который пойдёт в SELECT (полезно, когда непонятно с первого взгляда).

Не превращай это в догму — называй так, чтобы автору review через полгода было понятно. Это всегда выигрышнее любых жёстких правил.

Паттерн 3: CTE для отладки

В разработке часто полезно «отрезать» промежуточный шаг и посмотреть, что он возвращает. С CTE это тривиально:

WITH step_1 AS (...),
     step_2 AS (...),
     step_3 AS (...)
-- SELECT * FROM step_1;  -- проверяем первый шаг
-- SELECT * FROM step_2;  -- проверяем второй
SELECT * FROM step_3;     -- основной запрос

Это сильно ускоряет debug. Подзапросы в FROM такой возможности не дают — придётся либо комментировать большие куски, либо копировать запрос в отдельное окно.

Антипаттерн 1: CTE ради CTE

Иногда новички начинают использовать CTE везде, даже там, где он не нужен. Если запрос помещается в 5-10 строк и читается за один взгляд — CTE его только раздует.

Плохо:

WITH customers_data AS (
  SELECT * FROM customers
)
SELECT id, email FROM customers_data WHERE country = 'RU';

Это просто SELECT id, email FROM customers WHERE country = 'RU'. CTE здесь ничего не даёт, только лишний шум.

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

Антипаттерн 2: дублирование логики внутри CTE

Бывает так, что в двух CTE написана одна и та же подзадача. Тогда нужно вынести её в третий CTE и переиспользовать.

Плохо:

WITH ru_paid AS (
  SELECT o.id FROM orders o JOIN customers c ON c.id = o.customer_id
  WHERE c.country = 'RU' AND o.status = 'paid'
),
ru_delivered AS (
  SELECT o.id FROM orders o JOIN customers c ON c.id = o.customer_id
  WHERE c.country = 'RU' AND o.status = 'delivered'
)
SELECT ...;

Хорошо:

WITH ru_orders AS (
  SELECT o.id, o.status FROM orders o JOIN customers c ON c.id = o.customer_id
  WHERE c.country = 'RU'
)
SELECT
  (SELECT COUNT(*) FROM ru_orders WHERE status = 'paid') AS paid_count,
  (SELECT COUNT(*) FROM ru_orders WHERE status = 'delivered') AS delivered_count;

Антипаттерн 3: оптимизация без замера

«Я слышал, CTE медленнее подзапроса». Иногда это пытаются превратить в правило стиля и переписывать читаемые CTE в страшные nested-подзапросы «ради производительности». Это ошибка.

В PostgreSQL 12+ для не-рекурсивных, один раз используемых CTE без MATERIALIZED план идентичен подзапросу в FROM. Никакой потери. Зато читаемость теряется при переписывании сразу.

Правильный подход:

1. Сначала пиши читаемо. CTE как декомпозиция.
2. Если запрос медленный — измерь через EXPLAIN ANALYZE.
3. Только потом думай об оптимизации формы. И часто проблема не в CTE, а в отсутствии индекса или плохой статистике.

Когда читаемость важнее производительности

Никогда — если запрос работает за 50 ms в OLTP-системе на 100K строк. Производительность тут уже достаточна, и удобство сопровождения важнее любого микро-выигрыша.

И почти всегда — если запрос работает за 5 минут в аналитической системе на 100M строк. Тут каждый процент скорости конвертируется в стоимость инфраструктуры. И уродливая форма ради -30% времени становится разумной.

Граница между «никогда» и «почти всегда» — это знание твоего production-нагрузочного профиля. Без него любые рассуждения о «правильной форме SQL» — это абстракция.

Финальный паттерн: «inверсия пирамиды»

Когда я начинаю писать сложный запрос с нуля, я делаю так:

1. Пишу финальный SELECT — что именно нужно показать клиенту.
2. Понимаю, какие промежуточные сущности туда входят.
3. Пишу CTE для каждой сущности, идя «вверх» — от конца к началу.
4. Каждый CTE опирается на ранее объявленные (если есть) или на базовые таблицы.

Это похоже на TDD: сначала thinking about the API (финальный результат), потом — about implementation (как его собрать).

Чек-лист

• Один CTE — одна логическая задача. Имя — содержательное существительное.
• CTE добавляет ценность, когда упрощает чтение либо переиспользуется. Простое переименование — не повод.
• Никогда не оптимизируй CTE без EXPLAIN ANALYZE-замера. В PG 12+ inлайнинг убрал старые проблемы.
• Используй CTE как инструмент отладки — закомментируй финальный SELECT и проверь промежуточные шаги.
• Производительность важнее читаемости только если ты её замерил и нашёл реальную проблему.