Здравствуйте! У нас в базе существует таблица, хранящая иерархию ( Parent_id -child_id). Тип id узлов иерархии- bigint. Тип выбран давно и его уже нельзя менять (во многих местах используется). Недавно понадобилось дополнительно произвести расчет данной иерархии ( с большим количеством данным). Кроме того, нужно было хранить цепочку (Materialized path), на основе которой получена связь parent_id-child_id. Пример :

parent_id-15926816
child_id - 15706021

Цепочка элементов

lpath- 15926816.15762121.15917825.16109332.15706021

В качестве типа поля lpath я стал использовать ltree.




В функциях часто нужно находить записи , где lpath содержит определенный участок:



План выполнения:





Выяснилось, что если таблица public.test_gelt содержит 1 млн записей, то выборка достаточно быстрая, однако на реальных данных в 100 млн записей, даже при использовании индекса path_gist_idx работа запроса увеличивается до 2 минут. Подозреваю,что я плохо оценил скорость ltree на больших данных. Не хочется переделывать plpg-функции, ориентированные на работу с ltree.

Что можете посоветовать для увеличения выборки данных ltree по индексу?
Какие могут быть альтернативные типы данных для хранения и быстрого поиска в materialized path с учетом 100 миллионной таблицы?

лопата, проверил, размер индекса path_gist_idx - 15 ГБ . Размер test_gelt - 3 ГБ. Получается индекс в любом случае будет хранится на HDD, а не в памяти, ибо у нас не так много ОЗУ. Как оптимизировать скорость выборки?

Уважаемые коллеги, спасибо за советы! Совет buddy_ekb мне показался особо ценным. Т.к. особо не хотелось менять функции по работе с полями ltree, я подумал про смену формата поля lpath на int[] из модуля intarray от тех же Бартунова и Сигаева. Поначалу были сомнения, поскольку тип id связи - bigint. Однако таких связей все 100 млн и вряд ли они будут серьезно прибавляться. Поэтому конвертирую parent_id и child_id в int и помещаю в массив int[] вместо lpath.

Далее, создаю индекс на новом поле-пути:





Соответственно поиск цепочек в поле int[], содержащих известные одиночные связи стал следующим:





В результате такого преобразования:

-с наименьшими изменениями я модифицировал функции для работы с цепочками
-приблизительно раз в 10 ускорил выборку цепочек, где присутствуют те или иные одиночные связи.
-размер индекса ipath_gelt_idx оказался 1.5 Гб.


Вывод:
-видимо ltree хранит данные в формате text, поэтому индекс по этому полю был столь большим (15 Гб)
-если в цепочке присутствуют только числа, то лучше использовать intarray вместо ltree. при больших объемах таблицы цепочек замедление поиска особенно ощутимо.