Início / dba / Perguntas / 334004
Accepted
Justin Lowen
Asked: 2023-12-13 06:40:43 +0800 CST

PostgreSQL - atualizações de 30.000 linhas, definir coluna booleana não indexada como 'true' leva horas

  • 772

Estou executando o PostgreSQL 13, e uma única instrução UPDATE que parece trivial para ser concluída pode levar horas (pelo menos em um caso, mais de 24 horas).

A seleção das linhas com uma instrução SELECT leva menos de um segundo. A atualização das linhas é drasticamente mais lenta, com apenas 30.000 linhas afetadas.

A coluna em questão que estou tentando atualizar não está indexada. O banco de dados também está bem provisionado para a escala do banco de dados. Enquanto a consulta está ativa, posso ver 1 dos 4 vCPU em plena aceleração. As configurações de memória são ajustadas aos padrões típicos recomendados para um sistema de 32 GB de RAM.

Após a primeira execução, posso alternar a coluna booleana arquivada para frente e para trás com muito mais rapidez (5 minutos na primeira ATUALIZAÇÃO -> 15 segundos para fazer o inverso no meu caso simulado).

Existem algumas propriedades que tornam esta tabela única. O esquema do banco de dados é altamente normalizado. Esta tabela contém os nomes e uuids de muitos dos modelos de dados, portanto, há muitos relacionamentos FK. A tabela possui 16 colunas, com 10 índices e 4 restrições. O valor da coluna atualizado não faz parte de uma restrição ou índice.

Por que o desempenho desta consulta de atualização é tão ruim? Há algo que eu possa fazer para melhorar o desempenho?

Plano de consulta

postgresql

2 respostas

  1. Best Answer

    A declaração poderia ser reescrita para a forma mais simples

    UPDATE resource AS r
SET archived = TRUE
FROM sample_sheet AS ss
WHERE r.resource_id = ss.sample_sheet_id
  AND r.created_timestamp < current_timestamp - INTERVAL '120 00:00:00'
  AND ss.state IN ('done', 'failed');

    Mas isso não ajudará no seu desempenho. Todo o tempo é gasto na atualização real. Além de um disco muito, muito lento, as possíveis causas podem ser

    • bloqueios que bloqueiam a execução da atualização

    • muitos índices excessivos na tabela (e talvez índices lentos, como índices GIN)

    • um gatilho pode ser descartado, porque deve aparecer na EXPLAIN (ANALYZE)saída

    Eu definiria track_io_timing = one tentaria novamente a consulta EXPLAIN (ANALYZE, BUFFERS, WAL)para obter informações mais detalhadas.

    • 1

  2. Depois de conseguir reproduzir o problema com várias consultas semelhantes, parece que certas formas da consulta UPDATE podem resultar em um nó Materialize muito caro que faz um loop para juntar as linhas e colunas da tabela atualizada, bem como a tupla ctidpara as outras tabelas referenciadas.

    https://explain.dalibo.com/plan/08729h152df894fd

    EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
UPDATE resource
  SET archived = true
  FROM resource r_ss
  JOIN sample_sheet ss on r_ss.resource_id = ss.sample_sheet_id
  WHERE r_ss.created_timestamp < (now() - INTERVAL '120 DAY') AND ss.state in ('done', 'failed');

    Uma varredura sequencial é feita nas 333 linhas em sample_sheet. Eles são unidos 1:1 com uma varredura de índice para a tabela de recursos com o alias de r_ss (as entradas de recursos para essas planilhas). O resultado unido também carrega o ctid. Este resultado (restringido por filtros e pesquisas de índice) é então materializado (55.000.000 linhas) e unido novamente ao recurso de tabela atualizado (sem alias) em um loop. A própria tabela de recursos possui 200.000 linhas neste exemplo. Neste caso, a operação atualizou 277 linhas em 2 minutos.

    Esta forma de consulta não resulta em operações caras de materialização: https://explain.dalibo.com/plan/bf5377ed6d4dca95

    EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
UPDATE resource AS r_ss
  SET archived = true
  FROM sample_sheet ss
  WHERE 
  r_ss.resource_id = ss.sample_sheet_id
  AND r_ss.created_timestamp < (now() - INTERVAL '120 DAY') 
  AND ss.state in ('done', 'failed');

    Verificado que não houve bloqueio de bloqueios, que o vácuo teve impacto mínimo e que as atualizações HOT tiveram impacto mínimo. Vale a pena notar que a atualização de todas as 200.000 linhas sem qualquer cláusula WHERE levou cerca de 8 segundos com fator de preenchimento de 100% (apenas cerca de 10% das atualizações de tupla foram atualizações HOT). Com fator de preenchimento de 50% para a tabela, a atualização completa levou cerca de 2 segundos, com todas as atualizações sendo QUENTES. O nó materializado resultante da junção desnecessária da resourcetabela atualizada e do produto resource r_ss x sample_sheet ssparecia ser a causa raiz.

    • 1

relate perguntas