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 tbl1 (
   tbl1_id serial PRIMARY KEY
 , txt     text
);

CREATE  TABLE tbl2 (
   tbl2_id serial PRIMARY KEY
 , tbl1_id int
);

CREATE  TABLE tbl3 (
   tbl3_id serial PRIMARY KEY
 , tbl1_id int
);

CREATE TABLE

CREATE TABLE

CREATE TABLE

WITH i1 AS (
   INSERT INTO tbl1(txt)
   VALUES ('foo')
   RETURNING tbl1_id
   )
INSERT INTO tbl2(tbl1_id)
SELECT * FROM i1
RETURNING *;   -- optional, for display

tbl2_id
tbl1_id

1
1

INSERT 0 1

WITH ins1 AS (
   INSERT INTO tbl1(txt)
   VALUES ('bar')
   RETURNING tbl1_id
   )
 , ins2 AS (
   INSERT INTO tbl2(tbl1_id)
   SELECT tbl1_id FROM ins1
   )
INSERT INTO tbl3(tbl1_id)
SELECT tbl1_id FROM ins1;

INSERT 0 1

TABLE tbl1;
TABLE tbl2;
TABLE tbl3;

tbl1_id
txt

1
foo

2
bar

SELECT 2

tbl2_id
tbl1_id

1
1

2
2

SELECT 2

tbl3_id
tbl1_id

1
2

SELECT 1