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 mytable (col1 timestamp, tracker_id int,position int )

CREATE TABLE

insert into mytable values ('2020-02-01 21:53:45.571429+05:30',  15,      1)

INSERT 0 1

insert into mytable values ('2020-02-01 21:53:45.857143+05:30',  11,      1)

INSERT 0 1

insert into mytable values ('2020-02-01 21:53:46.428571+05:30',  15,      1)

INSERT 0 1

insert into mytable values ('2020-02-01 21:53:46.714286+05:30',  11,      2)

INSERT 0 1

select * from mytable m
where m.col1 = (select min(col1)
              from mytable m1
              where m.tracker_id = m1.tracker_id
              group by tracker_id)
and m.tracker_id in (11,15,12);

col1
tracker_id
position

2020-02-01 21:53:45.571429
15
1

2020-02-01 21:53:45.857143
11
1

SELECT 2