Início / dba / Perguntas / 213108
Accepted
Devdatta Tengshe
Asked: 2018-07-25 07:33:09 +0800 CST

Como melhorar o desempenho da consulta de fornecedores distintos em grandes tabelas de vendas?

  • 772

Eu tenho duas tabelas, uma com dados da loja:

              Table "public.store"
  Column   |          Type          | Modifiers
-----------+------------------------+-----------
 id        | integer                | not null
 hul_code  | character varying      |
 latitude  | numeric                |
 longitude | numeric                |
 name      | character varying(100) |
Indexes:
    "store_pkey" PRIMARY KEY, btree (id)
    "store-hlcode-idx" btree (hul_code)

Outro com os dados de vendas:

         Table "public.sale_data"
  Column   |       Type        | Modifiers
-----------+-------------------+-----------
 id        | integer           | not null
 hul_code  | character varying |
 partyname | character varying |
 rscode    | character varying |
 rsname    | character varying |
 cluster   | character varying |
 channel   | character varying |
 basepack  | character varying |
 total_16  | numeric           |
 total_17  | numeric           |
 total_18  | numeric           |
Indexes:
    "sale_data_pkey" PRIMARY KEY, btree (id)
    "hul-code-idx" btree (hul_code)
    "sd-bp-idx" btree (basepack)
    "sd-rscd-idx" btree (rscode)

A segunda tabela tem mais de 11 milhões de linhas.

Estou tentando obter a lista de todas as lojas que tiveram itens provenientes de um determinado fornecedor regional (identificado por rscode) usando uma consulta como:

Select s.hul_code, s.name, s.latitude, s.longitude
from store s 
Where hul_code in 
  (Select Distinct(hul_code) as hulcode
   from sale_data
   where rscode='133955')
AND s.latitude is not null;

Essa consulta leva mais de 1,5 a 2 segundos em média.

Como aumentar a velocidade desta consulta?

postgresql performance

1 respostas

  1. Best Answer

    Para distribuições de dados típicas, emular uma varredura de índice solta deve ser o truque de mágica para você.

    Requer um índice de várias colunas para ser rápido:

    CREATE INDEX ON sale_data(rscode, hul_code);

    A variante com subconsulta correlacionada no rCTE deve ser mais rápida para o caso simples:

    WITH RECURSIVE cte AS (
   SELECT min(hul_code) AS hul_code
   FROM   sale_data
   WHERE  rscode = '133955'    -- provide rscode here

   UNION ALL
   SELECT (SELECT hul_code
           FROM   sale_data
           WHERE  rscode = '133955'     -- repeat rscode here
           AND    hul_code > c.hul_code
           ORDER  BY 1
           LIMIT  1
           )
   FROM   cte c
   )
SELECT s.hul_code, s.name, s.latitude, s.longitude
FROM   cte c
JOIN   store s USING (hul_code);

    Explicação detalhada:

    Pode haver alternativas mais rápidas para distribuições de dados específicas.

    Apartes:

    DISTINCT pode fazer sentido para distribuições de dados com muito poucas linhas por distinta (rscode, hul_code)(produzindo um grande número de linhas resultantes). Mas o que você tem não faz muito sentido:

    IN (Select Distinct(hul_code) as hulcode ... )

    O alias da coluna ( as hulcode) é um ruído, pois INignora os nomes das colunas.

    DISTINCTnão requer parênteses . (hul_code)é interpretado como construtor ROW, abreviação de ROW(hul_code). O wrapper de linha é removido automaticamente para a coluna única neste caso. Mas você pode apresentar complicações para casos com várias colunas.

    Existe a variante do Postgres DISTINCT ON ()que requer parênteses, mas isso não é usado (nem necessário) aqui. Relacionado:

    Mas nada disso faz muito sentido com uma INconstrução, onde você pode simplesmente desistir DISTINCTcompletamente. Melhor ainda, reescrever como:

    SELECT s.hul_code, s.name, s.latitude, s.longitude
FROM  (
   SELECT DISTINCT hul_code
   FROM   sale_data
   WHERE  rscode='133955'
   ) sd
JOIN  store s USING (hul_code);

    .. no caso improvável de você ter essa distribuição de dados.

    E suas colunas rscodeparecem conter dados numéricos. Nesse caso, deve ser um tipo numérico , não varchar.

    • 2

relate perguntas