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.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit


CREATE TABLE if not exists cars (
    id serial PRIMARY KEY, 
    parent_code INT,
    descr varchar NOT NULL
);

-- Level 1
INSERT INTO cars(parent_code, descr) VALUES (null, 'Volkswagen');
INSERT INTO cars(parent_code, descr) VALUES (null, 'Tesla');

-- Level 2
INSERT INTO cars(parent_code, descr) VALUES (1, 'Polo');
INSERT INTO cars(parent_code, descr) VALUES (1, 'Golf');

INSERT INTO cars(parent_code, descr) VALUES (2, '3');
INSERT INTO cars(parent_code, descr) VALUES (2, 'S');
INSERT INTO cars(parent_code, descr) VALUES (2, 'X');

-- Level 3
INSERT INTO cars(parent_code, descr) VALUES (3, 'diesel');
INSERT INTO cars(parent_code, descr) VALUES (3, 'gasoline');

INSERT INTO cars(parent_code, descr) VALUES (4, 'diesel');
INSERT INTO cars(parent_code, descr) VALUES (4, 'gasoline');

INSERT INTO cars(parent_code, descr) VALUES (5, 'electric');
INSERT INTO cars(parent_code, descr) VALUES (6, 'electric');
INSERT INTO cars(parent_code, descr) VALUES (7, 'electric');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select * from cars


id
parent_code
descr


1
null
Volkswagen

2
null
Tesla

3
1
Polo

4
1
Golf

5
2
3

6
2
S

7
2
X

8
3
diesel

9
3
gasoline

10
4
diesel

11
4
gasoline

12
5
electric

13
6
electric

14
7
electric


with recursive cte as (
   select id, descr, parent_code, 1 as lvl_, array[id] as path_
   from cars 
   where parent_code is null
   union all
   select t1.id, lpad('->', t2.lvl_ * 4) || t1.descr, t1.parent_code, t2.lvl_ + 1, t2.path_||t1.id
   from cars t1
     join cte t2 on t1.parent_code = t2.id
)
select descr from cte
order by path_


descr


Volkswagen

  ->Polo

      ->diesel

      ->gasoline

  ->Golf

      ->diesel

      ->gasoline

Tesla

  ->3

      ->electric

  ->S

      ->electric

  ->X

      ->electric

id	parent_code	descr
1	null	Volkswagen
2	null	Tesla
3	1	Polo
4	1	Golf
5	2	3
6	2	S
7	2	X
8	3	diesel
9	3	gasoline
10	4	diesel
11	4	gasoline
12	5	electric
13	6	electric
14	7	electric