Как подсчитать данные для каждого узла (включая вложения) дерева?

Question

[firstmixon]

Есть таблица описывающая дерево

Create Table tree(id int, parent_id int, name string(10));

Как при использовании WITH RECURSIVE подсчитать кол-во дочерних узлов (в том числе вложенных)?
При попытке обратится к данным, пишет что подзапросы не поддерживаются.

ЗЫ может кто знает способ в постгресе создавать временные таблицы для расчетов, как MS sql server?

Comments

[Антон Антон]

поменять принцип построения дерева на materialized path или nestet set и не мучить СУБД рекурсивными запросами

[Akina]

Схематично:

WITH RECURSIVE
cte (id, parent_id, path) AS (
  SELECT id, NULL, id
  FROM table
  WHERE parent_id IS NULL
UNION ALL
  SELECT t.id, t.parent_id, c.path || t.id
  FROM table t
  JOIN cte с ON t.parent_id = c.id
)
SELECT c1.id, c1.parent_id, COUNT(*)
FROM cte c1
JOIN cte c2 ON c2.path LIKE c1.path || '%'
GROUP BY 1, 2

может кто знает способ в постгресе создавать временные таблицы для расчетов

CREATE TEMPORARY TABLE

[firstmixon]

Akina, Есть ли вариант memoryTable? в стандартной версии?

[Akina]

firstmixon, такого варианта нет.
Но есть опционная переменная temp_buffers, которая определяет количество памяти под временные таблицы на одно соединение. Если размер временных таблиц сессии не превосходит указанного объёма, то их данные полностью кэшированы в памяти, и соответственно скорость работы с ними высокая.
Значение переменной можно установить как постоянно для всех сессий (в postgresql.conf), так и индивидуально для текущей сессии (SET temp_buffers = 'XXX';).
См. https://postgrespro.ru/docs/postgresql/17/runtime-...
На всякий случай рекомендую помнить, что временные таблицы не вакуумятся, так что для интенсивно изменяющейся таблицы без явного ANALYZE планировщик может использовать устаревшую статистику данных и выдавать неоптимальный план. А без VACUUM - и вылететь за выделенный объём памяти за счёт неочищенного пространства. Так что явный VACUUM ANALYZE temp_table; может быть небесполезной операцией.