Início / dba / Perguntas / 343241
Accepted
Dennis
Asked: 2024-10-27 22:08:51 +0800 CST

Recupere partidas recentes por jogador de forma eficiente

  • 772

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.

postgresql

2 respostas

  1. Para a segunda consulta, o índice na matchtabela (match_id, started)deve ajudar (se o planejador o utilizar), porque com ele leremos menos dados da tabela que serão filtrados posteriormente.

    Se isso não for suficiente, implementar sua ideia (com coluna e índice adicionais) ajudará muito mais em ambas as consultas.

    • 1
  2. Best Answer

    Minha ideia é adicionar a starteddata da partida na tabela de jogadores e criar um índice em (profile_id, started).

    Sim, isso. Mas digite timestamp(não date) para corresponder à tabela match. Além disso, adicione match_idao índice para permitir varreduras somente de índice. Possivelmente apenas como INCLUDEcoluna:

    CREATE INDEX player_profile_id_started_match_id ON player (profile_id, started) INCLUDE (match_id);

    Isso adiciona alguma redundância desagradável. Você não gostaria que startedvalores relacionados em ambas as tabelas discordassem. Para impor integridade, você pode expandir a restrição FK para:

    FOREIGN key (started, match_id) REFERENCES public.match (started, match_id)

    ... que por sua vez requer uma UNIQUErestrição em public.match (started, match_id). Eu coloquei startedprimeiro, então o índice de restrição agora pode substituir o índice "IDX_e7b6cfca8139b9aa85880aab9e"em apenas (started)para evitar custos adicionais. Veja:

    Então otimize um pouco sua consulta. LIMITem uma subconsulta antes da junção:

    SELECT *  -- ???
FROM  (
   SELECT p.match_id  -- more ?
   FROM   player p
   WHERE  p.profile_id = 582058
   ORDER  BY p.started DESC
   LIMIT  50
   ) p
JOIN   match m USING (match_id)

    Ou:

    SELECT *  -- ???
FROM  (
   SELECT p.match_id  -- more ?
   FROM   player p
   WHERE  p.profile_id = 582058
   AND    p.started >= '2024-09-01'  -- surely, you want >=
   ) p
JOIN   match m USING (match_id);

    Supondo (profile_id, started)que seja único. (O mesmo jogador não pode começar várias partidas ao mesmo tempo?) Caso contrário, adicione outra coluna para ORDER BYdesempatar.

    Aparte

    Solte os índices "IDX_ba3de28aa98207f3a21145feb8"e "IDX_58afd2c450f166eacbdf982841". Ambos mal adicionam algo além de custo de gravação sobre os índices existentes idx_player_profile_matche o índice PK. Mesma lógica que para (started, match_id)vs. logo (started)acima.

    • 1

relate perguntas