Eu tenho uma tabela Postgres com ~ 2,1 milhões de linhas. Eu executei a atualização abaixo nele:
WITH stops AS (
SELECT id,
rank() OVER (ORDER BY offense_timestamp,
defendant_dl,
offense_street_number,
offense_street_name) AS stop
FROM consistent.master
WHERE citing_jurisdiction=1
)
UPDATE consistent.master
SET arrest_id=stops.stop
FROM stops
WHERE master.id = stops.id;
Essa consulta levou 39 horas para ser executada. Estou executando isso em um processador de laptop i7 Q720 de 4 núcleos (físicos), muita RAM, nada mais funcionando na grande maioria do tempo. Sem restrições de espaço no disco rígido. A tabela havia sido recentemente aspirada, analisada e reindexada.
Durante todo o tempo em que a consulta estava em execução, pelo menos após a WITHconclusão inicial, o uso da CPU geralmente era baixo e o HDD estava em uso 100%. O HDD estava sendo usado com tanta força que qualquer outro aplicativo rodava consideravelmente mais devagar do que o normal.
A configuração de energia do laptop estava em Alto desempenho (Windows 7 x64).
Segue a EXPLICAÇÃO:
Update on master (cost=822243.22..1021456.89 rows=2060910 width=312)
CTE stops
-> WindowAgg (cost=529826.95..581349.70 rows=2060910 width=33)
-> Sort (cost=529826.95..534979.23 rows=2060910 width=33)
Sort Key: consistent.master.offense_timestamp, consistent.master.defendant_dl, consistent.master.offense_street_number, consistent.master.offense_street_name
-> Seq Scan on master (cost=0.00..144630.06 rows=2060910 width=33)
Filter: (citing_jurisdiction = 1)
-> Hash Join (cost=240893.51..440107.19 rows=2060910 width=312)
Hash Cond: (stops.id = consistent.master.id)
-> CTE Scan on stops (cost=0.00..41218.20 rows=2060910 width=48)
-> Hash (cost=139413.45..139413.45 rows=2086645 width=268)
-> Seq Scan on master (cost=0.00..139413.45 rows=2086645 width=268)
citing_jurisdiction=1exclui apenas algumas dezenas de milhares de linhas. Mesmo com essa WHEREcláusula, ainda estou operando em mais de 2 milhões de linhas.
O disco rígido é todo criptografado com TrueCrypt 7.1a. Isso atrasa um pouco as coisas, mas não o suficiente para fazer com que uma consulta demore tantas horas.
A WITHparte leva apenas cerca de 3 minutos para ser executada.
O arrest_idcampo não tinha índice para chave estrangeira. Existem 8 índices e 2 chaves estrangeiras nesta tabela. Todos os outros campos da consulta são indexados.
O arrest_idcampo não tinha restrições, exceto NOT NULL.
A tabela tem um total de 32 colunas.
arrest_idé do tipo caractere variando(20) . Percebo que rank()produz um valor numérico, mas tenho que usar o caractere variando (20) porque tenho outras linhas em citing_jurisdiction<>1que usam dados não numéricos para esse campo.
O arrest_idcampo estava em branco para todas as linhas com citing_jurisdiction=1.
Este é um laptop pessoal de ponta (a partir de 1 ano atrás). Eu sou o único usuário. Nenhuma outra consulta ou operação estava em execução. Bloquear parece improvável.
Não há gatilhos em nenhum lugar desta tabela ou em qualquer outro lugar do banco de dados.
Outras operações neste banco de dados nunca levam muito tempo. Com a indexação adequada, SELECTas consultas geralmente são bastante rápidas.
Eu tive algo semelhante acontecendo recentemente com uma tabela de 3,5 milhões de linhas. Minha atualização nunca terminaria. Depois de muita experimentação e frustração, finalmente encontrei o culpado. Acabou sendo os índices na tabela sendo atualizados.
A solução foi descartar todos os índices na tabela sendo atualizada antes de executar a instrução de atualização. Depois de fazer isso, a atualização terminou em alguns minutos. Depois que a atualização foi concluída, recriei os índices e voltei aos negócios. Isso provavelmente não vai ajudá-lo neste momento, mas pode alguém procurar respostas.
Eu manteria os índices na tabela da qual você está extraindo os dados. Esse não precisará continuar atualizando nenhum índice e deve ajudar a encontrar os dados que você deseja atualizar. Funcionou bem em um laptop lento.
Seu maior problema é fazer grandes quantidades de trabalho pesado de gravação e busca em um disco rígido de laptop. Isso nunca será rápido, não importa o que você faça, especialmente se for o tipo de unidade mais lenta de 5400 RPM fornecida em muitos laptops.
TrueCrypt retarda as coisas mais do que "um pouco" para gravações. As leituras serão razoavelmente rápidas, mas as gravações fazem o RAID 5 parecer rápido. A execução de um banco de dados em um volume TrueCrypt será uma tortura para gravações, especialmente gravações aleatórias.
Nesse caso, acho que você estaria desperdiçando seu tempo tentando otimizar a consulta. Você está reescrevendo a maioria das linhas de qualquer maneira, e vai ser lento com sua situação de escrita horrível. O que eu recomendo é:
Suspeito que será mais rápido do que apenas descartar e recriar as restrições sozinhas, porque um UPDATE terá padrões de gravação bastante aleatórios que matarão seu armazenamento. Duas inserções em massa, uma em uma tabela não registrada e outra em uma tabela registrada no WAL sem restrições, provavelmente serão mais rápidas.
Se você tem backups absolutamente atualizados e não se importa em restaurar seu banco de dados a partir de backups , você também pode reiniciar o PostgreSQL com o
fsync=offparâmetro efull_page_writes=offtemporariamente para esta operação em massa. Qualquer problema inesperado, como perda de energia ou falha do sistema operacional, deixará seu banco de dados irrecuperável enquanto ofsync=off.O POSTGreSQL equivalente a "sem registro" é usar tabelas não registradas. Essas tabelas não registradas serão truncadas se o banco de dados for desligado de forma imprópria enquanto estiverem sujos. O uso de tabelas não registradas reduzirá pela metade sua carga de gravação e reduzirá o número de buscas, para que elas possam ser MUITO mais rápidas.
Como no Oracle, pode ser uma boa ideia descartar um índice e recriá-lo após uma grande atualização em lote. O planejador do PostgreSQL não consegue descobrir que uma grande atualização está ocorrendo, pausa as atualizações do índice e, em seguida, recria o índice no final; mesmo que pudesse, seria muito difícil descobrir em que ponto valeria a pena fazer isso, especialmente com antecedência.
Alguém dará uma resposta melhor para o Postgres, mas aqui estão algumas observações da perspectiva Oracle que podem ser aplicadas (e os comentários são muito longos para o campo de comentários).
Minha primeira preocupação seria tentar atualizar 2 milhões de linhas em uma transação. No Oracle, você escreveria uma imagem anterior de cada bloco que está sendo atualizado para que outra sessão ainda tenha uma leitura consistente sem ler seus blocos modificados e você tenha a capacidade de reverter. Essa é uma longa reversão sendo construída. Geralmente, é melhor fazer as transações em pequenos pedaços. Digamos 1.000 registros por vez.
Se você tiver índices na tabela e a tabela for considerada fora de operação durante a manutenção, geralmente é melhor remover os índices antes de uma grande operação e recriá-los novamente depois. Mais barato do que tentar constantemente manter os índices a cada registro atualizado.
O Oracle permite dicas "sem registro" em instruções para interromper o journalling. Ele acelera muito as declarações, mas deixa seu banco de dados em uma situação "irrecuperável". Portanto, você deseja fazer backup antes e fazer backup novamente imediatamente depois. Não sei se o Postgres tem opções semelhantes.