LATERAL join

Что LATERAL меняет во FROM

Прежде чем тянуться к N+1 циклу или нагромождать три коррелированных подзапроса, спроси себя: зависит ли каждая строка результата от текущего значения внешней строки? Если да — LATERAL сделает из этого один запрос вместо многих. Обычно табличные выражения в клаузе FROM независимы: FROM users u, orders o не даёт o знать ничего об u — условие join связывает их потом. Подзапрос во FROM такой же; он не видит другие таблицы. LATERAL убирает эту стену. LATERAL-подзапрос (или функция) во FROM может ссылаться на колонки элементов FROM, идущих перед ним, и вычисляется по разу на строку этих более ранних элементов.

В этом вся идея: коррелированный подзапрос, но во FROM, а не в SELECT или WHERE, поэтому он может вернуть несколько колонок и несколько строк на внешнюю строку, а не единственный скаляр.

-- Для каждого пользователя его 3 последних заказа — целые строки, не агрегаты
SELECT u.id, u.name, recent.id AS order_id, recent.created_at
FROM users u
CROSS JOIN LATERAL (
  SELECT o.id, o.created_at
  FROM orders o
  WHERE o.user_id = u.id        -- ссылается на u — законно лишь благодаря LATERAL
  ORDER BY o.created_at DESC
  LIMIT 3
) AS recent;

Внутренний подзапрос ссылается на u.id, что разрешено лишь из-за LATERAL. Для каждой строки пользователя Postgres выполняет этот маленький запрос «топ-3 заказа для этого пользователя» и приджойнивает (до 3) результаты обратно. Это каноническое решение top-N на группу, и нет краткого нелатерального эквивалента, возвращающего целые строки.

CROSS против LEFT JOIN LATERAL и set-returning функции

CROSS JOIN LATERAL (...) выбрасывает внешнюю строку, если её подзапрос ничего не вернул — пользователь без заказов исчезает. Чтобы оставить каждую внешнюю строку (NULL-расширенной при пустом подзапросе), используй LEFT JOIN LATERAL (...) ON true. ON true — обязательный синтаксис: латеральному left join всё равно нужен ON, а корреляция уже живёт внутри подзапроса, так что условие join — просто «всегда».

LATERAL также сияет с set-returning функциями — функциями, возвращающими много строк, как jsonb_array_elements или generate_series. Чтобы развернуть JSONB-массив-колонку в строки, по одной на элемент на родительскую строку:

-- По строке на каждый тег в JSONB-массиве tags каждого товара
SELECT p.id, tag.value AS tag
FROM products p
CROSS JOIN LATERAL jsonb_array_elements_text(p.tags) AS tag(value);

Вызов функции ссылается на p.tags, что неявно латерально, когда функция во FROM ссылается на более раннюю таблицу — Postgres даже позволяет опустить ключевое слово для функций, но писать его яснее. Этот паттерн «развернуть на строку» повсюду, как только ты хранишь массивы или JSON (раздел 06 глубоко копает JSONB).

Почему LATERAL часто бьёт коррелированный скалярный подзапрос

Иногда можно подделать построчные результаты коррелированными скалярными подзапросами в SELECT — но каждый возвращает единственное значение, и нужен отдельный подзапрос на колонку, каждый пересканирует те же заказы. Хочешь id последнего заказа и дату и сумму? Это три коррелированных подзапроса, три скана. Один LATERAL возвращает все три колонки из одного вычисления на строку. Меньше сканов, меньше round trip’ов, чем у N+1 цикла на стороне приложения, и планировщик может выбрать индексный nested loop, где внутренний LIMIT 3 читает лишь несколько индексных записей на пользователя — превращая 4-секундный N+1 эндпоинт в запрос менее 100 мс.