PostgreSQL має три алгоритми з'єднання таблиць, і планувальник обирає один для кожного JOIN за оцінками кількості рядків.
Nested Loop - для кожного рядка зовнішньої таблиці шукати відповідні рядки у внутрішній.
- Чудовий, коли зовнішніх рядків мало, а у внутрішній таблиці є індекс на ключі з'єднання: 20 замовлень → 20 пошуків по індексу користувачів.
- Катастрофічний, коли зовнішніх рядків багато: мільйон × пошук = мільйон звернень.
Hash Join - побудувати хеш-таблицю з меншого набору, потім пройти більший і шукати збіги в хеш-таблиці.
- Для великих наборів без потрібного порядку - типовий вибір для аналітики й звітів.
- Лише для умов рівності (
=). - Хеш-таблиця будується в пам'яті (
work_mem×hash_mem_multiplier); якщо не вміщується - ділиться на частини на диску, і з'єднання сповільнюється.
Merge Join - обидва набори впорядковані за ключем з'єднання, і їх «зшивають» за один прохід, як дві відсортовані колоди.
- Ефективний, коли обидва набори вже відсортовані (наприклад, читаються з індексів) або результат усе одно потрібен відсортованим.
- Якщо сортування треба робити окремо, часто програє Hash Join.
Як читати в EXPLAIN ANALYZE:
Nested Loop (actual rows=950000 loops=1)
-> Seq Scan on orders (actual rows=950000)
-> Index Scan using users_pkey on users (actual rows=1 loops=950000)
loops=950000 - сигнал: планувальник очікував кілька рядків зовні, а отримав майже мільйон. Майже завжди причина - помилкова оцінка кількості рядків (застаріла статистика, корельовані умови), а не сам алгоритм.
Виправляють не алгоритм, а оцінки: ANALYZE, розширена статистика, переписаний запит. Для перевірки гіпотези в сесії можна вимкнути алгоритм (SET enable_nestloop = off), але на проді так не залишають.
