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) as
select n,nullif(n-1,0)
from generate_series(1,40)n;
| setseed |
|---|
SELECT 1
SELECT 40
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;
| id | parent | lvl |
|---|---|---|
| 40 | 39 | 1 |
| 39 | 38 | 2 |
| 38 | 37 | 3 |
| 37 | 36 | 4 |
| 36 | 35 | 5 |
| 35 | 34 | 6 |
| 34 | 33 | 7 |
| 33 | 32 | 8 |
| 32 | 31 | 9 |
| 31 | 30 | 10 |
| 30 | 29 | 11 |
| 29 | 28 | 12 |
| 28 | 27 | 13 |
| 27 | 26 | 14 |
| 26 | 25 | 15 |
| 25 | 24 | 16 |
| 24 | 23 | 17 |
| 23 | 22 | 18 |
| 22 | 21 | 19 |
SELECT 19
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