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 IF NOT EXISTS public.lvl_one (
    id      bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    name    text,
    CONSTRAINT lvl_one_pk PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS public.lvl_two (
    id          bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    lvl_one_id  bigint NOT NULL,    
    name        text,
    CONSTRAINT lvl_two_pk PRIMARY KEY (id),
    CONSTRAINT lvl_two_lvl_one_id_fk FOREIGN KEY (lvl_one_id) REFERENCES public.lvl_one
);
CREATE TABLE IF NOT EXISTS public.lvl_three (
    id          bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    lvl_two_id  bigint NOT NULL,    
    name        text,
    CONSTRAINT lvl_three_pk PRIMARY KEY (id),
    CONSTRAINT lvl_three_lvl_two_id_fk FOREIGN KEY (lvl_two_id) REFERENCES public.lvl_two
);

-- initial data
INSERT INTO lvl_one(name)               VALUES ('Honda'),   ('Ford'),       ('Toyota');
INSERT INTO lvl_two(lvl_one_id, name)   VALUES (1,'Civic'), (1,'Passport'), (3,'Prius');
INSERT INTO lvl_three(lvl_two_id, name) VALUES (1,'door'),  (1,'window'),   (3,'trunk');
3 rows affected

3 rows affected

3 rows affected

SELECT one.id AS one_id, one.name AS one_name
     , two.id AS two_id, two.name AS two_name
     , three.id AS three_id, three.name AS three_name
FROM        lvl_one   AS one 
LEFT   JOIN lvl_two   AS two   ON one.id = two.lvl_one_id
LEFT   JOIN lvl_three AS three ON two.id = three.lvl_two_id
ORDER  BY one.id, two.id, three.id;


one_id
one_name
two_id
two_name
three_id
three_name


1
Honda
1
Civic
1
door

1
Honda
1
Civic
2
window

1
Honda
2
Passport
null
null

2
Ford
null
null
null
null

3
Toyota
3
Prius
3
trunk


WITH ins1 AS (
   INSERT INTO lvl_one(name)
   SELECT name
   FROM   lvl_one
   WHERE  id = 1  --  $1 here
   RETURNING id AS new_parent_id, name   -- just the one
   )
, sel2 AS (
   SELECT ins1.new_parent_id, t2.id, t2.name, nextval(pg_get_serial_sequence('lvl_two', 'id')) AS new_id
   FROM   ins1
   JOIN   lvl_two t2 ON t2.lvl_one_id = 1   --  $1 here
   )
, ins2 AS (
   INSERT INTO lvl_two(id, lvl_one_id, name) OVERRIDING SYSTEM VALUE 
   SELECT new_id, new_parent_id, name
   FROM   sel2
   )
, sel3 AS (
   SELECT sel2.new_id AS new_parent_id, t3.id, t3.name, nextval(pg_get_serial_sequence('lvl_three', 'id')) AS new_id
   FROM   sel2
   JOIN   lvl_three t3 ON t3.lvl_two_id = sel2.id  -- old parent ID
   )
, ins3 AS (
   INSERT INTO lvl_three(id, lvl_two_id, name) OVERRIDING SYSTEM VALUE 
   SELECT new_id, new_parent_id, name
   FROM   sel3
   )
SELECT ins1.new_parent_id AS lvl1_id, ins1.name AS lvl1_name
     , sel2.new_id        AS lvl2_id, sel2.name AS lvl2_name
     , sel3.new_id        AS lvl3_id, sel3.name AS lvl3_name
FROM   ins1 
LEFT   JOIN sel2 USING (new_parent_id)
LEFT   JOIN sel3 ON sel3.new_parent_id = sel2.new_id
ORDER  BY lvl1_id, lvl2_id, lvl3_id;


lvl1_id
lvl1_name
lvl2_id
lvl2_name
lvl3_id
lvl3_name


4
Honda
4
Civic
4
door

4
Honda
4
Civic
5
window

4
Honda
5
Passport
null
null

one_id	one_name	two_id	two_name	three_id	three_name
1	Honda	1	Civic	1	door
1	Honda	1	Civic	2	window
1	Honda	2	Passport	null	null
2	Ford	null	null	null	null
3	Toyota	3	Prius	3	trunk