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 level_quiz 
(
  id            INTEGER       NOT NULL,
  level_title   VARCHAR  (50) NOT NULL,
  quiz_desc     VARCHAR (200) NOT NULL,
  overall_quest VARCHAR (250) NOT NULL
);


CREATE TABLE student_points 
(
  student_no INTEGER   NOT NULL,
  level_id   INTEGER   NOT NULL,
  points     INTEGER   NULL,
  ts         TIMESTAMP NULL  -- << renamed timestamp to ts!
);
INSERT INTO level_quiz (id, level_title, quiz_desc, overall_quest) 
VALUES
(1, 'Sets',       'The purpose of...', 'Suppose we have... '),  -- << truncated strings
(2, 'Seqs',       'sequences desc...', 'overall question... '),
(3, 'Prop Logic', 'logic desc    ...', 'overall quest... '),
(4, 'Pred Logic', 'pred desc 1   ...', 'predicase quest...');
4 rows affected


SELECT * FROM level_quiz;



id
level_title
quiz_desc
overall_quest


1
Sets
The purpose of...
Suppose we have...

2
Seqs
sequences desc...
overall question...

3
Prop Logic
logic desc    ...
overall quest...

4
Pred Logic
pred desc 1   ...
predicase quest...


INSERT INTO student_points (student_no, level_id, points, 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'),
-- (40204123, 4, NULL, NULL),           -- <<<  -- see below what happens when this 
                                                -- record is inserted
                                                
(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');
-- (40213894, 4, NULL, NULL),               -- <<< see below also
10 rows affected

--
-- bb25 original SQL - no NULLs in the result
--

SELECT *
FROM level_quiz 
LEFT JOIN student_points 
  ON level_quiz.id = student_points.level_id 
WHERE student_points.student_no = 40204123 
OR student_points.student_no IS NULL  -- <<-- Makes NO difference


id
level_title
quiz_desc
overall_quest
student_no
level_id
points
ts


1
Sets
The purpose of...
Suppose we have...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40204123
2
75
2021-01-12 15:38:06

3
Prop Logic
logic desc    ...
overall quest...
40204123
3
30
2021-01-13 22:13:13


--
-- Solution proposed by nbk - a NULL in the result!
--

SELECT 
  lq.id, lq.level_title, lq.quiz_desc, lq.overall_quest,
  sp.student_no, sp.level_id, sp.points, sp.ts
FROM
  level_quiz lq
LEFT JOIN
 (
   SELECT * FROM student_points 
   WHERE student_no = 40204123
 ) sp
 ON lq.id = sp.level_id;


id
level_title
quiz_desc
overall_quest
student_no
level_id
points
ts


1
Sets
The purpose of...
Suppose we have...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40204123
2
75
2021-01-12 15:38:06

3
Prop Logic
logic desc    ...
overall quest...
40204123
3
30
2021-01-13 22:13:13

4
Pred Logic
pred desc 1   ...
predicase quest...
null
null
null
null


--
-- bb25's original SQL - with 2 students - but no NULLs in the result!
--

SELECT *
FROM level_quiz 
LEFT JOIN student_points 
  ON level_quiz.id = student_points.level_id 
WHERE student_points.student_no IN (40204123, 40213894) 
OR student_points.student_no IS NULL  -- << Makes NO difference!


id
level_title
quiz_desc
overall_quest
student_no
level_id
points
ts


1
Sets
The purpose of...
Suppose we have...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40204123
2
75
2021-01-12 15:38:06

3
Prop Logic
logic desc    ...
overall quest...
40204123
3
30
2021-01-13 22:13:13

1
Sets
The purpose of...
Suppose we have...
40213894
1
90
2021-01-14 21:52:00

2
Seqs
sequences desc...
overall question...
40213894
2
95
2021-01-17 22:42:50

4
Pred Logic
pred desc 1   ...
predicase quest...
40213894
4
100
2021-01-17 22:42:50


--
-- Solution proposed by nbk - with 2 students - but no NULLs in the result!
--

SELECT 
  lq.id, lq.level_title, lq.quiz_desc, lq.overall_quest,
  sp.student_no, sp.level_id, sp.points, sp.ts
FROM
  level_quiz lq
LEFT JOIN
 (
   SELECT * FROM student_points 
   WHERE student_no IN (40204123, 40213894)
 ) sp
 ON lq.id = sp.level_id;


id
level_title
quiz_desc
overall_quest
student_no
level_id
points
ts


1
Sets
The purpose of...
Suppose we have...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40204123
2
75
2021-01-12 15:38:06

3
Prop Logic
logic desc    ...
overall quest...
40204123
3
30
2021-01-13 22:13:13

1
Sets
The purpose of...
Suppose we have...
40213894
1
90
2021-01-14 21:52:00

2
Seqs
sequences desc...
overall question...
40213894
2
95
2021-01-17 22:42:50

4
Pred Logic
pred desc 1   ...
predicase quest...
40213894
4
100
2021-01-17 22:42:50


--
-- simple CROSS JOIN
--

SELECT 
  lq.id, lq.level_title, lq.quiz_desc, lq.overall_quest, 
  sp.student_no, sp.level_id, sp.points, sp.ts 
FROM level_quiz lq
CROSS JOIN student_points sp
WHERE sp.student_no = 40204123  -- try without the WHERE clause!
ORDER BY sp.student_no, lq.id, sp.level_id;
--
-- You can JOIN on whatever criteria you want - this is **ALL** the data you can get out
-- of this table for student_no = 40204123 by doing a CROSS JOIN

-- Notice that you can **only** ever have records with student_no = 40204123 (4 x 3)


id
level_title
quiz_desc
overall_quest
student_no
level_id
points
ts


1
Sets
The purpose of...
Suppose we have...
40204123
1
80
2021-01-12 15:37:11

1
Sets
The purpose of...
Suppose we have...
40204123
2
75
2021-01-12 15:38:06

1
Sets
The purpose of...
Suppose we have...
40204123
3
30
2021-01-13 22:13:13

2
Seqs
sequences desc...
overall question...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40204123
2
75
2021-01-12 15:38:06

2
Seqs
sequences desc...
overall question...
40204123
3
30
2021-01-13 22:13:13

3
Prop Logic
logic desc    ...
overall quest...
40204123
1
80
2021-01-12 15:37:11

3
Prop Logic
logic desc    ...
overall quest...
40204123
2
75
2021-01-12 15:38:06

3
Prop Logic
logic desc    ...
overall quest...
40204123
3
30
2021-01-13 22:13:13

4
Pred Logic
pred desc 1   ...
predicase quest...
40204123
1
80
2021-01-12 15:37:11

4
Pred Logic
pred desc 1   ...
predicase quest...
40204123
2
75
2021-01-12 15:38:06

4
Pred Logic
pred desc 1   ...
predicase quest...
40204123
3
30
2021-01-13 22:13:13


--
-- See the results of the query below for student_no = 40204123
--

1 Sets	The purpose of  Suppose we have	                40204123	1	80	2021-01-12 15:37:11
2 sequences	        sequences desc	overall  2	40204123	2	75	2021-01-12 15:38:06
3 Propositional Logic	logic desc	overall quest 	40204123	3	30	2021-01-13 22:13:13
ERROR:  syntax error at or near "1"
LINE 5: 1 Sets The purpose of  Suppose we have                 40204...
        ^


--
-- "Forcing a result"  <<-- Meaningless! You have all the fields in the first table
--  with everything in the second as NULL! And, see result below! You have no
--  student_no to go with your NULL results from the student_points table.
--
--
SELECT 
  l.id AS "l_id", l.level_title AS "l_title", 
  l.quiz_desc AS "l_q_desc", l.overall_quest AS "l_o_quest",
  tab.s_no, tab.s_l_id, COALESCE(tab.s_points, 0), tab.s_ts
FROM
  level_quiz l
LEFT JOIN
(
  SELECT 
    sp.student_no AS "s_no", sp.level_id AS "s_l_id", 
    sp.points AS "s_points", sp.ts AS "s_ts"
  FROM student_points sp WHERE sp.student_no = 40204123
) tab
ON l.id = tab.s_l_id;


l_id
l_title
l_q_desc
l_o_quest
s_no
s_l_id
coalesce
s_ts


1
Sets
The purpose of...
Suppose we have...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40204123
2
75
2021-01-12 15:38:06

3
Prop Logic
logic desc    ...
overall quest...
40204123
3
30
2021-01-13 22:13:13

4
Pred Logic
pred desc 1   ...
predicase quest...
null
null
0
null


-- With 2 students! 
--
--
-- NOW, you've "lost" the NULLs for BOTH students who should have them - 1 NULL
-- for student no. = 40204123 for level_quiz.id = 3 and 1 NULL for student no. =
-- 40213894 for level_quiz.id = 4
--

SELECT 
  l.id AS "l_id", l.level_title AS "l_title", 
  l.quiz_desc AS "l_q_desc", l.overall_quest AS "l_o_quest",
  tab.s_no, tab.s_l_id, tab.s_points, tab.s_ts
FROM
  level_quiz l
LEFT JOIN
(
  SELECT 
    s.student_no AS "s_no", s.level_id AS "s_l_id", 
    s.points AS "s_points", s.ts AS "s_ts"
  FROM student_points s WHERE s.student_no IN (40204123, 40213894) -- Where have the NULLs gone?
) tab
ON l.id = tab.s_l_id;


l_id
l_title
l_q_desc
l_o_quest
s_no
s_l_id
s_points
s_ts


1
Sets
The purpose of...
Suppose we have...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40204123
2
75
2021-01-12 15:38:06

3
Prop Logic
logic desc    ...
overall quest...
40204123
3
30
2021-01-13 22:13:13

1
Sets
The purpose of...
Suppose we have...
40213894
1
90
2021-01-14 21:52:00

2
Seqs
sequences desc...
overall question...
40213894
2
95
2021-01-17 22:42:50

4
Pred Logic
pred desc 1   ...
predicase quest...
40213894
4
100
2021-01-17 22:42:50


--
--             ADD
--
--           TWO RECORDS TO
-- 
--     THE student_points TABLE!!!!!!!!!
--
INSERT INTO student_points VALUES
(40204123, 4, NULL, NULL),     -- <<<  NOW we INSERT these records! 
(40213894, 3, NULL, NULL);
2 rows affected

--
--
--
--    Now, we obtain VALID results!!!!!
--
--
--
-- bb25 original SQL - 1 student - a NULLs NOW in the result
--

SELECT *
FROM level_quiz 
LEFT JOIN student_points 
  ON level_quiz.id = student_points.level_id 
WHERE student_points.student_no = 40204123 
ORDER BY student_points.student_no, level_quiz.id;


id
level_title
quiz_desc
overall_quest
student_no
level_id
points
ts


1
Sets
The purpose of...
Suppose we have...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40204123
2
75
2021-01-12 15:38:06

3
Prop Logic
logic desc    ...
overall quest...
40204123
3
30
2021-01-13 22:13:13

4
Pred Logic
pred desc 1   ...
predicase quest...
40204123
4
null
null


--
-- bb25 original SQL - 2 students - NULLs NOW in the result
--

SELECT *
FROM level_quiz 
LEFT JOIN student_points 
  ON level_quiz.id = student_points.level_id 
WHERE student_points.student_no IN (40204123, 40213894) 
ORDER BY student_points.student_no, level_quiz.id;


id
level_title
quiz_desc
overall_quest
student_no
level_id
points
ts


1
Sets
The purpose of...
Suppose we have...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40204123
2
75
2021-01-12 15:38:06

3
Prop Logic
logic desc    ...
overall quest...
40204123
3
30
2021-01-13 22:13:13

4
Pred Logic
pred desc 1   ...
predicase quest...
40204123
4
null
null

1
Sets
The purpose of...
Suppose we have...
40213894
1
90
2021-01-14 21:52:00

2
Seqs
sequences desc...
overall question...
40213894
2
95
2021-01-17 22:42:50

3
Prop Logic
logic desc    ...
overall quest...
40213894
3
null
null

4
Pred Logic
pred desc 1   ...
predicase quest...
40213894
4
100
2021-01-17 22:42:50


--
-- @nbk's answer for 1 record
-- 
-- This makes MUCH more sense! For 1 record.
--

SELECT 
  l.id AS "l_id", l.level_title AS "l_title", 
  l.quiz_desc AS "l_q_desc", l.overall_quest AS "l_o_quest",
  tab.s_no, tab.s_l_id, tab.s_points, tab.s_ts
FROM
  level_quiz l
LEFT JOIN
(
  SELECT 
    sp.student_no AS "s_no", sp.level_id AS "s_l_id", 
    sp.points AS "s_points", sp.ts AS "s_ts"
  FROM student_points sp 
  WHERE sp.student_no = 40204123
) tab
ON l.id = tab.s_l_id
ORDER BY tab.s_no, l.id;


l_id
l_title
l_q_desc
l_o_quest
s_no
s_l_id
s_points
s_ts


1
Sets
The purpose of...
Suppose we have...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40204123
2
75
2021-01-12 15:38:06

3
Prop Logic
logic desc    ...
overall quest...
40204123
3
30
2021-01-13 22:13:13

4
Pred Logic
pred desc 1   ...
predicase quest...
40204123
4
null
null


--
-- @nbk's answer for 2 records
--
-- This also makes MUCH more sense! For 2 (or more) records.
--
-- NOW, searching for any/all students with *_NO_* points for a given level_quiz.id
-- works and we can make sense of the NULLs

SELECT 
  l.id AS "l_id", l.level_title AS "l_title", 
  l.quiz_desc AS "l_q_desc", l.overall_quest AS "l_o_quest",
  tab.s_no, tab.s_l_id, tab.s_points, tab.s_ts
FROM
  level_quiz l
LEFT JOIN
(
  SELECT 
    sp.student_no AS "s_no", sp.level_id AS "s_l_id", 
    sp.points AS "s_points", sp.ts AS "s_ts"
  FROM student_points sp 
  WHERE sp.student_no IN (40204123, 40213894)
) tab
ON l.id = tab.s_l_id
ORDER BY tab.s_no, l.id;


l_id
l_title
l_q_desc
l_o_quest
s_no
s_l_id
s_points
s_ts


1
Sets
The purpose of...
Suppose we have...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40204123
2
75
2021-01-12 15:38:06

3
Prop Logic
logic desc    ...
overall quest...
40204123
3
30
2021-01-13 22:13:13

4
Pred Logic
pred desc 1   ...
predicase quest...
40204123
4
null
null

1
Sets
The purpose of...
Suppose we have...
40213894
1
90
2021-01-14 21:52:00

2
Seqs
sequences desc...
overall question...
40213894
2
95
2021-01-17 22:42:50

3
Prop Logic
logic desc    ...
overall quest...
40213894
3
null
null

4
Pred Logic
pred desc 1   ...
predicase quest...
40213894
4
100
2021-01-17 22:42:50


--
--
--
--
-- DELETE the records added for Solution 1!
--
DELETE FROM student_points WHERE points IS NULL;
2 rows affected

--
-- Now, we start afresh!
--
SELECT distinct sp1.student_no, t1.id
FROM student_points sp1
CROSS JOIN 
(
  SELECT distinct lq.id 
  FROM level_quiz lq
) AS t1
ORDER BY sp1.student_no, t1.id;


student_no
id


12345678
1

12345678
2

12345678
3

12345678
4

40204123
1

40204123
2

40204123
3

40204123
4

40213894
1

40213894
2

40213894
3

40213894
4


SELECT 
  t2.id, 
  SUBSTRING(lq2.level_title, 1, 6) AS "LT:", lq2.quiz_desc, lq2.overall_quest,
  t2.student_no, COALESCE(sp2.points, 0) AS "Points:", sp2.ts
FROM
(
  SELECT distinct sp1.student_no, t1.id
  FROM student_points sp1
  CROSS JOIN 
  (
    SELECT distinct lq1.id 
    FROM level_quiz lq1
  ) AS t1
) AS t2
LEFT JOIN student_points sp2
  ON t2.student_no = sp2.student_no
  AND t2.id = sp2.level_id
JOIN level_quiz lq2
  ON t2.id = lq2.id
WHERE t2.student_no IN (40204123, 40213894)
ORDER BY t2.student_no, t2.id;


id
LT:
quiz_desc
overall_quest
student_no
Points:
ts


1
Sets
The purpose of...
Suppose we have...
40204123
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40204123
75
2021-01-12 15:38:06

3
Prop L
logic desc    ...
overall quest...
40204123
30
2021-01-13 22:13:13

4
Pred L
pred desc 1   ...
predicase quest...
40204123
0
null

1
Sets
The purpose of...
Suppose we have...
40213894
90
2021-01-14 21:52:00

2
Seqs
sequences desc...
overall question...
40213894
95
2021-01-17 22:42:50

3
Prop L
logic desc    ...
overall quest...
40213894
0
null

4
Pred L
pred desc 1   ...
predicase quest...
40213894
100
2021-01-17 22:42:50


EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING)
SELECT 
  t2.id, 
  SUBSTRING(lq2.level_title, 1, 6) AS "LT:", lq2.quiz_desc, lq2.overall_quest,
  t2.student_no, COALESCE(sp2.points, 0) AS "Points:", sp2.ts
FROM
(
  SELECT distinct sp1.student_no, t1.id
  FROM student_points sp1
  CROSS JOIN 
  (
    SELECT distinct lq1.id 
    FROM level_quiz lq1
  ) AS t1
) AS t2
LEFT JOIN student_points sp2
  ON t2.student_no = sp2.student_no
  AND t2.id = sp2.level_id
JOIN level_quiz lq2
  ON t2.id = lq2.id
WHERE t2.student_no IN (40204123, 40213894)
ORDER BY t2.student_no, t2.id;


QUERY PLAN


Sort  (cost=177.08..178.06 rows=392 width=986) (actual time=0.173..0.177 rows=8 loops=1)

  Sort Key: t2.student_no, t2.id

  Sort Method: quicksort  Memory: 26kB

  Buffers: shared hit=4

  ->  Hash Join  (cost=115.17..160.19 rows=392 width=986) (actual time=0.142..0.158 rows=8 loops=1)

        Hash Cond: (t2.id = lq2.id)

        Buffers: shared hit=4

        ->  Hash Right Join  (cost=103.59..139.52 rows=1120 width=20) (actual time=0.098..0.109 rows=8 loops=1)

              Hash Cond: ((sp2.student_no = t2.student_no) AND (sp2.level_id = t2.id))

              Buffers: shared hit=3

              ->  Seq Scan on student_points sp2  (cost=0.00..27.00 rows=1700 width=20) (actual time=0.007..0.008 rows=10 loops=1)

                    Buffers: shared hit=1

              ->  Hash  (cost=86.79..86.79 rows=1120 width=8) (actual time=0.070..0.071 rows=8 loops=1)

                    Buckets: 2048  Batches: 1  Memory Usage: 17kB

                    Buffers: shared hit=2

                    ->  Subquery Scan on t2  (cost=64.39..86.79 rows=1120 width=8) (actual time=0.054..0.064 rows=8 loops=1)

                          Buffers: shared hit=2

                          ->  HashAggregate  (cost=64.39..75.59 rows=1120 width=8) (actual time=0.053..0.061 rows=8 loops=1)

                                Group Key: sp1.student_no, lq1.id

                                Buffers: shared hit=2

                                ->  Nested Loop  (cost=10.88..58.44 rows=1190 width=8) (actual time=0.031..0.043 rows=24 loops=1)

                                      Buffers: shared hit=2

                                      ->  HashAggregate  (cost=10.88..11.57 rows=70 width=4) (actual time=0.013..0.015 rows=4 loops=1)

                                            Group Key: lq1.id

                                            Buffers: shared hit=1

                                            ->  Seq Scan on level_quiz lq1  (cost=0.00..10.70 rows=70 width=4) (actual time=0.006..0.007 rows=4 loops=1)

                                                  Buffers: shared hit=1

                                      ->  Materialize  (cost=0.00..31.34 rows=17 width=4) (actual time=0.004..0.005 rows=6 loops=4)

                                            Buffers: shared hit=1

                                            ->  Seq Scan on student_points sp1  (cost=0.00..31.25 rows=17 width=4) (actual time=0.009..0.011 rows=6 loops=1)

                                                  Filter: (student_no = ANY ('{40204123,40213894}'::integer[]))

                                                  Rows Removed by Filter: 4

                                                  Buffers: shared hit=1

        ->  Hash  (cost=10.70..10.70 rows=70 width=1056) (actual time=0.021..0.021 rows=4 loops=1)

              Buckets: 1024  Batches: 1  Memory Usage: 9kB

              Buffers: shared hit=1

              ->  Seq Scan on level_quiz lq2  (cost=0.00..10.70 rows=70 width=1056) (actual time=0.015..0.016 rows=4 loops=1)

                    Buffers: shared hit=1

Planning Time: 0.309 ms

Execution Time: 0.292 ms
id	level_title	quiz_desc	overall_quest
1	Sets	The purpose of...	Suppose we have...
2	Seqs	sequences desc...	overall question...
3	Prop Logic	logic desc ...	overall quest...
4	Pred Logic	pred desc 1 ...	predicase quest...
student_no	id
12345678	1
12345678	2
12345678	3
12345678	4
40204123	1
40204123	2
40204123	3
40204123	4
40213894	1
40213894	2
40213894	3
40213894	4