Чем EXISTS отличается от IN? Когда они дают разный план?

Семантика EXISTS и IN

Оба оператора проверяют вхождение значения во множество, но делают это по-разному:

• IN (subquery) — вычисляет подзапрос полностью, строит набор значений и затем для каждой строки внешнего запроса проверяет принадлежность. NULL-значения в наборе ломают NOT IN: если подзапрос вернул хоть один NULL, NOT IN никогда не даёт TRUE.
• EXISTS (subquery) — для каждой строки внешнего запроса запускает подзапрос и останавливается на первой совпадающей строке (short-circuit). NULL в подзапросе безопасен, потому что проверяется только факт наличия хотя бы одной строки.

Когда планы расходятся

До PostgreSQL 9.3 оптимизатор не умел автоматически преобразовывать подзапросы IN в JOIN. Начиная с 9.3+ он делает это через механизм «subquery unnesting», но только при выполнении ряда условий. Планы начинают отличаться в следующих случаях:

• Подзапрос содержит DISTINCT, LIMIT, агрегаты или GROUP BY — unnesting блокируется, IN может деградировать до nested loop с полным сканированием подзапроса на каждую строку внешней таблицы.
• Одна из таблиц маленькая, другая большая: EXISTS с хорошим индексом на коррелированном условии остановится после первого попадания, тогда как IN без unnesting прочтёт весь подзапрос.
• Наличие NULL в столбце подзапроса: NOT IN с NULL-ами возвращает пустой результат, тогда как NOT EXISTS работает корректно.

Практический пример

-- Таблицы
CREATE TABLE orders (id bigint PRIMARY KEY, customer_id bigint, amount numeric);
CREATE TABLE blacklist (customer_id bigint);
INSERT INTO blacklist VALUES (NULL); -- опасный NULL

-- NOT IN с NULL: всегда вернёт 0 строк
SELECT * FROM orders
WHERE customer_id NOT IN (SELECT customer_id FROM blacklist);
-- Результат: 0 строк, даже если ни один заказ не попадает в blacklist!

-- NOT EXISTS: работает ожидаемо
SELECT * FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM blacklist b
  WHERE b.customer_id = o.customer_id
);
-- Результат: возвращает строки, не совпадающие с ненулевыми значениями blacklist

Сравнение планов через EXPLAIN

-- IN-вариант
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM orders
WHERE customer_id IN (
  SELECT customer_id FROM blacklist WHERE amount > 100
);

-- EXISTS-вариант
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM orders o
WHERE EXISTS (
  SELECT 1 FROM blacklist b
  WHERE b.customer_id = o.customer_id AND b.amount > 100
);

Если в обоих случаях план показывает Hash Semi Join — оптимизатор уже сделал unnesting и разницы в производительности нет. Если один из планов показывает Nested Loop без индекса — это сигнал переписать запрос.

Когда выбирать что

• Используйте IN с константным списком: WHERE status IN ('active', 'pending') — читаемо и оптимально.
• Используйте EXISTS для коррелированных подзапросов, когда нужен short-circuit, или когда подзапрос содержит сложную логику (GROUP BY, LIMIT).
• Используйте NOT EXISTS вместо NOT IN всегда, когда столбец подзапроса может содержать NULL.
• В современном PostgreSQL (14+) предпочтительнее явный JOIN ... SEMI через EXISTS или переписать в LATERAL, чтобы оптимизатор имел больше свободы.

Подводные камни

• NOT IN + NULL в подзапросе: возвращает 0 строк — самая частая скрытая ошибка. Всегда используйте NOT EXISTS или фильтруйте NULL в подзапросе: WHERE col IS NOT NULL.
• IN с огромным списком констант (тысячи значений) медленнее, чем временная таблица + JOIN.
• Подзапрос в IN с LIMIT не поддаётся unnesting — план будет nested loop с полным выполнением подзапроса на каждую строку.
• EXISTS не гарантирует порядок строк в подзапросе — ORDER BY внутри EXISTS бессмыслен и игнорируется оптимизатором.
• При unnesting IN превращается в Hash Semi Join, который строит хэш-таблицу из подзапроса — при огромном подзапросе это давление на work_mem.
• Коррелированный EXISTS с тяжёлым подзапросом выполняется N раз (по числу строк внешней таблицы) — не забывайте про индекс на коррелированном столбце.

• Игнорировать NULL в IN и NOT IN.
• Думать, что EXISTS всегда быстрее.
• Не смотреть фактический план.

• Просит пример с NULL.
• Проверяет понимание semi join.
• Уточняет, когда нужен EXPLAIN ANALYZE.