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 incoming_records(supervisor_id,emp_id)as values
( null ,'rt01') --top level resources with blank supervisor id
,('rt01','rt02')
,('rt02','rt03')
,('rt03','rt04')
,('rt04','rt05')
,('rt05','rt06')
,('rt06','rt07')
,('rt07','rt08')
,( null ,'rt10') --Another top level resource
,('rt10','rt11')
,('rt10','rt12')
,('rt10','rt13')
--loops of increasing length:
,('rt14','rt14')
,('rt15','rt16')
,('rt16','rt15')
,('rt17','rt18')
,('rt18','rt19')
,('rt19','rt17')
,('rt20','rt21')
,('rt21','rt22')
,('rt22','rt23')
,('rt23','rt20');
SELECT 22
WITH recursive tmp AS (
SELECT supervisor_id,
emp_id
FROM incoming_records
UNION ALL
SELECT tmp.supervisor_id,
t.emp_id
FROM tmp
INNER JOIN incoming_records AS t
ON t.supervisor_id = tmp.emp_id
)CYCLE emp_id SET is_cycle USING path
SELECT * FROM tmp
WHERE is_cycle;
| supervisor_id | emp_id | is_cycle | path |
|---|---|---|---|
| rt14 | rt14 | t | {(rt14),(rt14)} |
| rt15 | rt16 | t | {(rt16),(rt15),(rt16)} |
| rt16 | rt15 | t | {(rt15),(rt16),(rt15)} |
| rt17 | rt18 | t | {(rt18),(rt19),(rt17),(rt18)} |
| rt18 | rt19 | t | {(rt19),(rt17),(rt18),(rt19)} |
| rt19 | rt17 | t | {(rt17),(rt18),(rt19),(rt17)} |
| rt20 | rt21 | t | {(rt21),(rt22),(rt23),(rt20),(rt21)} |
| rt21 | rt22 | t | {(rt22),(rt23),(rt20),(rt21),(rt22)} |
| rt22 | rt23 | t | {(rt23),(rt20),(rt21),(rt22),(rt23)} |
| rt23 | rt20 | t | {(rt20),(rt21),(rt22),(rt23),(rt20)} |
SELECT 10