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?.

select version();


version


PostgreSQL 10.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit



SELECT 1


create table t(id int, name varchar(50),parent int)

CREATE TABLE


insert into t 
select 1          ,'ab'             ,null union all
       select 21         ,'aa'             ,1 union all
       select 33         ,'dd'             ,21  union all
       select 55         ,'ee'             ,null union all
       select 66         ,'bb'             ,55 union all
       select 77         ,'cc'             ,33 

INSERT 0 6


with recursive cte
  as (select id,name,parent,cast(id as varchar(50)) as concat_val,id as root,cast(1 as int)  as lvl
        from t
       where id=33
       union all
       select a.id,a.name,a.parent,cast(concat(a.id,'/',b.concat_val) as varchar(50)),b.root,cast(b.lvl+1 as int)
         from t a
         join cte b
           on b.parent=a.id
        )
  ,cte2
  as (select id,name,parent,cast(id as varchar(50)) as concat_val,id as root,cast(1 as int)  as lvl
        from t
       where id=33
       union all
       select a.id,a.name,a.parent,cast(concat(a.id,'/',b.concat_val) as varchar(50)),b.root,cast(b.lvl-1 as int)
         from t a
         join cte2 b
           on b.id=a.parent
        ) 
select distinct * from (        
select * 
  from cte
union all  
select * 
  from cte2
)x
order by lvl desc


id
name
parent
concat_val
root
lvl


1
ab
null
1/21/33
33
3

21
aa
1
21/33
33
2

33
dd
21
33
33
1

77
cc
33
77/33
33
0



SELECT 4