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.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit |
SELECT 1
create table test (id int, service varchar, subservice varchar, description varchar, cost decimal(10,2));
CREATE TABLE
create table newtest(id int, description varchar, parent_id int, cost decimal(10,2));
CREATE TABLE
CREATE SEQUENCE seq_parent_id INCREMENT BY 1 START WITH 1 NO CYCLE;
CREATE SEQUENCE
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 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
insert into newtest(id, description, cost) select nextval('seq_parent_id'), service, 0 from test group by 2
INSERT 0 2
insert into newtest select nextval('seq_parent_id'), subservice, t2.id,0 from test t1 inner join newtest t2 on t1.service=t2.description and t2.parent_id is null group by 2,3
INSERT 0 4
insert into newtest
select t1.id,t1.description,t3.id,t1.cost from test t1
inner join newtest t2 on t1.service=t2.description and t2.parent_id is null
inner join newtest t3 on t1.subservice=t3.description and t3.parent_id=t2.id
INSERT 0 5
select * from newtest
| id | description | parent_id | cost |
|---|---|---|---|
| 1 | Cardiology | null | 0.00 |
| 2 | Tests | null | 0.00 |
| 3 | Cardioversion | 1 | 0.00 |
| 4 | Ecocardiography | 1 | 0.00 |
| 5 | Tests | 1 | 0.00 |
| 6 | Tests | 2 | 0.00 |
| 1386 | Electric Cardioversi on programmed | 3 | 2000.00 |
| 1385 | Electric Cardioversion programmed | 3 | 200.00 |
| 7000 | Chest Ultrasound | 4 | 100.00 |
| 204 | Calcium | 5 | 50.00 |
| 2044 | Calcium | 6 | 50.00 |
SELECT 11