Так и не нашел однозначного ответа как лучше сделать древовидную структуру по времени выполнения запросов.
Суть задачи: есть таблица древовидная таблица свойств объектов и таблица объектов, где у объекта может быть несколько свойств.
Древовидная таблица~1000 записей, объектов 1000000 записей.
В финал вышли 2 варианта.
1. Расширение intarray
таблица tree-свойства


таблица object



2. Расширение ltree
таблица tree-свойства


таблица object



По функционалу мне больше нравится 2-й вариант, пусть даже он и на диске много места занимает, но в первую очередь интересует общая производительность на такой структуре.

Melkij, спасибо за ответ.
я про приоритет в общем написал выше.
На 1000 чтений будет 1 запись, т.е. основной критерий быстрый поиск при SELECT.
Для меня выбор не очевиден, т.к. древовидные структуры до этого редко использовал (обычно пользовался тем что уже было), поэтому и спрашиваю.
По найденной информации народ хвалит ltree, но практики использования у меня не было, хотя на мой взгляд по идее реализация intarray c рекурсией должна быть быстрее, но вот вопрос а на сколько быстрее, т.е. удобство ltree неоспоримо, при этом если потеря производительности будет, например, в пределах 10%-это того стоит, если кратно уже печально.

Melkij,
меня запись не волнует, даже если и будут проблемы их можно решить, при
соотношении 1000 к 1 или 100000 к 1 (чтение/запись).
Есть типовые операции с деревьями на чтение, у меня вряд ли будет новая америка.
Основной вопрос я описал, насколько в среднем на наборе типовых операций на чтение ltree медленнее.
Если цена вопроса 10-30% это не страшно, более 100% уже повод задуматься.
Есть еще вариант держать все в JSON'е и с помощью jsonpath доставать ветви деревьев,но
там чую будет гигантская проблема на запись, и из-за размера данных JSON будет вываливаться в toast.

Melkij,
и еще раз, я с вами не спорю, вы пишете все абсолютно корректно на сравнение одной и той же операции.
Но вы меня не слышите. Я не говорю про конкретную операцию.
Я говорю про среднюю температуру по больнице на НАБОРЕ операций на чтение.
Мне нравится само дополнение ltree, я бы выбрал его, я хочу понять, что я теряю по производительности.
То, что я теряю, на физическом размещении данных+индекса меня не волнует, так же как и накладные расходы
при вставке, изменении значений.

Melkij,
Мы с вами зашли в тупик, ваши примеры не корректны.
Я сравниваю два сравнимых в одной категории инструмента, т.е. они оба могут решать все задачи.
В ваших примерах применение разное, что некорректно.
Я не использовал ltree и вопрос был практический, вы увели тему в академическую плоскость, где в общем случае вы правы.
У кого есть практика по ltree сможет сразу однозначно ответить на мой практический вопрос.

Maxim BogukВопрос в контексте "Я не говорю про конкретную операцию." - тоже исключительно академический.
Какие то задачи будет ltree на порядки быстрее решать... какие то задачи integer[] будет быстрее решать тоже на порядки быстрее,
на каких то они будут сравнимые.

Можно легко придумать задачи и на первый случай и на второй случай и на третий случай.

Например задача перенести свойство в другую ветку дерева - в первой модели почти бесплатное... а во второй превращается в полный ад с обновлением всех обьектов (и там разница будет не на 1 порядок а на все 6 легко).

PS: кстати в общем случае intarray расширение для использования integer[] не нужно.

PPS: а какого типа индекс вы хотите использовать на весьма неожиданный для меня тип ltree[] т.е. на массив ltree и каким именно образом? Насколько я понимаю у вас там будут больши сложности с этим.
Я понимаю, что в частном конкретном запросе результаты будут разные.
Меня пока не интересует производительность связанная с вставкой и обновлением, я писал неоднократно выше.
массив ltree нужен потому как у одного объекта будет привязка к НЕСКОЛЬКИМ веткам дерева, а не одно как по классике.
Другого способа с ltree разместить данные с этими вводными я не знаю.
используя ltree @> ltree[] (из доки) я смогу найти объекты-потомки любого узла.
В доке написано, что @>использует индексы в отличии от ^@>.

про intarray читал, что только с ним работают индексы с операциями над массивами, возможно уже не так, я его года 3-4 назад смотрел, мне понравилось расширение.

qwwq,
да конечно Pk будет суррогатный.
А тэг или что то более серьезное какая разница.
Добавив JSONB-column в таблицу tree можно что угодно разметить.
Я просто упростил.