Início / dba / Perguntas / 213407
Accepted
AlexGordon
Asked: 2018-07-28 04:26:36 +0800 CST

O desempenho da consulta do PostgreSQL 9.5 depende da coluna JOINed na cláusula SELECT

  • 772

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
postgresql optimization

1 respostas

  1. Best Answer

    Depois de tentar várias configurações, acho que encontrei a resposta para minha própria pergunta. Não é 100% comprovado, mas faz sentido.

    O COUNT(*) OVER() as total_rowscomando executa a consulta inteira, incluindo a WHEREcláusula E os SELECTcampos. Portanto, quando o conjunto de resultados for muito grande, muito maior que o LIMIT 1000, ainda estaríamos copiando todos os registros correspondentes de acordo com as colunas indicadas em SELECT, incluindo o respayload.

    Como respayloadpode ser bastante grande, quando a contagem ilimitada de respostas é alta, digamos 10K, então 10K respayloads são copiados, apenas para serem descartados posteriormente, pois a cópia das colunas selecionadas acontece novamente apenas para os primeiros 1000 (por causa do limite) registros .

    É por isso que a omissão respayloadda declaração melhorou o desempenho tão drasticamente.

    Terei cuidado no futuro onde usar COUNT(*) OVER(), pois isso parece ser seriamente afetado pelas colunas selecionadas.

    • 0

relate perguntas