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 |