Postgres 12

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 e_8_user(
   user_id INTEGER  NOT NULL PRIMARY KEY 
  ,boss    INTEGER  NOT NULL
);
INSERT INTO e_8_user(user_id,boss) VALUES (1,1);
INSERT INTO e_8_user(user_id,boss) VALUES (2,1);
INSERT INTO e_8_user(user_id,boss) VALUES (3,2);
INSERT INTO e_8_user(user_id,boss) VALUES (4,3);
INSERT INTO e_8_user(user_id,boss) VALUES (5,2);
INSERT INTO e_8_user(user_id,boss) VALUES (6,6);
INSERT INTO e_8_user(user_id,boss) VALUES (7,6);

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

select * from e_8_user;

user_id
boss

1
1

2
1

3
2

4
3

5
2

6
6

7
6

SELECT 7

with recursive cte as (
	select u.user_id, u.boss, 1 as lvl from e_8_user u
	union all
	select u.user_id, c.boss, lvl + 1
	from cte c 
	inner join e_8_user u on u.boss = c.user_id and u.user_id != c.user_id
)
select user_id, boss 
from cte c
where lvl = (select max(c1.lvl) from cte c1 where c1.user_id = c.user_id) 
order by user_id

user_id
boss

1
1

2
1

3
1

4
1

5
1

6
6

7
6

SELECT 7

with recursive cte as (
	select u.user_id, u.boss, 1 as lvl from e_8_user u
	union all
	select u.user_id, c.boss, lvl + 1
	from cte c 
	inner join e_8_user u on u.boss = c.user_id and u.user_id != c.user_id
)
select distinct on (user_id) user_id, boss 
from cte c
order by user_id, lvl desc

user_id
boss

1
1

2
1

3
1

4
1

5
1

6
6

7
6

SELECT 7