Postgres 14

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 tbl (fid int, result int, time int);

CREATE TABLE


insert into tbl values
(1, 10, 100),
(2, 9, 90),
(3, 8, 80),
(3, 7, 70),
(3, 100, 60),
(4, 5, 50)

INSERT 0 6


select fid, result, time
from tbl where time in (select max(time) from tbl group by fid)
order by fid


fid
result
time


1
10
100

2
9
90

3
8
80

4
5
50



SELECT 4