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 students (id int, name text);
insert into students
values
(1, 'st1'),
(2, 'st2'),
(3, 'st3');
3 rows affected

create table classes (id int, title text, student_id int, visited boolean);
insert into classes
values
(1, 'class1', 1, false),
(2, 'class2', 1, true),
(3, 'class3', 1, false),
(4, 'class4', 1, false),
(5, 'class5', 2, true),
(6, 'class6', 3, false);
6 rows affected

select s.*
from students s
  left join classes c on s.id = c.student_id and c.visited
where c.id is null;


id
name


3
st3