CTE vs подзапросы: когда что

Что такое CTE

Common Table Expression — именованный подзапрос через WITH. Делает код читаемым, особенно для многошаговых трансформаций.

WITH active_users AS (
  SELECT user_id FROM events WHERE created_at > now() - interval '30 days'
),
revenue AS (
  SELECT user_id, SUM(amount) AS total FROM orders GROUP BY 1
)
SELECT au.user_id, r.total
FROM active_users au
LEFT JOIN revenue r USING (user_id);

CTE vs subquery: производительность

Раньше в PostgreSQL CTE были fence — оптимизатор не мог их инлайнить. С PG12 поведение изменилось — CTE инлайнятся по умолчанию если используются один раз.

• Inlined CTE (PG12+) = subquery, оптимизатор сам решит план
• Materialized CTE = всегда вычисляется заранее, кладётся в temp memory

WITH expensive AS MATERIALIZED (
  SELECT ...
)
SELECT * FROM expensive WHERE ...;

Когда использовать MATERIALIZED

• CTE дорогая И используется > 1 раза
• Нужно гарантировать одно вычисление
• Recursive CTE (всегда materialized)

Кейс из Halyk Bank

Аналитик написал CTE на 500K строк с 6 JOIN-ами, использовал её 3 раза в финальном SELECT. Запрос работал 4 минуты. Решение: пометить CTE как MATERIALIZED — стало 40 секунд.