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 c1.id, c1.name, c1.slug, c1.parent_id, c1.depth FROM categories AS c1 
WHERE ARRAY[c1.id] <@ (SELECT c2.path FROM categories AS c2 WHERE c2.id= 
(SELECT category_id FROM item_categories WHERE item_id=1 LIMIT 1)) ORDER BY c1.depth;


id
name
slug
parent_id
depth


1
name1
slug1
null
0

2
name2
slug2
1
1

3
name3
slug3
2
2

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