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 bigserial primary key,
  name text not null
);

create table marks (
  student_id bigint references students(id),
  mark int not null
);
insert into students (id, name) values
(1, 'john'), (2, 'dave'), (3, 'mike'), (4, 'lily'), (5, 'cobe'), (6, 'rose');
6 rows affected

insert into marks (student_id, mark) values
(1, 30), (2, 35), (3, 40), (5, 29);
4 rows affected

select id, name, COALESCE(mark, 0)
from students s
left join marks m on m.student_id = s.id
order by s.id;


id
name
coalesce


1
john
30

2
dave
35

3
mike
40

4
lily
0

5
cobe
29

6
rose
0