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 support (
id serial primary key,
height integer,
max_load integer
);
CREATE TABLE
create table wall (
color text
) inherits(support);
CREATE TABLE
create table pylon (
shape text
) inherits(support);
CREATE TABLE
create table node (
-- Referencial integrity must be enforced by trigger.
support_id integer not null
);
CREATE TABLE
insert into wall (height, max_load, color) values (5, 100, 'blue'), (10, 200, 'porange');
INSERT 0 2
insert into pylon (height, max_load, shape) values (20, 300, 'round'), (30, 400, 'square');
INSERT 0 2
select * from support;
| id | height | max_load |
|---|---|---|
| 1 | 5 | 100 |
| 2 | 10 | 200 |
| 3 | 20 | 300 |
| 4 | 30 | 400 |
SELECT 4
select * from wall;
| id | height | max_load | color |
|---|---|---|---|
| 1 | 5 | 100 | blue |
| 2 | 10 | 200 | porange |
SELECT 2
select * from pylon;
| id | height | max_load | shape |
|---|---|---|---|
| 3 | 20 | 300 | round |
| 4 | 30 | 400 | square |
SELECT 2
insert into node (support_id) values (1), (3);
INSERT 0 2
select *
from node
join support on node.support_id = support.id;
| support_id | id | height | max_load |
|---|---|---|---|
| 1 | 1 | 5 | 100 |
| 3 | 3 | 20 | 300 |
SELECT 2
select *
from node
join wall on node.support_id = wall.id;
| support_id | id | height | max_load | color |
|---|---|---|---|---|
| 1 | 1 | 5 | 100 | blue |
SELECT 1