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 log_message_view_uat (
  test_run_id integer, 
  test_id integer, 
  suite_id integer, 
  exe_length integer, 
  message varchar(50)
  );

CREATE TABLE


insert into log_message_view_uat values 
(4201, 32, 39, 11, 'Hub Status does1'), 
(4201, 32, 39, 17, 'Hub Status does3'),
(4201, 32, 39, 17, 'Hub Status does4'),
(4201, 32, 39, 18, 'Hub Status does7'),
(4200, 32, 39, 11, 'Hub Status does8'),
(4197, 32, 39, 11, 'Hub Status does0'),
(4196, 32, 39, 17, 'Hub Status doesn14'),
(4196, 32, 39, 18, 'Hub Status does134'),
(4195, NULL, 31, 14, 'Hub Status does123'),
(4187, 7, 12, 35, 'Hub Status d34567'),
(4187, 7, 12, 35, 'Hub Status 345566');

INSERT 0 11


--if wanting just one even if ties
select 
 test_run_id, 
 test_id, 
 suite_id, 
 exe_length, 
 message
from (
	select *, 
	  row_number() over (partition by test_run_id, test_id, suite_id order by exe_length) as rn
	from log_message_view_uat
	)z
where rn = 1
order by 1


test_run_id
test_id
suite_id
exe_length
message


4187
7
12
35
Hub Status 345566

4195
null
31
14
Hub Status does123

4196
32
39
17
Hub Status doesn14

4197
32
39
11
Hub Status does0

4200
32
39
11
Hub Status does8

4201
32
39
11
Hub Status does1



SELECT 6


--if wanting ties
select 
 test_run_id, 
 test_id, 
 suite_id, 
 exe_length, 
 message
from (
	select *, 
	  dense_rank() over (partition by test_run_id, test_id, suite_id order by exe_length) as rn
	from log_message_view_uat
	)z
where rn = 1
order by 1


test_run_id
test_id
suite_id
exe_length
message


4187
7
12
35
Hub Status 345566

4187
7
12
35
Hub Status d34567

4195
null
31
14
Hub Status does123

4196
32
39
17
Hub Status doesn14

4197
32
39
11
Hub Status does0

4200
32
39
11
Hub Status does8

4201
32
39
11
Hub Status does1



SELECT 7

test_run_id	test_id	suite_id	exe_length	message
4187	7	12	35	Hub Status 345566
4195	null	31	14	Hub Status does123
4196	32	39	17	Hub Status doesn14
4197	32	39	11	Hub Status does0
4200	32	39	11	Hub Status does8
4201	32	39	11	Hub Status does1