F.19. intarray — работа с массивами целых чисел #

Модуль intarray предоставляет ряд полезных функций и операторов для работы с массивами целых чисел без NULL. Также он поддерживает поиск по индексу для некоторых из этих операторов.

Все эти операции выдают ошибку, если в передаваемом массиве оказываются значения NULL.

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

Данный модуль считается «доверенным», то есть его могут устанавливать обычные пользователи, имеющие право CREATE в текущей базе данных.

F.19.1. Функции и операторы intarray #

Реализованные в модуле intarray функции перечислены в Таблице F.8, а операторы — в Таблице F.9.

Таблица F.8. Функции intarray

Функция

Описание

Примеры

icount ( integer[] ) → integer

Выдаёт число элементов в массиве.

icount('{1,2,3}'::integer[])3

sort ( integer[], dir text ) → integer[]

Сортирует массив в порядке возрастания или убывания. Направление определяется параметром dir, значением которого должно быть asc (по возрастанию) или desc (по убыванию).

sort('{1,3,2}'::integer[], 'desc'){3,2,1}

sort ( integer[] ) → integer[]

sort_asc ( integer[] ) → integer[]

Сортирует в порядке возрастания.

sort(array[11,77,44]){11,44,77}

sort_desc ( integer[] ) → integer[]

Сортирует в порядке убывания.

sort_desc(array[11,77,44]){77,44,11}

uniq ( integer[] ) → integer[]

Удаляет соседние дубликаты. Часто используется вместе с sort для удаления всех дубликатов.

uniq('{1,2,2,3,1,1}'::integer[]){1,2,3,1}

uniq(sort('{1,2,3,2,1}'::integer[])){1,2,3}

idx ( integer[], item integer ) → integer

Выдаёт индекс первого элемента, равного item, или 0, если такого элемента нет.

idx(array[11,22,33,22,11], 22)2

subarray ( integer[], start integer, len integer ) → integer[]

Извлекает часть массива, которая начинается с позиции start и содержит len элементов.

subarray('{1,2,3,2,1}'::integer[], 2, 3){2,3,2}

subarray ( integer[], start integer ) → integer[]

Извлекает часть массива, которая начинается с позиции start.

subarray('{1,2,3,2,1}'::integer[], 2){2,3,2,1}

intset ( integer ) → integer[]

Создаёт массив с одним элементом.

intset(42){42}


Таблица F.9. Операторы intarray

Оператор

Описание

integer[] && integer[]boolean

Массивы пересекаются (у них есть минимум один общий элемент)?

integer[] @> integer[]boolean

Левый массив содержит правый?

integer[] <@ integer[]boolean

Левый массив содержится в правом?

# integer[]integer

Выдаёт число элементов в массиве.

integer[] # integerinteger

Выдаёт индекс первого элемента, равного правому аргументу, или 0, если такого элемента нет. (Аналог функции idx.)

integer[] + integerinteger[]

Добавляет элемент в конец массива.

integer[] + integer[]integer[]

Соединяет два массива.

integer[] - integerinteger[]

Удаляет из массива элементы, равные правому аргументу.

integer[] - integer[]integer[]

Удаляет из левого массива элементы правого массива.

integer[] | integerinteger[]

Вычисляет объединение аргументов.

integer[] | integer[]integer[]

Вычисляет объединение аргументов.

integer[] & integer[]integer[]

Вычисляет пересечение аргументов.

integer[] @@ query_intboolean

Массив удовлетворяет запросу? (см. ниже)

query_int ~~ integer[]boolean

Массив удовлетворяет запросу? (коммутирующий оператор к @@)


Операторы &&, @> и <@ равнозначны встроенным операторам PostgreSQL с теми же именами, за исключением того, что они работают только с целочисленными массивами, не содержащими NULL, тогда как встроенные операторы работают с массивами любых типов. Благодаря этому ограничению, в большинстве случаев они работают быстрее, чем встроенные операторы.

Операторы @@ и ~~ проверяют, удовлетворяет ли массив запросу, представляемому в виде значения специализированного типа данных query_int. Запрос содержит целочисленные значения, сравниваемые с элементами массива, возможно с использованием операторов & (AND), | (OR) и ! (NOT). При необходимости могут использоваться скобки. Например, запросу 1&(2|3) удовлетворяют запросы, которые содержат 1 и также содержат 2 или 3.

F.19.2. Поддержка индексов #

Модуль intarray поддерживает индексы для операторов &&, @>, и @@, а также обычную проверку равенства массивов.

Модуль предоставляет два параметризованных класса операторов GiST: gist__int_ops (используется по умолчанию), подходящий для маленьких и средних по размеру наборов данных, и gist__intbig_ops, применяющий сигнатуру большего размера и подходящий для индексации больших наборов данных (то есть столбцов, содержащих много различных значений массива). В этой реализации используется структура данных RD-дерева со встроенным сжатием с потерями.

Класс gist__int_ops аппроксимирует набор целых чисел в виде массива диапазонов. В его необязательном целочисленном параметре numranges можно задать максимальное число диапазонов в одном ключе индекса. Параметр может принимать значения от 1 до 253, по умолчанию он равен 100. При увеличении числа массивов, составляющих ключ индекса GiST, поиск работает точнее (сканируется меньшая область в индексе и меньше страниц кучи), но сам индекс становится больше.

Класс gist__intbig_ops аппроксимирует набор целых чисел в виде сигнатуры битовой карты. В его необязательном целочисленном параметре siglen можно задать размер сигнатуры в байтах. Параметр может принимать значения от 1 до 2024, по умолчанию он равен 16. При увеличении размера сигнатуры поиск работает точнее (сканируется меньшая область в индексе и меньше страниц кучи), но сам индекс становится больше.

Есть также нестандартный класс операторов GIN, gin__int_ops, поддерживающий эти операторы наряду с <@.

Выбор между индексами GiST и GIN зависит от относительных характеристик производительности GiST и GIN, которые здесь не рассматриваются.

F.19.3. Пример #

-- сообщение может относиться к одной или нескольким «секциям»
CREATE TABLE message (mid INT PRIMARY KEY, sections INT[], ...);

-- создать специализированный индекс с длиной сигнатуры 32 байта
CREATE INDEX message_rdtree_idx ON message USING GIST (sections gist__intbig_ops (siglen = 32));

-- вывести сообщения из секций 1 или 2 — оператор пересечения
SELECT message.mid FROM message WHERE message.sections && '{1,2}';

-- вывести сообщения из секций 1 и 2 — оператор включения
SELECT message.mid FROM message WHERE message.sections @> '{1,2}';

-- тот же результат, но с оператором запроса
SELECT message.mid FROM message WHERE message.sections @@ '1&2'::query_int;

F.19.4. Тестирование производительности #

В каталоге исходного кода contrib/intarray/bench содержится пакет тестов, которые можно провести на установленном сервере PostgreSQL. (Для этого нужно установить пакет DBD::Pg.) Чтобы запустить эти тесты, выполните:

cd .../contrib/intarray/bench
createdb TEST
psql -c "CREATE EXTENSION intarray" TEST
./create_test.pl | psql TEST
./bench.pl

Скрипт bench.pl принимает несколько аргументов, о которых можно узнать, запустив его без аргументов.

F.19.5. Авторы #

Разработку осуществили Фёдор Сигаев () и Олег Бартунов (). Дополнительные сведения можно найти на странице http://www.sai.msu.su/~megera/postgres/gist/. Андрей Октябрьский проделал отличную работу, добавив новые функции и операторы.