Перевод: CYBERTEC. Стратегии соединения и производительность в PostgreSQL

И снова перевод. Сейчас продолжаю изучение работы планировщика PostgreSQL, наткнулся на данную статью и решил перевести — и материал изучить, и потренироваться в английском.

Поэтому, если найдете ошибки в переводе — напишите об этом в комментариях или в контактной форме.

Ссылка на оригинал.


В PostgreSQL есть три стратегии соединения, которые работают совершенно по-разному. Если PostgreSQL выбирает неправильную стратегию, производительность запросов может сильно пострадать. Эта статья объясняет стратегии соединения, как вы можете поддержать их с помощью индексов, что с ними может пойти не так и как вы можете настроить ваши соединения для улучшения производительности.

Терминология

Отношение (relation)

Соединение (join) объединяет данные из двух отношений. Такое отношение может быть непосредственно базовой таблицей (base relation) или результатом работы любого узла плана. Например, в соединении:

SELECT ...
FROM a
   JOIN (b LEFT JOIN c ON ...)
      ON ...

базовое отношение a будет соединено с результатом объединения b и c.

Внутреннее и внешнее отношение (inner и outer relation)

План выполнения для любого соединения выглядит примерно так:

EXPLAIN (COSTS OFF)
SELECT * FROM a JOIN b USING (id);
 
         QUERY PLAN         
----------------------------
 Hash Join
   Hash Cond: (a.id = b.id)
   -›  Seq Scan on a
   -›  Hash
         -›  Seq Scan on b
(5 rows)


Мы назовем верхнее соединяемое отношение (в данном случае последовательное сканирование таблицы a) внешним отношением соединения, и назовем нижнее соединяемое отношение (где вычисляется хеш из таблицы b) внутренним отношением.

Условие соединения и ключ соединения

Декартово произведение или cross join двух отношений — это то, что вы получите, если соединить каждую строку из одного отношения с каждой строкой другого. Условие соединения (join condition) — это фильтр, который исключает лишние строки из этих комбинаций. Есть несколько способов написать условие соединения, но все они могут быть преобразованы в:

a ‹join type› JOIN b ON ‹join condition›

Если условие соединения имеет вид:

a.col1 ‹operator› b.col2 [AND ...]

Я называю a.col1 и b.col2 ключами соединения (join keys).

Обратите внимание, что для внутренних соединений нет различия между условием соединения и условием WHERE, но это не относится к внешним соединениям.

Стратегия соединения вложенным циклом (nested loop)

Это самая простая и наиболее общая стратегия соединения из всех.

PostgreSQL сканирует внешнее отношение последовательно, и для каждой результирующей строки он сканирует внутреннее отношение на предмет совпадения строк.

Индексы, которые могут помочь с соединением вложенным циклом

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

Варианты использования стратегии соединения вложенными циклами

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

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

Стратегия соединения хешированием (hash join)

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

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

Индексы, которые могут помочь с соединением хешированием

Поскольку мы сканируем оба отношения последовательно, ни один индекс не поможет с хеш-соединением.

Варианты использования стратегии соединения хешированием

Хеш-соединения лучше всего подходят, если оба из участвующих отношений являются большими и хеш-таблица для отношения меньшего размера вписывается в work_mem. Это связано с тем, что в противном случае PostgreSQL будет создавать хеш в нескольких корзинах и сохранять их во временных файлах на диске, что вредит производительности. В таких случаях оптимизатор обычно выбирает другую стратегию соединения, такую как соединение слиянием.

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

Стратегия соединения слиянием (merge join)

В соединение слиянием PostgreSQL выбирает все условия соединения по оператору равенства. Затем сортирует обе таблицы по ключам соединения (это означает, что типы данных должны быть сортируемыми). Затем он перебирает оба отсортированных списка и находит подходящие записи.

Индексы, которые могут помочь с соединением слиянием

Индекс по ключам сортировки может ускорить сортировку, поэтому индекс по ключам соединения в обоих отношениях может ускорить соединение слиянием. Однако, явная сортировка часто обходится дешевле, если только не использовать сканирование только по индексу (index only scan).

Варианты использования стратегии соединения слиянием

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

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

Итоговая таблица для стратегий соединения PostgreSQL

Nested Loop

Hash Join

Merge Join

Алгоритм

Для каждой строки внешнего отношения сканируется внутреннее отношение

Строится хеш по внутреннему отношению, сканируется внешнее отношение, проверяется хеш

Сортируются оба отношения и сливаются строки

Индексы, которые помогают

Индекс по ключам соединения внутреннего отношения

Отсутствуют

Индексы по ключам соединения обоих отношений

Хорошая стратегия, если

Внешняя таблица маленькая

Хеш-таблица вписывается в work_mem

Обе таблицы большие

Влияние на производительность запросов

Выбор неправильной стратегии соединения приводит к плохой производительности:

  • Если оптимизатор недооценивает количество строк, по ошибке он может выбрать соединение вложенным циклом. Затем он сканирует внутреннее отношение чаще, чем рассчитывал, что приводит к плохой производительности.

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

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

Как заставить PostgreSQL выбрать правильную стратегию соединения

Узнайте, какова лучшая стратегия соединения (возможно, PostgreSQL все равно делает правильные вещи). Вы можете временно отключить различные стратегии соединения с помощью команды SET, который изменяет параметр в вашей текущей сессии базы данных:

SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET enable_nestloop = off;

Обратите внимание, что на самом деле вы не можете отключить соединение вложенным циклом, а только препятствовать PostgreSQL его использовать. Если нет условия соединения по равенству, соединение вложенным циклом является единственным способом.

Настройка запросов часто не является простой и понятной задачей. Тем не менее, вот некоторые рекомендации и идеи:

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

  • Попробуйте увеличить work_mem и посмотреть, получите ли вы более дешевое хеш-соединение.

  • Настройте параметры, которые сообщают PostgreSQL о вашем оборудовании и ресурсах: random_page_cost, effective_cache_size и effective_io_concurrency. Это позволит ему правильно оценить сканирование по индексу.

  • Вы можете ускорить вложенный цикл и соединение слиянием с помощью сканирования только по индексу. Для этого необходимо добавить все необходимые столбцы в индекс (в идеале с предложением INCLUDE) и убедитесь, что таблицы очищаются достаточно часто (VACUUM).

Вывод

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

Если вы хотите узнать больше о настройке запросов с соединениями, прочитайте некоторые другие наши статьи на эту тему, такие как Joining 1 million tables или Speeding up GROUP BY and joins.

Лоренц Альбе

Лоренц Альбе

Лоренц Альбе — старший консультант и инженер поддержки в CYBERTEC. Он работает с PostgreSQL и участвует в сообществе с 2006 года.


Еще раз ссылка на оригинал.


Be the first to comment

Leave a Reply

Ваш Mail не будет опубликован.


*