Postgres 10

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 categories(
  id integer
, name text
, path integer[]
, parent_id integer
, depth integer
, slug text
)
insert into categories(id,name,path,parent_id,depth, slug)
values (1,'name1',array[1],null,0, 'slug1')
     , (2,'name2',array[1,2],1,1, 'slug2')
     , (3,'name3',array[1,2,3],2,2, 'slug3')
     , (5,'name5',array[5],null,0,'slug5')
     , (9,'name9',array[5,9],5,1,'slug9')
;
5 rows affected

select * from categories;


id
name
path
parent_id
depth
slug


1
name1
{1}
null
0
slug1

2
name2
{1,2}
1
1
slug2

3
name3
{1,2,3}
2
2
slug3

5
name5
{5}
null
0
slug5

9
name9
{5,9}
5
1
slug9


create table items(
  id integer
, name text
)
insert into items(id,name)
values (1,'nameitems1');
1 rows affected

select * from items 


id
name


1
nameitems1


create table item_categories(
  id integer,
  item_id integer,
  category_id integer
)
insert into item_categories(id,item_id, category_id)
values (1, 1, 3)
     , (2, 1, 9)
;
2 rows affected

select
     c.id,
     json_agg(to_json(d) order by c2.depth)
from categories as c
    inner join categories as c2 on
        c2.id = any(c.path)
    cross join lateral(select c.name, c.slug) as d
where
    exists (
        select *
        from item_categories as tt
        where
            tt.item_id = 1 and
            tt.category_id = c.id
    )
group by
    c.id


id
json_agg


3
[{"name":"name3","slug":"slug3"}, {"name":"name3","slug":"slug3"}, {"name":"name3","slug":"slug3"}]

9
[{"name":"name9","slug":"slug9"}, {"name":"name9","slug":"slug9"}]

id	name	path	parent_id	depth	slug
1	name1	{1}	null	0	slug1
2	name2	{1,2}	1	1	slug2
3	name3	{1,2,3}	2	2	slug3
5	name5	{5}	null	0	slug5
9	name9	{5,9}	5	1	slug9

id	json_agg
3	[{"name":"name3","slug":"slug3"}, {"name":"name3","slug":"slug3"}, {"name":"name3","slug":"slug3"}]
9	[{"name":"name9","slug":"slug9"}, {"name":"name9","slug":"slug9"}]