AlexGordon's questions

Excluí ~ 65 milhões de linhas de uma tabela PostgreSQL com ~ 75 milhões de linhas. Assim que a consulta de exclusão foi concluída, a CPU caiu para 100% por cerca de cinco minutos.

A tabela da qual as linhas foram excluídas tem vários índices e estava em uso intenso durante a exclusão e depois dela. Infelizmente não tenho como reproduzir isso, pois aconteceu em ambiente de produção.

É provável que o autovacuum tenha entrado em ação e, em caso afirmativo, poderia conduzir um banco de dados com 32 núcleos de CPU para 100% de uso da CPU? Em caso afirmativo, existe uma maneira de limitar a ingestão de autovacuum para que não prejudique o desempenho do banco de dados após consultas de exclusão em massa?

Estou usando o PostgreSQL versão 14.8.

Eu escrevi a consulta que me fornece uma série temporal em algum intervalo de datas e intervalo que mostra a receita para cada intervalo de tempo:

SELECT
    interval_date,
    coalesce(campaign_revenue,0) AS campaign_revenue,
FROM
    -- generate_series helps fill the empty gaps in the following JOIN
    generate_series(
        $2::timestamp,
        $3::timestamp,
        $4) AS interval_date -- could be '1 day', '1 hour' or '1 minute'.
LEFT OUTER JOIN
    -- This SELECT gets all timeseries rows that have data
    (SELECT
        date_trunc($4, s.created) AS interval,
        SUM(s.revenue) campaign_revenue
    FROM
        sale_event AS s
    WHERE
        s.campaignid = $1 AND s.created BETWEEN $2 AND $3 AND s.event_type = 'session_closed'
    GROUP BY
        interval) results
ON
    (results.interval = interval_date);

A consulta pega todas as linhas da sale_eventtabela, trunca a data de criação para algum intervalo (alinha o createdtimestamp com a granularidade desejada da série temporal), agrupa por esse intervalo de tempo e soma as revenuecolunas nas linhas onde event_typeé session_closed.

Isso funciona muito bem e me dá a receita no intervalo especificado. O resultado pode se parecer com:

interval_date   |   campaign_revenue
------------------------------------
 2018-08-05     |   0.0
 2018-08-06     |   1.5
 2018-08-07     |   0.0
 2018-08-08     |   0.5
 2018-08-09     |   1.0

Quando o intervalo fornecido for 2018-08-05 - 2018-08-09e interval = '1 day'.

Quero adicionar ao resultado a soma das receitas até aquela data. Então, se antes 2018-08-05houvesse uma receita total de 10.0, o resultado seria:

interval_date   |   campaign_revenue   |   total_campaign_revenue
-----------------------------------------------------------------
 2018-08-05     |   0.0                |   10.0
 2018-08-06     |   1.5                |   11.5
 2018-08-07     |   0.0                |   11.5
 2018-08-08     |   0.5                |   12.0
 2018-08-09     |   1.0                |   13.0

Com o objetivo de melhorar o desempenho das consultas em uma tabela bastante grande (mais de 5 milhões de linhas) contendo jsonbcolunas, gostaria de criar visualizações materializadas com dados derivados dessas colunas. Segue uma versão simplificada do problema:

               Definição

CREATE TABLE minha_tabela (id int8, conteúdo jsonb);

               Conteúdo

identificação | contente
-------------------------------------------------- ---------------------------
 1 | { "customer": "John Doe", "items": {"product": "Whiskey","qty": 3}}
 2 | { "customer": "John Doe", "items": {"product": "Whiskey","qty": 4}}
 3 | { "customer": "Danny Boy", "items": {"product": "Cerveja","qty": 2}}

Agora eu gostaria de criar duas visualizações materializadas, uma conterá todos os nomes de clientes e a outra conterá a contagem de cada arquivo product.

Assim, o resultado final deve ser:

               Visão materializada 1 / os nomes dos clientes
nome do cliente
-------------
John Doe
Danny Boy

               Visão materializada 2 / a quantidade de todos os produtos

produto | quantidade
------------------
Cerveja | 2
Uísque | 7

Para a primeira visualização materializada, tentei o seguinte:

CRIAR VISTA MATERIALIZADA
  clientes AS
SELECIONAR DISTINTO
  content->'customer' AS customer_name FROM my_table;

Isso funciona bem, mas estou tendo problemas com a segunda visão materializada.

Como devo proceder para agregar as quantidades de cada produto de forma eficiente?

Dados:

Nas duas tabelas a seguir:

                                      Tabela "public.response_logs"
  Coluna | Tipo | Agrupamento | Anulável | Predefinição
----------+--------------------------+-----------+ ----------+--------------------------------------- ----
 identificação | inteiro | | não nulo | nextval('response_logs_id_seq'::regclass)
 estado | bigint | | |
 uuid | texto | | |
 carga útil | texto | | |
 acessado | timestamp com fuso horário | | não nulo | agora()
Índices:
    "response_logs_pkey" CHAVE PRIMÁRIA, btree (id)
    "response_logs_uuid_idx" ÚNICO, btree (uuid)
Restrições de chave estrangeira:
    "response_logs_uuid_fkey" FOREIGN KEY (uuid) REFERENCES request_logs(uuid)

E:

                                        Tabela "public.request_logs"
    Coluna | Tipo | Agrupamento | Anulável | Predefinição
---------------+--------------------------+------- ----+----------+---------------------------------- -------
 identificação | inteiro | | não nulo | nextval('access_logs_id_seq'::regclass)
 accountid | bigint | | |
 ID de usuário | bigint | | |
 applicationid | bigint | | |
 requesturi | texto | | |
 método | texto | | |
 acessado | timestamp com fuso horário | | não nulo | agora()
 uuid | texto | | não nulo |
 carga útil | texto | | não nulo | ''::texto
 apikeyid | bigint | | |
 cabeçalho | jsonb | | não nulo | '[]'::jsonb
Índices:
    "request_logs_pkey" CHAVE PRIMÁRIA, btree (uuid)
    "request_logs_application_idx" btree (id do aplicativo)
Referenciado por:
    TABELA "response_logs" CONSTRAINT "response_logs_uuid_fkey" FOREIGN KEY (uuid) REFERENCES request_logs(uuid)

Estou realizando a seguinte consulta:

SELECIONAR
  req.uuid,
  res.status,
  req.método,
  req.requesturi,
  req.acessado,
  req.payload reqpayload,
  res.payload repayload, /* #1 - carga útil de resposta */
  COUNT(*) OVER() AS total_rows /* #2 - contagem total de resposta em cada linha na resposta */
A PARTIR DE
  request_logs req
  INNER JOIN response_logs res ON req.uuid = res.uuid AND res.status NÃO É NULO
ONDE
   req.applicationid = 1 AND
   req. acessado ENTRE '2018-01-01 15:04:05 +0000' E '2019-01-02 15:04:05+0000' AND
   req.requesturi NOT ILIKE '/v1/sessions%'
ORDENAR POR
   acessou DESC LIMIT 1000;

O que leva em média 270 ms.

O problema:

Posso tornar a consulta muito mais rápida omitindo #2( COUNT(*) OVER() AS total_rows) por motivos bastante óbvios. Iria para cerca de 40 ms.

Porém, e isso eu acho muito confuso , a consulta vai para cerca de 34 ms se eu omitir respayloadda resposta.

Perguntas!

1. O gargalo da garrafa não deveria ser COUNT(*) OVER() AS total_rows? Como a consulta pode ir para 40 ms com isso ainda sendo calculado?

2. Por que omitir o respayloaddá uma melhora tão grande? A remoção reqpayloadnão tem um efeito semelhante e mesmo que não adicionemos respayload, ainda precisamos pegar o response_logUUID correspondente para copiar statusna resposta.

3. Considerando que os únicos valores de consulta alterados são a applicationide a data com a qual comparamos accessed, quais melhorias adicionais, incluindo a indexação, podem ser feitas para ajudar no desempenho?

Mais dados!

Aqui está explain analysepara as diferentes configurações de consulta:

Com respayloade COUNT(*) OVER() AS total_rows:

Limit  (cost=2826.59..2829.09 rows=1000 width=823) (actual time=408.535..419.136 rows=1000 loops=1)
      ->  Sort  (cost=2826.59..2829.79 rows=1281 width=823) (actual time=408.524..412.154 rows=1000 loops=1)
            Sort Key: req.accessed DESC
            Sort Method: top-N heapsort  Memory: 2064kB
            ->  WindowAgg  (cost=1090.16..2760.47 rows=1281 width=823) (actual time=368.207..390.866 rows=3951 loops=1)
                  ->  Hash Join  (cost=1090.16..2744.46 rows=1281 width=823) (actual time=50.244..127.325 rows=3951 loops=1)
                        Hash Cond: (res.uuid = req.uuid)
                        ->  Seq Scan on response_logs res  (cost=0.00..1607.26 rows=9126 width=758) (actual time=0.008..36.196 rows=9129 loops=1)
                              Filter: (status IS NOT NULL)
                        ->  Hash  (cost=1044.85..1044.85 rows=3625 width=102) (actual time=38.739..38.739 rows=4046 loops=1)
                              Buckets: 4096  Batches: 1  Memory Usage: 1122kB
                              ->  Index Scan using request_logs_application_idx on request_logs req  (cost=0.29..1044.85 rows=3625 width=102) (actual time=0.035..22.009 rows=4046 loops=1)
                                    Index Cond: (applicationid = 1)
                                    Filter: ((accessed >= '2018-01-01 15:04:05+00'::timestamp with time zone) AND (accessed <= '2019-01-02 15:04:05+00'::timestamp with time zone) AND (requesturi !~~* '/v1/sessions%'::text))
    Planning time: 2.699 ms
    Execution time: 423.068 ms

Com respayloade sem COUNT(*) OVER() AS total_rows:

Limit  (cost=2810.58..2813.08 rows=1000 width=823) (actual time=136.977..146.820 rows=1000 loops=1)
  ->  Sort  (cost=2810.58..2813.78 rows=1281 width=823) (actual time=136.967..140.334 rows=1000 loops=1)
        Sort Key: req.accessed DESC
        Sort Method: top-N heapsort  Memory: 2064kB
        ->  Hash Join  (cost=1090.16..2744.46 rows=1281 width=823) (actual time=47.127..119.808 rows=3951 loops=1)
              Hash Cond: (res.uuid = req.uuid)
              ->  Seq Scan on response_logs res  (cost=0.00..1607.26 rows=9126 width=758) (actual time=0.015..33.307 rows=9129 loops=1)
                    Filter: (status IS NOT NULL)
              ->  Hash  (cost=1044.85..1044.85 rows=3625 width=102) (actual time=38.328..38.328 rows=4046 loops=1)
                    Buckets: 4096  Batches: 1  Memory Usage: 1122kB
                    ->  Index Scan using request_logs_application_idx on request_logs req  (cost=0.29..1044.85 rows=3625 width=102) (actual time=0.047..21.813 rows=4046 loops=1)
                          Index Cond: (applicationid = 1)
                          Filter: ((accessed >= '2018-01-01 15:04:05+00'::timestamp with time zone) AND (accessed <= '2019-01-02 15:04:05+00'::timestamp with time zone) AND (requesturi !~~* '/v1/sessions%'::text))
Planning time: 3.882 ms
Execution time: 150.465 ms

Sem respayloade com COUNT(*) OVER() AS total_rows:

Limit  (cost=2826.59..2829.09 rows=1000 width=110) (actual time=164.428..174.760 rows=1000 loops=1)
  ->  Sort  (cost=2826.59..2829.79 rows=1281 width=110) (actual time=164.418..167.956 rows=1000 loops=1)
        Sort Key: req.accessed DESC
        Sort Method: top-N heapsort  Memory: 564kB
        ->  WindowAgg  (cost=1090.16..2760.47 rows=1281 width=110) (actual time=133.997..148.382 rows=3951 loops=1)
              ->  Hash Join  (cost=1090.16..2744.46 rows=1281 width=110) (actual time=46.282..119.070 rows=3951 loops=1)
                    Hash Cond: (res.uuid = req.uuid)
                    ->  Seq Scan on response_logs res  (cost=0.00..1607.26 rows=9126 width=45) (actual time=0.009..33.656 rows=9129 loops=1)
                          Filter: (status IS NOT NULL)
                    ->  Hash  (cost=1044.85..1044.85 rows=3625 width=102) (actual time=37.844..37.844 rows=4046 loops=1)
                          Buckets: 4096  Batches: 1  Memory Usage: 1122kB
                          ->  Index Scan using request_logs_application_idx on request_logs req  (cost=0.29..1044.85 rows=3625 width=102) (actual time=0.029..21.602 rows=4046 loops=1)
                                Index Cond: (applicationid = 1)
                                Filter: ((accessed >= '2018-01-01 15:04:05+00'::timestamp with time zone) AND (accessed <= '2019-01-02 15:04:05+00'::timestamp with time zone) AND (requesturi !~~* '/v1/sessions%'::text))
Planning time: 3.758 ms
Execution time: 178.675 ms

Estou usando o PostgreSQL 9.5 no High Sierra.

Sobre as duas tabelas:

request_logs - ~ 26 mil linhas

response_logs - ~ 9 mil linhas

Eu tenho a seguinte consulta (com JOIN):

SELECIONAR
  req.uuid,
  res.status,
  req.método,
  req.requesturi,
  req.acessado,
  req.payload reqpayload,

  res.payload repayload,
  COUNT(*) OVER() AS total_rows
A PARTIR DE
  request_logs req
  LEFT OUTER JOIN response_logs res ON req.uuid = res.uuid
ONDE
   req.conta = 2 AND
   req.requesturi não é semelhante a '/v1/sessions%' AND
   req. acessado ENTRE “2018-01-01 15:04:05 +0000” e “2019-01-02 15:04:05+0000” E
   res.status NÃO É NULO E
   req.id do aplicativo = 1
ORDENAR POR
   acessou DESC LIMIT 1000

Como estou tentando otimizar a consulta, experimentei diferentes índices: Aqui está uma lista do que tentei:

Configuração 1:
 1. request_log.uuid (pkey, exclusivo)
 2. response_log.uuid (pkey, exclusivo, chave estrangeira)

Tempo de resposta méd. : 260ms

Configuração 2:
 1. request_log.uuid (pkey, exclusivo)
 2. request_log.applicationid
 3. response_log.uuid (pkey, exclusivo, chave estrangeira)

Tempo de resposta méd. : 230ms

Configuração 3:
 1. request_log.uuid (pkey, exclusivo)
 2. request_log.applicationid
 3. request_log.accessed (timestampz)
 4. response_log.uuid (pkey, exclusivo, chave estrangeira)

Tempo de resposta méd. : 230ms

Configuração 4:
 1. request_log.uuid (pkey, exclusivo)
 2. request_log.applicationid
 3. request_log.accessed (timestampz)
 4. request_log.accountid
 5. response_log.uuid (pkey, exclusivo, chave estrangeira)

Tempo de resposta méd. : 230ms

Configuração 5:
 1. request_log.uuid (pkey, exclusivo)
 2. request_log.applicationid, request_log.accessed (combinado)
 3. response_log.uuid (pkey, exclusivo, chave estrangeira)

Tempo de resposta méd. : 240ms

Como é visível no resultado, a indexação por applicationid(an int8) ajudou um pouco, enquanto a indexação por timestampz accessednão ajudou em nada. Talvez o mau desempenho seja devido ao JOIN? No geral, parece bastante lento e tento não pensar no que acontecerá quando essas tabelas contiverem milhões de registros (10M+).

Qual seria a melhor maneira de indexar essas tabelas para tornar essa consulta mais rápida?

EDITAR:

Aqui está EXPLAIN ANALYZEa última configuração:

Limite (custo=3489,80..3490,69 linhas=356 largura=823) (tempo real=241,152..241,345 linhas=1000 loops=1)
  -> Ordenar (custo=3489.80..3490.69 linhas=356 largura=823) (tempo real=241.150..241.288 linhas=1000 loops=1)
        Chave de classificação: DESC acessado por req.
        Método de classificação: top-N heapsort Memória: 2064kB
        -> WindowAgg (custo=1829.41..3474.71 linhas=356 largura=823) (tempo real=230.040..237.993 linhas=3951 loops=1)
              -> Hash Join (custo=1829.41..3470.26 linhas=356 largura=823) (tempo real=8.622..17.974 linhas=3951 loops=1)
                    Hash Cond: (res.uuid = req.uuid)
                    -> Seq Scan on response_logs res (custo=0,00..1604.21 linhas=8821 largura=758) (tempo real=0,006..4.527 linhas=9124 loops=1)
                          Filtro: (status NÃO É NULO)
                    -> Hash (custo=1816.39..1816.39 linhas=1042 largura=102) (tempo real=8.243..8.243 linhas=4046 loops=1)
                          Baldes: 4096 (originalmente 2048) Lotes: 1 (originalmente 1) Uso de memória: 1122kB
                          -> Bitmap Heap Scan on request_logs req (cost=105.85..1816.39 rows=1042 width=102) (tempo real=0.581..6.449 rows=4046 loops=1)
Tempo de execução: 242,154 ms