Início / dba / Perguntas / 313403
Accepted
Joe
Asked: 2022-06-16 07:26:42 +0800 CST

UPDATE FROM com uma tabela grande é lento e usa Seq Scans

  • 772

Eu tenho uma tabela grande (talvez talvez um bilhão de linhas, mas atualmente ~ 26 milhões) para a qual quero definir um sinalizador no PK mais alto para um determinado agrupamento, em um lote único.

Optei por criar uma tabela temporária que armazena os PKs que devem ser configurados current=true, o restante deve ser configurado current=false. Fiz uma tabela temporária em vez de uma visão materializada, mas acho que não faria diferença real.

O processo de descobrir o ID máximo para cada um não é muito doloroso:

CREATE TABLE assertion (
    pk integer NOT NULL,
    a bigint NOT NULL,
    b bigint NOT NULL,
    c bigint NOT NULL,
    d integer NOT NULL,
    current boolean DEFAULT false NOT NULL
);

CREATE INDEX assertion_current_idx ON assertion USING btree (current) WHERE (current = true);
CREATE INDEX assertion_current_idx1 ON assertion USING btree (current);
CREATE UNIQUE INDEX assertion_a_b_c_d_idx ON assertion USING btree (a, b, c, d) WHERE (current = true);

SELECT COUNT(pk) FROM assertion;

-- 26916858
-- Time: 2912.403 ms (00:02.912)

CREATE TEMPORARY TABLE assertion_current AS
    (SELECT MAX(pk) as pk, a, b, c, d
      FROM assertion
      GROUP BY a, b, c, d);

-- Time: 72218.755 ms (01:12.219)

ANALYZE assertion_current;

CREATE INDEX ON assertion_current(pk);

-- Time: 22107.698 ms (00:22.108)

SELECT COUNT(pk) FROM assertion_current;

-- 26455092
-- Time: 15650.078 ms (00:15.650)

De acordo com a contagem de assertion_current, precisamos definir o sinalizador 'atual' como verdadeiro para 98% das linhas.

O complicado é como atualizar a assertiontabela em tempo razoável com base nos valores atuais. Existe uma restrição única na a, b, c, d, currentqual deve ser mantida, portanto, a atualização da currentcoluna precisa ser atômica para evitar a quebra da restrição.

Tenho algumas opções:

Opção 1

Atualize apenas os currentvalores que mudam. Isso tem a vantagem de atualizar o menor número de linhas necessário com base em um campo indexado:


BEGIN;
UPDATE assertion
   SET current = false
   WHERE assertion.current = true AND PK NOT IN (SELECT pk FROM assertion_current);
UPDATE assertion
   SET current = true
   WHERE assertion.current = false AND PK IN (SELECT pk FROM assertion_current);
COMMIT;

Mas ambas as consultas envolvem varreduras de sequência nas assertion_currentquais (eu acho) teriam que ser multiplicadas por um grande número de linhas.

Update on assertion  (cost=0.12..431141.55 rows=0 width=0)
   ->  Index Scan using assertion_current_idx on assertion  (cost=0.12..431141.55 rows=1 width=7)
         Index Cond: (current = true)
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  Materialize  (cost=0.00..787318.40 rows=29982560 width=4)
                 ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=4)

e

 Update on assertion  (cost=595242.56..596693.92 rows=0 width=0)
   ->  Nested Loop  (cost=595242.56..596693.92 rows=17974196 width=13)
         ->  HashAggregate  (cost=595242.00..595244.00 rows=200 width=10)
               Group Key: assertion_current.pk
               ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=10)
         ->  Index Scan using assertion_pkey on assertion  (cost=0.56..8.58 rows=1 width=10)
               Index Cond: (pk = assertion_current.pk)
               Filter: (NOT current)

Isso significa que uma dessas consultas (muitas atuais verdadeiras ou muitas atuais falsas) sempre leva muito tempo.

opção 2

Passe único, mas tem que tocar em todas as linhas desnecessariamente.

UPDATE assertion
   SET current =
     (CASE WHEN assertion.pk IN (select PK from assertion_current)
     THEN TRUE ELSE FALSE END)

mas isso resulta em uma varredura de sequência em assertion_current novamente

 Update on assertion  (cost=0.00..15498697380303.70 rows=0 width=0)
   ->  Seq Scan on assertion  (cost=0.00..15498697380303.70 rows=35948392 width=7)
         SubPlan 1
           ->  Materialize  (cost=0.00..787318.40 rows=29982560 width=4)
                 ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=4)

Opção 3

Como a opção 1, mas use WHEREna atualização:

BEGIN;
UPDATE assertion SET current = false WHERE current = true;
UPDATE assertion SET current = true FROM assertion_current
  WHERE assertion.pk = assertion_current.pk;
COMMIT;

mas a segunda consulta envolve duas varreduras seq:

 Update on assertion  (cost=1654256.82..2721576.65 rows=0 width=0)
   ->  Hash Join  (cost=1654256.82..2721576.65 rows=29982560 width=13)
         Hash Cond: (assertion_current.pk = assertion.pk)
         ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=10)
         ->  Hash  (cost=1029371.92..1029371.92 rows=35948392 width=10)
               ->  Seq Scan on assertion  (cost=0.00..1029371.92 rows=35948392 width=10)

Opção 4

Obrigado @jjanes, isso levou > 6 horas, então eu cancelei.

UPDATE assertion
   SET current = not current
   WHERE current <>
     (CASE WHEN assertion.pk IN (select PK from assertion_current)
     THEN TRUE ELSE FALSE END)

produz

 Update on assertion  (cost=0.00..11832617068493.14 rows=0 width=0)
   ->  Seq Scan on assertion  (cost=0.00..11832617068493.14 rows=27307890 width=7)
         Filter: (current <> CASE WHEN (SubPlan 1) THEN true ELSE false END)
         SubPlan 1
           ->  Materialize  (cost=0.00..787318.40 rows=29982560 width=4)
                 ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=4)

Opção 5

Obrigado @a_horse_with_no_name. Isso leva 24 minutos na minha máquina.

UPDATE assertion tg SET current = EXISTS (SELECT pk FROM assertion_current cr WHERE cr.pk = tg.pk);

dá

 Update on assertion tg  (cost=0.00..233024784.94 rows=0 width=0)
   ->  Seq Scan on assertion tg  (cost=0.00..233024784.94 rows=27445116 width=7)
         SubPlan 1
           ->  Index Only Scan using assertion_current_pk_idx on assertion_current cr  (cost=0.44..8.46 rows=1 width=0)
                 Index Cond: (pk = tg.pk)

Existe uma maneira melhor de conseguir isso em tempo hábil?

postgresql update

2 respostas

  1. Best Answer

    ... precisamos definir o sinalizador 'atual' como verdadeiro para 98% das linhas

    Assim NOT currentserá o caso raro. Parece que você tem tentado fazer as coisas de trás para frente até agora.

    Seus índices atuais são ativamente inúteis para a distribuição de dados fornecida:

    CREATE INDEX assertion_current_idx ON assertion USING btree (current) WHERE (current = true);  
CREATE INDEX assertion_current_idx1 ON assertion USING btree (current);  
CREATE UNIQUE INDEX assertion_a_b_c_d_idx ON assertion USING btree (a, b, c, d) WHERE (current = true);

    Precisamos manter o UNIQUEíndice para impor seus requisitos, e também é útil:

    CREATE UNIQUE INDEX assertion_a_b_c_d_idx ON assertion (a, b, c, d) WHERE current;

    Mas simplifique (current = true)para apenas current. Não adianta executar uma expressão redundante, apenas use o booleanvalor.

    assertion_current_idxé absolutamente inútil e nunca será usado. Mas ainda precisa ser mantido atualizado. Largue.

    assertion_current_idx1é quase tão inútil. Pelo menos faria sentido para consultas procurando por arquivos current = false. Mas é muito mais barato ter esse índice parcial - que também suporta minha segunda consulta sugerida abaixo:

    CREATE INDEX assertion_not_current_idx ON assertion (a, b, c, d, pk) WHERE NOT current;

    Crie este índice após a "consulta inicial" abaixo.

    Observe que eu pulo a tabela temporária completamente em favor de um CTE. Menos sobrecarga.

    Consulta inicial

    Você divulgou em um comentário posterior que "todas as linhas estão definidas current = false" inicialmente. Podemos usar uma consulta muito mais simples e rápida para init. Basta atualizar uma linha por grupo onde não há outra com um PK maior. Nenhuma violação única possível, nenhuma atualização para "não atual":

    UPDATE assertion a
SET    current = true
WHERE  NOT EXISTS (
   SELECT FROM assertion x
   WHERE (x.a, x.b, x.c, x.d)
       = (a.a, a.b, a.c, a.d)
   AND   x.pk > a.pk
   );

    Consulta geral

    Assumindo que não pode haver nenhuma linha atual por grupo.

    WITH new_current AS (  -- only these groups require updates
   SELECT *
   FROM  (
      -- get row with greatest non-current pk per group
      SELECT a, b, c, d, MAX(pk) AS new_pk
          , (SELECT a2.pk  -- get current row of the same group
             FROM   assertion a2
             WHERE (a2.a, a2.b, a2.c, a2.d)
                 = (a1.a, a1.b, a1.c, a1.d)
             AND    a2.current
            ) AS old_pk
      FROM   assertion a1
      WHERE  NOT current
      GROUP  BY a, b, c, d
      ) a1
   WHERE (old_pk < new_pk   -- only if old pk is lower (!)
       OR old_pk IS NULL)   -- or does not exist
   )
, up1(dummy) AS (  -- update current to false FIRST
   UPDATE assertion a
   SET    current = false
   FROM   new_current n
   WHERE  (a.a, a.b, a.c, a.d, a.pk)
        = (n.a, n.b, n.c, n.d, n.old_pk)  -- only matches existing old pk
   RETURNING true
   )
UPDATE assertion a  -- then update the new current row
SET    current = true
FROM   new_current n
LEFT   JOIN (SELECT FROM up1 LIMIT 1) AS force_update_order ON true  -- !!!
WHERE  (a.a, a.b, a.c, a.d, a.pk)
     = (n.a, n.b, n.c, n.d, n.new_pk);

    Deve superar qualquer outra coisa que você tentou até agora.

    A subconsulta a1obtém a linha com maior descontinuidade por grupo com uma agregação pksimples . max()Esse é o ideal enquanto houver poucas linhas candidatas ( NOT current) por grupo. Caso contrário, otimize ainda mais esta etapa com uma verificação de salto de índice emulado:

    A parte complicada é manter a UNIQUErestrição feliz . Ele não permite duas linhas atuais por grupo em um determinado momento. Não há ordem de execução entre CTEs de uma mesma consulta - desde que uma não faça referência à outra. Eu coloquei uma referência fictícia para forçar a ordem das atualizações .

    Quando ainda não havia nenhuma linha atual, o CTE up1não retorna nenhuma linha. Então usamos LEFT JOIN. E LIMIT 1nunca duplicar linhas.

    • 3

  2. Eu não acho que você deve tentar evitar cada varredura seq. Mas você não quer uma varredura seq que será executada um grande número de vezes (como em um subplano sem hash ou no segundo filho de um loop aninhado).

    Em seu primeiro plano, ele tem uma varredura seq em um subplano sem hash, mas afirma que isso será executado apenas uma vez, portanto, não seria tão ruim se isso fosse preciso. Mas isso parece contradizer sua descrição de "o sinalizador 'atual' é verdadeiro para 98% das linhas", então talvez as estatísticas estejam totalmente erradas.

    Você pode aumentar nosso work_mem até que o subplano mude para um subplano com hash, ou você pode reescrever sua consulta de NOT IN para NOT EXISTS.

    No caso da opção 2, basta adicionar um WHERE para eliminar as atualizações inoperantes:

    UPDATE assertion
   SET current = not current
   WHERE current <>  
     (CASE WHEN assertion.pk IN (select PK from assertion_current)
     THEN TRUE ELSE FALSE END)

    Mas, novamente, usar EXISTS em vez de IN provavelmente será melhor.

    • 1

relate perguntas