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 table chain_data
(
    id                    integer      not null,
    unique_identifiers_id integer      not null,
    chain_data_name       varchar(255) not null,
    carriage              boolean default false,
    left_id               integer      ,
    right_id              integer
);

INSERT INTO chain_data (id, unique_identifiers_id, chain_data_name, carriage, left_id, right_id) VALUES (10, 8, 'dddd_2', true, 22, null);
INSERT INTO chain_data (id, unique_identifiers_id, chain_data_name, carriage, left_id, right_id) VALUES (22, 8, 'shuba', false, 23, 10);
INSERT INTO chain_data (id, unique_identifiers_id, chain_data_name, carriage, left_id, right_id) VALUES (24, 8, 'viktor', false, null, 23);
INSERT INTO chain_data (id, unique_identifiers_id, chain_data_name, carriage, left_id, right_id) VALUES (23, 8, 'ivan', false, 24, 22);


SELECT * FROM chain_data
1 rows affected

1 rows affected

1 rows affected

1 rows affected



id
unique_identifiers_id
chain_data_name
carriage
left_id
right_id


10
8
dddd_2
t
22
null

22
8
shuba
f
23
10

24
8
viktor
f
null
23

23
8
ivan
f
24
22


WITH RECURSIVE chain AS (
    SELECT id, right_id
    FROM chain_data
    WHERE left_id IS NULL
    
    UNION 
    
    SELECT cd.id, cd.right_id
    FROM chain_data cd
    JOIN chain c ON c.right_id = cd.id
)
SELECT
    *
FROM
    chain


id
right_id


24
23

23
22

22
10

10
null


WITH RECURSIVE chain AS (
    SELECT id, right_id
    FROM chain_data
    WHERE left_id IS NULL
    
    UNION 
    
    SELECT cd.id, cd.right_id
    FROM chain_data cd
    JOIN chain c ON c.right_id = cd.id
)
SELECT
    string_agg(id::text, ', ')
FROM
    chain


string_agg


24, 23, 22, 10

id	unique_identifiers_id	chain_data_name	carriage	left_id	right_id
10	8	dddd_2	t	22	null
22	8	shuba	f	23	10
24	8	viktor	f	null	23
23	8	ivan	f	24	22