Postgres 15

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 things(
  thing_id serial PRIMARY KEY
, thing_name text NOT NULL
, destination_id bigint
);

INSERT INTO things(thing_id, thing_name) VALUES
  (10, 'thing 10')
, (15, 'thing 15')
;

CREATE TABLE parent_destination(
  destination_id serial PRIMARY KEY
);

CREATE TABLE child_destination(
  destination_id SERIAL PRIMARY KEY
, destination_name text NOT NULL
, CONSTRAINT fk_child_destination_parent_destination FOREIGN KEY (destination_id) REFERENCES parent_destination (destination_id)
);

CREATE TABLE

INSERT 0 2

CREATE TABLE

CREATE TABLE

WITH upd AS (
   UPDATE things t
   SET    destination_id = nextval(pg_get_serial_sequence('things', 'thing_id'))
   RETURNING thing_id, thing_name, destination_id
   )
, ins_parent AS (   
   INSERT INTO parent_destination(destination_id)
   SELECT destination_id
   FROM   upd
   )
INSERT INTO child_destination(destination_id, destination_name)
SELECT destination_id, concat('[thing]', thing_name)
FROM   upd;

INSERT 0 2

TABLE things;
TABLE parent_destination;
TABLE child_destination;

thing_id
thing_name
destination_id

10
thing 10
1

15
thing 15
2

SELECT 2

destination_id

1

2

SELECT 2

destination_id
destination_name

1
[thing]thing 10

2
[thing]thing 15

SELECT 2