Início / dba / Perguntas / 25890
Accepted
davetapley
Asked: 2012-10-13 21:21:38 +0800 CST

A atualização de um campo parcialmente indexado adiciona ou remove de um índice no Postgres

  • 772

Podemos criar um índice parcial para um campo no Postgres.

Eu estou olhando para usar isso para parar as linhas que estão archivedem um índice em um created_at field:

  1. Primeiro criamos o índice:

    test=# CREATE INDEX idx_my_table_created_at ON my_table(created_at) WHERE NOT(archived);

  2. Inserimos duas linhas, uma que entra idx_my_table_created_ate outra que não:

     id |         created_at         | archived
----+----------------------------+----------
  2 | 2012-10-12 17:39:17.28511  | t
  1 | 2012-10-12 17:38:55.531278 | f

  3. Agora temos UPDATEuma linha que está atualmente no índice, de forma que não deveria estar no índice:

    test=# UPDATE my_table SET archived = true WHERE id = 1;


Minha pergunta: id = 1Ainda está em idx_my_table_created_at?

Esta questão segue uma resposta a uma pergunta anterior que fiz sobre como lidar com linhas que não serão consultadas quando forem antigas, portanto, archived.

postgresql performance

1 respostas

  1. Best Answer

    (Observação: não sou um profundo especialista em Pg guts. Este é apenas o meu entendimento. Você realmente deve ler as seções mvcc e internas do manual) .

    Sim, inicialmente a linha com id = 1ainda está no índice, apesar da WHEREcláusula do índice excluí-la porque foi atualizada com archived = true. Em algum ponto da trilha, a entrada do índice será limpa VACUUMou autovácua, liberando o espaço.

    A razão para isso é o design MVCC do PostgreSQL. Outras transações simultâneas ainda podem ver a versão antiga da linha com id = 1, archived = false- READ COMMITTEDtransações com uma instrução de execução longa ou SERIALIZABLEtransações que não veem as alterações confirmadas após o início. Se a entrada do índice fosse removida quando a linha fosse atualizada, essas transações não encontrariam a linha nas varreduras de índice e produziriam o resultado errado.

    Tecnicamente, o que realmente está acontecendo é que a linha antiga ainda existe, ela apenas tinha um xmaxconjunto para que as transações mais recentes a ignorem quando a virem. Após a atualização, o índice aponta para essa linha, como antes. Uma nova linha com archived = truefoi inserida com um xminacima da linha antiga xmax, portanto, qualquer transação visualiza a versão antiga ou a nova, nunca ambas. Essa nova linha não é adicionada ao índice.

    Eventualmente, não haverá nenhuma transação aberta que ainda possa ver a linha antiga, então VACUUMela aparecerá e removerá a linha antiga e sua entrada de índice, liberando o espaço para reutilização.

    Observe que o próprio índice não possui informações de visibilidade de linha. Você pode - e geralmente tem - muitas versões de uma linha, cada uma com entradas de índice. Quando uma transação procura entradas de índice correspondentes, ela lê todas as versões (a menos que possa dizer no mapa de visibilidade que não precisa) e ignora todas, exceto aquela que possui um xmine xmaxque a torna visível para essa transação.

    Este é um dos muitos bons motivos para garantir que o autovaccum seja executado com frequência. Ele reduz o número de entradas de índice mortas e linhas mortas que você tem desperdiçando espaço e E/S.

    • 4

relate perguntas