CTE или LATERAL: что выбрать

На следующий день Лена ворвалась в кабинет Макса с горящими глазами:

— Смотри, что я придумала! Тот случай с table2, я нашла быстрый способ получить несколько агрегатов без отдельных подзапросов!

На экране ее ноутбука красовался новый вариант запроса.

EXPLAIN ANALYZE
WITH aggregated_t2 AS (
    SELECT user_id,
        max(value) AS max_val,
        avg(new_numeric_field) AS avg_num,  -- Новое поле!
        min(new_date_field) AS min_date     -- И ещё одно!
    FROM table2
    GROUP BY user_id
)
SELECT avg(t1.value), t2.max_val, t2.avg_num, t2.min_date, avg(t3.value), u.id
FROM users u
  JOIN table1 t1 ON t1.user_id = u.id
  JOIN aggregated_t2 t2 ON t2.user_id = u.id  -- Вот где магия!
  JOIN table3 t3 ON t3.user_id = u.id
GROUP BY u.id, t2.max_val, t2.avg_num, t2.min_date;

— Видишь? — торжествующе произнесла Лена. — Вместо трёх подзапросов — одно CTE! И план показывает, что данные из table2 берутся за один проход!

Макс одобрительно кивнул, скрывая улыбку. Лена явно демонстрировала успехи.

— Ты молодец! — сказал он, — Классный результат. Кстати, есть еще и альтернативный вариант.

— Есть еще варианты?

— Да, можно использовать LATERAL JOIN. Вот смотри.

EXPLAIN ANALYZE
SELECT avg(t1.value), t2_agg.max_val, t2_agg.avg_num, t2_agg.min_date, avg(t3.value), u.id
FROM users u
  JOIN table1 t1 ON t1.user_id = u.id
  JOIN LATERAL (
    SELECT
        max(value) AS max_val,
        avg(new_numeric_field) AS avg_num,
        min(new_date_field) AS min_date
    FROM table2 t2
    WHERE t2.user_id = u.id
) t2_agg ON true
JOIN table3 t3 ON t3.user_id = u.id
GROUP BY u.id, t2_agg.max_val, t2_agg.avg_num, t2_agg.min_date;

— Хм и чем это отличается от моего варианта? — Нахмурилась Лена. — Ты мой подзапрос из CTE убрал за этот странный JOIN.

— Почти. Главное отличие, что теперь вместо группировки по user_id используется фильтрация по этому полю внутри подзапроса. Это очень важное преимущество lateral join - возможность обращаться к полям других таблиц запроса до соединения результатов.

— Интересный вариант. Спасибо, я запомню его. Кстати, план запроса у тебя получается компактнее, однако время выполнения немного больше моего.

— Да, потому что твой вариант с CTE читает большую таблицу линейно (Parallel Seq Scan) и дальше использует агрегацию в памяти (HashAggregate), а LATERAL хоть и использует индекс по user_id (Bitmap Heap Scan), но использует множество отдельных чтений. В этом разница между ними.

— Но все говорят, что seqScan это плохо, индексы же специально созданы для ускорения запросов!

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

• Предварительная агрегация часто выгодна, когда нужна значительная часть групп

• Коррелированный LATERAL часто выигрывает на небольшом внешнем наборе при хорошем индексе внутренней таблицы

Это не свойства синтаксиса сами по себе: CTE может быть встроен планировщиком, а LATERAL может получить разные способы доступа. Всегда проверяйте фактический план.

Лена задумчиво кивнула. В её глазах разгорался огонек настоящего исследователя баз данных.

Практика

Воспроизведите оба варианта на тестовых данных и меняйте долю пользователей, для которых нужны агрегаты. Сравнивайте не название узла само по себе, а actual rows, loops, обращения к буферам и полное время. CTE и LATERAL выражают разные стратегии доступа; победитель меняется вместе с объёмом входа и индексами.