Postgres 9.6

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?.

select version();


version


PostgreSQL 9.6.24 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit



SELECT 1


CREATE TABLE student
(
  student_id SERIAL,
  student_name VARCHAR (50),
  CONSTRAINT student_pk PRIMARY KEY (student_id)
);

CREATE TABLE course
(
  course_id SERIAL,
  course_year SMALLINT NOT NULL,
  course_name VARCHAR (100) NOT NULL,
  CONSTRAINT course_pk PRIMARY KEY (course_id)
);

CREATE TABLE registration
(
  reg_course_id INTEGER NOT NULL,
  reg_student_id INTEGER NOT NULL,

  CONSTRAINT reg_course_fk FOREIGN KEY (reg_course_id) REFERENCES course (course_id),
  CONSTRAINT reg_student_fk FOREIGN KEY (reg_student_id) REFERENCES student (student_id),
  CONSTRAINT registration_pk PRIMARY KEY (reg_course_id, reg_student_id),  
  CONSTRAINT registration_sc_uq  UNIQUE (reg_student_id, reg_course_id)
);

CREATE TABLE


--
-- Could add extra fields for 1st semester, 2nd semester and year - although
-- the year could be in the course_id? Modify as required...
--

CREATE TABLE grade
(
  grade_student_id INTEGER NOT NULL,
  grade_course_id  INTEGER NOT NULL,
  grade_s_c        INTEGER NOT NULL,
  -- grade_s_c     CHAR(1) NOT NULL,
  CONSTRAINT grade_s_c_percent_ck CHECK (grade_s_c >= 0 AND grade_s_c <= 100),
  -- CONSTRAINT grade_s_c_percent_ck CHECK (grade_s_c IN ('A', 'B', 'C', 'D', 'E', 'F', 'U')),
  CONSTRAINT grade_pk PRIMARY KEY (grade_student_id, grade_course_id),
  CONSTRAINT grade_matches_s_c_fk FOREIGN KEY (grade_student_id, grade_course_id)
    REFERENCES registration (reg_course_id, reg_student_id)

);

CREATE TABLE