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 buildings (
    id_building serial4 NOT NULL,
    street  text NULL,
    CONSTRAINT buildings_pkey PRIMARY KEY (id_building)
);

CREATE TABLE


CREATE TABLE families (
    id_family serial4 NOT NULL,
    id_building int NOT NULL,
    name    text NULL,
    CONSTRAINT families_pkey PRIMARY KEY (id_family),
    CONSTRAINT families_id_building_fkey FOREIGN KEY (id_building) REFERENCES buildings (id_building)
);

CREATE TABLE


CREATE TABLE people (
    id_person serial4 NOT NULL,
    id_family int NOT NULL,
    name    text NULL,
    CONSTRAINT people_pkey PRIMARY KEY (id_person),
    CONSTRAINT people_id_family_fkey FOREIGN KEY (id_family) REFERENCES families (id_family)
);

CREATE TABLE


WITH b AS (
    INSERT INTO buildings (street)
    VALUES
        ('St Michael''s Rd'),
        ('Crossford St'),
        ('Stockwell Park Cres')
    RETURNING id_building, street
),
f AS (
    INSERT INTO families (id_building, name)
    SELECT b.id_building, f.name
    FROM b
    JOIN (VALUES
        ('St Michael''s Rd', 'White'),
        ('St Michael''s Rd', 'Brown'),
        ('Stockwell Park Cres', 'Smith')
    ) AS f(street, name) ON f.street = b.street
    RETURNING id_family, name
)
INSERT INTO people (id_family, name)
SELECT f.id_family, p.name
FROM f
JOIN (VALUES
    ('White', 'John'),
    ('White', 'Jenny'),
    ('White', 'Pepy'),
    ('Brown', 'Conny'),
    ('Smith', 'John')
) AS p(family, name) ON p.family = f.name;

INSERT 0 5


SELECT *
FROM  buildings;

SELECT *
FROM families;

SELECT *
FROM people;


id_building
street


1
St Michael's Rd

2
Crossford St

3
Stockwell Park Cres



SELECT 3




id_family
id_building
name


1
1
Brown

2
1
White

3
3
Smith



SELECT 3




id_person
id_family
name


1
2
John

2
2
Jenny

3
2
Pepy

4
1
Conny

5
3
John



SELECT 5