Postgres 14

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 delete_me 
(
  amount int,
  id text,
  reversed_by text
);
INSERT INTO delete_me (amount, id, reversed_by)
VALUES
( 12,'ea75921c', NULL     ),
(-12,'e72d92d9','ea75921c'),
(-12,'84529ee0', NULL     ),
( 12,'dc102220','84529ee0'),
(-12,'beca097b','dc102220'),
(  8,'c362b6c5', NULL     ),
( -8,'4e35510a','c362b6c5'),
( 10,'8fa22925', NULL     );
8 rows affected

with recursive cte as (
  select dm.*, id as root, 1 as level
  from delete_me dm
  where dm.reversed_by is null
  union all
  select ch.*, cte.root, cte.level + 1
  from delete_me ch
    join cte on cte.id = ch.reversed_by
)
select *
from cte
order by root, level


amount
id
reversed_by
root
level


-12
84529ee0
null
84529ee0
1

12
dc102220
84529ee0
84529ee0
2

-12
beca097b
dc102220
84529ee0
3

10
8fa22925
null
8fa22925
1

8
c362b6c5
null
c362b6c5
1

-8
4e35510a
c362b6c5
c362b6c5
2

12
ea75921c
null
ea75921c
1

-12
e72d92d9
ea75921c
ea75921c
2

amount	id	reversed_by	root	level
-12	84529ee0	null	84529ee0	1
12	dc102220	84529ee0	84529ee0	2
-12	beca097b	dc102220	84529ee0	3
10	8fa22925	null	8fa22925	1
8	c362b6c5	null	c362b6c5	1
-8	4e35510a	c362b6c5	c362b6c5	2
12	ea75921c	null	ea75921c	1
-12	e72d92d9	ea75921c	ea75921c	2