By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select setseed(.42);
create table the_hierarchy(id,parent,child) as
select n,nullif(n-1,0),nullif(n+1,40)
from generate_series(1,40)n;
| setseed |
|---|
SELECT 1
SELECT 40
with recursive cte as(
(select id,parent,child,1 as lvl
from the_hierarchy
order by id desc
limit 1)
union all
(select h.id,h.parent,h.child,cte.lvl+1
from the_hierarchy h join cte
on cte.parent=h.id
union all
select h.id,h.parent,h.child,cte.lvl+1
from the_hierarchy h join cte
on cte.child=h.id)
)--CYCLE id SET is_cycle USING path
select * from cte where lvl<20;
ERROR: recursive reference to query "cte" must not appear more than once LINE 12: from the_hierarchy h join cte ^
with recursive cte as(
(select id,parent,1 as lvl
from the_hierarchy
order by id desc
limit 1)
union all
(select h.id,h.parent,cte.lvl+1
from the_hierarchy h join cte
on cte.parent=h.id
union all (select 1,1,19 limit 0))
) CYCLE id SET is_cycle USING path
select * from cte where lvl<20;
ERROR: with a SEARCH or CYCLE clause, the right side of the UNION must be a SELECT