При написании SQL-запросов с JOIN важно понимать, какой алгоритм соединения выберет PostgreSQL. От этого зависит скорость выполнения запроса, особенно на больших объемах данных. В этой статье разберем:

• Какие типы JOIN существуют.

• Как PostgreSQL выбирает метод соединения.

• Какие параметры влияют на производительность.

• Как оптимизировать JOIN для ускорения запросов.

Типы JOIN в PostgreSQL

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

1. Nested Loop Join (Вложенные циклы)

Этот метод прост: для каждой строки из первой таблицы PostgreSQL ищет подходящие строки во второй таблице. Индекс на ключевом поле ускоряет поиск.

EXPLAIN ANALYZE
SELECT u.id, u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.15..340.55 rows=1570 width=52) (actual time=0.020..0.043 rows=4 loops=1)
   ->  Seq Scan on orders o  (cost=0.00..25.70 rows=1570 width=20) (actual time=0.006..0.010 rows=4 loops=1)
   ->  Index Scan using users_pkey on users u  (cost=0.15..0.20 rows=1 width=36) (actual time=0.003..0.004 rows=1 loops=4)
         Index Cond: (id = o.user_id)
 Planning Time: 0.212 ms
 Execution Time: 0.070 ms
(6 rows)

Когда Nested Loop может быть полезен?

• При наличии индексов на ключах соединения.

• Если одна таблица маленькая, а вторая большая (например, справочники, фильтрация по небольшому набору данных).

2. Hash Join (Хеш-соединение)

PostgreSQL создаёт хеш-таблицу для одной из таблиц (обычно для меньшей). Затем он ищет строки из второй таблицы по этому хешу.

EXPLAIN ANALYZE
SELECT u.id, u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=38.58..68.41 rows=1570 width=52) (actual time=0.034..0.050 rows=4 loops=1)
   Hash Cond: (o.user_id = u.id)
   ->  Seq Scan on orders o  (cost=0.00..25.70 rows=1570 width=20) (actual time=0.005..0.010 rows=4 loops=1)
   ->  Hash  (cost=22.70..22.70 rows=1270 width=36) (actual time=0.015..0.017 rows=3 loops=1)
         Buckets: 2048  Batches: 1  Memory Usage: 17kB
         ->  Seq Scan on users u  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.002..0.006 rows=3 loops=1)
 Planning Time: 0.199 ms
 Execution Time: 0.081 ms
(8 rows)

Если индексов нет, PostgreSQL выберет Hash Join.

Когда Hash Join предпочтительнее?

• При отсутствии индексов на соединяемых полях.

• Когда work_mem достаточно велик, чтобы уместить хеш-таблицу в памяти.

• Если таблицы слишком большие для эффективного Nested Loop.

3. Merge Join (Слиянием)

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

EXPLAIN ANALYZE
SELECT u.id, u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=109.19..202.97 rows=1570 width=52) (actual time=0.044..0.067 rows=4 loops=1)
   Merge Cond: (u.id = o.user_id)
   ->  Index Scan using users_pkey on users u  (cost=0.15..67.20 rows=1270 width=36) (actual time=0.006..0.010 rows=3 loops=1)
   ->  Sort  (cost=109.04..112.96 rows=1570 width=20) (actual time=0.033..0.039 rows=4 loops=1)
         Sort Key: o.user_id
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on orders o  (cost=0.00..25.70 rows=1570 width=20) (actual time=0.005..0.010 rows=4 loops=1)
 Planning Time: 0.183 ms
 Execution Time: 0.097 ms
(9 rows)

Когда Merge Join эффективен?

• Если обе таблицы уже отсортированы по ключу соединения.

• Если сортировка дешевая, а сами данные большие.

Как PostgreSQL выбирает JOIN?

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

1️⃣ Анализирует статистику таблиц (ANALYZE).
2️⃣ Рассчитывает стоимость выполнения (cost).
3️⃣ Выбирает наиболее дешевый вариант.

* Что будет, если отключить все JOIN?

SET enable_nestloop TO off;
SET enable_hashjoin TO off;
SET enable_mergejoin TO off;

Даже при отключении всех трёх параметров, планировщик запросов PostgreSQL может быть вынужден использовать nested-loop join в случаях, когда нет альтернативных методов соединения. Однако такое сочетание настроек может значительно ограничить возможности оптимизации запросов и привести к снижению производительности.

Как ресурсы влияют на выбор JOIN?

Параллельные JOIN в PostgreSQL

PostgreSQL поддерживает многопоточное выполнение JOIN, если данные разделены на страницы. Параметры, влияющие на параллельность:

• parallel_tuple_cost – стоимость обработки одной строки.

• parallel_setup_cost – затраты на инициализацию потока.

Как понять, что нужно увеличить work_mem?

1️⃣ Запустить EXPLAIN ANALYZE – если в плане есть упоминание disk (например, spilled to disk), памяти не хватает.
2️⃣ Проверить логи (pg_log) – если есть temporary file: size 256MB, значит, PostgreSQL писал на диск.
3️⃣ Включить логирование временных файлов:

SET log_temp_files = 0;

4️⃣ Не увеличивать work_mem слишком сильно – это может привести к нехватке оперативной памяти при множестве параллельных запросов.

Как оценить random_page_cost для вашей системы?

PostgreSQL не умеет автоматически определять тип диска и не настраивает random_page_cost сам. Этот параметр задается вручную в конфигурации или на уровне сессии.

1️⃣ Определите тип хранилища

• Если у вас SSD, установите random_page_cost ближе к 1.1–1.5.

• Если HDD, оставьте random_page_cost = 4.0 (значение по умолчанию).

• Для NVMe SSD можно опустить до 1.0–1.1.

2️⃣ Измерьте скорость случайного и последовательного чтения
Выполните pg_test_timing:

pg_test_timing

Если разница между случайным и последовательным доступом минимальна, можно снижать random_page_cost.

3️⃣ Проверьте фактическую работу запросов
Запустите EXPLAIN ANALYZE на сложных запросах и сравните планы выполнения до и после изменения random_page_cost.

4️⃣ Ручная настройка
Временно изменить для текущей сессии:

SET random_page_cost = 1.1;

Установить глобально в postgresql.conf:

random_page_cost = 1.1

Затем перезапустить сервер.

Если у вас SSD или NVMe, снижение random_page_cost сделает индексные сканы более приоритетными, что часто ускоряет JOIN и выборки.

Дополнительные способы оптимизации запросов

1. Использование индексов

Создание индексов на ключах соединения ускоряет Nested Loop Join и Merge Join.

? Пример:

CREATE INDEX idx_customer_id ON orders(customer_id);

2. Использование ANALYZE и VACUUM

ANALYZE обновляет статистику таблиц, помогая планировщику выбрать лучший JOIN.

VACUUM предотвращает разрастание таблиц и ускоряет доступ к данным.

? Пример:

VACUUM ANALYZE orders;

3. Разбиение таблиц (Partitioning)

Разделение больших таблиц на части снижает объем сканируемых данных.

PostgreSQL поддерживает range, list и hash partitioning.

? Пример:

CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

4. Оптимизация параметров конфигурации

Увеличение work_mem помогает Hash Join работать быстрее.

effective_cache_size должен отражать объем доступной памяти кэша.

random_page_cost можно снизить при использовании SSD.

? Пример:

SET work_mem = '256MB';
SET effective_cache_size = '4GB';

Итоговый чек-лист по оптимизации JOIN

Этот чек-лист поможет быстро проверить, оптимизирован ли ваш запрос:

✔️ Используйте индексы на ключах соединения и проверяйте их актуальность.
✔️ Запускайте ANALYZE и VACUUM, чтобы статистика была актуальной.
✔️ Проверяйте планы выполнения запросов через EXPLAIN ANALYZE.
✔️ Избегайте Seq Scan, если его можно заменить Index Scan.
✔️ Настройте work_mem, чтобы избежать записи на диск.
✔️ Используйте разбиение таблиц (Partitioning) для больших объемов данных.
✔️ Настройте параметры конфигурации PostgreSQL под вашу нагрузку.

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