intagg

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

Функции

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

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

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

Во многих СУБД есть понятие таблицы отношений «один ко многим». Такая таблица обычно находится между двумя индексированными таблицами, например:

CREATE TABLE left (id INT PRIMARY KEY, ...);
CREATE TABLE right (id INT PRIMARY KEY, ...);
CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);

Обычно она используется так:

SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)
  WHERE one_to_many.left = item;

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

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

CREATE TABLE summary AS
  SELECT left, int_array_aggregate(right) AS right
  FROM one_to_many
  GROUP BY left;

Эта команда создаст таблицу, содержащую одну строку для каждого элемента слева с массивом элементов справа. Она довольно бесполезна, пока нет подходящего способа использования этого массива; именно для этого и нужен нумератор массива. Вы можете выполнить:

SELECT left, int_array_enum(right) FROM summary WHERE left = item;

Приведенный выше запрос с вызовом int_array_enum выдает те же результаты, что и

SELECT left, right FROM one_to_many WHERE left = item;

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

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

SELECT right, count(right) FROM
  ( SELECT left, int_array_enum(right) AS right
    FROM summary JOIN (SELECT left FROM left_table WHERE left = item) AS lefts
         ON (summary.left = lefts.left)
  ) AS list
  GROUP BY right
  ORDER BY count DESC;