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 tbl (
  id  int NOT NULL
, fid int NOT NULL CHECK (fid IN (995, 996))  -- ??
, val text
, PRIMARY KEY (id, fid)  -- !!
);

INSERT INTO tbl 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')

, (666, 995, null)  -- allowed?
, (666, 996, null)
;

CREATE TABLE



INSERT 0 10


SELECT id
FROM   tbl
GROUP  BY id, val   -- !!
HAVING count(*) = 2
ORDER  BY id;  -- optional


id


200

202

666



SELECT 3


-- if the CHECK constraint is there, too
SELECT id
FROM   tbl
WHERE  fid IN (995, 996)  -- if there can be other fid
GROUP  BY id, val   -- !!
HAVING count(*) = 2
ORDER  BY id;  -- optional


id


200

202

666



SELECT 3