Início / dba / Perguntas / 46624
Accepted
Nathanael Weiss
Asked: 2013-07-19 11:08:38 +0800 CST

Usando um índice para exclusividade e pesquisa rápida?

  • 772

No momento, tenho um banco de dados postgresql 8.4 configurado para jogadores em um jogo multijogador. Eu quero que a coluna de nome de usuário seja única. Eu também quero ser capaz de procurar um jogador pelo nome de usuário rapidamente. Aqui está a saída de \d players:

                            Table "public.players"
   Column   |  Type   |                      Modifiers                       
------------+---------+------------------------------------------------------
 id         | bigint  | not null default nextval('players_id_seq'::regclass)
 username   | text    | not null
 trophies   | integer | 
Indexes:
    "pk_id" PRIMARY KEY, btree (id)
    "players_username_key" UNIQUE, btree (username)
    "players_trophies_index" btree (trophies DESC NULLS LAST)
    "players_username_index" btree (username)

Eu não sou um DBA, então tenha paciência comigo. Isso parece um uso ineficiente do espaço em disco por ter dois índices na coluna de nome de usuário: um para exclusividade e outro para pesquisa rápida. É possível combiná-los em um índice, mantendo exclusividade e pesquisa rápida? Em caso afirmativo, há alguma desvantagem em tal abordagem?

postgresql index

2 respostas

  1. Best Answer

    Você só precisa de um único índice, o único. As consultas a serem pesquisadas usernameusarão o índice exclusivo. Não há vantagem em ter um segundo índice na mesma coluna marcado como não exclusivo.

    Você pode verificar isso executando um EXPLAINna consulta. Conecte-se ao seu banco de dados e compare os resultados do seguinte antes e depois de descartar o índice e confirme se são os mesmos:

    EXPLAIN VERBOSE
SELECT *
FROM public.players
WHERE username = 'foobar'

    Em ambos os casos, deve mostrar algo como:

    Index scan using using players_username_key on public.players (cost=...[truncated])
  Output: id, username, trophies ...
    • 4

  2. Com a duplicação de índice, você não apenas desperdiça espaço em disco, mas também aumenta a sobrecarga para alterações na tabela. Cada índice adicional aumenta o tempo para adicionar e excluir linhas e pode aumentar o tempo para atualizações.

    Esse é o pior tipo de duplicação de chave e, conforme observado, apenas o índice exclusivo é necessário. As entradas no outro índice serão exclusivas, mas podem não ser armazenadas com tanta eficiência, pois o índice precisa lidar com entradas não exclusivas.

    Também corro índices que são um subconjunto de outro índice. Estes geralmente não são necessários, embora em alguns casos possam ser justificáveis. Raramente meus testes mostraram uma melhoria significativa na velocidade de consulta do índice extra.

    • 1

relate perguntas