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 mytable(
   Id            INTEGER  NOT NULL PRIMARY KEY 
  ,IdRef         INTEGER  NOT NULL
  ,myColumn      INTEGER  NOT NULL
  ,anotherColumn VARCHAR(3) NOT NULL
);
INSERT INTO mytable(Id,IdRef,myColumn,anotherColumn) VALUES (448,70,1,'228');
INSERT INTO mytable(Id,IdRef,myColumn,anotherColumn) VALUES (449,70,1,'2s8');
INSERT INTO mytable(Id,IdRef,myColumn,anotherColumn) VALUES (451,70,1,'228');
INSERT INTO mytable(Id,IdRef,myColumn,anotherColumn) VALUES (455,70,2,'2a8');
INSERT INTO mytable(Id,IdRef,myColumn,anotherColumn) VALUES (456,70,2,'s28');
INSERT INTO mytable(Id,IdRef,myColumn,anotherColumn) VALUES (457,70,2,'28');
INSERT INTO mytable(Id,IdRef,myColumn,anotherColumn) VALUES (458,70,3,'v');
INSERT INTO mytable(Id,IdRef,myColumn,anotherColumn) VALUES (459,70,3,'28');
INSERT INTO mytable(Id,IdRef,myColumn,anotherColumn) VALUES (460,70,4,'22');
INSERT INTO mytable(Id,IdRef,myColumn,anotherColumn) VALUES (461,70,3,'54');
INSERT INTO mytable(Id,IdRef,myColumn,anotherColumn) VALUES (462,70,4,'45');
INSERT INTO mytable(Id,IdRef,myColumn,anotherColumn) VALUES (463,70,3,'s28');

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

id	idref	mycolumn	anothercolumn
448	70	1	228
455	70	2	2a8
458	70	3	v
460	70	4	22
461	70	3	54
462	70	4	45
463	70	3	s28