Postgres 12

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 main_table
(
    id        serial PRIMARY KEY,
    is_active BOOLEAN,
    name      VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE nested_table_1
(
    id   serial PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE nested_table_2
(
    id   serial PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE bridge_table_1
(
    main_table_id     INTEGER REFERENCES main_table (id),
    nested_table_1_id INTEGER REFERENCES nested_table_1 (id),
    PRIMARY KEY(main_table_id, nested_table_1_id)
);

CREATE TABLE bridge_table_2
(
    main_table_id     INTEGER REFERENCES main_table (id),
    nested_table_2_id INTEGER REFERENCES nested_table_2 (id),
    PRIMARY KEY(main_table_id, nested_table_2_id)
);
INSERT INTO main_table (is_active, name, id) VALUES (true, 'main 1', 1);
INSERT INTO main_table (is_active, name, id) VALUES (true, 'main 2', 2);
SELECT * FROM main_table;
INSERT INTO nested_table_1 (name, id) VALUES ('Contact', 1);
INSERT INTO nested_table_1 (name, id) VALUES ('Dropley', 2);
SELECT * FROM nested_table_1;
INSERT INTO nested_table_2 (name, id) VALUES ('nested table second 1', 1);
INSERT INTO nested_table_2 (name, id) VALUES ('nested table second 2', 2);
INSERT INTO nested_table_2 (name, id) VALUES ('nested table second 3', 3);
INSERT INTO nested_table_2 (name, id) VALUES ('Drop', 4);
SELECT * FROM nested_table_2;
INSERT INTO bridge_table_1 (main_table_id, nested_table_1_id) VALUES (1, 1);
INSERT INTO bridge_table_2 (main_table_id, nested_table_2_id) VALUES (1, 1);
-- Count for contact_compliants should be zero till here but its 1
INSERT INTO bridge_table_2 (main_table_id, nested_table_2_id) VALUES (1, 2);
-- Count for contact_compliants should be 1 after inserting above but its 2
SELECT * FROM bridge_table_1;
SELECT * FROM bridge_table_2;
1 rows affected

1 rows affected



id
is_active
name


1
t
main 1

2
t
main 2


1 rows affected

1 rows affected



id
name


1
Contact

2
Dropley


1 rows affected

1 rows affected

1 rows affected

1 rows affected



id
name


1
nested table second 1

2
nested table second 2

3
nested table second 3

4
Drop


1 rows affected

1 rows affected

1 rows affected



main_table_id
nested_table_1_id


1
1




main_table_id
nested_table_2_id


1
1

1
2


SELECT *
FROM main_table AS main
        INNER JOIN bridge_table_1 AS bt_1
                    ON bt_1.main_table_id = main.id
        INNER JOIN nested_table_1 AS nes_1
                    ON bt_1.nested_table_1_id = nes_1.id
        INNER JOIN bridge_table_2 AS bt_2
                    ON bt_2.main_table_id = main.id
        INNER JOIN nested_table_2 AS nes_2
                    ON nes_2.id = bt_2.nested_table_2_id;


id
is_active
name
main_table_id
nested_table_1_id
id
name
main_table_id
nested_table_2_id
id
name


1
t
main 1
1
1
1
Contact
1
2
2
nested table second 2

1
t
main 1
1
1
1
Contact
1
1
1
nested table second 1


SELECT *
FROM main_table AS main
        LEFT JOIN bridge_table_1 AS bt_1
                    ON bt_1.main_table_id = main.id
        LEFT JOIN nested_table_1 AS nes_1
                    ON bt_1.nested_table_1_id = nes_1.id
        LEFT JOIN bridge_table_2 AS bt_2
                    ON bt_2.main_table_id = main.id
        LEFT JOIN nested_table_2 AS nes_2
                    ON nes_2.id = bt_2.nested_table_2_id;


id
is_active
name
main_table_id
nested_table_1_id
id
name
main_table_id
nested_table_2_id
id
name


1
t
main 1
1
1
1
Contact
1
2
2
nested table second 2

1
t
main 1
1
1
1
Contact
1
1
1
nested table second 1

2
t
main 2
null
null
null
null
null
null
null
null

id	name
1	nested table second 1
2	nested table second 2
3	nested table second 3
4	Drop

id	is_active	name	main_table_id	nested_table_1_id	id	name	main_table_id	nested_table_2_id	id	name
1	t	main 1	1	1	1	Contact	1	2	2	nested table second 2
1	t	main 1	1	1	1	Contact	1	1	1	nested table second 1