Início / dba / Perguntas / 81456
Accepted
Exelian
Asked: 2014-10-31 01:28:02 +0800 CST

Varreduras de índice lentas em uma tabela grande

  • 772

Atualização 2020-08-04:

Como essa resposta aparentemente ainda está sendo vista regularmente, gostaria de fornecer uma atualização sobre a situação. No momento, estamos usando o PG 11 com particionamento de tabela ativado timestampe estamos lidando facilmente com alguns bilhões de linhas na(s) tabela(s). As varreduras somente de índice são um salva-vidas e não seria possível sem.

Usando o PostgreSQL 9.2, tenho problemas com consultas lentas em uma tabela relativamente grande (mais de 200 milhões de linhas). Não estou tentando nada maluco, apenas adicionando valores históricos. Abaixo está a consulta e a saída do plano de consulta.

Meu layout de mesa:

                                   Table "public.energy_energyentry"
  Column   |           Type           |                            Modifiers
-----------+--------------------------+-----------------------------------------------------------------
 id        | integer                  | not null default nextval('energy_energyentry_id_seq'::regclass)
 prop_id   | integer                  | not null
 timestamp | timestamp with time zone | not null
 value     | double precision         | not null
Indexes:
    "energy_energyentry_pkey" PRIMARY KEY, btree (id)
    "energy_energyentry_prop_id" btree (prop_id)
    "energy_energyentry_prop_id_timestamp_idx" btree (prop_id, "timestamp")
Foreign-key constraints:
    "energy_energyentry_prop_id_fkey" FOREIGN KEY (prop_id) REFERENCES gateway_peripheralproperty(id) DEFERRABLE INITIALLY DEFERRED

Os dados variam de 2012-01-01 até agora, com novos dados sendo adicionados constantemente. Existem cerca de 2,2k valores distintos na prop_idchave estrangeira, distribuídos uniformemente.

Percebo que as estimativas de linha não estão muito distantes, mas as estimativas de custo parecem maiores por fator 4x. Isso provavelmente não é um problema, mas há algo que eu possa fazer sobre isso?

Espero que o acesso ao disco possa ser o problema, já que a tabela não está na memória o tempo todo.

EXPLAIN ANALYZE 
SELECT SUM("value") 
FROM "energy_energyentry" 
WHERE 
  "prop_id"=82411 
  AND "timestamp">'2014-06-11' 
  AND "timestamp"<'2014-11-11'
;

 Aggregate  (cost=214481.45..214481.46 rows=1 width=8) (actual time=51504.814..51504.814 rows=1 loops=1)
   ->  Index Scan using energy_energyentry_prop_id_timestamp_idx on  energy_energyentry (cost=0.00..214434.08 rows=18947 width=8) (actual time=136.030..51488.321 rows=13578 loops=1)
         Index Cond: ((prop_id = 82411) AND ("timestamp" > '2014-06-11 00:00:00+00'::timestamp with time zone) AND ("timestamp" < '2014-11-11 00:00:00+00'::timestamp with time zone))
 Total runtime: 51504.841 ms

Alguma sugestão de como tornar isso mais rápido?
Também estou bem em apenas ouvir que não fiz nada de estranho.

postgresql index

2 respostas

  1. Best Answer

    Sua tabela é grande , assim como qualquer índice que abranja toda a tabela. Assumindo que:

    • apenas novos dados (com timestamp = now()) são inseridos
    • as linhas existentes não são alteradas nem excluídas.
    • você tem dados desde 2012-01-01, mas as consultas são predominantemente no ano atual (?)

    Eu sugeriria um índice parcial de várias colunas (cobrindo!) :

    CREATE INDEX ON energy_energyentry (prop_id, "timestamp", value)
WHERE "timestamp" >= '2014-01-01 0:0';  -- adapt to your needs

    No Postgres 11 ou posterior, use um índice de "cobertura":

    CREATE INDEX ON energy_energyentry (prop_id, "timestamp") INCLUDE (value)
WHERE "timestamp" >= '2014-01-01 0:0';

    Ver:

    Inclua apenas o intervalo de tempo que é consultado regularmente. Só faz sentido se você cortar a maior parte da mesa grande dessa maneira. A eficácia se deteriora ao longo do tempo com novas entradas. Recrie o índice de tempos em tempos. (Talvez seja necessário adaptar suas consultas.) Veja a resposta vinculada abaixo.

    O último valor da coluna é incluído apenas para obter varreduras somente de índice . A configuração agressiva de autovacuum pode ajudar mantendo o mapa de visibilidade atualizado, como @jjanes já mencionado .

    O índice parcial deve caber na RAM mais facilmente e permanecer lá por mais tempo.

    Talvez seja necessário incluir essa WHEREcondição nas consultas para que o planejador entenda que o índice é aplicável à consulta, como:

    SELECT sum(value) AS sum_value
FROM   energy_energyentry
WHERE  prop_id = 82411 
AND   "timestamp" > '2014-06-11 0:0' 
AND   "timestamp" < '2014-11-11 0:0'
AND   "timestamp" >= '2014-01-01 0:0'; -- seems redundant, but may be needed

    Como sua consulta está somando muitas linhas ( rows=13578), isso levará algum tempo, mesmo com uma verificação somente de índice. Não deve ser em qualquer lugar perto de 50 segundos, no entanto. Menos de um segundo em qualquer hardware decente.

    Relacionado (mas ignore CLUSTERe FILLFACTOR, ambos são irrelevantes se você puder obter varreduras somente de índice disso) :

    Aparte:
    Como você atualmente tem um índice em (prop_id, "timestamp"), o índice adicional em apenas (prop_id)pode custar mais do que vale:

    • 16

  2. Se você fizer o índice em (prop_id, "timestamp", "value"), ele poderá usar uma varredura somente de índice para calcular o valor sem nunca visitar a tabela. Isso pode economizar muito acesso aleatório ao disco.

    Para obter o máximo benefício, você precisa ser agressivo ao aspirar a mesa. As configurações de autovac padrão não são agressivas o suficiente para tabelas somente de inserção nas quais você deseja oferecer suporte eficiente a varreduras somente de índice.

    • 5

relate perguntas