Postgres 13

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	INT,
projectname VARCHAR(50),	
number	 INT,
filename VARCHAR(50),
type	VARCHAR(50),
unq   VARCHAR(50),
count INT );

INSERT INTO myTable VALUES (8,'prj1',2,'a','t1','888389f661e117',1);
INSERT INTO myTable VALUES (9,'prj1',2,'a','t1','888389f661e117',2);
INSERT INTO myTable VALUES (10,'prj1',2,'a','t1','888389f661e117',2);
INSERT INTO myTable VALUES (11,'prj1',2,'a','t2','816418549711c3d33',6);
INSERT INTO myTable VALUES (12,'prj1',2,'a','t2','816418549711c3d33',7);
INSERT INTO myTable VALUES (13,'prj1',2,'a','t2','816418549711c3d33',1);
INSERT INTO myTable VALUES (14,'prj1',2,'a','t3',NULL,NULL);
INSERT INTO myTable VALUES (15,'prj1',2,'a','t3',NULL,NULL);
INSERT INTO myTable VALUES (16,'prj1',2,'a','t3',NULL,NULL);
INSERT INTO myTable VALUES (17,'prj1',36,'b','t1','8dac5bdffc7f86502',0);
INSERT INTO myTable VALUES (18,'prj1',36,'b','t1','8dac5bdffc7f86502',0);
INSERT INTO myTable VALUES (19,'prj1',36,'b','t1','8dac5bdffc7f86502',0);
INSERT INTO myTable VALUES (19,'prj1',36,'b','t2','8dac5bdffc7f86502',16);

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

1 rows affected

number	type	case
2	t1	2.8000000000000000
2	t2	null
36	t1	INF
36	t2	null