GROUP BY и DISTINCT в плане запроса

Лена вошла в кабинет Макса с задумчивым видом.

— Макс, я снова запуталась. У меня два почти одинаковых запроса. В одном SELECT user_id ... GROUP BY user_id, в другом — SELECT DISTINCT user_id. Я думала, результат будет идентичным, но планы совершенно разные! В одном — какой-то HashAggregate, а в другом — Sort и Unique. Почему так?

Макс отпил кофе и улыбнулся.

— Это одна из интересных задач для планировщика — как эффективно сгруппировать данные или найти уникальные значения. Он использует для этого две основные стратегии. Я называю их конвейер и большой котел.

Макс взял лист бумаги и ручку.

1. HashAggregate: большой котёл

Представь, что тебе нужно посчитать, сколько раз в большой коробке с деталями встречается каждая уникальная деталь. Ты берешь деталь, смотришь на ее артикул, и если такого еще не видела — создаешь для него новую ячейку в своем «справочнике» (хеш-таблице) и кладешь туда деталь. Если уже видела — просто увеличиваешь счетчик в существующей ячейке.

HashAggregate работает так же:

• Что делает: Читает строки одну за другой и строит в памяти хеш-таблицу, где ключ — это значение из колонки для группировки (GROUP BY key).

• Когда хорош: Эффективен для группировки больших объемов данных, когда порядок не важен.

• Слабое место: Требует много памяти. Если хеш-таблица не умещается в work_mem, он начинает сбрасывать данные на диск, что резко снижает производительность.

Пример плана:

EXPLAIN SELECT status, count(*) FROM orders GROUP BY status;

-- HashAggregate
--   -> Seq Scan on orders

Здесь Postgres читает всю таблицу (Seq Scan) и “на лету” группирует данные в хеш-таблице.

2. GroupAggregate и Sort + Unique: конвейерная сортировка

А теперь представь, что детали на конвейере уже отсортированы по артикулу. Тебе не нужен справочник. Ты просто смотришь на текущую деталь и на предыдущую. Если артикул тот же — продолжаешь считать. Как только приехала деталь с новым артикулом — ты фиксируешь результат для предыдущей группы и начинаешь считать заново.

GroupAggregate работает по этому принципу:

• Что делает: Объединяет строки, которые уже отсортированы по ключу группировки. Если порядок не гарантирован, планировщик добавляет операцию Sort.

• Когда хорош: Когда вход уже отсортирован по ключу группировки. Такой порядок иногда даёт индекс, но может дать и другой узел плана.

• Как выглядит в плане: Ты можешь увидеть GroupAggregate или Unique (для DISTINCT).

Пример плана с индексом:

-- Есть индекс по status
EXPLAIN SELECT DISTINCT status FROM orders;

-- Unique
--   -> Index Only Scan using orders_status_idx on orders

Postgres читает данные прямо из индекса, где они уже отсортированы, и просто отсекает дубликаты (Unique). Никаких лишних затрат на сортировку или хеширование.

GROUP BY против DISTINCT

Для планировщика SELECT DISTINCT a FROM table — это почти то же самое, что и SELECT a FROM table GROUP BY a. Он выберет ту же стратегию (HashAggregate или Sort + Unique), основываясь на наличии подходящего индекса и объеме данных.

— Так вот почему у меня планы были разные! — воскликнула Лена. — Для GROUP BY у меня не было индекса, но хватало work_mem, и он использовал HashAggregate. А для DISTINCT по другой колонке индекс был, и он выбрал Index Only Scan + Unique!

— Именно! — подтвердил Макс. — Postgres нашел короткий путь с помощью индекса и воспользовался им.

Что запомнить

• GROUP BY и DISTINCT решают похожую задачу, и для них используются одинаковые стратегии.

• HashAggregate: Быстрый, но требовательный к памяти. Используется, когда нет отсортированных данных.

• GroupAggregate / Sort + Unique: Эффективен, если данные уже отсортированы (например, взяты из индекса). Позволяет избежать больших затрат памяти.

• Подходящий порядок входных данных помогает избежать отдельного Sort; индекс — один из способов получить этот порядок, но не бесплатный и не универсальный.

• Если HashAggregate или Sort сбрасывает данные на диск, осторожно протестируйте другое work_mem только для этой сессии и сравните полный план. Увеличение памяти не гарантирует смену стратегии и при высокой конкурентности может навредить серверу.