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 node (
  id serial primary key
);

CREATE TABLE

create table wall (
  id serial primary key
);

CREATE TABLE

create table pylon (
  id serial primary key
);

CREATE TABLE

create table node_wall (
  node_id integer not null references node(id),
  wall_id integer not null references wall(id)
);

CREATE TABLE

create table node_pylon (
  node_id integer not null references node(id),
  pylon_id integer not null references pylon(id)
);

CREATE TABLE

insert into node values (DEFAULT), (DEFAULT), (DEFAULT);

INSERT 0 3

insert into wall values (DEFAULT), (DEFAULT);

INSERT 0 2

insert into pylon values (DEFAULT), (DEFAULT);

INSERT 0 2

insert into node_wall values (1, 2);

INSERT 0 1

insert into node_pylon values (2, 1);

INSERT 0 1

create view node_support as
  select
    node.id,
    coalesce(pylon_id, wall_id) as support_id,
    case when pylon_id is not null then 'pylon'
         when wall_id is not null then 'wall'
    end as type
  from node
  left join node_wall on node_wall.node_id = node.id
  left join node_pylon on node_pylon.node_id = node.id

CREATE VIEW

select * from node_support;

id
support_id
type

2
1
pylon

1
2
wall

3
null
null

SELECT 3