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,
  score   INTEGER   NOT NULL,
  ts    TIMESTAMP NOT NULL,

  CONSTRAINT sp_pk PRIMARY KEY (ss_s_id, ss_q_id),
  CONSTRAINT sp_s_no_fk FOREIGN KEY (ss_s_id)  REFERENCES student (s_id),
  CONSTRAINT sp_ql_id   FOREIGN KEY (ss_q_id) REFERENCES quiz (q_id)
);
INSERT INTO student VALUES
(12345678, 'Student1_name'),
(40204123, 'Student2_name'),
(40213894, 'Student3_name'),
(98765432, 'Student4_name');   -- <<-- Extra student record! Not possibe under previous 
                               -- <<-- schema!
4 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, score, 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.*, ss.* 
FROM quiz q 
LEFT JOIN student_score ss 
  ON q.q_id = ss.ss_q_id 
WHERE ss.ss_s_id = 40204123 
OR ss.ss_s_id IS NULL;


q_id
q_title
ss_s_id
ss_q_id
score
ts


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

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

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


SELECT q.q_id, q.q_title
FROM quiz q;


q_id
q_title


1
Quiz 1

2
Quiz 2

3
Quiz 3

4
Quiz 4


SELECT 
  s_id, s_name 
FROM student s
WHERE s.s_id = 40204123;


s_id
s_name


40204123
Student2_name


  SELECT 
    q.q_id, q.q_title,
    s.s_id, s.s_name 
  FROM quiz q
  CROSS JOIN student s
  WHERE s.s_id = 40204123;


q_id
q_title
s_id
s_name


1
Quiz 1
40204123
Student2_name

2
Quiz 2
40204123
Student2_name

3
Quiz 3
40204123
Student2_name

4
Quiz 4
40204123
Student2_name


SELECT 
  q.q_id, q.q_title,
  s.s_id, s.s_name  -- , COALESCE(ss.score, 0) AS score
FROM quiz q
CROSS JOIN student s
LEFT JOIN student_score ss
  ON ss.ss_s_id = s.s_id
  AND ss.ss_q_id = q.q_id;


q_id
q_title
s_id
s_name


1
Quiz 1
12345678
Student1_name

2
Quiz 2
12345678
Student1_name

3
Quiz 3
12345678
Student1_name

4
Quiz 4
12345678
Student1_name

1
Quiz 1
40204123
Student2_name

2
Quiz 2
40204123
Student2_name

3
Quiz 3
40204123
Student2_name

4
Quiz 4
40204123
Student2_name

1
Quiz 1
40213894
Student3_name

2
Quiz 2
40213894
Student3_name

3
Quiz 3
40213894
Student3_name

4
Quiz 4
40213894
Student3_name

1
Quiz 1
98765432
Student4_name

2
Quiz 2
98765432
Student4_name

3
Quiz 3
98765432
Student4_name

4
Quiz 4
98765432
Student4_name


SELECT 
  q.q_id, q.q_title,
  s.s_id, s.s_name, COALESCE(ss.score, 0) AS score
FROM quiz q
CROSS JOIN student s
LEFT JOIN student_score ss
  ON ss.ss_s_id = s.s_id
  AND ss.ss_q_id = q.q_id
WHERE s.s_id -- IN (40204123, 40213894) test with and without!
ORDER BY s.s_id, q.q_id;
ERROR:  argument of WHERE must be type boolean, not type integer
LINE 9: WHERE s.s_id -- IN (40204123, 40213894) test with and withou...
              ^


EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING)
SELECT 
  q.q_id, q.q_title,
  s.s_id, s.s_name, COALESCE(ss.score, 0) AS score
FROM quiz q
CROSS JOIN student s
LEFT JOIN student_score ss
  ON ss.ss_s_id = s.s_id
  AND ss.ss_q_id = q.q_id
WHERE s.s_id = 40204123;


QUERY PLAN


Hash Left Join  (cost=14.64..46.31 rows=540 width=188) (actual time=0.077..0.083 rows=4 loops=1)

  Hash Cond: ((s.s_id = ss.ss_s_id) AND (q.q_id = ss.ss_q_id))

  Buffers: shared hit=5

  ->  Nested Loop  (cost=0.15..28.97 rows=540 width=184) (actual time=0.032..0.035 rows=4 loops=1)

        Buffers: shared hit=3

        ->  Index Scan using student_pk on student s  (cost=0.15..8.17 rows=1 width=62) (actual time=0.020..0.021 rows=1 loops=1)

              Index Cond: (s_id = 40204123)

              Buffers: shared hit=2

        ->  Seq Scan on quiz q  (cost=0.00..15.40 rows=540 width=122) (actual time=0.008..0.009 rows=4 loops=1)

              Buffers: shared hit=1

  ->  Hash  (cost=14.37..14.37 rows=8 width=12) (actual time=0.027..0.027 rows=3 loops=1)

        Buckets: 1024  Batches: 1  Memory Usage: 9kB

        Buffers: shared hit=2

        ->  Bitmap Heap Scan on student_score ss  (cost=4.21..14.37 rows=8 width=12) (actual time=0.017..0.019 rows=3 loops=1)

              Recheck Cond: (ss_s_id = 40204123)

              Heap Blocks: exact=1

              Buffers: shared hit=2

              ->  Bitmap Index Scan on sp_pk  (cost=0.00..4.21 rows=8 width=0) (actual time=0.006..0.007 rows=3 loops=1)

                    Index Cond: (ss_s_id = 40204123)

                    Buffers: shared hit=1

Planning Time: 0.254 ms

Execution Time: 0.188 ms


EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING)
SELECT
  tab.q_id, tab.q_title,
  tab.s_id, tab.s_name, COALESCE(ss.score, 0) AS the_score
FROM
  student_score ss
RIGHT JOIN
(
  SELECT 
    q.q_id, q.q_title,
    s.s_id, s.s_name 
  FROM quiz q
  CROSS JOIN
  student s
  WHERE s.s_id = 40204123
) AS tab
ON ss.ss_s_id = tab.s_id
AND ss.ss_q_id = tab.q_id
ORDER BY tab.s_id, tab.q_id;


QUERY PLAN


Merge Left Join  (cost=67.97..70.81 rows=540 width=188) (actual time=0.078..0.083 rows=4 loops=1)

  Merge Cond: (q.q_id = ss.ss_q_id)

  Join Filter: (ss.ss_s_id = s.s_id)

  Buffers: shared hit=5

  ->  Sort  (cost=53.47..54.82 rows=540 width=184) (actual time=0.046..0.047 rows=4 loops=1)

        Sort Key: q.q_id

        Sort Method: quicksort  Memory: 25kB

        Buffers: shared hit=3

        ->  Nested Loop  (cost=0.15..28.97 rows=540 width=184) (actual time=0.025..0.028 rows=4 loops=1)

              Buffers: shared hit=3

              ->  Index Scan using student_pk on student s  (cost=0.15..8.17 rows=1 width=62) (actual time=0.016..0.017 rows=1 loops=1)

                    Index Cond: (s_id = 40204123)

                    Buffers: shared hit=2

              ->  Seq Scan on quiz q  (cost=0.00..15.40 rows=540 width=122) (actual time=0.006..0.007 rows=4 loops=1)

                    Buffers: shared hit=1

  ->  Sort  (cost=14.49..14.51 rows=8 width=12) (actual time=0.028..0.029 rows=3 loops=1)

        Sort Key: ss.ss_q_id

        Sort Method: quicksort  Memory: 25kB

        Buffers: shared hit=2

        ->  Bitmap Heap Scan on student_score ss  (cost=4.21..14.37 rows=8 width=12) (actual time=0.013..0.014 rows=3 loops=1)

              Recheck Cond: (ss_s_id = 40204123)

              Heap Blocks: exact=1

              Buffers: shared hit=2

              ->  Bitmap Index Scan on sp_pk  (cost=0.00..4.21 rows=8 width=0) (actual time=0.004..0.004 rows=3 loops=1)

                    Index Cond: (ss_s_id = 40204123)

                    Buffers: shared hit=1

Planning Time: 0.231 ms

Execution Time: 0.133 ms

q_id	q_title	ss_s_id	ss_q_id	score	ts
1	Quiz 1	40204123	1	80	2021-01-12 15:37:11
2	Quiz 2	40204123	2	75	2021-01-12 15:38:06
3	Quiz 3	40204123	3	30	2021-01-13 22:13:13

q_id	q_title	s_id	s_name
1	Quiz 1	40204123	Student2_name
2	Quiz 2	40204123	Student2_name
3	Quiz 3	40204123	Student2_name
4	Quiz 4	40204123	Student2_name

q_id	q_title	s_id	s_name
1	Quiz 1	12345678	Student1_name
2	Quiz 2	12345678	Student1_name
3	Quiz 3	12345678	Student1_name
4	Quiz 4	12345678	Student1_name
1	Quiz 1	40204123	Student2_name
2	Quiz 2	40204123	Student2_name
3	Quiz 3	40204123	Student2_name
4	Quiz 4	40204123	Student2_name
1	Quiz 1	40213894	Student3_name
2	Quiz 2	40213894	Student3_name
3	Quiz 3	40213894	Student3_name
4	Quiz 4	40213894	Student3_name
1	Quiz 1	98765432	Student4_name
2	Quiz 2	98765432	Student4_name
3	Quiz 3	98765432	Student4_name
4	Quiz 4	98765432	Student4_name