Início / user-286242

Kyle Chamberlin's questions

Martin Hope
Kyle Chamberlin
Asked: 2024-02-15 11:34:47 +0800 CST
  • 5

Estou trabalhando em um projeto com um conjunto de dados bastante grande. Exigimos agregados arbitrários neste conjunto de dados, que são gerados no momento da solicitação de um usuário. Aqui está uma descrição básica de nossa configuração atual no PostgresQL v11 (sim, sabemos que é EOL, a atualização está prevista para o próximo trimestre)

A estrutura básica da tabela é assim:

create table if not exists sales
(
    category_a  smallint,    -- sequential integer values from 0 - 10000
    category_b  varchar(3),  -- 3-digit ids (all numeric, padded with zeros)
    product     varchar(14), -- essentially random 14 character identifiers
    location_id varchar(5),  -- location id, 5-digit number (left padded with zeros)
    units       int,         -- value of interest
    sales       float,       -- second value of interest
    primary key (category_a, category_b, product, location_id)
) partition by range (category_a);

Atualmente particionamos por Aporque eles mudam após cerca de 200 valores e são eliminados do conjunto de dados. Apartições são subparticionadas por B. Cada uma dessas A_Bpartições contém cerca de 50 a 70 milhões de linhas.

Os valores para Bnão são sequenciais e possuem lacunas.

Existem muitos valores diferentes de produto, cerca de um milhão.

location_id, existem cerca de 50 a 100 locais por categoria B, cada um com a maioria dos produtos.

Um exemplo de consulta é semelhante a este:

select category_a, category_b, product, sum(units), sum(sales)
from sales
where category_a between 1 and 100
  and sales.category_b in ('001', '010', '018', '019', '024')
  and product in ('00000000000147', '00000000000900', '00000000000140', '00000000009999')
group by category_a, category_b, product;

A explicação para esta consulta indica que realizamos uma verificação sequencial completa para cada partição no conjunto de dados. Isso parece estranho, pois temos o índice único com os três valores à esquerda sendo os três nas cláusulas where e group. Não entendo por que isso não usa o índice.

Aqui está uma consulta que carregará dados de exemplo na tabela:

insert into sales
    (category_a, category_b, product, location_id, units, sales)
select cat_a,
       lpad(cat_b::varchar, 3, '0'),
       lpad(product::varchar, 14, '0'),
       lpad(location_id::varchar, 5, '0'),
       (random() * 10000)::int,
       (random() * 100000)::int
from generate_series(1, 50) cat_a
         cross join generate_series(1, 25) cat_b
         cross join generate_series(1, 10) location_id
         cross join generate_series(1, 5000) product;

A explicação para essa consulta é muito longa, mas posso fornecê-la se acharmos que vai ajudar.

Essas consultas podem ser extremamente lentas (minutos, às vezes mais de 10). Terei todo o prazer em fornecer detalhes adicionais, mas esta é a informação essencial (a meu ver).

Existem alterações que podemos/devemos fazer em nossa tabela ou consulta que aumentariam o desempenho desta consulta?

postgresql