Tenho um site para mostrar partidas de um jogo RTS.
Um dos principais casos de uso é mostrar partidas recentes de um jogador ou de um grupo de jogadores .
PostgreSQL 14.13
Estrutura da tabela:
create table public.match (
match_id integer primary key not null,
started timestamp(3) without time zone,
...
);
create index "IDX_e7b6cfca8139b9aa85880aab9e" on match using btree (started);
create table public.player (
match_id integer not null,
profile_id integer not null,
slot smallint not null,
...,
primary key (match_id, profile_id, slot),
foreign key (match_id) references public.match (match_id)
match simple on update cascade on delete restrict,
foreign key (profile_id) references public.profile (profile_id)
match simple on update cascade on delete restrict
);
create index idx_player_profile_match on player using btree (profile_id, match_id);
create index "IDX_58afd2c450f166eacbdf982841" on player using btree (match_id);
create index "IDX_ba3de28aa98207f3a21145feb8" on player using btree (profile_id);
A tabela de partidas tem 50 milhões de registros. A tabela de jogadores tem 200 milhões de registros.
Há cerca de 50.000 partidas com 4 jogadores em média adicionados por dia ao banco de dados.
As tabelas atualmente contêm dados dos últimos 3 anos. A tabela match tem relation_size 6 GB e total_relation_size 12 GB. A tabela player 15 GB e 37 GB.
Consultar o BD para as 50 partidas mais recentes de um jogador ou para todas as partidas do último mês de um jogador leva quase um segundo . Após a primeira execução, é mais rápido porque é armazenado em cache, mas isso não me ajuda, pois frequentemente são solicitados usuários diferentes.
-- most recent 50 matches for a player
EXPLAIN ANALYSE
SELECT * FROM match m
JOIN player p on p.match_id = m.match_id
WHERE p.profile_id=582058 ORDER BY started desc LIMIT 50;
Limit (cost=12075.34..12081.09 rows=50 width=107) (actual time=675.517..682.833 rows=50 loops=1)
-> Gather Merge (cost=12075.34..12158.48 rows=723 width=107) (actual time=675.516..682.828 rows=50 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (cost=11075.33..11077.14 rows=723 width=107) (actual time=663.726..663.731 rows=38 loops=2)
Sort Key: m.started DESC
Sort Method: top-N heapsort Memory: 39kB
Worker 0: Sort Method: top-N heapsort Memory: 37kB
-> Nested Loop (cost=22.66..11051.31 rows=723 width=107) (actual time=3.286..661.017 rows=1882 loops=2)
-> Parallel Bitmap Heap Scan on player p (cost=22.09..4853.22 rows=723 width=40) (actual time=2.774..210.891 rows=1882 loops=2)
Recheck Cond: (profile_id = 582058)
Heap Blocks: exact=1855
" -> Bitmap Index Scan on ""IDX_ba3de28aa98207f3a21145feb8"" (cost=0.00..21.79 rows=1229 width=0) (actual time=4.818..4.819 rows=3772 loops=1)"
Index Cond: (profile_id = 582058)
-> Index Scan using match_pkey on match m (cost=0.56..8.57 rows=1 width=67) (actual time=0.237..0.237 rows=1 loops=3764)
Index Cond: (match_id = p.match_id)
Planning Time: 0.305 ms
Execution Time: 682.897 ms
-- matches in the last month for a player
EXPLAIN ANALYSE
SELECT * FROM match m
JOIN player p on p.match_id = m.match_id
WHERE m.started > '2024-09-01' AND
p.profile_id=271202 ORDER BY started desc;
Gather Merge (cost=12053.93..12057.61 rows=32 width=107) (actual time=797.710..797.881 rows=161 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (cost=11053.92..11054.00 rows=32 width=107) (actual time=782.572..782.578 rows=80 loops=2)
Sort Key: m.started DESC
Sort Method: quicksort Memory: 36kB
Worker 0: Sort Method: quicksort Memory: 35kB
-> Nested Loop (cost=22.66..11053.12 rows=32 width=107) (actual time=171.842..781.756 rows=80 loops=2)
-> Parallel Bitmap Heap Scan on player p (cost=22.09..4853.22 rows=723 width=40) (actual time=1.254..268.523 rows=1682 loops=2)
Recheck Cond: (profile_id = 271202)
Heap Blocks: exact=1721
" -> Bitmap Index Scan on ""IDX_ba3de28aa98207f3a21145feb8"" (cost=0.00..21.79 rows=1229 width=0) (actual time=1.712..1.712 rows=3378 loops=1)"
Index Cond: (profile_id = 271202)
-> Index Scan using match_pkey on match m (cost=0.56..8.58 rows=1 width=67) (actual time=0.304..0.304 rows=0 loops=3365)
Index Cond: (match_id = p.match_id)
Filter: (started > '2024-09-01 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 1
Planning Time: 0.446 ms
Execution Time: 797.931 ms
Ao executar isso novamente, o tempo de execução cai para cerca de 40 ms devido ao cache.
Até onde eu entendo, o Postgres primeiro encontra todos os registros de jogadores para o profile_id fornecido e então ordena por data de início e então filtra por contagem/data de início . Dessa forma, a consulta se tornará cada vez mais ineficiente à medida que mais correspondências forem adicionadas ao banco de dados.
Minha ideia é adicionar a data de início da partida à tabela de jogadores e criar um índice em profile_id, iniciado.
Você acha que essa é uma boa solução? Existem outras opções?
Também pensei em particionar a tabela de partidas/jogadores por data de início, mas isso parece dar muito trabalho.
Atualização usando a resposta de Erwin Brandstetter:
Obrigado pelo seu conselho!
Criei o índice e preenchi a startedcoluna da playertabela:
CREATE INDEX player_profile_id_started_match_id ON player (profile_id, started) INCLUDE (match_id);
A consulta de partidas recentes para um jogador agora é muito rápida:
-- get recent 50 matches for one player
EXPLAIN ANALYSE
SELECT *
FROM (
SELECT p.match_id
FROM player p
WHERE p.profile_id=199325
ORDER BY p.started DESC
LIMIT 50
) p
JOIN match m USING (match_id);
Nested Loop (cost=1.14..500.47 rows=50 width=67) (actual time=2.721..21.442 rows=50 loops=1)
-> Limit (cost=0.57..70.85 rows=50 width=12) (actual time=2.702..14.494 rows=50 loops=1)
-> Index Only Scan Backward using player_profile_id_started_match_id on player p (cost=0.57..1739.19 rows=1237 width=12) (actual time=2.701..14.480 rows=50 loops=1)
Index Cond: (profile_id = 199325)
Heap Fetches: 50
-> Index Scan using match_pkey on match m (cost=0.56..8.58 rows=1 width=67) (actual time=0.138..0.138 rows=1 loops=50)
Index Cond: (match_id = p.match_id)
Planning Time: 0.222 ms
Execution Time: 21.482 ms
Também tenho o caso de uso de consultar as partidas recentes de vários jogadores (um ou mais jogadores precisam estar na partida).
-- get recent 50 matches from multiple players
EXPLAIN ANALYSE
SELECT *
FROM (
SELECT p.match_id
FROM player p
WHERE p.profile_id IN (1136191, 19455781)
ORDER BY p.started DESC
LIMIT 50
) p
JOIN match m USING (match_id);
Nested Loop (cost=3557.08..3986.26 rows=50 width=67) (actual time=511.692..515.601 rows=50 loops=1)
-> Limit (cost=3556.51..3556.64 rows=50 width=12) (actual time=511.658..511.671 rows=50 loops=1)
-> Sort (cost=3556.51..3562.70 rows=2473 width=12) (actual time=511.657..511.663 rows=50 loops=1)
Sort Key: p.started DESC
Sort Method: top-N heapsort Memory: 29kB
-> Index Only Scan using player_profile_id_started_match_id on player p (cost=0.57..3474.36 rows=2473 width=12) (actual time=0.592..508.846 rows=5277 loops=1)
" Index Cond: (profile_id = ANY ('{1136191,19455781}'::integer[]))"
Heap Fetches: 2965
-> Index Scan using match_pkey on match m (cost=0.56..8.58 rows=1 width=67) (actual time=0.078..0.078 rows=1 loops=50)
Index Cond: (match_id = p.match_id)
Planning Time: 1.090 ms
Execution Time: 515.662 ms
Neste exemplo, ele estava muito lento. Mas depois de uma execução VACUUM ANALYZE public.player;que levou 7 minutos, ele está rodando rápido (cerca de 30 ms).
Observação: ainda não atualizei a chave estrangeira e o índice exclusivo porque a data de início nunca será alterada após a criação dos registros de partidas e jogadores.