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 type support_types as enum('wall', 'pylon');

CREATE TYPE


create table node (
  id serial primary key,
  -- A trigger to ensure referencial integrity is left to the reader.
  support_id integer not null,
  support_type support_types not null
);

CREATE TABLE


create table wall (
  id serial primary key,
  height integer,
  max_load integer,
  color text
);

CREATE TABLE


create table pylon (
  id serial primary key,
  height integer,
  max_load integer,
  shape text
);

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


insert into node (support_id, support_type) values (1, 'wall'), (2, 'pylon');

INSERT 0 2


create view node_support as
  select
    node.id,
    support_id,
    support_type,
    case when pylon.id is not null then pylon.height
         when wall.id is not null then wall.height
    end as height,
    case when pylon.id is not null then pylon.max_load
         when wall.id is not null then wall.max_load
    end as max_load
  from node
  left join wall on node.support_type = 'wall' and wall.id = node.support_id
  left join pylon on node.support_type = 'pylon' and pylon.id = node.support_id

CREATE VIEW


select * from node_support;


id
support_id
support_type
height
max_load


1
1
wall
5
100

2
2
pylon
30
400



SELECT 2