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?.
drop table if exists access_level;
create table access_level(id serial primary key, parent_id int, child_id int, level int, entity text);
insert into access_level (parent_id, child_id, level, entity) values
(11, 22, 4, 'a'),
(22, 33, 5, 'a'),
(33, 44, 6, 'a'),
(11, 22, 7, 'b'),
(22, 33, 4, 'b'),
(33, 44, 5, 'b');
DROP TABLE
CREATE TABLE
INSERT 0 6
with recursive cte as (
select parent_id, child_id, level, entity
from access_level t
where not exists (
select from access_level l
where l.child_id = t.parent_id)
union all
select t.parent_id, t.child_id, least(c.level, t.level), t.entity
from cte c
join access_level t
on t.parent_id = c.child_id and t.entity = c.entity
)
select *
from cte
order by entity, parent_id
| parent_id | child_id | level | entity |
|---|---|---|---|
| 11 | 22 | 4 | a |
| 22 | 33 | 4 | a |
| 33 | 44 | 4 | a |
| 11 | 22 | 7 | b |
| 22 | 33 | 4 | b |
| 33 | 44 | 4 | b |
SELECT 6