Início / dba / Perguntas / 189190
Accepted
Parker
Asked: 2017-10-25 04:37:25 +0800 CST

Desempenho de consulta ruim no intervalo de timestamp por semana no PostgreSQL 9.3

  • 772

Eu tenho uma consulta lenta que gera um relatório de atividade da conta por semana no ano passado. Atualmente, a tabela tem quase 5 milhões de linhas e essa consulta leva 8 segundos para ser executada. O gargalo (atual) é a varredura sequencial no intervalo de carimbo de data/hora.

account=> EXPLAIN ANALYZE SELECT to_timestamp(to_char(date_trunc('week', event_time), 'IYYY-IW'), 'IYYY-IW')::date AS date, count(DISTINCT account) FROM account_history WHERE event_time BETWEEN now() - interval '51 weeks' AND now() GROUP BY date ORDER BY date;

 GroupAggregate  (cost=450475.76..513465.44 rows=2290534 width=12) (actual time=7524.474..8003.291 rows=52 loops=1)
   Group Key: ((to_timestamp(to_char(date_trunc('week'::text, event_time), 'IYYY-IW'::text), 'IYYY-IW'::text))::date)
   ->  Sort  (cost=450475.76..456202.09 rows=2290534 width=12) (actual time=7519.053..7691.924 rows=2314164 loops=1)
         Sort Key: ((to_timestamp(to_char(date_trunc('week'::text, event_time), 'IYYY-IW'::text), 'IYYY-IW'::text))::date)
         Sort Method: external sort  Disk: 40704kB
         ->  Seq Scan on account_history  (cost=0.00..169364.81 rows=2290534 width=12) (actual time=1470.438..6222.076 rows=2314164 loops=1)
               Filter: ((event_time <= now()) AND (event_time >= (now() - '357 days'::interval)))
               Rows Removed by Filter: 2591679
 Planning time: 0.126 ms
 Execution time: 8011.160 ms

A mesa:

account=> \d account_history
                    Table "public.account_history"
   Column    |            Type             |         Modifiers
-------------+-----------------------------+---------------------------
 account     | integer                     | not null
 event_code  | text                        | not null
 event_time  | timestamp without time zone | not null default now()
 description | text                        | not null default ''::text
Indexes:
    "account_history_idx" btree (account, event_time DESC)
    "account_id_idx" btree (account, event_code, event_time)
Foreign-key constraints:
    "account_fk" FOREIGN KEY (account) REFERENCES account(id) ON UPDATE CASCADE ON DELETE RESTRICT
    "event_code_fk" FOREIGN KEY (event_code) REFERENCES domain_account_event(code) ON UPDATE CASCADE ON DELETE RESTRICT

Quando criei originalmente esta tabela, adicionei a coluna timestamp como parte de um índice btree, mas imaginei que a varredura sequencial era devido ao (então) pequeno número de linhas na tabela (consulte a pergunta relacionada ).

No entanto, agora que a tabela cresceu para milhões, notei um problema de desempenho com a consulta e descobri que o índice não está sendo usado na consulta.

Eu tentei adicionar um índice ordenado conforme recomendado aqui , mas isso claramente também não está sendo usado no plano de execução.

Existe uma maneira melhor de indexar essa tabela ou há algo inerente à minha consulta que está ignorando esses dois índices?

Atualização: quando adiciono um índice apenas no carimbo de data/hora, esse índice é usado. No entanto, apenas reduziu o tempo de execução em 25%:

account=> CREATE INDEX account_history_time_idx ON account_history (event_time DESC);

account=> EXPLAIN ANALYZE VERBOSE SELECT to_timestamp(to_char(date_trunc('week', event_time), 'IYYY-IW'), 'IYYY-IW')::date AS date, count(DISTINCT account) FROM account_history WHERE event_time BETWEEN now() - interval '51 weeks' AND now() GROUP BY date ORDER BY date;

 GroupAggregate  (cost=391870.30..454870.16 rows=2290904 width=12) (actual time=5481.930..6104.838 rows=52 loops=1)
   Output: ((to_timestamp(to_char(date_trunc('week'::text, event_time), 'IYYY-IW'::text), 'IYYY-IW'::text))::date), count(DISTINCT account)
   Group Key: ((to_timestamp(to_char(date_trunc('week'::text, account_history.event_time), 'IYYY-IW'::text), 'IYYY-IW'::text))::date)
   ->  Sort  (cost=391870.30..397597.56 rows=2290904 width=12) (actual time=5474.181..5771.903 rows=2314038 loops=1)
         Output: ((to_timestamp(to_char(date_trunc('week'::text, event_time), 'IYYY-IW'::text), 'IYYY-IW'::text))::date), account
         Sort Key: ((to_timestamp(to_char(date_trunc('week'::text, account_history.event_time), 'IYYY-IW'::text), 'IYYY-IW'::text))::date)
         Sort Method: external merge  Disk: 40688kB
         ->  Index Scan using account_history_time_idx on public.account_history  (cost=0.44..110710.59 rows=2290904 width=12) (actual time=0.108..4352.143 rows=2314038 loops=1)
               Output: (to_timestamp(to_char(date_trunc('week'::text, event_time), 'IYYY-IW'::text), 'IYYY-IW'::text))::date, account
               Index Cond: ((account_history.event_time >= (now() - '357 days'::interval)) AND (account_history.event_time <= now()))
 Planning time: 0.204 ms
 Execution time: 6112.832 ms

https://explain.depesz.com/s/PSfU

Eu também tentei VACUUM FULLcomo sugerido aqui , mas não fez diferença no tempo de execução.

Aqui estão os planos de execução para algumas consultas mais simples na mesma tabela:

Simplesmente contar as linhas leva 0,5 segundos:

account=> EXPLAIN ANALYZE VERBOSE SELECT COUNT(*) FROM account_history;

 Aggregate  (cost=97401.04..97401.05 rows=1 width=0) (actual time=551.179..551.179 rows=1 loops=1)
   Output: count(*)
   ->  Seq Scan on public.account_history  (cost=0.00..85136.43 rows=4905843 width=0) (actual time=0.039..344.675 rows=4905843 loops=1)
         Output: account, event_code, event_time, description
 Planning time: 0.075 ms
 Execution time: 551.209 ms

E usar a mesma cláusula de intervalo de tempo leva menos de um segundo:

account=> EXPLAIN ANALYZE VERBOSE SELECT COUNT(*) FROM account_history WHERE event_time BETWEEN now() - interval '51 weeks' AND now();

 Aggregate  (cost=93527.57..93527.58 rows=1 width=0) (actual time=997.436..997.436 rows=1 loops=1)
   Output: count(*)
   ->  Index Only Scan using account_history_time_idx on public.account_history  (cost=0.44..87800.45 rows=2290849 width=0) (actual time=0.100..897.776 rows=2313987 loops=1)
         Output: event_time
         Index Cond: ((account_history.event_time >= (now() - '357 days'::interval)) AND (account_history.event_time <= now()))
         Heap Fetches: 2313987
 Planning time: 0.239 ms
 Execution time: 997.473 ms

Com base nos comentários, tentei uma forma simplificada da consulta:

account=> EXPLAIN ANALYZE VERBOSE SELECT date_trunc('week', event_time) AS date, count(DISTINCT account) FROM account_history
WHERE event_time BETWEEN now() - interval '51 weeks' AND now() GROUP BY date ORDER BY date;

 GroupAggregate  (cost=374676.22..420493.00 rows=2290839 width=12) (actual time=2475.556..3078.191 rows=52 loops=1)
   Output: (date_trunc('week'::text, event_time)), count(DISTINCT account)
   Group Key: (date_trunc('week'::text, account_history.event_time))
   ->  Sort  (cost=374676.22..380403.32 rows=2290839 width=12) (actual time=2468.654..2763.739 rows=2313977 loops=1)
         Output: (date_trunc('week'::text, event_time)), account
         Sort Key: (date_trunc('week'::text, account_history.event_time))
         Sort Method: external merge  Disk: 49720kB
         ->  Index Scan using account_history_time_idx on public.account_history  (cost=0.44..93527.35 rows=2290839 width=12) (actual time=0.094..1537.488 rows=2313977 loops=1)
               Output: date_trunc('week'::text, event_time), account
               Index Cond: ((account_history.event_time >= (now() - '357 days'::interval)) AND (account_history.event_time <= now()))
 Planning time: 0.220 ms
 Execution time: 3086.828 ms
(12 rows)

account=> SELECT date_trunc('week', current_date) AS date, count(DISTINCT account) FROM account_history WHERE event_time BETWE
EN now() - interval '51 weeks' AND now() GROUP BY date ORDER BY date;
          date          | count
------------------------+-------
 2017-10-23 00:00:00-04 |   132
(1 row)

De fato, isso reduziu o tempo de execução pela metade, mas infelizmente não dá os resultados desejados, como em:

account=> SELECT to_timestamp(to_char(date_trunc('week', event_time), 'IYYY-IW'), 'IYYY-IW')::date AS date, count(DISTINCT account) FROM account_history WHERE event_time BETWEEN now() - interval '51 weeks' AND now() GROUP BY date ORDER BY date;
    date    | count
------------+-------
 2016-10-31 |    14
...
 2017-10-23 |   584
(52 rows)

Se eu puder encontrar uma maneira mais barata de agregar esses registros por semana, isso ajudará bastante a resolver esse problema.

Estou aberto a quaisquer sugestões sobre como melhorar o desempenho da consulta semanal com a GROUP BYcláusula, inclusive alterando a tabela.

Eu criei uma visualização materializada como um teste, mas é claro que a atualização leva exatamente a mesma quantidade de tempo que a consulta original, portanto, a menos que eu a atualize apenas algumas vezes por dia, isso não ajuda muito, ao custo de adicionar complexidade:

account=> CREATE MATERIALIZED VIEW account_activity_weekly AS SELECT to_timestamp(to_char(date_trunc('week', event_time), 'IYYY-IW'), 'IYYY-IW')::date AS date, count(DISTINCT account) FROM account_history WHERE event_time BETWEEN now() - interval '51 weeks' AND now() GROUP BY date ORDER BY date;
SELECT 52

Com base em um comentário adicional, revisei minha consulta da seguinte forma, o que reduziu o tempo de execução pela metade e entrega o conjunto de resultados esperado:

account=> EXPLAIN ANALYZE VERBOSE SELECT to_timestamp(to_char(date_trunc('week', event_time), 'IYYY-IW'), 'IYYY-IW')::date AS date, count(DISTINCT account) FROM account_history WHERE event_time BETWEEN now() - interval '51 weeks' AND now() GROUP BY date_trunc('week', event_time) ORDER BY date;

 Sort  (cost=724523.11..730249.97 rows=2290745 width=12) (actual time=3188.495..3188.496 rows=52 loops=1)
   Output: ((to_timestamp(to_char((date_trunc('week'::text, event_time)), 'IYYY-IW'::text), 'IYYY-IW'::text))::date), (count(DISTINCT account)), (date_trunc('week'::text, event_time))
   Sort Key: ((to_timestamp(to_char((date_trunc('week'::text, account_history.event_time)), 'IYYY-IW'::text), 'IYYY-IW'::text))::date)
   Sort Method: quicksort  Memory: 29kB
   ->  GroupAggregate  (cost=374662.50..443384.85 rows=2290745 width=12) (actual time=2573.694..3188.451 rows=52 loops=1)
         Output: (to_timestamp(to_char((date_trunc('week'::text, event_time)), 'IYYY-IW'::text), 'IYYY-IW'::text))::date, count(DISTINCT account), (date_trunc('week'::text, event_time))
         Group Key: (date_trunc('week'::text, account_history.event_time))
         ->  Sort  (cost=374662.50..380389.36 rows=2290745 width=12) (actual time=2566.086..2859.590 rows=2313889 loops=1)
               Output: (date_trunc('week'::text, event_time)), event_time, account
               Sort Key: (date_trunc('week'::text, account_history.event_time))
               Sort Method: external merge  Disk: 67816kB
               ->  Index Scan using account_history_time_idx on public.account_history  (cost=0.44..93524.23 rows=2290745 width=12) (actual time=0.090..1503.985 rows=2313889 loops=1)
                     Output: date_trunc('week'::text, event_time), event_time, account
                     Index Cond: ((account_history.event_time >= (now() - '357 days'::interval)) AND (account_history.event_time <= now()))
 Planning time: 0.205 ms
 Execution time: 3198.125 ms
(16 rows)
optimization postgresql-9.3

2 respostas

  1. Best Answer

    Graças a quem contribuiu nos comentários, reduzi o tempo de consulta de ~8000 ms para ~1650 ms:

    • Adicionando um índice apenas na coluna de carimbo de data/hora (melhoria de ~2000 ms).
    • Removendo a conversão extra de timestamp-to-char-to-timestamp (ou adicionando date_trunc('week', event_time)à GROUP BYcláusula) (melhoria de ~3000 ms).

    Para referência, a estrutura da tabela atual e o plano de execução estão abaixo.

    Eu brinquei com as outras variações de indexação em várias colunas, mas nenhum desses índices foi usado pelo plano de execução.

    Além disso, segui o conselho de outro comentário e segui os seguintes passos (seguidos por VACUUM e REINDEX):

    • Descartou as restrições da coluna de descrição e definiu todas as strings vazias como NULL
    • Converteu a coluna de carimbo de data/hora de WITHOUT TIME ZONEparaWITH TIME ZONE
    • Aumentou o work_mem para 100 MB (via postgresql.conf).
    ALTER TABLE account_history ALTER event_time TYPE timestamptz USING event_time AT TIME ZONE 'UTC';
ALTER TABLE account_history ALTER COLUMN description DROP NOT NULL;
ALTER TABLE account_history ALTER COLUMN description DROP DEFAULT;
UPDATE account_history SET description=NULL WHERE description='';
VACUUM FULL;
REINDEX TABLE account_history;

account=> show work_mem;
 work_mem
----------
 100MB

    Essas alterações adicionais reduziram outros 400 ms do tempo de execução e também reduziram o tempo de planejamento. Uma coisa a notar é que o método de classificação mudou de "classificação externa" para "fusão externa". Como o 'Disk' ainda estava sendo usado para a classificação, aumentei o work_mem para 200 MB, o que resultou no uso do método quicksort (memória) (176 MB). Isso diminuiu um segundo inteiro do tempo de execução (embora isso seja realmente muito alto para ser usado em nossas instâncias de servidor).

    A tabela atualizada e o plano de execução estão abaixo.

    account=> \d account_history
                 Table "public.account_history"
   Column    |           Type           |       Modifiers
-------------+--------------------------+------------------------
 account     | integer                  | not null
 event_code  | text                     | not null
 event_time  | timestamp with time zone | not null default now()
 description | text                     |
Indexes:
    "account_history_account_idx" btree (account)
    "account_history_account_time_idx" btree (event_time DESC, account)
    "account_history_time_idx" btree (event_time DESC)
Foreign-key constraints:
    "account_fk" FOREIGN KEY (account) REFERENCES account(id) ON UPDATE CASCADE ON DELETE RESTRICT
    "event_code_fk" FOREIGN KEY (event_code) REFERENCES domain_account_event(code) ON UPDATE CASCADE ON DELETE RESTRICT

    account=> EXPLAIN ANALYZE VERBOSE SELECT date_trunc('week', event_time) AS date, count(DISTINCT account) FROM account_history WHERE event_time BETWEEN now() - interval '51 weeks' AND now() GROUP BY date ORDER BY date;

 GroupAggregate  (cost=334034.60..380541.52 rows=2325346 width=12) (actual time=1307.742..1685.676 rows=52 loops=1)
   Output: (date_trunc('week'::text, event_time)), count(DISTINCT account)
   Group Key: (date_trunc('week'::text, account_history.event_time))
   ->  Sort  (cost=334034.60..339847.97 rows=2325346 width=12) (actual time=1303.565..1361.540 rows=2312418 loops=1)
         Output: (date_trunc('week'::text, event_time)), account
         Sort Key: (date_trunc('week'::text, account_history.event_time))
         Sort Method: quicksort  Memory: 176662kB
         ->  Index Only Scan using account_history_account_time_idx on public.account_history  (cost=0.44..88140.73 rows=2325346 width=12) (actual time=0.028..980.822 rows=2312418 loops=1)
               Output: date_trunc('week'::text, event_time), account
               Index Cond: ((account_history.event_time >= (now() - '357 days'::interval)) AND (account_history.event_time <= now()))
               Heap Fetches: 0
 Planning time: 0.153 ms
 Execution time: 1697.824 ms

    Estou muito feliz com as melhorias até agora, mas agradeço qualquer outra contribuição para melhorar o desempenho desta consulta, pois esta ainda é a consulta mais lenta que tenho em uma das minhas visualizações.

    • 2

  2. Para resolver uma data rápida entre o problema da consulta. Eu converti datas para hora Unix (UTC) (eu só precisava de precisão de "segundo", mas você poderia ir mais fino se precisar) Em seguida, crie um método para converter suas datas em bigint/long (inclua sua conversão de fuso horário aqui). Em seguida, execute sua consulta e apenas pesquise entre os 2 inteiros. Pode soar um pouco selvagem, mas funciona como um sonho.

    • 0

relate perguntas