Оптимизация JOIN в PostgreSQL: как ускорить объединения таблиц

JOIN — самая дорогостоящая операция в SQL. Неправильный JOIN на больших таблицах может сделать запрос в 1000 раз медленнее. Разберём как понять и улучшить производительность.

Как PostgreSQL выполняет JOIN

Планировщик выбирает один из трёх алгоритмов:

Nested Loop Join

EXPLAIN SELECT * FROM orders o JOIN users u ON u.id = o.user_id WHERE o.id = 42;
-- Nested Loop
--   Index Scan on orders (id=42)
--   Index Scan on users (id=...)

Для каждой строки из внешней таблицы — поиск в индексе внутренней. Быстрый когда внешняя таблица маленькая и есть индекс.

Хорош для: o.id = 42 — одна строка во внешней, индекс во внутренней.

Hash Join

EXPLAIN SELECT * FROM orders o JOIN users u ON u.id = o.user_id;
-- Hash Join (cost=1500..25000)
--   Hash Cond: o.user_id = u.id
--   Seq Scan on orders
--   Hash on users

Строит хэш-таблицу из меньшей таблицы, сканирует большую. Быстрый для больших таблиц без индекса.

Хорош для: JOIN больших таблиц, равенство условий (=).

Merge Join

-- Merge Join (cost=5000..12000)
--   Sort on orders (user_id)
--   Sort on users (id) или Index Scan если есть индекс

Объединяет отсортированные данные. Эффективен если обе стороны уже отсортированы.

Хорош для: JOIN с ORDER BY по полю соединения, большие таблицы с индексами.

Диагностика: EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, u.name, o.amount
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days';

На что смотреть:

Hash Join  (cost=1500..25000 rows=5000 width=50)
           (actual time=120..890 rows=4823 loops=1)
  Buffers: shared hit=450 read=12000
                                ↑ read=12000 — много чтений с диска, нужен индекс
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders  (cost=0..18000 rows=5000)
                ↑ Seq Scan — нет индекса для фильтра
      Filter: (status='pending' AND created_at > ...)
      Rows Removed by Filter: 195177  ← убирает много строк = нужен индекс
  ->  Hash on users (cost=800..800 rows=56000)
        Buckets: 65536  Batches: 1  Memory Usage: 3584kB
                                    ↑ хэш-таблица в памяти — хорошо

Индексы для JOIN

-- Правило: индекс нужен на ПРАВОЙ стороне JOIN (по которой идёт поиск)
SELECT * FROM orders o JOIN users u ON u.id = o.user_id;
--                                     ↑ нужен индекс на users.id (обычно PRIMARY KEY)

SELECT * FROM orders o JOIN products p ON p.id = o.product_id;
--                                         ↑ нужен индекс на products.id

-- Индекс на внешнем ключе (часто забывают!)
CREATE INDEX ON orders (user_id);
-- Без этого: каждый запрос по orders.user_id → Seq Scan

-- Составной индекс для JOIN + WHERE
CREATE INDEX ON orders (user_id, status, created_at DESC);
-- Полезен для: JOIN users ON u.id = o.user_id WHERE status = 'completed'

Распространённые проблемы

JOIN без индекса на внешнем ключе

-- Проверить внешние ключи без индексов
SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS references_table,
    ccu.column_name AS references_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
LEFT JOIN pg_indexes pi
    ON pi.tablename = tc.table_name
    AND pi.indexdef LIKE '%' || kcu.column_name || '%'
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND pi.indexname IS NULL;  -- нет индекса!

Слишком много JOIN

-- Медленно: 7 JOIN подряд
SELECT u.name, o.amount, p.title, c.name, cat.title, d.name, r.rating
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
JOIN categories cat ON cat.id = p.category_id
JOIN countries c ON c.id = u.country_id
JOIN discounts d ON d.id = o.discount_id
JOIN reviews r ON r.order_id = o.id;

-- Решение: разбить на CTE или подзапросы, фильтровать раньше
WITH filtered_orders AS (
    SELECT * FROM orders WHERE created_at > CURRENT_DATE - 30
)
SELECT ...
FROM filtered_orders o
JOIN users u ON u.id = o.user_id
...

JOIN на функции (убивает индекс)

-- ❌ Медленно: функция на колонке JOIN
SELECT * FROM orders o
JOIN users u ON LOWER(u.email) = o.email_lower;
-- PostgreSQL не может использовать индекс на u.email

-- ✅ Быстро: функциональный индекс
CREATE INDEX ON users (LOWER(email));
-- Или: хранить email в нормализованном виде

Декартово произведение (случайное)

-- ❌ Забыли условие JOIN → декартово произведение!
SELECT u.name, o.amount
FROM users u, orders o  -- старый синтаксис, опасный
-- 10 000 users × 500 000 orders = 5 000 000 000 строк

-- ✅ Явный JOIN с условием
SELECT u.name, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id

Оптимизация через материализацию CTE

-- PostgreSQL 12+: CTE НЕ материализуется по умолчанию (inline)
WITH active_users AS (
    SELECT id FROM users WHERE is_active = true
)
SELECT o.* FROM orders o
JOIN active_users au ON au.id = o.user_id;
-- ← планировщик может оптимизировать

-- Принудительная материализация (когда CTE используется много раз)
WITH MATERIALIZED expensive_calc AS (
    SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
)
SELECT u.name, e.total
FROM users u JOIN expensive_calc e ON e.user_id = u.id
WHERE e.total > 10000;

work_mem: память для хэш-таблиц

-- Hash Join требует памяти для хэш-таблицы
-- Если памяти не хватает → Batches > 1 → данные пишутся на диск

-- Проверить
EXPLAIN ANALYZE SELECT ...;
-- Hash ... Batches: 4  ← плохо, идёт на диск

-- Увеличить work_mem для сессии
SET work_mem = '256MB';
EXPLAIN ANALYZE SELECT ...;
-- Hash ... Batches: 1  ← всё в памяти

-- Глобально в postgresql.conf (осторожно: умножается на max_connections)
work_mem = 64MB

Статистика: ANALYZE

PostgreSQL использует статистику для выбора плана. Устаревшая статистика → плохой план.

-- Обновить статистику таблицы
ANALYZE orders;
ANALYZE users;

-- Увеличить точность статистики для важных столбцов
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;  -- default 100
ANALYZE orders;

-- Просмотр статистики
SELECT tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders';

Итог: чеклист оптимизации JOIN

1. EXPLAIN (ANALYZE, BUFFERS) — найдите узкое место
2. Seq Scan с Rows Removed by Filter: N — нужен индекс
3. Индекс на внешних ключах (user_id, product_id, order_id)
4. read=N в Buffers — чтение с диска, нужно кеширование или индекс
5. Hash Batches: N > 1 — увеличьте work_mem
6. Никогда не JOIN без условия (декартово произведение)
7. Фильтруйте данные ДО JOIN (в CTE или подзапросе)
8. ANALYZE после массовой загрузки данных

Правильные индексы на полях JOIN + актуальная статистика решают 90% проблем с производительностью.