Java Interview Questions

Главная / Все ответы / Базы данных SQL PostgreSQL

Вопрос 9 · Раздел 1

Что лучше: JOIN или подзапрос?

PostgreSQL умеет Unnesting (расплющивание) — превращает подзапросы в JOIN на уровне плана выполнения. Это значит, что написанный вами подзапрос физически выполняется как соедине...

Russian Базы данных SQL PostgreSQL Исходный Markdown
Версии по языкам: English Russian Ukrainian

🟢 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;

Типичные ошибки

  1. Скалярный подзапрос в SELECT на больших данных 
    -- ❌ Катастрофа производительности
SELECT u.name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id)
FROM users u;  -- 1 млн строк → 1 млн подзапросов!
  2. 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);
  3. Избыточный 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

  1. Volatile функции в подзапросах 
    -- NOW() стабилен в рамках запроса
SELECT * FROM users WHERE created_at < NOW();
   
-- RANDOM() вычисляется каждый раз!
SELECT * FROM users WHERE id = (SELECT FLOOR(RANDOM() * 1000000));
-- Вернёт РАЗНЫЕ значения для каждой строки!
  2. Подзапрос в 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 (гораздо быстрее!)
  3. Пустой подзапрос 
    -- Если подзапрос вернул 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

  1. EXISTS > DISTINCT JOIN для проверки существования
  2. NOT EXISTS > NOT IN (избегайте NULL ловушки)
  3. Избегайте скалярных подзапросов в SELECT на больших данных
  4. LATERAL только для малых внешних выборок (< 10,000 строк)
  5. Управляйте CTE через MATERIALIZED / NOT MATERIALIZED осознанно
  6. UPDATE/DELETE используйте FROM/USING вместо подзапросов
  7. Memoize (PG 14+) может спасти LATERAP — проверяйте Hit rate
  8. Всегда проверяйте план через 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