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?.
CREATE TABLE t
(F varchar(99), S varchar(99))
;
INSERT INTO t
(F, S)
VALUES
('a', 'b'),
('b', 'c'),
('b', 'd'),
('b', 'e'),
('e', 'f'),
('e', 'g'),
('b', 'm'),
('z', 'n'),
('m', 'k')
;
CREATE TABLE
INSERT 0 9
select *
from t
| f | s |
|---|---|
| a | b |
| b | c |
| b | d |
| b | e |
| e | f |
| e | g |
| b | m |
| z | n |
| m | k |
SELECT 9
with
recursive cte(id, path) as
(
select f, f::text from t where f not in(select s from t where s is not null)
union all
select t.s, cte.path || '/' || t.s from cte join t on cte.id = t.f
)
select id
,path[1] as root
,path[2] as l1
,path[3] as l2
,path[4] as l3
,path[5] as l4
from
(
select id
,regexp_split_to_array(path, '/') as path
from cte
) cte
| id | root | l1 | l2 | l3 | l4 |
|---|---|---|---|---|---|
| a | a | null | null | null | null |
| z | z | null | null | null | null |
| b | a | b | null | null | null |
| n | z | n | null | null | null |
| m | a | b | m | null | null |
| e | a | b | e | null | null |
| d | a | b | d | null | null |
| c | a | b | c | null | null |
| k | a | b | m | k | null |
| g | a | b | e | g | null |
| f | a | b | e | f | null |
SELECT 11