Tenho uma tabela com índice multicolunas, e tenho dúvidas sobre a ordenação adequada dos índices para obter o máximo desempenho nas consultas.
O cenário:
PostgreSQL 8.4, tabela com cerca de um milhão de linhas
Os valores na coluna c1 podem ter cerca de 100 valores diferentes . Podemos assumir que os valores são distribuídos uniformemente, então temos cerca de 10.000 linhas para cada valor possível.
A coluna c2 pode ter 1.000 valores diferentes . Temos 1000 linhas para cada valor possível.
Ao pesquisar dados, a condição sempre inclui valores para essas duas colunas, portanto, a tabela tem um índice de várias colunas combinando c1 e c2. Eu li sobre a importância de ordenar corretamente as colunas em um índice de várias colunas se você tiver consultas usando apenas uma coluna para filtragem. Este não é o caso em nosso cenário.
A minha pergunta é esta:
Dado que um dos filtros seleciona um conjunto de dados muito menor, posso melhorar o desempenho se o primeiro índice for o mais seletivo (o que permite um conjunto menor)? Eu nunca havia considerado essa questão até ver os gráficos do artigo referenciado:
Imagem retirada do artigo referenciado sobre índices multicolunas .
As consultas usam valores das duas colunas para filtragem. Não tenho consultas usando apenas uma coluna para filtragem. Todos eles são: WHERE c1=@ParameterA AND c2=@ParameterB
. Também existem condições como esta:WHERE c1 = "abc" AND c2 LIKE "ab%"
Responda
Como você se refere ao site
use-the-index-luke.com
, considere o capítulo:Use The Index, Luke › The Where Clause › Procurando por Intervalos › Maior, Menor e ENTRE
Ele tem um exemplo que corresponde perfeitamente à sua situação (índice de duas colunas, um é testado para igualdade , o outro para intervalo ), explica (com mais desses bons gráficos de índice) por que o conselho de @ypercube é preciso e resume:
Também é bom para apenas uma coluna?
O que fazer para consultas em apenas uma coluna parece estar claro. Mais detalhes e referências sobre isso nesta questão relacionada:
Coluna menos seletiva primeiro?
Além disso, e se você tiver apenas condições de igualdade para ambas as colunas ?
Não importa . Coloque primeiro a coluna com maior probabilidade de receber condições próprias, o que realmente importa.
Demonstração
Uma tabela simples de duas colunas com 100 mil linhas. Um com muito poucos , o outro com muitos valores distintos. Teste original executado em 2013 com o Postgres 9.2:
Consulta:
EXPLAIN ANALYZE
saída (melhor de 10 para excluir efeitos de cache):Adicione índice, teste novamente:
Adicione outro índice, teste novamente:
Repetido 2021 com Postgres 13, mesma conclusão:
db<>fique aqui
Se, como você diz, as consultas envolvendo essas 2 colunas, são todas verificações de igualdade de ambas as colunas, por exemplo:
não se incomode com isso. Duvido que haja alguma diferença e, se houver, será insignificante. Você sempre pode testar, é claro, com seus dados e configurações do servidor. Diferentes versões de um SGBD podem se comportar de forma ligeiramente diferente em relação à otimização.
A ordem dentro do índice importaria para outros tipos de consultas, tendo verificações de apenas uma coluna, ou condições de desigualdade, ou condições em uma coluna e agrupamento na outra, etc.
Se eu fosse escolher uma das duas ordens, escolheria colocar a coluna menos seletiva primeiro. Considere uma tabela com colunas
year
emonth
. É mais provável que você precise de umaWHERE year = 2000
condição ou umWHERE year BETWEEN 2000 AND 2013
ou umWHERE (year, month) BETWEEN (1999, 6) AND (2000, 5)
.Uma consulta do tipo
WHERE month = 7 GROUP BY year
pode ser desejada com certeza (Encontre pessoas nascidas em julho), mas seria menos frequente. Isso depende, é claro, dos dados reais armazenados em sua tabela. Escolha um pedido por enquanto, digamos o(c1, c2)
e você sempre poderá adicionar outro índice posteriormente(c2, c1)
.Atualização, após o comentário do OP:
Esse tipo de consulta é exatamente uma condição de intervalo na
c2
coluna e precisaria de um(c1, c2)
índice. Se você também tiver consultas do tipo inverso:então seria bom se você tivesse um
(c2, c1)
índice também.