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 mytable(
link VARCHAR(1) NOT NULL PRIMARY KEY
,link_path VARCHAR(5)
,info VARCHAR(5)
);
INSERT INTO mytable(link,link_path,info) VALUES ('a','b','asdjh');
INSERT INTO mytable(link,link_path,info) VALUES ('w','y','akhaq');
INSERT INTO mytable(link,link_path,info) VALUES ('b','c','uiqwd');
INSERT INTO mytable(link,link_path,info) VALUES ('c',null, 'isado');
INSERT INTO mytable(link,link_path,info) VALUES ('y','z','qwiuu');
INSERT INTO mytable(link,link_path,info) VALUES ('z',null, 'nzabo');
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
with recursive cte as (
select t.* from mytable t where link = 'a'
union all
select t.*
from cte c
inner join mytable t on t.link = c.link_path
)
select * from cte
| link | link_path | info |
|---|---|---|
| a | b | asdjh |
| b | c | uiqwd |
| c | null | isado |
SELECT 3