Postgres 16

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