Postgres 13

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 extension if not exists ltree;

drop table if exists my_table;
create table my_table(
    id int primary key,
    tree ltree,
    person_id int);
insert into my_table values
    (1, '1', 1),
    (2, '1.L2', 2),
    (3, '1.R2', 3),
    (4, '1.L2.L3', 4),
    (5, '1.L2.R3', 5),
    (6, '1.L2.L3.L4', 6),
    (7, '1.L2.R3.R4', 7),
    (8, '1.L2.R3.L4', 13);

CREATE EXTENSION



DROP TABLE



CREATE TABLE



INSERT 0 8


select 
    tree, 
    nlevel(tree) as level, 
    subpath(tree, 0, -1) as parent,
    substring(subpath(tree, -1, 1)::text from '[R|L]') as side
from my_table


tree
level
parent
side


1
1

null

1.L2
2
1
L

1.R2
2
1
R

1.L2.L3
3
1.L2
L

1.L2.R3
3
1.L2
R

1.L2.L3.L4
4
1.L2.L3
L

1.L2.R3.R4
4
1.L2.R3
R

1.L2.R3.L4
4
1.L2.R3
L



SELECT 8


with recursive recursive_tree as (
    select *
    from my_table
    where tree = '1'
union all
    select t.*
    from my_table t
    join recursive_tree r 
        on subpath(t.tree, 0, -1) = r.tree
        and left(subpath(t.tree, -1, 1)::text, 1) = 'L'
    )
select *
from recursive_tree


id
tree
person_id


1
1
1

2
1.L2
2

4
1.L2.L3
4

6
1.L2.L3.L4
6



SELECT 4


select *
from my_table
where subpath(tree, 0, 1) = '1'
and tree::text not like '%R%'


id
tree
person_id


1
1
1

2
1.L2
2

4
1.L2.L3
4

6
1.L2.L3.L4
6



SELECT 4

tree	level	parent	side
1	1		null
1.L2	2	1	L
1.R2	2	1	R
1.L2.L3	3	1.L2	L
1.L2.R3	3	1.L2	R
1.L2.L3.L4	4	1.L2.L3	L
1.L2.R3.R4	4	1.L2.R3	R
1.L2.R3.L4	4	1.L2.R3	L