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

select version();


version


PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


create table if not exists stack (
    parent int,
    child int
);
insert into stack (parent, child) values
    (1,2),
    (2,3),
    (3,4),
    (4,5),
    (5,6),
    (6,7),
    (7,8),
    (8,9),
    (9,null),
    (1,7),
    (7,8),
    (8,9),
    (9,null);
13 rows affected

with recursive
  good_stack as (select distinct * from stack)
 ,cte as 
  (
    select
      parent,
      child,
      0 as level,
      array[parent,
      child] as path
    from good_stack
    where good_stack.parent = 1
    union all
    select
      good_stack.parent,
      good_stack.child,
      cte.level + 1,
      cte.path || good_stack.child
    from cte
    left join good_stack on cte.child = good_stack.parent
    where cte.child is not null and good_stack.child is not null
  )
select * from cte;


parent
child
level
path


1
2
0
{1,2}

1
7
0
{1,7}

7
8
1
{1,7,8}

2
3
1
{1,2,3}

3
4
2
{1,2,3,4}

8
9
2
{1,7,8,9}

4
5
3
{1,2,3,4,5}

5
6
4
{1,2,3,4,5,6}

6
7
5
{1,2,3,4,5,6,7}

7
8
6
{1,2,3,4,5,6,7,8}

8
9
7
{1,2,3,4,5,6,7,8,9}

parent	child	level	path
1	2	0	{1,2}
1	7	0	{1,7}
7	8	1	{1,7,8}
2	3	1	{1,2,3}
3	4	2	{1,2,3,4}
8	9	2	{1,7,8,9}
4	5	3	{1,2,3,4,5}
5	6	4	{1,2,3,4,5,6}
6	7	5	{1,2,3,4,5,6,7}
7	8	6	{1,2,3,4,5,6,7,8}
8	9	7	{1,2,3,4,5,6,7,8,9}