Certas atualizações demoram muito em tabelas grandes do Postgres. Dadas estas condições:
- apenas uma coluna está sendo atualizada e não é indexada
- a coluna já contém dados para cada linha devido a uma atualização anterior
- os dados não estão mudando de tamanho (por exemplo, reescrevendo um valor booleano)
- não há nenhuma outra coluna nesta ou em qualquer outra tabela que dependa do valor da coluna que está sendo atualizada
- não há outras consultas sendo realizadas no banco de dados (este é um banco de dados de pesquisa pessoal em uma estação de trabalho, não um banco de dados corporativo)
- existem índices em outras colunas
- unidade giratória (não SSD) com Bitlocker e PC rápido com Windows 8.1 x64
- a tabela tem 10 milhões de linhas e 60 colunas
...você pensaria que a atualização levaria um tempo razoável em relação às expectativas de mídia giratória com o Bitlocker. Não estamos criando mais dados, portanto, os dados existentes não precisam ser movidos no HDD, apenas precisam ser substituídos. Os outros índices não devem precisar de alterações. Etc. Em vez disso, após 20 horas de moagem constante do HDD, canso-me de esperar e paro a consulta. Se eu soltar todos os índices em outras colunas e executar novamente a consulta, levará apenas cerca de 30 minutos.
Por que os índices em colunas irrelevantes para esta consulta aumentam o tempo de atualização?
Esse não é o caso.
Para oferecer suporte à reversão e segurança contra falhas, o PostgreSQL deve gravar uma nova cópia de cada linha modificada , em vez de modificar a linha no local. Duas vezes, na verdade, porque deve ser gravado no WAL (um log sequencial para recuperação de falhas) e depois na tabela.
O autovacuum do PostgreSQL aparece mais tarde e marca as versões de linha antigas como espaço livre que pode ser reutilizado.
Consulte o manual do usuário para obter mais informações sobre controle de simultaneidade e MVCC.
Irrelevante, porque a linha deve ser reescrita de qualquer maneira.
Isso só importa na medida em que afeta as atualizações HOT, onde o PostgreSQL pode potencialmente evitar a gravação de novas entradas de índice para uma atualização de linha se nenhuma coluna indexada for modificada e houver espaço livre suficiente na mesma página de disco (bloco de 8k) para armazenar uma nova cópia do fileira.
Na maioria dos casos, o PostgreSQL deve adicionar novas entradas de índice, mesmo que você não tenha modificado as colunas indexadas, porque ele precisa gravar uma nova versão da linha em uma página separada do banco de dados.
Definir um
FILLFACTORde50ou menos pode ajudar com isso, pois incentiva o PostgreSQl a manter mais espaço livre para atualizações, ao custo de varreduras que precisam ler e processar mais dados.