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 employee
(
  ssn             varchar(10) PRIMARY KEY NOT NULL,
  bdate           date,
  fname           varchar(25),
  minit           varchar(5),
  lname           varchar(25),
  address         varchar(40),
  sex             varchar(6),
  salary          int,
  supervisorssn   varchar(10),
  dnumber         int
);

CREATE TABLE department
(
  dnumber             int PRIMARY KEY NOT NULL,
  dname               varchar(15),
  mgrssn              varchar(10),
  mgrstartdate        date,
  numberofemployees   int,
  CONSTRAINT department_mgrssn_fk FOREIGN KEY (mgrssn) REFERENCES employee (ssn) ON DELETE SET DEFAULT ON UPDATE CASCADE
);

ALTER TABLE employee 
  ADD CONSTRAINT employee_supervisorssn_fk FOREIGN KEY (supervisorssn) REFERENCES employee (ssn) ON DELETE SET DEFAULT ON UPDATE CASCADE,
  ADD CONSTRAINT employee_dnumber_fk FOREIGN KEY (dnumber) REFERENCES department(dnumber) ON DELETE SET DEFAULT ON UPDATE CASCADE;
  
ALTER TABLE department
  add constraint fk_dempt2emp foreign key (mgrssn) references employee;

with new_depts as (
  insert into department 
    (dnumber, dname, mgrssn)
  values 
    (1, 'One', '456'),
    (2, 'Two', '456'),
    (3, 'Three', '789')
)
insert into employee (ssn, fname, lname, supervisorssn, dnumber)
values 
  ('123', 'Arthur', 'Dent', '456', 1),
  ('456', 'Ford', 'Prefect', null, 2),
  ('789', 'Zaphod', 'Beeblebrox', null, 3)
;
3 rows affected

select *
from department


dnumber
dname
mgrssn
mgrstartdate
numberofemployees


1
One
456
null
null

2
Two
456
null
null

3
Three
789
null
null


select *
from employee


ssn
bdate
fname
minit
lname
address
sex
salary
supervisorssn
dnumber


123
null
Arthur
null
Dent
null
null
null
456
1

456
null
Ford
null
Prefect
null
null
null
null
2

789
null
Zaphod
null
Beeblebrox
null
null
null
null
3

ssn	bdate	fname	minit	lname	address	sex	salary	supervisorssn	dnumber
123	null	Arthur	null	Dent	null	null	null	456	1
456	null	Ford	null	Prefect	null	null	null	null	2
789	null	Zaphod	null	Beeblebrox	null	null	null	null	3