Алгоритмы соединения и каскад ошибок оценки строк

Запрос возвращает 50 строк за 50 мс на staging. В production — та же схема, идентичные индексы, тот же запрос — 4.2 секунды. EXPLAIN ANALYZE показывает Nested Loop ... loops=520000. Внутренний index scan выполнился полмиллиона раз. Планировщик думал, что на внешней стороне 50 строк. Их было 520,000. Одна плохая оценка. Один неверный выбор соединения. Замедление в 80×.

Три алгоритма соединения

Nested Loop

Для каждой строки внешнего отношения ищет совпадающие строки во внутреннем — как правило, через индекс. Стоимость: outer_rows × inner_cost_per_lookup. Побеждает, когда внешняя сторона мала (десятки строк), потому что стоимость внутреннего поиска по индексу уплачивается столько же раз. Катастрофически проигрывает при недооценке внешней стороны: если планировщик думает, что внешних строк 10, а их 10,000, внутренний поиск выполняется 10,000 раз вместо 10 — в 1000× больше работы.

Диагностика: счётчик loops на внутреннем узле. В здоровом Nested Loop: loops=50. В «взорвавшемся»: loops=520000.

Hash Join

Строит хеш-таблицу из меньшей (build) стороны, затем проверяет её строками из большей (probe) стороны. Стоимость: build + probe. Побеждает для средних и больших equi-join, где ни одна сторона не имеет индекса, выровненного по ключу соединения. Критический параметр — work_mem: хеш-таблица должна помещаться в памяти. Когда нет, Hash Batches превышает 1 и таблица сбрасывается на диск — ищите Batches: 64 или аналогичное в выводе плана. Исправление: SET work_mem = '64MB' для сессии (не глобально, без учёта max_connections).

Merge Join

Сортирует обе стороны по ключу соединения (или использует индексы, уже обеспечивающие порядок), затем сливает параллельно. Побеждает, когда обе стороны приходят отсортированными — например, при соединении двух таблиц с ORDER BY id и соответствующими индексами. Не добавляет дополнительной стоимости сортировки. Полезен для range join и когда порядок сортировки нужен и для финального результата.

Каскад ошибок оценки строк

Это самая важная концепция урока. Плохая оценка строк на одном узле плана каскадирует на каждый узел выше:

1. Планировщик думает, что фильтр WHERE country='US' AND region='CA' AND status='shipped' вернёт 50 строк (независимые вероятности: 50% × 5% × 20% = 0.5%)
2. Планировщик выбирает Nested Loop — дёшево когда внешняя сторона мала
3. Реальность: колонки коррелированы (все CA-заказы в US), реальная селективность 5% × 20% = 1% — и планировщик ошибся с кардинальностью индекса; реальных строк = 520,000
4. Внутренний index scan выполняется 520,000 раз вместо 50 — в 10,400× больше работы

Неверный алгоритм (Nested Loop вместо Hash Join) — симптом. Неверная оценка строк — причина. Принудительный выбор алгоритма (например, SET enable_nestloop = off) маскирует симптом, оставляя корень причины. Исправьте оценку — выбор алгоритма последует.

Несаргабельные предикаты

Предикат «саргабелен» (Search ARGument-ABLE), если планировщик может использовать индекс для его вычисления. Несаргабельные предикаты принудительно вызывают Seq Scan и искажают оценки строк.

Типичные нарушители:

• WHERE LOWER(email) = 'alice@x.com' — функция на индексируемой колонке → используйте выражаемый индекс CREATE INDEX ON users (LOWER(email))
• WHERE created_at::date = '2026-01-01' — приведение типа → перепишите как WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'
• WHERE EXTRACT(year FROM created_at) = 2026 — вызов функции → тот же диапазонный рефактор
• WHERE id::text = '42' — неявное приведение → WHERE id = 42 (исправьте тип в приложении)
• WHERE name LIKE '%foo' — лидирующий wildcard → GIN-индекс pg_trgm для нечёткого поиска

EXPLAIN сразу это обнаруживает: Seq Scan + Filter там, где ожидался Index Scan. Строка Filter показывает, что применялось после сканирования, а не до — то есть индекс не помог.