SQL-Ex blog

Что делает агрегация по разделам (partitionwise aggregation)

Агрегируйте секционированную таблицу обычным способом — SELECT category, sum(amount) FROM sales GROUP BY category — и PostgreSQL сканирует каждый раздел, объединяет строки через Append и агрегирует объединённый поток на верхнем уровне с помощью одного GroupAggregate или HashAggregate. Вся работа по группировке выполняется один раз, в конце, над всеми данными.

Агрегация по разделам, добавленная в PostgreSQL 11, проталкивает группировку внутрь каждого раздела. Каждый раздел агрегируется отдельно, а результаты затем объединяются. Существует две формы, и какая из них будет использована, зависит от одного вопроса: включает ли GROUP BY ключ секционирования?

Если включает — вы группируете по столбцу, по которому таблица секционирована, — то проталкивание является полным. Каждая группа полностью находится внутри одного раздела (в этом и заключается смысл секционирования по этому ключу), поэтому каждый раздел может быть агрегирован до финальных результатов независимо, и план представляет собой просто Append поверх HashAggregate для каждого раздела. Финализация не требуется; разделы выдают непересекающиеся группы, которые просто объединяются.

Если не включает — вы группируете по какому-то другому столбцу, — то строки группы могут быть распределены по многим разделам, поэтому каждый раздел может вычислить только частичный агрегат, и шаг Finalize Aggregate над Append должен объединить эти частичные результаты. План показывает Partial GroupAggregate под каждым разделом, питающий Finalize GroupAggregate на верхнем уровне — ту же двухступенчатую структуру partial/finalize, которую использует параллельная агрегация. Это всё равно часто даёт выигрыш, потому что частичные агрегаты работают на меньших входных данных на раздел и хорошо параллелизуются, но это менее «чистый» случай, чем полное проталкивание.

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

Почему по умолчанию выключено

Вот компромисс, объясняющий поведение по умолчанию, и его стоит сформулировать точно, потому что именно по этой причине данный параметр не просто включён, как его «собратья». Когда агрегация проталкивается в разделы, план получает по одному узлу агрегации на раздел — и каждый из этих узлов ограничен параметром work_mem. В документации прямо указано следствие: количество узлов в плане, ограниченных work_mem, может расти линейно с количеством сканируемых разделов. Таблица из ста разделов может превратить один узел агрегации в сто, и если каждый из них имеет право на work_mem, пиковое потребление памяти запросом возрастает в это количество раз. Кроме того, рассмотрение планов с агрегацией по разделам само по себе значительно удорожает планирование с точки зрения CPU и памяти, потому что планировщику приходится оценивать больше альтернативных путей.

Таким образом, возможность выключена по умолчанию не потому, что она редко полезна, а потому, что её глобальное включение было бы «минной ловушкой» для памяти в базах данных с множеством разделов и щедрым work_mem — именно в тех базах данных, где, скорее всего, есть большие секционированные таблицы. Разработчики PostgreSQL решили, что вы должны сознательно выбирать эту возможность для конкретной рабочей нагрузки, убедившись, что она помогает, а не навязывать умножение памяти всем.

Когда это стоит включить

Это один из немногих параметров enable_*, которые вы можете легитимно установить в ненулевое значение в рабочей среде, и логика здесь является зеркальным отражением остального семейства. Вы включаете его — для сессии, для роли или для базы данных, — когда у вас есть большие секционированные таблицы и аналитические запросы, которые группируют по (в идеале) ключу секционирования, и вы подтвердили на реальных данных, что план с проталкиванием работает быстрее. Подтверждение важно: установите enable_partitionwise_aggregate = on, выполните EXPLAIN (ANALYZE) и сравните с планом по умолчанию. Вы ищете узлы HashAggregate/Partial GroupAggregate для каждого раздела и меньшее общее время выполнения — и при этом следите, чтобы стоимость памяти, умноженная на количество ваших разделов, была приемлемой при вашей конкурентности.

Дисциплина применения здесь противоположна диагностическим переключателям. Вместо того чтобы оставлять его включённым и отключать для диагностики, вы оставляете его выключенным и включаете для конкретной рабочей нагрузки, которая выигрывает от этого — классический случай — это роль для отчётности, выполняющая большие группирующие сканирования по секционированной таблице фактов; установите ALTER ROLE reporting SET enable_partitionwise_aggregate = on. Включение его на уровне всего кластера оправдано только в том случае, если вы учли наихудший случай использования памяти для каждого секционированного запроса, который выполняется на сервере, а в смешанной рабочей нагрузке быть в этом уверенным сложнее, чем кажется. Параметр действительно полезен; он выключен по умолчанию, потому что полезность и безопасность по умолчанию — это не одно и то же, и здесь эти понятия расходятся.