SQL-Ex blog

Три соединения, кратко

Вложенный цикл (Nested Loop) сканирует внешнее отношение и для каждой строки ищет соответствующие строки во внутреннем отношении — дёшево, когда внешняя сторона мала или внутренняя сторона имеет индекс по ключу соединения, и жестоко, когда обе стороны велики и не индексированы, потому что это O(внешняя × внутренняя). Соединение слиянием (Merge Join) требует, чтобы оба входа были отсортированы по ключу соединения, затем проходит по двум отсортированным потокам синхронно, продвигая тот, который отстаёт, — отлично, когда обе стороны уже отсортированы (индекс обеспечивает порядок бесплатно), но для этого нужна сортировка, а сортировка двух больших несортированных входов дорога. Хеш-соединение (Hash Join) строит хеш-таблицу из одного входа и зондирует её другим; ему совершенно не важен порядок входа, что делает его «рабочей лошадкой» для соединения больших несортированных таблиц по условию равенства.

Хеш-соединение работает в две фазы. Фаза построения (build phase) сканирует меньший вход — планировщик намеренно выбирает меньший, чтобы минимизировать использование памяти, — и строит хеш-таблицу, ключи которой — столбцы соединения. Фаза зондирования (probe phase) проходит по большему входу, хеширует ключ соединения каждой строки и ищет совпадения. Поскольку оба входа сканируются последовательно, индекс по условию соединения не помогает хеш-соединению вообще; именно поэтому вы часто видите, что планировщик выбирает последовательное сканирование под хеш-соединением, даже когда индексы существуют, и это правильно, а не ошибка. Хеш-соединению нужно только условие равенства (нельзя хешировать <) и достаточно памяти, чтобы разместить хеш-таблицу строящейся стороны.

Эта память и есть «загвоздка», и бюджет тот же, что и для хеш-агрегации: work_mem × hash_mem_multiplier (множитель был повышен с 1.0 до 2.0 в PostgreSQL 15). Если хеш-таблица строящейся стороны помещается, соединение выполняется за один проход. Если нет, PostgreSQL разбивает оба входа на пакеты (batches) по ключу соединения и обрабатывает по одной паре пакетов за раз, сбрасывая остальные во временные файлы, — это работает, но ввод-вывод вредит, и планировщик часто предпочтёт соединение слиянием, если хеш не помещается в память.

Что на самом деле делает его отключение

Стоит знать для всех трёх переключателей соединений: enable_hashjoin = off на самом деле не запрещает хеш-соединения. Он добавляет disable_cost — жёстко заданное огромное число, 1e10, — к каждому пути хеш-соединения, поэтому планировщик всё равно использует его, если у него буквально нет другого способа выполнить соединение. Для соединения по равенству он переключится на слияние или вложенный цикл, но для соединения, которое другие методы не могут обработать, вы увидите хеш-соединение в вашем плане, несмотря на то, что переключатель выключен. Переключатели «отговаривают», но не запрещают строго.

Симптомы, оправдывающие его переключение

Признак находится в узле Hash Join в EXPLAIN (ANALYZE): Batches: больше 1, что означает, что хеш-таблица строящейся стороны не поместилась в work_mem × hash_mem_multiplier, и соединение было сброшено на диск в несколько пакетов. Сбрасывающееся хеш-соединение часто составляет большую часть времени выполнения медленного запроса, и это сигнал, который отправляет вас к этому параметру.

Когда вы видите это, выполните SET enable_hashjoin = off для сеанса и повторно запустите EXPLAIN (ANALYZE). Планировщик переключается на соединение слиянием (или вложенный цикл), и вы получаете сравнение: если альтернатива быстрее, вы подтвердили, что сбрасывающееся хеш-соединение было проблемой, и теперь вы выбираете реальное исправление. Чаще всего это память — увеличьте work_mem (для каждой роли или сеанса, но никогда слепо для всего кластера, поскольку это бюджет на оператор, умноженный на вашу параллельность) или увеличьте hash_mem_multiplier, чтобы дать хеш-операциям больше пространства. Иногда это индекс, который делает соединение слиянием действительно дешевле, предоставляя предварительно отсортированный вход, или делает вложенный цикл с индексированной внутренней стороной лучшим выбором для маленькой стороны зондирования. И часто более глубокая причина находится выше по течению: хеш-соединение (или любой сюрприз с методом соединения) часто связан с ошибкой оценки количества строк — планировщик построил план для неверной кардинальности, — поэтому соединение — это место, где вы тратите время, но ANALYZE или лучшая статистика — это то, где вы это исправляете. Классическая версия: планировщик занижает оценку количества строк, выбирает хеш-соединение, рассчитанное на гораздо меньшее количество строк, чем приходит на самом деле, и выполняет интенсивный сброс на диск.

Оставление enable_hashjoin = off в postgresql.conf — это обычная ошибка семейства, и притом плохая: хеш-соединение является правильной, незаменимой стратегией для больших несортированных соединений по равенству, и его отключение для всего кластера заставляет каждое такое соединение использовать сортировку для слияния или повторяющиеся сканирования для вложенного цикла. Диагностируйте с помощью переключателя, исправьте реальное ограничение — work_mem, hash_mem_multiplier, индекс или статистику, — и верните его обратно. Зонд говорит вам, что хеш-соединение навредило; он не говорит вам, что нужно жить без хеш-соединений.