Início / dba / Perguntas / 124227
Accepted
uldall
Asked: 2015-12-22 03:12:00 +0800 CST

SOMA da coluna cada vez maior

  • 772

Tenho uma tabela de "transações" onde cada transação possui um valor: http://sqlfiddle.com/#!15/42849/1

Os registros na tabela nunca são REMOVE'ed ou UPDATE'ed. Apenas novas transações são adicionadas.

Desejo calcular a SOMA dos valores. O cálculo não precisa estar 100% atualizado para cada solicitação.

Em um conjunto de dados de cerca de um milhão de linhas, isso leva cerca de 400 ms no meu banco de dados. Isso é muito lento para o meu aplicativo e estou tentando encontrar a melhor solução para acelerar isso.

O que eu tentei até agora

  1. Visualização materializada: Adiciona a complexidade de ter um cronjob em execução que atualiza a visualização a cada X segundos.
  2. Cache no servidor de aplicativos: Cada solicitação X será lenta quando o cache precisar de uma atualização.
  3. Armazenando resultados de consultas em um subconjunto antigo: armazene a SOMA da solicitação anterior e use-a para calcular o total correto. Adiciona complexidade.

Pergunta

O PostgreSQL fornece uma solução para acelerar esse tipo de consulta?

Atualização 1

A consulta SUM é apenas uma soma básica em uma única coluna, então não acredito que essa consulta em si possa ficar mais rápida. A solução provavelmente é fazer algum tipo de cache/pré-cálculo ou similar. O PostgreSQL possui algum recurso nesse sentido?

Atualização 2

Tabela em questão:

CREATE TABLE transactions
(
  id bigserial NOT NULL,
  amount bigint NOT NULL
);

Consulta em questão:

SELECT SUM(amount) FROM transactions;

Atualização 3

Descobri que também preciso de um "tipo".

Tabela atualizada:

CREATE TABLE transactions
(
  id bigserial NOT NULL,
  amount bigint NOT NULL,
  type int NOT NULL
);

Consulta atualizada:

SELECT SUM(amount) FROM transactions GROUP BY type;

SQL Fiddle: http://sqlfiddle.com/#!15/77e67/2

postgresql

3 respostas

  1. Best Answer

    Aqui está uma ideia que você pode avaliar:

    CREATE TABLE last_transaction
(    last_id bigserial NOT NULL
,    cumulative_amount bigint NOT NULL
);  

INSERT INTO last_transaction (last_id, cumulative_amount) VALUES (-1,0);

    O valor atual deve ser algo como:

    SELECT coalesce(SUM(t.amount),0) + coalesce(lt.cumulative_amount,0) 
FROM transactions t
RIGHT JOIN last_transaction lt
    ON t.id > lt.last_id
GROUP BY lt.cumulative_amount;

    Regularmente, você pode atualizar last_transaction de forma semelhante a:

    update last_transaction
    set last_id = (select max(id) from transactions)
      , cumulative_amount = (select sum(amount) from transactions);

    A versão do PostgreSQL em seu violino não suporta (talvez nenhuma versão suporte?)

    set (last_id, cumulative_amount) = (select ...)

    Apenas uma ideia, que pode ou não atender às suas necessidades.

    Editar: tipo adicionado

    Se um tipo deve ser incluído (considere nomeá-lo como transaction_type ou algo semelhante), podemos estender last_transaction:

    CREATE TABLE last_transaction
(    type int not null
,    last_id bigserial NOT NULL
,    cumulative_amount bigint NOT NULL
,        constraint pk_last_transaction primary key (type)
);  

INSERT INTO last_transaction (type, last_id, cumulative_amount) 
SELECT distinct type, -1, 0
FROM transactions;

    Para obter o current_amount, precisamos adicionar type à GROUP BYcláusula, bem como à ONcláusula.

    SELECT lt.type
     , coalesce(SUM(t.amount),0) + coalesce(lt.cumulative_amount,0) 
FROM transactions t
RIGHT JOIN last_transaction lt
    ON t.id > lt.last_id
   AND t.type = lt.type
GROUP BY lt.type, lt.cumulative_amount;

    Para fazer uma atualização completa (de acordo com a sugestão de @Andriy M) de last_transaction:

    UPDATE last_transaction AS lt
    SET last_id = t.last_id
      , cumulative_amount = t.cumulative_amount
FROM (
    SELECT TYPE
         , MAX(id)
         , SUM(amount)
    FROM transactions
    GROUP BY TYPE
) AS t (type, last_id, cumulative_amount)
WHERE t.type = lt.type;

    Ainda não examinei a sugestão do @YperSillyCubeᵀᴹ.

    Adicionei cerca de um milhão de linhas à tabela de transações e o que acredito serem índices relevantes, mas o plano no sqlfiddle parece meio decepcionante.

    • 2

  2. Se houver poucos tipos e as linhas forem distribuídas uniformemente entre os tipos, é provável que uma nova linha esteja na mesma página que a linha anterior de seu tipo. Portanto, ler a linha anterior seria rápido. Isso pode ser (quase) garantido com clustering.

    Adicione uma nova coluna à tabela para conter o total corrente. À medida que uma linha é gravada, leia a linha correspondente anterior para obter seu total acumulado, calcule o total acumulado para a nova linha e grave-o.

    No entanto, isso pode acabar serializando toda a sua carga de trabalho, o que pode ser indesejável.

    • 1

  3. Você pode adicionar outra tabela apenas para armazenar os totais. Teria duas colunas - type e total_value. À medida que uma transação é inserida, o total corrente é atualizado, seja no código do aplicativo ou por um gatilho. Em taxas de transação mais altas, essa tabela rapidamente se torna um gargalo para maior rendimento. Algum alívio pode ser obtido ajustando o fator de preenchimento para que haja apenas um valor por página. Isso só irá até certo ponto.

    Como você pode tolerar alguma desatualização, o ponto de acesso pode ser evitado por atualizações em lote. Digamos que você possa tolerar 1 minuto de atraso entre uma transação e o total exibido. A cada 30 segundos, leia o ID mais alto e o valor total da transação. Cada ciclo registra o id mais alto para que cada transação seja processada apenas uma vez. Um pouco assim:

    update running_total
  .. 
select max(id), sum(value)
where id > last_id
group by type

    Para evitar contenção com gravações de transações em andamento, você pode ter

    where id > last_id
and id < {highest id in table} - X

    Onde X é grande o suficiente para garantir que essa agregação em segundo plano não esteja lendo da mesma página de dados em que as transações estão gravando ativamente, cerca de duas páginas, eu acho.

    • 0

relate perguntas