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 test (id int, service varchar, subservice varchar, description varchar, cost decimal(10,2));
CREATE SEQUENCE seq_parent_id INCREMENT BY 1 START WITH 1 NO CYCLE;
insert into test values(2044,'Tests','Tests','Calcium',50);
insert into test values(1385,'Cardiology','Cardioversion','Electric Cardioversion programmed',200);
insert into test values(204,'Cardiology','Tests','Calcium',50);
insert into test values(1386,'Cardiology','Cardioversion','Electric Cardioversi   on programmed',2000);
insert into test values(7000,'Cardiology','Ecocardiography','Chest Ultrasound',100);
insert into test values(7001,'Carology','Ecocardiography','Chest Ultrasound',100);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

alter table test add column parent_id int;
alter table test add constraint uniq_id unique(id);
with cte(id, description, parent_id,cost) as 
(select nextval('seq_parent_id'), service, null::bigint,0::decimal  
from test group by 2),
cte1(id, description, parent_id,cost) as 
(select nextval('seq_parent_id'), subservice, t2.id,0::decimal from test t1 inner join cte t2 on t1.service=t2.description and t2.parent_id is null group by 2,3)

insert into test(id, description, parent_id,cost)
select * from cte
union all
select * from cte1
union all
select t1.id,t1.description,t3.id,t1.cost from test t1 
inner join cte t2 on t1.service=t2.description and t2.parent_id is null
inner join cte1 t3 on t1.subservice=t3.description and t3.parent_id=t2.id
on conflict (id)
do update set parent_id=excluded.parent_id
14 rows affected

alter table test drop column service;
alter table test drop column subservice;
select * from test


id
description
cost
parent_id


1
Tests
0.00
null

2
Cardiology
0.00
null

3
Carology
0.00
null

4
Cardioversion
0.00
2

5
Ecocardiography
0.00
2

6
Ecocardiography
0.00
3

7
Tests
0.00
1

8
Tests
0.00
2

2044
Calcium
50.00
7

1385
Electric Cardioversion programmed
200.00
4

204
Calcium
50.00
8

1386
Electric Cardioversi   on programmed
2000.00
4

7000
Chest Ultrasound
100.00
5

7001
Chest Ultrasound
100.00
6

id	description	cost	parent_id
1	Tests	0.00	null
2	Cardiology	0.00	null
3	Carology	0.00	null
4	Cardioversion	0.00	2
5	Ecocardiography	0.00	2
6	Ecocardiography	0.00	3
7	Tests	0.00	1
8	Tests	0.00	2
2044	Calcium	50.00	7
1385	Electric Cardioversion programmed	200.00	4
204	Calcium	50.00	8
1386	Electric Cardioversi on programmed	2000.00	4
7000	Chest Ultrasound	100.00	5
7001	Chest Ultrasound	100.00	6