Início / user-35551

uldall's questions

Martin Hope
uldall
Asked: 2015-12-22 03:12:00 +0800 CST
  • 5

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
Martin Hope
uldall
Asked: 2014-03-13 06:10:30 +0800 CST
  • 15

Estou tentando determinar quais índices usar para uma consulta SQL com uma WHEREcondição e uma GROUP BYque está sendo executada muito lentamente.

Minha consulta:

SELECT group_id
FROM counter
WHERE ts between timestamp '2014-03-02 00:00:00.0' and timestamp '2014-03-05 12:00:00.0'
GROUP BY group_id

A tabela atualmente possui 32.000.000 linhas. O tempo de execução da query aumenta muito quando eu aumento o time-frame.

A tabela em questão fica assim:

CREATE TABLE counter (
    id bigserial PRIMARY KEY
  , ts timestamp NOT NULL
  , group_id bigint NOT NULL
);

Atualmente tenho os seguintes índices, mas o desempenho ainda é lento:

CREATE INDEX ts_index
  ON counter
  USING btree
  (ts);

CREATE INDEX group_id_index
  ON counter
  USING btree
  (group_id);

CREATE INDEX comp_1_index
  ON counter
  USING btree
  (ts, group_id);

CREATE INDEX comp_2_index
  ON counter
  USING btree
  (group_id, ts);

A execução de EXPLAIN na consulta fornece o seguinte resultado:

"QUERY PLAN"
"HashAggregate  (cost=467958.16..467958.17 rows=1 width=4)"
"  ->  Index Scan using ts_index on counter  (cost=0.56..467470.93 rows=194892 width=4)"
"        Index Cond: ((ts >= '2014-02-26 00:00:00'::timestamp without time zone) AND (ts <= '2014-02-27 23:59:00'::timestamp without time zone))"

SQL Fiddle com dados de exemplo: http://sqlfiddle.com/#!15/7492b/1

A questão

O desempenho desta consulta pode ser melhorado adicionando índices melhores ou devo aumentar o poder de processamento?

Editar 1

PostgreSQL versão 9.3.2 é usado.

Editar 2

Eu tentei a proposta de @Erwin com EXISTS:

SELECT group_id
FROM   groups g
WHERE  EXISTS (
   SELECT 1
   FROM   counter c
   WHERE  c.group_id = g.group_id
   AND    ts BETWEEN timestamp '2014-03-02 00:00:00'
                 AND timestamp '2014-03-05 12:00:00'
   );

Mas infelizmente isso não pareceu aumentar o desempenho. O plano de consulta:

"QUERY PLAN"
"Nested Loop Semi Join  (cost=1607.18..371680.60 rows=113 width=4)"
"  ->  Seq Scan on groups g  (cost=0.00..2.33 rows=133 width=4)"
"  ->  Bitmap Heap Scan on counter c  (cost=1607.18..158895.53 rows=60641 width=4)"
"        Recheck Cond: ((group_id = g.id) AND (ts >= '2014-01-01 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"
"        ->  Bitmap Index Scan on comp_2_index  (cost=0.00..1592.02 rows=60641 width=0)"
"              Index Cond: ((group_id = g.id) AND (ts >= '2014-01-01 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"

Editar 3

O plano de consulta para a consulta LATERAL do ypercube:

"QUERY PLAN"
"Nested Loop  (cost=8.98..1200.42 rows=133 width=20)"
"  ->  Seq Scan on groups g  (cost=0.00..2.33 rows=133 width=4)"
"  ->  Result  (cost=8.98..8.99 rows=1 width=0)"
"        One-Time Filter: ($1 IS NOT NULL)"
"        InitPlan 1 (returns $1)"
"          ->  Limit  (cost=0.56..4.49 rows=1 width=8)"
"                ->  Index Only Scan using comp_2_index on counter c  (cost=0.56..1098691.21 rows=279808 width=8)"
"                      Index Cond: ((group_id = $0) AND (ts IS NOT NULL) AND (ts >= '2010-03-02 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"
"        InitPlan 2 (returns $2)"
"          ->  Limit  (cost=0.56..4.49 rows=1 width=8)"
"                ->  Index Only Scan Backward using comp_2_index on counter c_1  (cost=0.56..1098691.21 rows=279808 width=8)"
"                      Index Cond: ((group_id = $0) AND (ts IS NOT NULL) AND (ts >= '2010-03-02 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"
postgresql index