Início / dba / Perguntas / 27949
Accepted
alfonx
Asked: 2012-11-01 05:28:15 +0800 CST

Corro o risco de perder os benefícios da indexação se tiver um índice em cada coluna?

  • 772

Eu uso o PostgreSQL 9.2. Eu tenho uma tabela com aproximadamente 5 milhões de linhas e 150 colunas. A tabela não muda nada (eu a substituo uma vez por ano). Os usuários consultam esta tabela com todos os tipos de filtros em qualquer uma das colunas, por exemplo

select * from table where C > 43 and H is not null;
select * from table where A is null and F < 10 and F > 1 and X > 2;

Para desempenho, pretendo criar um índice em cada coluna da tabela. Um sentimento no estômago me diz para perguntar primeiro aos especialistas: é um bom design para o caso de uso descrito acima criar um índice em cada coluna?

ATUALIZAÇÃO: Tenho que especular sobre casos de uso reais. Ainda não consigo medir as consultas exatas. Isso está em fase de projeto.

O servidor está bem equipado com RAM e armazenamento SSD, então as consultas já são "rápidas" agora e posso sentir o efeito do cache quando aciono consultas semelhantes em sequência.

As colunas são dos tipos double, integer, timestamp e geometry (que explicitamente obtém um índice 'gist').

As consultas incluirão de 1 a 10 colunas. Normalmente ~6. Os resultados geralmente serão <20k linhas. As consultas em uma coluna nunca serão relacionadas a outra coluna.

Obrigado por todas as explicações. O que vou fazer: * selecionar 1/4 das colunas que acho que serão mais utilizadas e criar índices. * aguarde mais testes/uso e comece a medir/analisar as consultas e os casos de uso.

obrigada

postgresql index

2 respostas

  1. Quando você diz "para desempenho", está adotando uma visão restrita - "desempenho" SELECTapenas como desempenho, uma consulta por vez, sem considerar o cache e os problemas de contenção de E/S.

    Sim, ter um índice em cada coluna pode melhorar o desempenho de leitura . Certamente diminuirá o desempenho de gravação ( , e ), embora isso não seja uma preocupação para o seu banco de dados. Mais sutilmente, mais índices significam mais contenção de espaço na RAM para armazenar em cache os índices, o que significa que há uma chance maior de que uma verificação de qualquer índice resulte em leituras de disco lentas.INSERTUPDATEDELETE

    Depois, há o fato de que você pode indexar mais do que apenas colunas individuais. Muitas vezes, é mais produtivo criar índices de várias colunas, parciais, de expressão e/ou descendentes para melhor atender às necessidades de suas consultas. Você simplesmente não pode criar todos os índices possíveis. Aqui está um exemplo recente de um índice do mundo real que você nunca criaria sem a análise das consultas que o exigiam :

    CREATE INDEX contfloattable_tag_and_timeseg
ON contfloattable(tagindex, (floor(extract(epoch FROM dateandtime) / 60 / 15) ));

    Então: você poderia criar um índice em cada coluna, mas é uma má ideia. Use EXPLAINAND EXPLAIN ANALYZE, possivelmente por meio do auto_explainmódulo, para analisar sua carga de trabalho e consultas e tomar decisões de indexação com base em como você realmente consulta as tabelas. Um índice que você não usa irá prejudicá-lo em um ambiente de leitura/gravação e não será bom em um ambiente somente leitura.

    Não visualize as consultas isoladamente. Olhe para os padrões. Se query1 parece querer um índice col1e query2 parece querer um índice em ambos col1e col2, não se preocupe em criar um índice apenas em col1. Crie um índice composto em col1, col2; será quase tão rápido para uma pesquisa col1sozinha e muito melhor do que manter dois índices e fazer com que eles briguem por espaço de cache e E/S de disco.

    BTW, isso parece mais uma carga de trabalho OLAP do que OLTP , então você pode querer considerar esquemas projetados para cargas de trabalho OLAP como esquema em estrela com tabelas de fatos e dimensões.

    • 7
  2. Best Answer

    A resposta é (quase sempre) não.

    Cada índice que você cria significa mais espaço em disco usado, janelas de manutenção mais longas e um custo mais alto se a tabela que nunca deve mudar (exceto para ser substituída uma vez por ano) realmente for alterada. Eles aumentam o tamanho de seus backups e dificultam muito a adaptação à janela de recuperação de desastres. Além disso, muitos desses índices provavelmente nunca serão usados, então eles são apenas um peso morto.

    Então, você deve pensar em índices de várias colunas. Em ambos os exemplos, você tem duas colunas. Se você indexar apenas colunas únicas, nenhum índice único não agrupado pode ser bom o suficiente e, portanto, a consulta retornará ao índice agrupado. Depois, há os índices de três colunas, índices de quatro colunas, etc.

    A melhor aposta é determinar quais consultas são comuns e começar a indexá-las. Não estou familiarizado com os internos do postgresql, mas existem alguns links flutuando que podem ajudar a configurar a coleta de estatísticas . Depois de obter uma ideia bastante sólida de quais consultas os usuários estão realmente usando, você pode criar índices que podem ajudar. Mesmo assim, esta não é uma estratégia perfeita: você está (metaforicamente) construindo estradas onde as pessoas estão andando, mas as pessoas que criam esses caminhos têm que se arrastar por elas até que você descubra para onde estão indo e construa a estrada para ajuda. Este é um processo reacionário, mas provavelmente pegaria uma porcentagem bastante grande de usos comuns.

    Como este é um banco de dados somente leitura, no entanto, poderia criar com segurança subconjuntos de dados comuns que atendem a uma grande porcentagem de solicitações do usuário (por meio de tabelas separadas ou criando visualizações materializadas ). Você também pode obter mais índices não clusterizados do que gostaria para uma tabela OLTP típica. Certamente existem custos marginais para índices, mas eles são menores quando você não precisa se preocupar muito com inserções, atualizações e exclusões. E se um grande número de consultas for criado em torno de data ou algum outro campo semelhante, você poderá particionar a tabela nessa coluna.

    • 7

relate perguntas