Início / dba / Perguntas / 234521
Accepted
Mikko Rantalainen
Asked: 2019-04-11 23:07:09 +0800 CST

Como indexar/otimizar a soma de duas colunas no PostgreSQL?

  • 772

Temos um banco de dados onde uma tabela contém dados temporários serializados que precisam ser mantidos por vários momentos (geralmente entre dezenas de minutos e duas semanas). Também temos um processo em segundo plano de baixa prioridade que remove as linhas antigas da tabela. O processo em segundo plano remove até 1.000 linhas durante uma transação:

delete from temporarydata
    where id in (
        select id from temporarydata
            where (created + ttl) <= 1553755330 limit 1000
    )

O 1553755330no exemplo é o número atual de segundos desde a época do UNIX e createdcontém segundos desde a época do UNIX em que os dados foram adicionados e ttlcontém o número de segundos em que os dados devem ser mantidos ativos.

Tecnicamente, isso funciona, mas existem cerca de 2 milhões de linhas nos dados temporários e a subseleção fica muito lenta porque a soma exige uma varredura sequencial na tabela para encontrar todas as linhas correspondentes. Isso causa uma carga extra em segundo plano no banco de dados.

> explain (analyze,verbose,timing,buffers) select id from temporarydata
        where (created + ttl) <= 1553755330 limit 1000

Limit  (cost=0.00..402.34 rows=1000 width=16) (actual time=6735.811..6735.811 rows=0 loops=1)
  Output: id
  Buffers: shared hit=3068 read=230500
  ->  Seq Scan on public.temporarydata  (cost=0.00..262980.99 rows=653622 width=16) (actual time=6735.809..6735.809 rows=0 loops=1)
        Output: id
        Filter: ((temporarydata.created + temporarydata.ttl) <= 1553755330)
        Rows Removed by Filter: 1916405
        Buffers: shared hit=3068 read=230500
Planning time: 0.402 ms
Execution time: 6735.849 ms

Eu prefiro apenas adicionar um novo índice que sempre pode conter a soma do created + ttlque o PostgreSQL foi capaz de usar para esta consulta automaticamente. Isso é possível com alto desempenho?

(Estou pensando em reescrever o código do aplicativo para salvar createde expiresem vez de ttlwhere expiresis created+ ttl. Então eu calculo lógica ttlcomo diferença desses valores. Acho que o aplicativo não emite consultas pesadas ttlsozinho.)

postgresql index

2 respostas

  1. Se você usou o mesmo TTL para todos os registros, pode evitar a indexação funcional simplesmente movendo o TTL para o lado direito da comparação (obrigado jjanespela correção):

    DELETE
  FROM temporarydata
 WHERE id IN ( SELECT id 
                 FROM temporarydata
                WHERE created <= 1553755330 - ttl
                LIMIT 1000
             )

    O Optimizer calculará a diferença apenas uma vez e a usará na createdcoluna indexada.

    Se você precisar de um TTL diferente para registros diferentes, poderá armazenar não o created -- ttlpar, mas created -- expiresum pré-calculado

    E você pode usar a indexação funcional:

    CREATE INDEX expires ON temporarydata ((created+ttl));
    • 1
  2. Best Answer

    Acho que refatorar a tabela para armazenar a expiração seria uma boa ideia. Se você não quiser fazer isso, poderá criar uma expressão index on temporarydata ((created + ttl)).

    No entanto, pode ser necessário algum incentivo para que ele use esse índice, pois o sistema de estatísticas pode não fornecer estimativas suficientemente boas para isso naturalmente. Adicionar um ORDER BY à sua subseleção deve fornecer este incentivo:

    where (created + ttl) <= 1553755330 order by (created + ttl) limit 1000

    (Além disso, parece fazer sentido que você queira excluir primeiro o mais atrasado. Na verdade, não sei por que você quer o LIMIT.)

    • 1

relate perguntas