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 data (Task_detail_id int, Task_id int , Creation_date timestamp);
insert into data 
values 
(1   , 20, timestamp '2020-05-02 20:28:23.354'),
(2   , 21, timestamp '2020-05-02 20:28:23.354'),
(3   , 22, timestamp '2020-05-02 19:28:23.354'),
(4   , 22, timestamp '2020-05-02 18:28:23.354'),
(5   , 22, timestamp '2020-05-02 17:28:23.354'),
(6   , 22, timestamp '2020-05-02 16:28:23.354'),
(7   , 22, timestamp '2020-05-02 15:28:23.354'),
(8   , 23, timestamp '2020-05-02 10:28:23.354'),
(9   , 24, timestamp '2020-05-02 09:28:23.354'),
(10  , 24, timestamp '2020-05-02 08:28:23.354'),
(11  , 24, timestamp '2020-05-02 07:28:23.354');
11 rows affected

select task_detail_id, task_id, creation_date
from (
  select task_detail_id, task_id, creation_date, 
         row_number() over (partition by task_id order by creation_date desc) as rn
  from data
) t
where rn <= 2
order by task_detail_id, task_id;


task_detail_id
task_id
creation_date


1
20
2020-05-02 20:28:23.354

2
21
2020-05-02 20:28:23.354

3
22
2020-05-02 19:28:23.354

4
22
2020-05-02 18:28:23.354

8
23
2020-05-02 10:28:23.354

9
24
2020-05-02 09:28:23.354

10
24
2020-05-02 08:28:23.354

task_detail_id	task_id	creation_date
1	20	2020-05-02 20:28:23.354
2	21	2020-05-02 20:28:23.354
3	22	2020-05-02 19:28:23.354
4	22	2020-05-02 18:28:23.354
8	23	2020-05-02 10:28:23.354
9	24	2020-05-02 09:28:23.354
10	24	2020-05-02 08:28:23.354