Задача : для быстрого поиска значений int8 в массиве int8[] построить совместный индекс на поля int4 и int8[] .
Для построения совместного GIST-индекса используются расширения btree_gist и intarray .

Проблема в том, что для int8[] расширение intarray не создаёт GIST-операторов:

Если для int8[] явно указать класс операторов gist__int_ops , получаем:

Если для int8[] явно указать класс операторов gist__intbig_ops , получаем:


P.S . Для int8[] GIN -оператор расширение intarray создаёт. Но с GIN-индексом на массиве можно будет проверять только одно значение на вхождение и всё. Т.е. желателен именно GIST-индекс.

MelkijЕсли вы почему-то решили, что gist__intbig_ops имеет отношение к bigint - вы невнимательно читаете документацию, где объясняется почему их два.Я понимаю, что к integer и bigint прямого отношения он не имеет (касается числа элементов в массиве, а не типа элементов массива).
Оба варианта GIST/gist__int_ops и GIST/gist__intbig_ops я привёл для "полноты картины".

MelkijCyrax_02Для int8[] GIN-оператор расширение intarray создаёт.Нет. Это встроенный array_ops.> https://postgrespro.ru/docs/postgresql/13/gin-examples
> Также классы операторов GIN содержатся в следующих модулях contrib:
> intarray - Расширенная поддержка int[]

Да, в справке об этом тоже упоминается, что только для int4[]
(правда, в описании модуля intarray об этом почему-то ни слова):

> https://postgrespro.ru/docs/postgresql/13/gist-examples
> Классы операторов GiST содержатся также и в следующих дополнительных модулях (contrib):
> intarray - RD-дерево для одномерных массивов значений int4

Тогда получается, что на int8[] построить GIST невозможно ? Без написания собственных операторов.

авторк integer и bigint прямого отношения он не имеет (касается числа элементов в массиве, а не типа элементов массива)Вернее, так: названия этих классов операторов касаются числа элементов в массивах, а сами классы операторов различаются реализацией .

По поводу модуля intarray . Он создаёт классы операторов только для int4[] :
GIST : gist__int_ops и gist__intbig_ops
GIN : gin__int_ops

Также есть стандартный класс операторов array_ops , который работает с int2[], int4[] и int8[] .
Т.е. для работы с int8[] придётся создавать GIN-индекс со стандартным классом операторов array_ops , который не поддерживает операцию @@ .

Здесь возникают такие вопросы:
1 ) Какой индекс на int8[] работает быстрее: GIN/array_ops или GIN/gin__int_ops ?
2 ) Какой индекс на int8[] занимает меньше места: GIN/array_ops или GIN/gin__int_ops ?

3 ) Что делает оператор @@ применительно к массивам ?
В справке он описывается только применительно к полнотекстовому поиску (соответствие) и геометрическим фигурам (геометрический центр).
Но применительно к массивам этот оператор не упоминается...

автор3) Что делает оператор @@ применительно к массивам ?
В справке он описывается только применительно к полнотекстовому поиску (соответствие) и геометрическим фигурам (геометрический центр).
Но применительно к массивам этот оператор не упоминается... Ага, это такой же оператор запроса, что и в случае с jsonb . Вводится тем же самым расширением intarray:
https://postgrespro.ru/docs/postgresql/13/intarray#id-1.11.7.27.8 integer[] @@ query_int → boolean
Массив удовлетворяет запросу? (см. ниже)

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

тот же результат, но с оператором запроса
SELECT message.mid FROM message WHERE message.sections @@ '1&2'::query_int;
По поводу GIN/array_ops или GIN/gin__int_ops напрашивается такой вывод.
Если не требуется оператор @@, то для массивов целых чисел лучше использовать стандартный GIN/array_ops:

1 ) GIN-индексы занимают меньше места + быстрее строятся
2 ) стандартный класс операторов [array_ops] позволяет работать с любыми типами (в т.ч. с int1 , uint1 , uint2 , uint4 , uint8 из расширения pg_uint ) без приведения их к int4[] - а это экономия места за счёт малых размеров индекса

Но, судя по всему, GIN/gin__int_ops работает быстрее, чем стандартный GIN/array_ops .

Так GIN/array_ops - это не intarray , а стандартный класс операторов с массивами.
И с int8[] он работает, и с uint1[] работает...

MelkijНе представляю, откуда вы этот оператор взяли.Видно, что модулем intarray вы не пользовались ))

https://postgrespro.ru/docs/postgresql/13/intarray#id-1.11.7.27.8 Модуль intarray поддерживает индексы для операторов &&, @>, <@ и @@, а также обычную проверку равенства массивов.Т.е. разница со стандартным GIN-индексом array_ops только в операторе @@ .

intarray -вский GIN/gin__int_ops со стандартным всеядным GIN/array_ops применительно к массивам целых чисел.

MelkijТак ведь не существует gin__int_ops для int8[]Вот и получили мы infinite loop ))
Речь о массивах, для которых существуют оба класса операторов. Таковыми являются только int4[] .

Это не я.