У меня есть простой вопрос. Почему-то я не смог найти окончательного ответа.
Насколько синтаксис WITH RECURSIVE оптимизирован в PostgreSQL? Под этим я подразумеваю: это просто синтаксический сахар для ряда нерекурсивных запросов, ИЛИ это больше одно утверждение, которое, несмотря на то, что его сложная семантика была оптимизирована в целом. Следующий вопрос - сколько можно оптимизировать этот синтаксис? Конечно, некоторые конкретные данные по этому вопросу приветствуются.
Мой опыт в том, что он действительно очень хорошо оптимизирован.
Просмотрите план выполнения вашего запроса, сгенерированного EXPLAIN ANALYZE, и вы увидите, насколько "дорогостоящим" он является на самом деле (и затем сравните его, например, с самозаписываемой рекурсивной функцией)
Я нашел, что он оптимизирован до определенной точки.
Различные подзапросы повторно используются, как ожидалось, и оптимизируются индивидуально, а Postgres оптимизирует последний, как и любой другой запрос.
Моя главная проблема с этим связана с тем, что он не будет вводить ограничения в CTE, когда это возможно.
Например:
with recursive
parents as (
select node.id,
node.parent_id
from nodes as node
union all
select node.id,
parent.parent_id
from parents as node
join nodes as parent on parent.id = node.parent_id
)
select parent_id
from parents
where id = 2;
Postgres идеально понимал бы в приведенном выше примере, что (поскольку node.id возвращается как есть), он может делать:
with recursive
parents as (
select node.id,
node.parent_id
from nodes as node
where id = 2
union all
select node.id,
parent.parent_id
from parents as node
join nodes as parent on parent.id = node.parent_id
)
select parent_id
from parents;
... и используйте индексный просмотр первичного ключа. На практике это будет делать точно, когда CTE говорит ему: рекурсивно вытащить всех родителей для всех строк, поместите результирующий набор в неназванную временную таблицу, если это необходимо, а затем проверьте каждую строку из набора результатов для id = 2.
Другими словами, CTE не сохраняет следы "исходных" таблиц/строк/столбцов, которые он возвращает. Пока это не будет оптимизировано правильно, создание представления по рекурсивному запросу в лучшем случае безумно.
Хорошим обходным решением в то же время является создание sql-функции:
create function parents(id int) as returns table (id int) $$
with recursive
parents as (
select node.id,
node.parent_id
from nodes as node
where id = $1
union all
select node.id,
parent.parent_id
from parents as node
join nodes as parent on parent.id = node.parent_id
)
select parent_id
from parents;
$$ language sql stable strict rows 5 cost 1;
Другая проблема заключается в том, что вы не можете использовать FOR UPDATE с рекурсивными CTE (по той же самой причине).