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 
(
    del_no character varying(10) NOT NULL,
    pkt	character varying(2) NOT NULL,
    direction character varying(5) NOT NULL,
    env	character varying(5) NOT NULL,
    start_datetimestamp	timestamp without time zone NOT NULL
);

INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('H_00002', '2', 'SOUTH', 'PROD', '29/10/2022 16:20:57');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('E20', '20', 'NORTH', 'PROD', '30/10/2022 16:41:37');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('H_00002', '2', 'NORTH', 'TEST', '30/10/2022 17:21:17');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('E20', '20', 'SOUTH', 'DEV', '30/10/2022 17:30:24');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('H_00004', '2', 'NORTH', 'PROD', '30/10/2022 16:52:48');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('H_00004', '2', 'SOUTH', 'PROD', '30/10/2022 19:03:36');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('H_00007', '2', 'NORTH', 'PROD', '30/10/2022 20:52:48');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('H_00007', '2', 'SOUTH', 'PROD', '30/10/2022 21:03:36');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('H_00015', '2', 'SOUTH', 'TEST', '13/11/2022 19:11:10');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('L0013', '13', 'NORTH', 'PROD', '14/11/2022 20:06:46');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('H_00015', '2', 'NORTH', 'TEST', '15/11/2022 20:17:40');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('L0021', '21', 'SOUTH', 'TEST', '15/11/2022 20:56:18');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('H_00015', '2', 'NORTH', 'PROD', '15/11/2022 20:17:40');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('L0027', '21', 'SOUTH', 'DEV', '30/11/2022 20:56:18');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('H_00019', '2', 'NORTH', 'PROD', '30/11/2022 20:17:40');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('L0023', '21', 'SOUTH', 'TEST', '30/11/2022 20:56:18');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('H_00019', '2', 'SOUTH', 'TEST', '30/11/2022 20:17:40');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('L0025', '21', 'SOUTH', 'TEST', '30/11/2022 20:56:18');
INSERT INTO tableName (del_no, Pkt, direction, Env, start_datetimestamp) VALUES ('H_00019', '2', 'SOUTH', 'DEV', '30/11/2022 20:17:40');

select * from tablename;

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

del_no	pkt	direction	env	start_datetimestamp
H_00002	2	SOUTH	PROD	2022-10-29 16:20:57
E20	20	NORTH	PROD	2022-10-30 16:41:37
H_00002	2	NORTH	TEST	2022-10-30 17:21:17
E20	20	SOUTH	DEV	2022-10-30 17:30:24
H_00004	2	NORTH	PROD	2022-10-30 16:52:48
H_00004	2	SOUTH	PROD	2022-10-30 19:03:36
H_00007	2	NORTH	PROD	2022-10-30 20:52:48
H_00007	2	SOUTH	PROD	2022-10-30 21:03:36
H_00015	2	SOUTH	TEST	2022-11-13 19:11:10
L0013	13	NORTH	PROD	2022-11-14 20:06:46
H_00015	2	NORTH	TEST	2022-11-15 20:17:40
L0021	21	SOUTH	TEST	2022-11-15 20:56:18
H_00015	2	NORTH	PROD	2022-11-15 20:17:40
L0027	21	SOUTH	DEV	2022-11-30 20:56:18
H_00019	2	NORTH	PROD	2022-11-30 20:17:40
L0023	21	SOUTH	TEST	2022-11-30 20:56:18
H_00019	2	SOUTH	TEST	2022-11-30 20:17:40
L0025	21	SOUTH	TEST	2022-11-30 20:56:18
H_00019	2	SOUTH	DEV	2022-11-30 20:17:40

SELECT 19