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 student 
(
  s_id INTEGER NOT NULL,
  s_name VARCHAR (20) NOT NULL,
  CONSTRAINT student_pk PRIMARY KEY (s_id)
);
CREATE TABLE quiz 
(
  q_id         INTEGER       NOT NULL,
  q_title   VARCHAR  (50) NOT NULL,
  CONSTRAINT ql_pk PRIMARY KEY (q_id)
);


CREATE TABLE student_score
(
  ss_s_id  INTEGER   NOT NULL,
  ss_q_id INTEGER   NOT NULL,
  points   INTEGER   NOT NULL,
  ss_ts    TIMESTAMP NOT NULL,

  CONSTRAINT ss_pk PRIMARY KEY (ss_s_id, ss_q_id),
  CONSTRAINT ss_s_no_fk FOREIGN KEY (ss_s_id)  REFERENCES student (s_id),
  CONSTRAINT ss_ql_id   FOREIGN KEY (ss_q_id) REFERENCES quiz (q_id)
);
INSERT INTO student VALUES
(12345678, 'Student1_name'),
(40204123, 'Student2_name'),
(40213894, 'Student3_name');
3 rows affected

INSERT INTO quiz (q_id, q_title) 
VALUES
(1, 'Quiz 1'),
(2, 'Quiz 2'),
(3, 'Quiz 3'),
(4, 'Quiz 4');
4 rows affected

INSERT INTO student_score (ss_s_id, ss_q_id, points, ss_ts) VALUES
(12345678, 1, 80, '2021-01-15 16:07:43'),
(12345678, 2, 25, '2021-01-13 17:15:10'),
(12345678, 3, 90, '2021-01-17 22:41:55'),
(12345678, 4, 90, '2021-01-17 22:41:55'),

(40204123, 1, 80, '2021-01-12 15:37:11'),
(40204123, 2, 75, '2021-01-12 15:38:06'),
(40204123, 3, 30, '2021-01-13 22:13:13'),

(40213894, 1, 90, '2021-01-14 21:52:00'),
(40213894, 2, 95, '2021-01-17 22:42:50'),
(40213894, 4, 100, '2021-01-17 22:42:50');
10 rows affected

SELECT q.q_id, ss.ss_q_id, ss.ss_s_id
FROM quiz q
LEFT OUTER JOIN student_score ss
  ON q.q_id = ss.ss_q_id
ORDER By ss.ss_s_id, q.q_id;


q_id
ss_q_id
ss_s_id


1
1
12345678

2
2
12345678

3
3
12345678

4
4
12345678

1
1
40204123

2
2
40204123

3
3
40204123

1
1
40213894

2
2
40213894

4
4
40213894


SELECT q.*, ss.*, s.*
FROM quiz q
LEFT JOIN student_score ss
  ON q.q_id = ss.ss_q_id
LEFT JOIN student s
  ON ss.ss_s_id = s.s_id
WHERE s.s_id = 40204123
-- OR q.q_id IS NULL;
ORDER BY q.q_id;


q_id
q_title
ss_s_id
ss_q_id
points
ss_ts
s_id
s_name


1
Quiz 1
40204123
1
80
2021-01-12 15:37:11
40204123
Student2_name

2
Quiz 2
40204123
2
75
2021-01-12 15:38:06
40204123
Student2_name

3
Quiz 3
40204123
3
30
2021-01-13 22:13:13
40204123
Student2_name

q_id	ss_q_id	ss_s_id
1	1	12345678
2	2	12345678
3	3	12345678
4	4	12345678
1	1	40204123
2	2	40204123
3	3	40204123
1	1	40213894
2	2	40213894
4	4	40213894

q_id	q_title	ss_s_id	ss_q_id	points	ss_ts	s_id	s_name
1	Quiz 1	40204123	1	80	2021-01-12 15:37:11	40204123	Student2_name
2	Quiz 2	40204123	2	75	2021-01-12 15:38:06	40204123	Student2_name
3	Quiz 3	40204123	3	30	2021-01-13 22:13:13	40204123	Student2_name