intagg — агрегатор и нумератор целых чисел#

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

Функции#

Агрегатор реализуется функцией int_array_aggregate(integer), которая создает массив целых чисел, содержащий ровно те целые числа, которые были переданы ей. Это оболочка вокруг array_agg, который делает то же самое для любого типа массива.

Нумератор – это функция int_array_enum(integer[]), которая возвращает setof integer. По сути, это обратная операция агрегата: данная целочисленная матрица расширяется до набора строк. Это оболочка вокруг unnest, который делает то же самое для любого типа массива.

Примеры использования#

Многие системы баз данных имеют понятие таблицы «один ко многим». Такая таблица обычно находится между двумя индексированными таблицами, например:

CREATE TABLE left_table  (id INT PRIMARY KEY, ...);
CREATE TABLE right_table (id INT PRIMARY KEY, ...);
CREATE TABLE many_to_many(id_left  INT REFERENCES left_table,
                          id_right INT REFERENCES right_table);

Обычно это используется следующим образом:

SELECT right_table.*
FROM right_table JOIN many_to_many ON (right_table.id = many_to_many.id_right)
WHERE many_to_many.id_left = item;

Запрос вернет все элементы в правой таблице для записи в левой таблице. Это очень распространенная конструкция в SQL.

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

CREATE TABLE summary AS
  SELECT id_left, int_array_aggregate(id_right) AS rights
  FROM many_to_many
  GROUP BY id_left;

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

SELECT id_left, int_array_enum(rights) FROM summary WHERE id_left = item;

Запрос выше, используя int_array_enum, дает те же результаты, что и:

SELECT id_left, id_right FROM many_to_many WHERE id_left = item;

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

В тестовой системе команда EXPLAIN показала что стоимость запроса 8488 была уменьшена до 329. Исходный запрос представлял собой соединение, включающее таблицу many_to_many, которая была заменена:

SELECT id_right, count(id_right) FROM
  ( SELECT id_left, int_array_enum(rights) AS id_right
    FROM summary
    JOIN (SELECT id FROM left_table
          WHERE id = item) AS lefts
    ON (summary.id_left = lefts.id)
  ) AS list
  GROUP BY id_right
  ORDER BY count DESC;