14.07.2025 05:15
ITmization 6 1200 Источник

TL;DR Иногда «убить» самый тяжёлый JOIN — проще, чем кажется. Достаточно вынести агрегат в коррелированный под-запрос и дать движку опереться на индекс.

Стенд и исходный запрос

Для целей демонстрации создан тестовый стенд:

Скрипт
-- 1. Схема test
CREATE SCHEMA IF NOT EXISTS test;
SET search_path TO test;

-- 2. Создаем таблицы
CREATE TABLE users(id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE table1(id SERIAL, user_id INT, value NUMERIC(10,2));
CREATE TABLE table2 (id SERIAL, user_id INT, value NUMERIC(10,2));
CREATE TABLE table3(id SERIAL, user_id INT, value NUMERIC(10,2));

-- 3. Генерация тестовых данных (займет несколько секунд)
-- 1k пользователей
INSERT INTO users (name)
SELECT 'User ' || g 
FROM generate_series(1, 1000) g;

-- По 30 записей на пользователя в table1
INSERT INTO table1 (user_id, value)
SELECT u.id, random() * 1000
FROM users u
CROSS JOIN generate_series(1, 30);

-- По 500 записей на пользователя в table2
INSERT INTO table2 (user_id, value)
SELECT u.id, random() * 1000
FROM users u
CROSS JOIN generate_series(1, 500);

-- По 20 записей на пользователя в table3
INSERT INTO table3 (user_id, value)
SELECT u.id, random() * 1000
FROM users u
CROSS JOIN generate_series(1, 20);

-- 4. Создаем индексы (как в реальном примере)
CREATE INDEX ON table1 (user_id);
CREATE INDEX ON table2 (user_id);
CREATE INDEX ON table3 (user_id);

-- 5. Собираем статистику
ANALYZE test.users, test.table1, test.table2, test.table3;

Исходная задача -- для каждого пользователя посчитать какие-то агрегации. В примере:

  • среднее из table1.value,

  • максимум из table2.value,

  • среднее из table3.value.

Самый прямой путь — свести всё одним JOIN и сгруппировать:

SELECT count(*),
       avg(t1.value),
       max(t2.value),
       avg(t3.value),
       u.id
FROM users u
  JOIN table1 t1 ON t1.user_id = u.id   -- 30 строк на пользователя
  JOIN table2 t2 ON t2.user_id = u.id   -- 500 строк на пользователя
  JOIN table3 t3 ON t3.user_id = u.id   -- 20 строк на пользователя
GROUP BY u.id;
План запроса
Finalize GroupAggregate  (cost=4220168.43..4220315.93 rows=1000 width=108) (actual time=47895.012..47914.842 rows=1000 loops=1)
  Group Key: u.id
  ->  Gather Merge  (cost=4220168.43..4220283.43 rows=1000 width=108) (actual time=47894.985..47912.748 rows=2000 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        ->  Sort  (cost=4219168.42..4219170.92 rows=1000 width=108) (actual time=47392.226..47392.350 rows=1000 loops=2)
              Sort Key: u.id
              Sort Method: quicksort  Memory: 204kB
              Worker 0:  Sort Method: quicksort  Memory: 204kB
              ->  Partial HashAggregate  (cost=4219078.59..4219093.59 rows=1000 width=108) (actual time=47391.206..47391.984 rows=1000 loops=2)
                    Group Key: u.id
                    Batches: 1  Memory Usage: 833kB
                    Worker 0:  Batches: 1  Memory Usage: 833kB
                    ->  Hash Join  (cost=18970.58..2015329.77 rows=176299906 width=22) (actual time=220.078..14658.147 rows=150000000 loops=2)
                          Hash Cond: (t2.user_id = t1.user_id)
                          ->  Parallel Seq Scan on table2 t2  (cost=0.00..5644.18 rows=294118 width=10) (actual time=2.151..80.963 rows=250000 loops=2)
                          ->  Hash  (cost=7954.58..7954.58 rows=600000 width=24) (actual time=216.992..217.012 rows=600000 loops=2)
                                Buckets: 131072  Batches: 8  Memory Usage: 5737kB
                                ->  Hash Join  (cost=587.50..7954.58 rows=600000 width=24) (actual time=55.810..119.583 rows=600000 loops=2)
                                      Hash Cond: (t1.user_id = t3.user_id)
                                      ->  Hash Join  (cost=28.50..570.58 rows=30000 width=14) (actual time=13.236..23.096 rows=30000 loops=2)
                                            Hash Cond: (t1.user_id = u.id)
                                            ->  Seq Scan on table1 t1  (cost=0.00..463.00 rows=30000 width=10) (actual time=2.915..7.656 rows=30000 loops=2)
                                            ->  Hash  (cost=16.00..16.00 rows=1000 width=4) (actual time=10.298..10.301 rows=1000 loops=2)
                                                  Buckets: 1024  Batches: 1  Memory Usage: 44kB
                                                  ->  Seq Scan on users u  (cost=0.00..16.00 rows=1000 width=4) (actual time=0.030..10.098 rows=1000 loops=2)
                                      ->  Hash  (cost=309.00..309.00 rows=20000 width=10) (actual time=42.374..42.377 rows=20000 loops=2)
                                            Buckets: 32768  Batches: 1  Memory Usage: 1116kB
                                            ->  Seq Scan on table3 t3  (cost=0.00..309.00 rows=20000 width=10) (actual time=13.665..36.930 rows=20000 loops=2)
Planning Time: 1.958 ms
Execution Time: 47915.138 ms

Что не так

  • Каскадное «умножение» строк
    Hash Join объединяет table2 (500) × table1 (30) × table3 (20) для каждого пользователя.
    → 150 000 000 реально обработанных строк (ещё и по двум воркерам).

  • Тяжёлая сортировка
    После частичной агрегации данные всё-таки нужно упорядочить для Gather Merge, поэтому:
    Sort → Gather Merge → Finalize GroupAggregate — лишние десятки секунд.

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

Идея оптимизации

Нам от table2 нужен только максимум по каждому пользователю.
Зачем таскать 500 строк ради одного числа? Пусть СУБД сходила бы за ним один раз на пользователя по индексу.

SELECT count(*),
       avg(t1.value),
       (SELECT max(value) FROM table2 t2 
        WHERE t2.user_id = u.id),   -- подзапрос вместо join
       avg(t3.value),
       u.id
FROM users u
  JOIN table1 t1 ON t1.user_id = u.id
  JOIN table3 t3 ON t3.user_id = u.id
GROUP BY u.id;
План запроса
GroupAggregate  (cost=0.86..1298413.27 rows=1000 width=108) (actual time=11.410..639.096 rows=1000 loops=1)
  Group Key: u.id
  ->  Nested Loop  (cost=0.86..10173.90 rows=600000 width=16) (actual time=0.119..102.265 rows=600000 loops=1)
        ->  Merge Join  (cost=0.56..1643.49 rows=30000 width=14) (actual time=0.082..18.908 rows=30000 loops=1)
              Merge Cond: (u.id = t1.user_id)
              ->  Index Only Scan using users_pkey on users u  (cost=0.28..44.27 rows=1000 width=4) (actual time=0.028..0.533 rows=1000 loops=1)
                    Heap Fetches: 1000
              ->  Index Scan using table1_user_id_idx on table1 t1  (cost=0.29..1221.71 rows=30000 width=10) (actual time=0.048..14.665 rows=30000 loops=1)
        ->  Memoize  (cost=0.30..0.66 rows=20 width=10) (actual time=0.000..0.001 rows=20 loops=30000)
              Cache Key: t1.user_id
              Cache Mode: logical
              Hits: 29000  Misses: 1000  Evictions: 0  Overflows: 0  Memory Usage: 926kB
              ->  Index Scan using table3_user_id_idx on table3 t3  (cost=0.29..0.65 rows=20 width=10) (actual time=0.002..0.012 rows=20 loops=1000)
                    Index Cond: (user_id = t1.user_id)
  SubPlan 1
    ->  Aggregate  (cost=1282.21..1282.22 rows=1 width=32) (actual time=0.452..0.452 rows=1 loops=1000)
          ->  Bitmap Heap Scan on table2 t2  (cost=8.30..1280.96 rows=500 width=6) (actual time=0.092..0.402 rows=500 loops=1000)
                Recheck Cond: (user_id = u.id)
                Heap Blocks: exact=500000
                ->  Bitmap Index Scan on table2_user_id_idx  (cost=0.00..8.17 rows=500 width=0) (actual time=0.046..0.046 rows=500 loops=1000)
                      Index Cond: (user_id = u.id)
Planning Time: 2.393 ms
Execution Time: 639.305 ms

Что изменилось в плане

Было (оригинал)

Стало (оптимизация)

Hash Join на 150 млн строк

Bitmap Index Scan + Aggregate 500 строк на пользователя

Глобальная сортировка Sort → Gather Merge (≈ 47 394 мс)

Сортировки нет: Nested Loop + Memoize

1 воркер + Gather (два потока данных)

Один поток, меньше копирования

Execution Time ≈ 47 915 мс

≈ 639 мс

Ключевые цифры

Метрика

До

После

Строк в самом тяжёлом узле

150 000 000

600 000

Время планирования

1.96 мс

2.39 мс

Общее время выполнения

47.9 с

0.64 с

Почему это работает

  1. Ликвидация комбинаторного «взрыва»
    До оптимизации приходилось перемножать 30 × 500 × 20 строк для каждого пользователя, потому что MAX(t2.value) вычислялся после объединения таблиц. Когда мы вынесли MAX() в коррелированный под-запрос, объём данных, проходящих через основной JOIN, сократился в 250 раз.

  2. Опора на селективный индекс
    Bitmap Index Scan по table2_user_id_idx мгновенно находит страницы, где лежат 500 нужных строк. Затем Aggregate берёт максимум прямо «на месте», не таская их дальше по плану.

  3. Отсутствие глобальной сортировки
    Старый план тратил ~33 секунды исключительно на Sort → Gather Merge, потому что частичная агрегация всё-таки требовала упорядочить данные. Новый план агрегирует на лету — группировка идёт в том порядке, в каком строки приходят из вложенных циклов.

  4. Меньше параллелизма — лучше параллелизм
    Ранее каждый воркер дублировал 100 % работы, а потом данные «сливались» в один узел. Сейчас один процесс справляется быстрее, чем два прежних, потому что объём данных стал в сотни раз меньше; диспетчер контекстов ЦП и планировщик ОС не тратят время на переключения.

Итого совокупный выигрыш объясняется не одним «магическим» трюком, а композицией приёмов: индекс-доступ → ранняя агрегация → упрощение потока данных.

Аналогия из жизни

Вообразите крупный ресторан, где каждому столику подают закуски, основные блюда и десерты.
Шеф-повар хочет узнать, какой самый дорогой десерт заказывал каждый столик, а ещё посчитать общее число поданных блюд и среднюю стоимость закусок.

Плохой вариант — заставить официантов одновременно сгрузить все тарелки с кухни на один длиннющий стол, потом сортировать: «эти пирожные со стола 3, те эклеры – со стола 17…». Горы посуды, толчея, в зале не протолкнуться – ровно как наш JOIN, который ради одного числа таскает сотни лишних строк.

Хороший вариант — у каждого столика уже лежит чек с позициями и ценами. Официант просто берёт чек, одним взглядом находит самую дорогую строчку из раздела «Десерты» и идёт дальше. Никаких штабелей тарелок, только точечные «забеги» по индексам.

Результат тот же, но кухня не превращается в поле боя, а официанты работают в разы быстрее — как и оптимизированный запрос.

Выводы:

  1. Агрегаты «один-к-одному» лучше отделять.
    Нужен MAX/MIN/SUM из таблицы, связанной «много-к-многим»? Попробуйте вынести в под-запрос с индексом вместо того, чтобы тащить все строки через JOIN.

  2. Проверьте, где начинается дисбаланс.
    Если одна из таблиц в JOIN на порядок больше остальных, задумайтесь: действительно ли нужно присоединять её целиком?

  3. Смотрите на количество строк в узлах плана, а не только на «cost».
    Именно реальный поток данных определяет, сколько памяти, сортировки и диска понадобится, — а не абстрактное оценочное число.

  4. Не бойтесь «Nested Loop» — бойтесь «Nested Loop без индекса».
    Когда каждая итерация цикла обращается к индексированной таблице, это может оказаться быстрее любого хеш-или мерж-джойна.

Если заходят такие разборы живых кейсов и нюансы планов, заглядывайте в мой Telegram-канал IT без паники — там регулярно свежие примеры и лайфхаки про базы.

Комментарии (6)


  1. miksoft
    14.07.2025 05:28
    #28567866

    А не пробовали вариант с отдельной группировкой table2 и джойном к общему запросу после группировки?


    1. miksoft
      14.07.2025 05:28
      #28567880

      И на первый взгляд кажется, что с table1 и table2 можно сделать аналогично.


    1. ITmization Автор
      14.07.2025 05:28
      #28568394

      Да, есть еще варианты с отдельной группировкой в CTE. В постгрес еще есть lateral join


  1. Ninil
    14.07.2025 05:28
    #28567916

    ЧатЖПП, как из элементарной задачи на 1 минуту сделать целую статью?)


  1. aamonster
    14.07.2025 05:28
    #28567964

    Программисты, привыкшие писать код, а не тяп-ляп запрос к бд, плачут... Всё-таки эта история, где за оптимизацию запроса отвечает движок БД, здорово ударила по этому направлению, превратив некоторые вещи в магию.

    Впору придумывать расширение для SQL – assert для временнОй сложности (в данном случае – O(n1)+O(n2)+O(n3)+O(nr*log(nr)), где n1, n2, n3 – количество строк в исходных таблицах, а nr – в результате).


    1. ITmization Автор
      14.07.2025 05:28
      #28568424

      К сожалению, многие программисты сейчас вообще не пишут запросы к БД, а просто используют orm типа hibernate. Что получается на выходе их мало беспокоит.