Краткий ответ

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

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

Исторический контекст

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

Индексы B-деревьев изначально решали задачу быстрого поиска, но их упорядоченность полезна и для операций, которым нужен отсортированный поток. Потоковая агрегация использует это свойство, избегая отдельной сортировки или большой структуры групп в памяти.

Постановка проблемы

Рассмотрим группировку большого набора данных. Хеш-агрегация может потребовать значительный объём памяти; при нехватке выделенной памяти часть данных будет обрабатываться через временное хранилище, что ухудшит производительность.

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

Подробное решение

Для потоковой агрегации строки должны приходить сгруппированными по ключу группировки. Например, индекс с ведущим столбцом category_id обеспечивает порядок по этому столбцу. Если запрос читает этот порядок напрямую, агрегатор может завершить обработку группы при переходе к следующему значению.

CREATE INDEX ix_sales_category ON sales (category_id);

SELECT category_id, SUM(amount)
FROM sales
GROUP BY category_id;

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

Для составного индекса важен ведущий префикс. Индекс (category_id, sale_date) сохраняет строки в порядке category_id, поэтому он может поддержать группировку по category_id. Но он обычно не дает общего порядка только по sale_date, поскольку перед датой присутствует другой ключ.

Фильтрация также влияет на применимость порядка. Условие по ведущему ключу с фиксированным значением оставляет один непрерывный диапазон индекса, а диапазонный фильтр по ведущему ключу может сформировать несколько сегментов. Оптимизатор должен проверить, сохраняется ли требуемый порядок для всей входной последовательности; иногда после нескольких диапазонов порядок недостаточен и требуется сортировка.

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

Покрытие индекса может дополнительно изменить расчёт стоимости. Если индекс содержит все столбцы, необходимые для фильтрации и вычисления агрегата, оптимизатору не нужны обращения к базовой таблице. Но добавление широких включённых столбцов увеличивает размер индекса и стоимость его поддержки, поэтому такой индекс оправдан только при реальной нагрузке.

Ситуация из практики

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

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

Выбрали последний вариант после проверки плана и нагрузки на запись. В результате оптимизатор получил возможность использовать потоковую агрегацию, а риск переполнения памяти исчез. Решение было принято не по самому факту наличия индекса, а по совокупной стоимости чтения, покрытию запроса и цене сопровождения индекса.

Что кандидаты часто упускают

Достаточно ли индекса по столбцу группировки, если запрос выбирает дополнительные неиндексированные столбцы?

Нет. Индекс может дать нужный порядок, но дополнительные обращения к таблице способны сделать такой план дороже хеш-агрегации. Оптимизатор сопоставляет стоимость этих обращений с затратами полного сканирования и обработки неупорядоченного потока.

Всегда ли потоковая агрегация использует меньше памяти, чем хеш-агрегация?

Обычно ей требуется состояние только для текущей группы, но это преимущество сохраняется лишь при действительно упорядоченном входе. Если перед агрегацией нужна сортировка, память потребуется уже для сортировщика, и общий план может оказаться не легче хеш-агрегации.

Почему индекс по ключу группировки может не использоваться, хотя его порядок идеально подходит?

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