В предыдущих уроках мы разбирали antipatterns и индексы. Этот урок — про то, как переписать существующий запрос, оставив логику той же самой, но сделав его в 10-100 раз быстрее. Это не про индексы, это про форму SQL: одну и ту же задачу можно сформулировать пятью разными способами, и planner на каждом построит разный план.

Эта дисциплина в индустрии называется query refactoring. Канонических паттернов немного — 4 покроют ~90% случаев. Все остальные — вариации.

Pattern 1: EXISTS вместо IN с подзапросом

Классика. Два запроса, которые делают одно и то же:

-- Вариант A
SELECT id FROM orders WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'RU'
);

-- Вариант B
SELECT o.id FROM orders o WHERE EXISTS (
  SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.country = 'RU'
);

В большинстве современных версий PostgreSQL planner умеет конвертировать IN (SELECT ...) в semi-join — и оба плана получаются идентичными. Но не всегда:

• Если подзапрос возвращает NULL’ы, IN имеет другую семантику (NULL входит в подзапрос — внешний предикат становится UNKNOWN), и planner обязан проявить осторожность с трансформацией.
• Если подзапрос большой и не возвращает уникальные значения, IN требует материализации distinct-set, а EXISTS останавливается на первом совпадении.

Правило: для проверки «существует ли хотя бы один…» используй EXISTS. Оно явно говорит planner’у: «короткое замыкание после первого match’а». Для случая, когда нужны конкретные id для дальнейшей фильтрации — JOIN ещё лучше (см. следующий pattern).

Pattern 2: JOIN вместо коррелированного подзапроса

Коррелированный подзапрос — это подзапрос, который ссылается на внешнюю таблицу. Пример:

-- Bad: коррелированный подзапрос — выполняется для КАЖДОЙ orders строки
SELECT o.id,
       (SELECT email FROM customers c WHERE c.id = o.customer_id) AS email
FROM orders o
WHERE o.placed_at >= '2024-01-01';

Если planner не справится с трансформацией — это buktrue O(N×M) или nested loop с миллионом подзапросов. Каждый раз идём в customers, ищем по id, возвращаем email. Index Scan ускоряет, но мы всё равно делаем N отдельных запросов.

Fix через JOIN:

SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at >= '2024-01-01';

Planner свободен выбрать Hash Join (для крупных наборов) или Merge Join (если оба отсортированы), которые работают в O(N+M). На больших таблицах — порядок-ускорение.

Когда коррелированный подзапрос всё-таки уместен: если ты выбираешь одно скалярное значение, и подзапрос предельно простой (например, (SELECT MAX(version) FROM v WHERE v.entity_id = e.id)). Planner часто умеет переписать такое в LATERAL или Hash Join. Но как правило — явный JOIN всегда лучше.

Pattern 3: LATERAL для top-N-per-group

Задача: «для каждого customer — три последних заказа». Наивный путь:

-- Bad: window function — корректно, но требует ANALYZE всей orders + сортировка
SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) AS rn
  FROM orders o
) sub
WHERE rn <= 3;

Window function работает на всём orders, считает row_number для каждой строки, а затем фильтрует. Если у тебя 100M orders и 1M customers — это 100M операций для возврата 3M строк.

Fix через LATERAL:

SELECT c.id, c.email, last.id AS order_id, last.placed_at
FROM customers c
JOIN LATERAL (
  SELECT id, placed_at FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY placed_at DESC
  LIMIT 3
) last ON true;

LATERAL = «для каждой строки левой таблицы выполни этот подзапрос». Здесь — для каждого customer один LIMIT 3 поиск. Если есть индекс (customer_id, placed_at DESC) — каждый подзапрос делает 3 index lookup’а. Итого 3M операций вместо 100M.

Когда применять: всегда, когда нужны top-N-per-group и N маленькое (1-10). LATERAL даёт O(N_groups × log M) против O(M) window function.

Pattern 4: Materialized view как кеш дорогих aggregations

SELECT COUNT(*), SUM(total), AVG(total) FROM orders GROUP BY country на таблице 1B строк — это секунды (минимум). Если такой запрос нужен в дашборде каждые 5 секунд — production задохнётся.

CREATE MATERIALIZED VIEW country_stats AS
SELECT
  country,
  COUNT(*) AS orders_total,
  SUM(total_cents) AS revenue_cents,
  AVG(total_cents) AS avg_order_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY country;

CREATE UNIQUE INDEX ON country_stats (country);

Теперь дашборд читает SELECT * FROM country_stats за миллисекунды. Раз в час/день запускаем REFRESH MATERIALIZED VIEW [CONCURRENTLY] country_stats и данные обновляются.

CONCURRENTLY — отдельная история. Обычный REFRESH берёт AccessExclusiveLock на view (запросы к нему блокируются). CONCURRENTLY пишет в shadow-таблицу и атомарно подменяет — старые запросы продолжают читать старую версию. Требует UNIQUE-индекс на view.

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

• Aggregations, которые нужно отдавать быстро, и которые приемлемо устаревшие на минуты-часы.
• Дорогие JOIN’ы для отчётов.
• Pre-computed дашбордовые метрики.

Когда НЕ использовать:

• Когда нужны real-time данные (используй обычный VIEW или прямой запрос).
• Когда REFRESH дороже, чем сам запрос (например, refresh = 10 секунд, а запрос — 1 секунда и нужен раз в день).
• Когда данные часто меняются и stale-window недопустим.

Альтернатива — incremental update. PostgreSQL пока не имеет native incremental materialized views (есть планы), но можно реализовать триггерами или через extension типа pg_ivm. Для read-heavy дашбордов с rare-write — отличная штука.

Bonus pattern: CTE materialization

В PostgreSQL до 12-й версии любой WITH cte AS (...) материализовался — выполнялся отдельно, результат складывался в memory, и затем использовался. С 12-й версии planner может inline-ить CTE (если он используется один раз и не имеет side effects).

Иногда хочется обратного — заставить материализацию: WITH cte AS MATERIALIZED (...). Это полезно, когда:

• CTE сложный, и planner inline-ит его в плохой план.
• CTE используется несколько раз, и пересчитывать его дорого.
• Хочется явно отбросить статистику корреляции для следующих этапов запроса (planner после материализации видит CTE как «таблицу с известным cardinality»).

Обратное — AS NOT MATERIALIZED — заставит planner inline даже там, где он бы не стал.

Сравнительная таблица

Чек-лист

• EXISTS для проверки существования — явная семантика semi-join, короткое замыкание.
• JOIN вместо коррелированного подзапроса — даёт planner свободу выбрать Hash/Merge Join (O(N+M) вместо O(N×log M)).
• LATERAL для top-N-per-group — параметризованный подзапрос для каждой строки левой таблицы; идеален с N=1-10 и подходящим индексом.
• MATERIALIZED VIEW для read-heavy aggregations — кеш с REFRESH (CONCURRENTLY для production).
• CTE с AS MATERIALIZED / NOT MATERIALIZED — явный контроль над inline vs materialization (PostgreSQL 12+).
• Универсальная проверка: EXPLAIN до и после refactoring; сравни actual time и план целиком.