Aren Cambre's questions

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?

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.