Início / dba / Perguntas / 190156
Accepted
ldrg
Asked: 2017-11-06 08:42:47 +0800 CST

O Postgres pode usar uma varredura somente de índice para esta consulta com tabelas unidas?

  • 772

Esta é uma continuação de: A cobertura de índices no PostgreSQL ajuda as colunas JOIN?

Considere o inverso do esquema na outra pergunta em que você filtra na tabela unida:

CREATE TABLE thing_types(
   id              INTEGER PRIMARY KEY
 , first_lvl_type  TEXT
 , second_lvl_type TEXT
);

CREATE TABLE things(
   id         INTEGER PRIMARY KEY
 , thing_type INTEGER REFERENCES thing_types(id)
 , t1c1       INTEGER
);

E uma consulta assim:

SELECT things.t1c1
FROM   things
JOIN   thing_types ON things.thing_type = thing_types.id
WHERE  thing_types.first_lvl_type = 'Book'
AND    thing_types.second_lvl_type = 'Biography';

É loucura ter um índice como:

CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type, id);

que cobre a chave primária para uso nessa junção? O índice será usado como um índice de cobertura para ajudar JOINna consulta acima? Devo alterar minha estratégia de indexação para cobrir a chave primária com mais frequência quando sei que a tabela será JOINeditada assim?

postgresql index

2 respostas

  1. Best Answer

    Se as pré-condições adicionais para uma varredura somente de índice forem atendidas, faz todo o sentido anexar a coluna idcomo coluna final ao índice (não como coluna inicial):

    CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type, id);

    O Postgres 11 introduz índices de cobertura reais com a palavra- INCLUDEchave .

    CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type) INCLUDE (id);

    Apenas um pequeno benefício para o seu caso, mas é uma ótima opção para adicionar colunas a um índice ou restrição UNIQUE ou PK.

    Sobre verificações somente de índice:

    A pré-condição mais importante: O mapa de visibilidade da tabela thing_typesdeve mostrar a maioria ou todas as páginas como "visíveis" para todas as transações. Ou seja, a tabela é somente leitura ou suas configurações de autovacuum são agressivas o suficiente para limpar continuamente após as gravações na tabela.

    Cada índice adicional adiciona custos. Principalmente para escrever desempenho. Mas também efeitos colaterais, como capacidades de cache esgotadas. (Várias consultas usando os mesmos índices têm uma chance melhor de residir no cache.) Portanto, também é uma questão de tamanho . idé normalmente uma coluna muito pequena integerou bigint. Torna-o um bom candidato para o caso de uso.

    Em particular, adicionar uma coluna a um índice desativa a opção de atualizações HOT envolvendo a coluna. Mas como idé indexado de qualquer maneira e normalmente não é atualizado (sendo o PK), isso não é um problema neste caso. Relacionado:

    Se você realmente obtém verificações somente de índice desses índices na maioria das vezes, normalmente faz sentido usá-los. Teste com EXPLAIN.

    Havia limitações para índices parciais em versões mais antigas. Citando as notas de lançamento do Postgres 9.6 :

    • Permitir o uso de uma varredura somente de índice em um índice parcial quando a WHEREcláusula do índice fizer referência a colunas não indexadas (Tomas Vondra, Kyotaro Horiguchi)

      Por exemplo, um índice definido por CREATE INDEX tidx_partial ON t(b) WHERE a > 0agora pode ser usado para uma verificação somente de índice por uma consulta que especifica WHERE a > 0e não usa de outra formaa . Anteriormente, isso não era permitido porque a não está listado como uma coluna de índice.

    • 3

  2. Você precisa experimentá-lo e ver o seu plano de consulta específico. Você está fazendo muitas suposições gerais sobre o conselho dado e até mesmo o potencial para que ele seja útil para sua consulta.

    • Tamanho.
    • Número principal do PostgreSQL
    • Configuração para custos.
    • Staleness e precisão das estatísticas.

    Todas essas coisas importam.

    Não quero ser vago aqui, mas eu poderia evocar alguns exemplos para mostrar isso.

    Geralmente, eu não indexaria algo que já está indexado na tabela. Se por nenhum outro motivo, porque para cada vez que um índice cobre uma coluna específica, você tem mais um índice que precisa ser atualizado quando você altera a linha.

    • 0

relate perguntas