Início / dba / Perguntas / 33196
Accepted
jap1968
Asked: 2013-01-20 05:36:43 +0800 CST

Índice e desempenho de várias colunas

  • 772

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:

insira a descrição da imagem aqui

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%"

postgresql index

2 respostas

  1. Best Answer

    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:

    Regra geral: primeiro índice para igualdade — depois para intervalos.

    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:

    CREATE TABLE tbl AS
SELECT (random() * 10000)::int AS lots
     , (random() * 4)::int     AS few
FROM generate_series (1, 100000);

DELETE FROM tbl WHERE random() > 0.9;  -- create some dead tuples, more "real-life"

VACUUM ANALYZE tbl;

SELECT count(distinct lots)   -- 9999
     , count(distinct few)    --    5
FROM   tbl;

    Consulta:

    SELECT *
FROM   tbl
WHERE  lots = 2345
AND    few = 2;

    EXPLAIN ANALYZEsaída (melhor de 10 para excluir efeitos de cache):

    Seq Scan on tbl (custo=0,00..5840,84 linhas=2 largura=8)
                 (tempo real=5.646..15.535 linhas=2 voltas=1)
  Filtro: ((lotes = 2345) E (poucos = 2))
  Buffers: hit local=443
Tempo de execução total: 15,557 ms

    Adicione índice, teste novamente:

    CREATE INDEX tbl_lf_idx ON tbl(lots, few);
    Varredura de índice usando tbl_lf_idx em t (custo=0,00..3,76 linhas=2 largura=8)
                                       (tempo real=0.008..0.011 linhas=2 voltas=1)
  Índice Cond: ((lotes = 2345) E (poucos = 2))
  Buffers: hit local=4
Tempo de execução total: 0,027 ms

    Adicione outro índice, teste novamente:

    DROP INDEX tbl_lf_idx;
CREATE INDEX tbl_fl_idx ON tbl(few, lots);
    Varredura de índice usando tbl_fl_idx em tbl (custo=0,00..3,74 linhas=2 largura=8)
                                    (tempo real=0.007..0.011 linhas=2 voltas=1)
  Índice Cond: ((poucos = 2) E (lotes = 2345))
  Buffers: hit local=4
Tempo de execução total: 0,027 ms

    Repetido 2021 com Postgres 13, mesma conclusão:

    db<>fique aqui

    • 43

  2. Se, como você diz, as consultas envolvendo essas 2 colunas, são todas verificações de igualdade de ambas as colunas, por exemplo:

    WHERE c1=@ParameterA AND c2=@ParameterB

    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 yeare month. É mais provável que você precise de uma WHERE year = 2000condição ou um WHERE year BETWEEN 2000 AND 2013ou um WHERE (year, month) BETWEEN (1999, 6) AND (2000, 5).

    Uma consulta do tipo WHERE month = 7 GROUP BY yearpode 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:

    Também existem condições como esta:WHERE c1 = 'abc' AND c2 LIKE 'ab%'

    Esse tipo de consulta é exatamente uma condição de intervalo na c2coluna e precisaria de um (c1, c2)índice. Se você também tiver consultas do tipo inverso:

    WHERE c2 = 'abc' AND c1 LIKE 'ab%'

    então seria bom se você tivesse um (c2, c1)índice também.

    • 11

relate perguntas