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 food as
select *
from (values (1, 2),
             (1, 3),
             (1, 4),
             (2, 1),
             (2, 2),
             (2, 3),
             (2, 4),
             (3, 1),
             (3, 2),
             (3, 4)
     ) v(pId,aId)
10 rows affected

create table animal as
select *
from (values (1, 'Ant'),
             (2, 'Bear'),
             (3, 'Cat'),
             (4, 'Dog')
     ) v(id, name)
4 rows affected

select pid
from food
group by pid
having count(*) = (select count(*) - 1 from animal) and
       sum(case when aid = pid then 1 else 0 end) = 0


pid


3

1