Postgres 16

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 tablename (id int, fid int, val char(4));

CREATE TABLE

insert into tablename values
  (200, 995, 'XXLL'),
  (200, 996, 'XXLL'),
  (201, 995, 'OOOP'),
  (201, 996, 'OOOS'),
  (202, 995, 'OKIL'),
  (202, 996, 'OKIL'),
  (203, 995, 'LLLL'),
  (203, 996, 'CCCC');

INSERT 0 8

select id from
(
  select id, val from tablename where fid = 995
  intersect
  select id, val from tablename where fid = 996
) dt

id

202

200

SELECT 2

select id
from tablename
where fid in (995, 996)
group by id
having count(distinct val) = 1

   and count(distinct fid) = 2; -- add to verify both 995 and 996 are there

id

200

202

SELECT 2