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');

INSERT INTO tableName VALUES
('H_00018',  '2',     'SOUTH',     'PROD'  , '2023-10-31 20:17:40'),
('H_00018',  '2',     'NORTH',     'PROD'  , '2023-11-01 03:17:40');

INSERT INTO tableName VALUES
('H_00033' ,  '2',  'SOUTH'  , 'PROD'  , '2023-10-31 20:17:40'),
('H_00033' ,  '2',  'NORTH'  , 'DEV'   , '2023-11-02 03:17:40');

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

INSERT 0 2

INSERT 0 2

_month	env	counts
2022-10	PROD	2
2022-10	PROD/TEST	1
2022-11	DEV/PROD/TEST	1
2022-11	PROD/TEST	1
2023-10	DEV/PROD	1
2023-10	PROD	1

SELECT 6