Що краще: JOIN чи підзапит?

PostgreSQL вміє Unnesting (розплющування) — перетворює підзапити на JOIN на рівні плану виконання. Це означає, що написаний вами підзапит фізично виконується як з'єднання таблиц...

🟢 Junior Level

Однозначної відповіді немає — залежить від ситуації. У більшості сучасних баз даних (включаючи PostgreSQL) оптимізатор часто перетворює підзапити на JOIN, тому продуктивність може бути однаковою.

Просте правило:

JOIN — коли потрібні дані з обох таблиць
Підзапит (EXISTS/IN) — коли потрібно тільки перевірити існування

Приклад:

-- JOIN: отримати користувачів ТА їхні замовлення
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

-- EXISTS: перевірити, чи є замовлення у користувача
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Коли що використовувати:

Потрібні колонки з обох таблиць → JOIN
Потрібна лише фільтрація → EXISTS/IN
Складна логіка з агрегацією → підзапит

🟡 Middle Level

Як оптимізатор обробляє підзапити

PostgreSQL вміє Unnesting (розплющування) — перетворює підзапити на JOIN на рівні плану виконання. Це означає, що написаний вами підзапит фізично виконується як з’єднання таблиць, що зазвичай швидше.

При цьому часто використовується Semi-Join — особливий тип з’єднання: для кожного рядка лівої таблиці перевіряє, чи є хоча б один збіг у правій, і повертає рядок лівої таблиці ОДИН РАЗ (без дублів). У SQL немає прямого синтаксису для Semi-Join — він виникає всередині планувальника.

-- Підзапит у WHERE
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- Оптимізатор перетворює на:
SELECT * FROM users
INNER JOIN (SELECT user_id FROM orders WHERE amount > 100) o 
ON users.id = o.user_id;
-- Plan: Hash Semi-Join (дуже ефективно)

Semi-Join vs JOIN

Проблема JOIN: Join Amplification

-- ❌ Якщо у користувача 10 замовлень, він з'явиться 10 разів!
SELECT DISTINCT u.* FROM users u
JOIN orders o ON u.id = o.user_id;
-- Потім DISTINCT прибирає дублі (додаткове сортування/хешування)

-- ✅ EXISTS використовує Semi-Join (немає дублів!)
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- Зупиняється після першого збігу → швидше

EXISTS використовує Semi-Join: щойно знайдено ПЕРШИЙ збіг для рядка лівої таблиці, сканування правої таблиці для цього рядка припиняється (short-circuit evaluation). Не потрібно будувати повний результат з’єднання й потім робити DISTINCT для видалення дублів.

Оптимізаційні бар’єри (Optimization Fences)

Деякі конструкції заважають оптимізатору розплющити підзапит:

Бар’єр	Що відбувається
`LIMIT` / `OFFSET`	Підзапит виконується окремо
`DISTINCT`	Не може бути витягнутий у JOIN
`GROUP BY` / Агрегати	Підзапит матеріалізується
`UNION` / `INTERSECT`	Окремий вузол у плані

-- ❌ LIMIT створює бар'єр
SELECT * FROM users u
WHERE id IN (SELECT user_id FROM orders ORDER BY amount DESC LIMIT 10);
-- Plan: SubPlan (виконується для кожного рядка!)

-- ✅ Замініть на JOIN LATERAL
SELECT u.*, o.amount
FROM users u
LEFT JOIN LATERAL (
    SELECT amount FROM orders 
    WHERE o.user_id = u.id 
    ORDER BY amount DESC 
    LIMIT 10
) o ON true;

Скалярні підзапити в SELECT

-- ❌ ПОГАНО: підзапит виконується для кожного рядка
SELECT u.name, 
       (SELECT SUM(amount) FROM orders o WHERE o.user_id = u.id) as total
FROM users u;
-- Plan: SubPlan (1,000,000 підзапитів для 1,000,000 користувачів!)

-- ✅ ДОБРЕ: JOIN + GROUP BY
SELECT u.name, SUM(o.amount) as total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;
-- Plan: Hash Join + HashAggregate (набагато швидше!)

CTE (WITH) та матеріалізація

PostgreSQL 12+: CTE за замовчуванням вбудовуються (Inlined):

До PG 12 CTE ЗАВЖДИ матеріалізувалися — це був оптимізаційний бар’єр. Підзапит усередині CTE обчислювався один раз, результат зберігався в тимчасову структуру. Це могло бути плюсом (уникнення повторних обчислень), так і мінусом (оптимізатор не міг «зазирнути» всередину CTE і застосувати фільтри із зовнішнього запиту).

-- PG 12+: цей CTE буде вбудовано в основний запит
WITH active_users AS (
    SELECT * FROM users WHERE status = 'ACTIVE'
)
SELECT * FROM active_users u
JOIN orders o ON u.id = o.user_id;
-- Plan: так, ніби написали JOIN напряму

Керування матеріалізацією:

-- Примусова матеріалізація (обчислити CTE один раз)
WITH c AS MATERIALIZED (
    SELECT user_id, SUM(amount) as total FROM orders GROUP BY user_id
)
SELECT * FROM users u JOIN c ON u.id = c.user_id;

-- Примусове вбудовування
WITH c AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM c JOIN users u ON c.user_id = u.id;

Типові помилки

Скалярний підзапит у SELECT на великих даних

-- ❌ Катастрофа продуктивності
SELECT u.name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id)
FROM users u;  -- 1 млн рядків → 1 млн підзапитів!

NOT IN з NULL

-- ❌ Поверне порожній результат, якщо є NULL!
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);
   
-- ✅ Використовуйте NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Надлишковий DISTINCT з JOIN

-- ❌ DISTINCT після JOIN — ознака Join Amplification
SELECT DISTINCT u.* FROM users u
JOIN orders o ON u.id = o.user_id;
   
-- ✅ EXISTS ефективніший
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Практичне порівняння

Ситуація	Найкращий інструмент	Чому
Дані з обох таблиць	JOIN	Максимум стратегій виконання
Перевірка існування	EXISTS	Hash Semi-Join, немає дублів
Залежна вибірка (топ-N)	LATERAL	Використовує індекси всередині
Агрегація для кожного рядка	JOIN + GROUP BY	Один прохід замість N підзапитів
Складна логіка з LIMIT	LATERAL	Планувальник бачить індекси

🔴 Senior Level

Unnesting та Decorrelation

Як оптимізатор “розплющує” підзапити:

-- Початковий запит
SELECT * FROM users u
WHERE u.id IN (
    SELECT o.user_id FROM orders o 
    WHERE o.amount > (SELECT AVG(amount) FROM orders)
);

-- Кроки трансформації:
-- 1. Скалярний підзапит AVG → InitPlan (обчислюється один раз)
-- 2. IN підзапит → Hash Semi-Join
-- 3. Фінальний план:
--    InitPlan: AVG(amount)
--    → Hash Semi-Join (users ↔ orders)

Декореляція:

-- Корельований підзапит (залежить від зовнішнього рядка)
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100
);

-- Після декореляції:
-- Hash Semi-Join:
--   1. Будуємо хеш із orders WHERE amount > 100
--   2. Скануємо users, перевіряємо наявність у хеші
-- Складність: O(N + M) замість O(N × M)!

SubPlan vs InitPlan vs Param

SubPlan: Виконується для кожного рядка зовнішнього запиту

EXPLAIN SELECT u.name,
       (SELECT SUM(amount) FROM orders o WHERE o.user_id = u.id)
FROM users u;

-- Plan:
-- Seq Scan on users
--   SubPlan 1
--     -> Aggregate
--        -> Index Scan on orders (user_id = u.id)
-- ⚠️ Виконується 1,000,000 разів!

InitPlan: Виконується один раз перед основним запитом

EXPLAIN SELECT * FROM users
WHERE amount > (SELECT AVG(amount) FROM orders);

-- Plan:
-- InitPlan 1
--   -> Aggregate (AVG)
--      -> Seq Scan on orders
-- → Seq Scan on users (з фільтром за InitPlan результатом)
-- ✅ Виконується 1 раз!

Memoize Node (PostgreSQL 14+)

Memoize — вузол у плані виконання, який кешує результати підзапиту. Якщо підзапит викликається з тими самими параметрами повторно, результат береться з кешу замість повторного виконання. Кеш обмежений пам’яттю (параметр memoize_cache_size, за замовчуванням 10MB на вузол Memoize).

Революційна оптимізація для корельованих підзапитів:

-- Запит з LATERAL (вимушений Nested Loop)
SELECT u.name, o.amount
FROM users u
LEFT JOIN LATERAL (
    SELECT amount FROM orders o
    WHERE o.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 1
) o ON true;

-- PostgreSQL 14+: може вставити Memoize
-- Plan:
-- Nested Loop
--   → Seq Scan on users
--   → Memoize
--       → Limit
--          → Index Scan Backward on orders

-- Memoize кешує результати для кожного user_id
-- Якщо user_id повторюється → бере з кешу!
-- 🚀 Прискорення в 10–100 разів на даних із повторами

Моніторинг Memoize:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ... LATERAL ...;

-- Шукайте в плані:
-- Memoize  (cost=... rows=... actual rows=...)
--   Hits: 500000  ← взято з кешу
--   Misses: 10000 ← обчислено заново
--   Evictions: 500  ← витіснено з кешу

LATERAL JOIN: Коли і чому

Lateral обирає Nested Loop:

LATERAL майже завжди → Nested Loop
→ Чудово, якщо зовнішня таблиця маленька (< 10,000 рядків)
→ Катастрофа, якщо зовнішня таблиця велика (> 1,000,000 рядків)

Альтернативи для великих даних:

-- ❌ LATERAL на 1 млн користувачів → повільно
SELECT u.name, last_order.amount
FROM users u
LEFT JOIN LATERAL (
    SELECT amount FROM orders o
    WHERE o.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 1
) last_order ON true;

-- ✅ Window Function → Hash Join
SELECT name, amount
FROM (
    SELECT u.name, o.amount,
           ROW_NUMBER() OVER(PARTITION BY u.id ORDER BY o.created_at DESC) as rn
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
) sub
WHERE rn = 1;

-- ✅ DISTINCT ON → Sort + Unique
SELECT DISTINCT ON (u.id) u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
ORDER BY u.id, o.created_at DESC;

CTE: MATERIALIZED vs NOT MATERIALIZED

Коли використовувати MATERIALIZED:

-- CTE використовується кілька разів → матеріалізація вигідна
WITH expensive_calc AS MATERIALIZED (
    SELECT user_id, SUM(amount) * 1.2 as total
    FROM orders
    GROUP BY user_id
)
SELECT * FROM users u JOIN expensive_calc ec ON u.id = ec.user_id
UNION ALL
SELECT * FROM archived_users au JOIN expensive_calc ec ON au.id = ec.user_id;
-- CTE обчислюється 1 раз, використовується 2 рази

Коли використовувати NOT MATERIALIZED:

-- CTE використовується 1 раз + має фільтрацію → вбудовування вигідніше
WITH filtered_orders AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE amount > 1000 AND status = 'COMPLETED'
)
SELECT * FROM users u
JOIN filtered_orders fo ON u.id = fo.user_id
WHERE u.created_at > '2024-01-01';
-- Дозволяє оптимізатору "проштовхнути" фільтр u.created_at всередину CTE

Edge Cases

Volatile функції в підзапитах

-- NOW() стабільний у межах запиту
SELECT * FROM users WHERE created_at < NOW();
   
-- RANDOM() обчислюється щоразу!
SELECT * FROM users WHERE id = (SELECT FLOOR(RANDOM() * 1000000));
-- Поверне РІЗНІ значення для кожного рядка!

Підзапит в UPDATE/DELETE

-- ❌ Корельований підзапит в UPDATE → повільно
UPDATE orders o SET status = 'VIP'
WHERE user_id IN (SELECT id FROM users WHERE vip = true);
   
-- ✅ FROM синтаксис → JOIN
UPDATE orders o SET status = 'VIP'
FROM users u
WHERE o.user_id = u.id AND u.vip = true;
-- Plan: Hash Join (набагато швидше!)

Порожній підзапит

-- Якщо підзапит повернув 0 рядків:
-- IN () → FALSE
-- NOT IN () → TRUE (обережно з NULL!)
-- EXISTS () → FALSE
-- NOT EXISTS () → TRUE

Performance Comparison

Підхід	Складність	Коли використовувати
JOIN	O(N + M) з Hash Join	Дані з обох таблиць
EXISTS/IN	O(N + M) зі Semi-Join	Перевірка існування
Scalar Subquery	O(N × M)	Уникайте на великих даних
LATERAL	O(N × log M)	Залежні вибірки, малі зовнішні
CTE MATERIALIZED	O(M) + O(N)	CTE використовується > 1 разу
CTE NOT MATERIALIZED	Як JOIN	CTE використовується 1 раз

Production Experience

Реальний сценарій #1: SubPlan вбиває API

REST API: для кожного з 10,000 користувачів рахуємо баланс
Запит: скалярний підзапит у SELECT
Час: 45 секунд (10,000 підзапитів!)

Рішення:

-- Замінили на JOIN + GROUP BY
SELECT u.*, COALESCE(SUM(o.amount), 0) as balance
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

Результат: 200ms (прискорення в 225 разів)

Реальний сценарій #2: Memoize рятує від LATERAL

Звіт: топ-5 замовлень для кожного клієнта (100,000 клієнтів)
LATERAL без Memoize: > 5 хвилин
Після PG 14 з Memoize: 3 секунди
Hits/Misses ratio: 95%/5% (багато клієнтів мають однакові замовлення в кеші)

Реальний сценарій #3: CTE матеріалізація

Аналітика: CTE з агрегацією використовується в 4 UNION ALL
Без MATERIALIZED: CTE обчислюється 4 рази → 2 хвилини
З MATERIALIZED: обчислюється 1 раз → 30 секунд
Але: якщо CTE маленький, MATERIALIZED може сповільнити (накладні витрати)

Monitoring

-- 1. Перевірка SubPlan у планах
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id)
FROM users u;
-- Шукайте "SubPlan" → потенційна проблема

-- 2. Перевірка Memoize ефективності
EXPLAIN (ANALYZE)
SELECT ... LATERAL ...;
-- Memoize (Hits: X, Misses: Y)
-- Hit rate = Hits / (Hits + Misses)
-- > 80% → чудово, < 50% → Memoize не допомагає

-- 3. CTE матеріалізація
EXPLAIN (ANALYZE)
WITH c AS MATERIALIZED (...)
SELECT ...;
-- CTE Scan on c → матеріалізовано
-- Шукайте "CTE c" у підпланах → вбудовано

-- 4. Пошук скалярних підзапитів у pg_stat_statements
SELECT query, mean_exec_time
FROM pg_stat_statements
WHERE query ~ 'SELECT.*\(SELECT.*FROM.*\).*FROM'
ORDER BY mean_exec_time DESC;

Best Practices

EXISTS > DISTINCT JOIN для перевірки існування
NOT EXISTS > NOT IN (уникайте NULL пастки)
Уникайте скалярних підзапитів у SELECT на великих даних
LATERAL тільки для малих зовнішніх вибірок (< 10,000 рядків)
Керуйте CTE через MATERIALIZED / NOT MATERIALIZED усвідомлено
UPDATE/DELETE використовуйте FROM/USING замість підзапитів
Memoize (PG 14+) може врятувати LATERAL — перевіряйте Hit rate
Завжди перевіряйте план через EXPLAIN (ANALYZE)

Резюме для Senior

Оптимізатор вміє Unnesting та Decorrelation підзапитів
SubPlan на кожен рядок → катастрофа; замінюйте на JOIN
Memoize (PG 14+) кешує результати LATERAL → перевіряйте Hits/Misses
CTE у PG 12+ за замовчуванням вбудовується; керуйте через MATERIALIZED
EXISTS = Semi-Join (немає Join Amplification)
LATERAL ≈ Nested Loop → тільки для малих зовнішніх вибірок
UPDATE/DELETE через FROM/USING замість корельованих підзапитів
Завжди перевіряйте план: шукайте SubPlan, Batches, CTE Scan

🎯 Шпаргалка для інтерв’ю

Обов’язково знати:

PostgreSQL вміє Unnesting — перетворює підзапити на JOIN (Hash Semi-Join)
SubPlan = виконується для кожного рядка → O(N × M) → КАТАСТРОФА
InitPlan = виконується один раз → O(M) + O(N) → чудово
EXISTS = Semi-Join: немає Join Amplification (дублі від JOIN), зупиняється після 1-го збігу
NOT EXISTS > NOT IN: NOT IN з NULL поверне порожній результат
Memoize (PG 14+): кешує результати LATERAL → Hit rate > 80% = чудово
CTE у PG 12+: за замовчуванням вбудовується (NOT MATERIALIZED), керуйте явно
Скалярний підзапит у SELECT = N підзапитів → JOIN + GROUP BY краще
UPDATE FROM / DELETE USING швидші за корельовані підзапити

Часті уточнюючі питання:

«Чому EXISTS кращий за DISTINCT JOIN?» → Semi-Join не створює дублів, немає сортування для DISTINCT
«Коли Memoize не допомагає?» → Усі ключі унікальні → 0% Hits
«CTE MATERIALIZED чи NOT MATERIALIZED?» → MATERIALIZED якщо використовується > 1 разу
«Чому скалярний підзапит у SELECT поганий?» → N підзапитів для N рядків

Червоні прапорці (НЕ говорити):

❌ «Підзапит завжди повільніший за JOIN» (декорельований EXISTS може бути швидшим)
❌ «NOT IN безпечний» (NULL поверне порожній результат!)
❌ «CTE завжди матеріалізується» (PG 12+ вбудовує за замовчуванням)
❌ «Memoize завжди допомагає» (потрібні повторювані ключі)

Пов’язані теми:

Які типи JOIN існують → Semi-Join, Anti-Join
Що таке корельований підзапит → SubPlan vs InitPlan, Memoize
У чому різниця між INNER JOIN та LEFT JOIN → JOIN Amplification