Postgres 13

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 players (match_id integer, account_id integer, win boolean, id int);
 insert into players (match_id, account_id, win, id) values (165, 617348, true, 30);
1 rows affected

 insert into players (match_id, account_id, win, id) values (165, 617348, true, 22);
1 rows affected

 insert into players (match_id, account_id, win, id) values (165, 617348, true, 31);
1 rows affected

 insert into players (match_id, account_id, win, id) values (165, 812395, false, 32);
1 rows affected

    select * from players;


match_id
account_id
win
id


165
617348
t
30

165
617348
t
22

165
617348
t
31

165
812395
f
32


create  table players_tmp as select match_id, account_id, win, id from (select match_id, account_id, win, id,
rank() OVER (PARTITION BY match_id, account_id ORDER BY id) as rn
from players) r where rn = 1;
2 rows affected

alter table players rename to players_tmp2;

alter table players_tmp rename to players;
   select * from players;


match_id
account_id
win
id


165
617348
t
22

165
812395
f
32