Вмоме частном случае связи дерева лежат в jsonb поле. Такова структура данных, переделывать которую не представлялось возможным в связи с текущей бизнес логикой. Немного переделав запрос, можно обращаться и к обычным полям таблицы.
Минимальный набор столбцов у меня такой:
create table if not exists data_table
(
id serial not null
constraint data_table_pkey
primary key,
data jsonb
);
В поле data прилетает json, у которого есть поле parent_id, по которому осуществляется связь. Сам запрос на получение полного дерева выглядит так:
WITH RECURSIVE r AS (
SELECT
id,
data ->> 'parent_id' as parent
FROM data_table
-- Дополнительное условие выборки данных
-- WHERE some_additional_field = 'some value'
UNION
SELECT
data_table.id,
data_table.data ->> 'parent_id'
FROM data_table
JOIN r
-- Тут надо обратить внимание на вызов функции to_char т.к. в json поле paren_id будет как строка
ON data_table.data ->> 'parent_id' = to_char(r.id, '10')
)
SELECT *
FROM r
-- Опциональная сортировка данных
ORDER BY id ASC;
Рекурсивный запрос начинается с union.
Данный запрос выдает дерево из 10 000 элементов примерно за 191 млс. на моей разработческой машине с выборкой дополнительных полей и дополнительным условием выборки. Цифра не самя лучшая, но для стендовых условий вполне приемлема. Если брать в расчет что в коде, выполняющем данный запрос, в дальнейшей обработке, будет кэширование, то результат вполне оправдывает себя.
Результат работы на продакшене вполне хороший. Полная загрузка страницы с отработкой маршрутиризатора, контроллера, нескольких служебных запросов в бд, выборке дополнительных моделей длиться в среднем 200-230 млс.
