Início / dba / Perguntas / 313274
Accepted
tuk
Asked: 2022-06-13 10:20:20 +0800 CST

Vantagem de usar INCLUDE em vez de adicionar a coluna em INDEX para cobrir o índice

  • 772

Os documentos do Postgres informam o seguinte sobre varreduras somente de índice e índices de cobertura :

se você costuma executar consultas como

SELECT y FROM tab WHERE x = 'key';

a abordagem tradicional para acelerar essas consultas seria criar um índice somente em x. No entanto, um índice definido como

CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

poderia tratar essas consultas como varreduras somente de índice, porque y pode ser obtido do índice sem visitar o heap.

Como a coluna y não faz parte da chave de pesquisa do índice, ela não precisa ser de um tipo de dados que o índice possa manipular; ele é meramente armazenado no índice e não é interpretado pelo mecanismo do índice. Além disso, se o índice for um índice exclusivo, isso é

CREATE UNIQUE INDEX tab_x_y ON tab(x) INCLUDE (y);

a condição de exclusividade se aplica apenas à coluna x, não à combinação de x e y. (Uma cláusula INCLUDE também pode ser escrita em restrições UNIQUE e PRIMARY KEY, fornecendo sintaxe alternativa para configurar um índice como este.)

Pergunta 1: Se o tipo de dados ypode ser adicionado no índice e não há requisito de exclusividade, há alguma vantagem em usar CREATE INDEX tab_x_y ON tab(x) INCLUDE (y)para CREATE INDEX tab_x_y ON tab(x, y)consultas como SELECT y FROM tab WHERE x = 'key';?

É aconselhável ser conservador ao adicionar colunas de carga útil não chave a um índice, especialmente colunas largas. Se uma tupla de índice exceder o tamanho máximo permitido para o tipo de índice, a inserção de dados falhará. De qualquer forma, as colunas não chave duplicam os dados da tabela do índice e aumentam o tamanho do índice, potencialmente tornando as pesquisas mais lentas.

Pergunta 2: Alguém pode explicar com um exemplo o que wide columnssignifica?

Pergunta 3: Alguém pode explicar a afirmação abaixo no contexto de INCLUDE(y). Se INCLUDEsuportar apenas varreduras de índice y, também terá que ser armazenado em index. Então, como a declaração abaixo não vale para INCLUDE(y).

De qualquer forma, as colunas não chave duplicam os dados da tabela do índice e aumentam o tamanho do índice

postgresql index

3 respostas

  1. Best Answer

    Regra geral 1: Se você nunca usa uma coluna de índice para filtrar ou classificar (ou unir, ou impor exclusividade), pode movê-la para a INCLUDEcláusula. Nada perdido, algo ganho.

    Regra geral 2: INCLUDEas colunas só fazem sentido se você realmente obtiver varreduras somente de índice delas. E em alguns casos nem assim.

    Resposta 1: O INCLUDErecurso é predominantemente útil para os dois casos excluídos: exclusividade, ou não permitido no índice de outra forma. Mas ainda há pequenos benefícios para outros casos. O manual explica mais abaixo:

    O truncamento de sufixo sempre remove colunas não chave dos níveis superiores da Árvore B. Como colunas de carga útil, elas nunca são usadas para orientar varreduras de índice. O processo de truncamento também remove uma ou mais colunas-chave à direita quando o prefixo restante da(s) coluna(s)-chave é suficiente para descrever tuplas no nível B-Tree mais baixo. Na prática, cobrir índices sem uma INCLUDEcláusula geralmente evita armazenar colunas que são efetivamente carga útil nos níveis superiores. No entanto, definir explicitamente as colunas de carga útil como colunas sem chave mantém as tuplas nos níveis superiores pequenas de forma confiável.

    Agora, os índices de árvore B têm apenas alguns níveis de profundidade. Mas os níveis superiores são aqueles que devem ser lidos o tempo todo . Manter esses pequenos ajuda mais. Mesmo um pequeno efeito é reforçado por isso. O benefício é maior para cardinalidades grandes (vários níveis de índice) e pouca duplicação (truncamento de sufixo não pode compensar a não movimentação de uma coluna de carga útil para a INCLUDEparte).

    Além disso, há um caso com índices de expressão . O Postgres atualmente (Postgres 15) não é inteligente o suficiente para escolher uma varredura somente de índice, a menos que a própria coluna envolvida seja incluída no índice. O manual novamente:

    Se uma varredura somente de índice parece valer a pena, isso pode ser contornado adicionando xcomo uma coluna incluída, por exemplo

    CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);

    (A menos que o plain xtambém esteja envolvido na consulta) INCLUDE (x)serve apenas como uma dica estranha para o planejador de consulta, enquanto only f(x)é realmente usado.

    Resposta 2: Colunas "largas" são colunas grandes, colunas que ocupam muito espaço de armazenamento "no disco" (geralmente não é um "disco" hoje em dia) ou na RAM. O armazenamento em disco controla quantas páginas de dados devem ser visitadas para atender a uma consulta, que geralmente é o fator mais importante para o desempenho. O que conta é a representação interna, não a representação de texto que você vê . Teste com pg_column_size()- mas esteja ciente de que o tamanho dos dados no formato "empacotado" ("no disco") pode ser mais compacto do que na RAM. E há várias despesas gerais. Ver:

    Resposta 3: Os custos de gravação e o tamanho inchado se aplicam a INCLUDE(y)colunas de índice regulares. A questão é se adicionar INCLUDE colunas, que são sempre logicamente opcionais. (Colunas de índice regulares geralmente não são opcionais.) Veja também a resposta 1 .

    • 1

    1. A diferença entre cobrir e incluir não está na hora de selecionar, é na hora de inserir e atualizar. As colunas INCLUDEd não precisam ser mantidas em uma ordem estável, portanto, se você atualizar essas colunas (sem alterar seu tamanho, se variável, ou atualizar as outras cobertas pelo índice), as coisas não precisam ser reordenadas. Isso pode reduzir as divisões de página ou outras gravações extras, tornando a operação mais eficiente e reduzindo a fragmentação interna.

    2. Suponho que colunas largas significam strings de qualquer tamanho significativo ou tamanho variável. Outros tipos de colunas são geralmente menores e de tamanho fixo.

    3. Exatamente o que diz: o valor é copiado no índice para que o índice seja maior e, se houver uma verificação de índice ou verificação parcial, mais páginas provavelmente precisarão ser acessadas.

    Nota: Eu sou uma pessoa do MS SQL Server principalmente, pode haver bordas específicas do postgres em torno disso.

    • 1

  3. Além da ótima resposta de Erwin, há uma vantagem adicional em usar a INCLUDEsintaxe: documentação.

    Imagine que você decida que precisa de um índice nas colunas (a, b)da tabela tab. Agora você descobre que já existe um índice em (a, c). Nesta situação você tem duas opções:

    • simplesmente vá em frente e crie outro índice

    • se você tiver certeza de que a coluna csó foi adicionada ao índice para dar suporte a uma varredura somente de índice e nunca é usada como uma condição de pesquisa , você pode descartar o índice antigo e criar um novo em (a, b, c), salvando assim um índice

    Agora, geralmente é difícil determinar que uma coluna de índice nunca é usada como uma condição de pesquisa, a menos que – bem, a menos que apareça na INCLUDEcláusula. Nesse caso, você não precisa pensar duas vezes e pode substituir o index on (a) INCLUDE (c)por um on (a, b) INCLUDE (c).

    • 1

relate perguntas