Postgres 15

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 asset (id int, name text, groupid int);
insert into asset values (1, 'Ass1', 1), (2, 'Ass2', 1), (3, 'Ass3', 2), (4, 'Ass4', 3), (5, 'Ass5', 4), (6, 'Ass6', 4), (7, 'Ass7', 4);
create table assetgroup (id int, name text, path text, level int);
insert into assetgroup values (1, 'grp1', '1', 1), (2, 'grp2', '1.2', 2), (3, 'grp3', '3', 1), (4, 'grp4', '3.4', 2), (5, 'grp5', '5', 1)

CREATE TABLE



INSERT 0 7



CREATE TABLE



INSERT 0 5


select * from assetgroup


id
name
path
level


1
grp1
1
1

2
grp2
1.2
2

3
grp3
3
1

4
grp4
3.4
2

5
grp5
5
1



SELECT 5


with recursive cte as (
   select g.id, g.name, g.path, g.level, g.level level1, g.id id1, g.path p1 
   from assetgroup g
   union all
   select c.id, c.name, c.path, c.level, g1.level level1, g1.id id1, g1.path p1 from cte c 
   join assetgroup g1 on g1.path ~ ('^'||c.path) 
   where c.level1 < g1.level
)
select c.id, c.name, c.path, c.level, sum(case when a.groupid is null then 0 else 1 end) c from cte c 
left join asset a on a.groupid = c.id1
group by c.id, c.name, c.path, c.level
order by c.id


id
name
path
level
c


1
grp1
1
1
3

2
grp2
1.2
2
1

3
grp3
3
1
4

4
grp4
3.4
2
3

5
grp5
5
1
0



SELECT 5