Краткий ответ

Hash join рационален, когда соединение обрабатывает большую долю строк, а построение хеш-таблицы и последовательное чтение входов дешевле множества случайных обращений к индексу. Наличие индексов само по себе не делает nested loop выгодным: оптимизатор сравнивает стоимость альтернатив на основе кардинальности, распределения данных, стоимости чтения и доступной памяти.

Если внешний набор мал и условие соединения высокоселективно, обычно выигрывает nested loop с индексным поиском во внутренней таблице. Если обе стороны велики, hash join часто уменьшает число случайных обращений и выполняет работу ближе к последовательному проходу.

Исторический контекст

Индексы появились как способ не читать всю таблицу для поиска небольшого набора строк. Однако запросы с соединениями требуют не только найти строки, но и выбрать способ сопоставить два набора данных.

Поэтому современные оптимизаторы используют стоимостную модель. Она оценивает несколько планов: например, повторный индексный поиск для каждой строки внешнего набора, построение хеш-таблицы или соединение уже упорядоченных потоков.

Постановка проблемы

Предположим, внешняя таблица после фильтра содержит миллион строк, а внутренняя таблица имеет индекс по ключу соединения. Nested loop выполнит до миллиона обращений к этому индексу, причём обращения могут читать разные страницы данных и приводить к случайному вводу-выводу.

Если же оптимизатор ожидает, что фильтр оставит только несколько строк, hash join может оказаться избыточным: построение хеш-таблицы добавит накладные расходы, тогда как несколько индексных поисков будут дешевле. Ошибка в статистике способна перевернуть выбор и привести к медленному плану.

Подробное решение

При nested loop оптимизатор выбирает внешний поток. Для каждой его строки он ищет совпадения во внутреннем источнике, часто используя индекс. Стоимость особенно чувствительна к числу строк внешнего потока и к цене одного поиска во внутренней таблице.

При hash join одна сторона становится входом построения: из её строк создаётся хеш-таблица по ключу соединения. Вторая сторона затем просматривается, а для каждой строки выполняется проверка соответствующего хеш-бакета. Для равенств это позволяет избежать отдельного индексного поиска для каждой строки.

Главные факторы выбора — оценка количества строк, селективность фильтров, размер страниц, стоимость последовательного и случайного чтения, доступная память, параллелизм и возможность повторного использования результатов. Если хеш-таблица не помещается в память, возникают разбиение на части и дополнительные операции чтения с диска; тогда преимущество hash join может исчезнуть.

Индекс по ключу соединения всё равно может быть полезен: он может сделать выгодным nested loop для малой выборки, обеспечить порядок для merge join или ускорить фильтрацию до соединения. Но индекс не гарантирует использование именно индексного соединения — оптимизатор выбирает план для всего запроса, а не отдельного объекта.

Минимальная иллюстрация ситуации:

SELECT o.id, c.name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.created_at >= '2025-01-01';

Если условие по дате оставляет небольшое число заказов, индекс по customers.id может сделать nested loop выгодным. Если условие оставляет большую часть заказов, последовательное чтение заказов с построением хеш-таблицы по клиентам может стоить меньше множества обращений к индексу.

Проверять причину нужно по фактическому плану: сравнить оценочное и реальное число строк, увидеть типы чтения, потребление памяти, предупреждения о проливах на диск и фактическое число итераций внутреннего входа. Простая замена hash join на nested loop без устранения ошибки оценок обычно лишь маскирует проблему и может ухудшить другие варианты параметров.

Ситуация из практики

Отчёт соединял большую таблицу заказов с таблицей клиентов. На обеих таблицах были индексы по ключам соединения, но после расширения периода отчёта план с nested loop стал выполнять сотни тысяч индексных обращений и заметно замедлился.

Рассматривались три варианта. Принудительный nested loop сохранял прежнее поведение, но был плох при больших периодах; добавление ещё одного индекса могло уменьшить стоимость доступа к данным, но не устраняло большое число обращений; обновление статистики и проверка памяти позволяли оптимизатору выбрать алгоритм по актуальным оценкам.

Выбрали обновление статистики с последующей проверкой фактического плана. После того как оптимизатор увидел реальный объём выборки, он выбрал hash join; дополнительная проверка показала, что хеш-таблица помещается в память без пролива на диск. Время отчёта снизилось, а план сохранил приемлемое поведение для больших диапазонов дат.

Что кандидаты часто упускают

Всегда ли hash join читает обе таблицы целиком?

Нет. До соединения могут применяться фильтры, партиционирование, индексные операции или другие узлы плана, поэтому вход hash join может быть существенно меньше исходной таблицы. Корректно оценивать нужно фактические входы конкретного узла, а не размер таблиц в схеме.

Почему одинаковая оценка числа строк не гарантирует одинаковую стоимость nested loop и hash join?

Стоимость зависит не только от количества строк, но и от формы доступа. Nested loop может выполнять случайные чтения, повторно посещать одни страницы или извлекать данные из некрывающего индекса; hash join требует памяти и обычно последовательно обрабатывает входы. Поэтому оптимизатор сравнивает модель физических операций, а не только итоговую кардинальность.

Что означает ситуация, когда hash join неожиданно проливается на диск?

Это обычно означает, что рабочая хеш-таблица не поместилась в выделенную память либо фактический объём строк оказался значительно больше оценки. Соединение разбивается на части, которые обрабатываются дополнительными чтениями и записями, поэтому задержка возрастает. Следует проверить расхождение оценочных и фактических строк, распределение ключей, доступную память и статистику; простое увеличение памяти не заменяет исправление неверной оценки.