Postgres 10

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 tbl (_id int, stud_id int, collage_id int, university_id int, active bool); 
insert into tbl values
   (1, 2, 2, 2, true),
   (6, 2, 3, 3, true),
   (7, 6, 2, 2, true),
   (8, 7, 3, 2, true),
   (9, 8, 2, 2, true),
  (10, 8, 3, 2, true),
  (11, 6, 2, 2, true);
7 rows affected

select   stud_id
from     tbl
group by stud_id
having   count(distinct collage_id) = 1;


stud_id


6

7


select *
from   tbl
where  collage_id = 3
and    stud_id in (select   stud_id
                   from     tbl
                   group by stud_id
                   having   count(distinct collage_id) = 1);


_id
stud_id
collage_id
university_id
active


8
7
3
2
t