Início / dba / Perguntas / 335852
Accepted
Kyle Chamberlin
Asked: 2024-02-15 11:34:47 +0800 CST

Precisa de ajuda para identificar oportunidades de melhoria na configuração de tabelas e design de consultas

  • 772

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

2 respostas

  1. Para que o índice seja utilizado, você deve ter productcomo primeira coluna sua chave primária. Para que a terceira coluna do índice seja usada efetivamente com uma INcondição, todas as colunas do índice anteriores teriam que ser comparadas com =, e não com IN.

    • 0
  2. Best Answer

    Usando este código pastebin para construir dados de teste.

    Usando postgres 16.1.

    Um índice btree funciona como uma lista telefônica, que usarei como exemplo. As entradas são ordenadas por (sobrenome,nome). Portanto, consultas como esta:

    last_name=constant AND first_name BETWEEN first AND last

    ...resulta em uma busca para (last_name=constante, first_name=first) e depois verifica a linha do índice em ordem até atingir (last_name=constant, first_name=last). Isso é rápido.

    Infelizmente, para consultas como:

    WHERE last_name BETWEEN ... AND first_name BETWEEN ....

    é mais complicado e requer um método diferente: verifique o índice para encontrar todos os sobrenomes que satisfaçam a condição e, para cada um, faça o que está descrito no parágrafo anterior. Infelizmente, este método não está implementado atualmente no postgres. Portanto, temos que dar um pouco de ajuda manual.

    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 consulta original leva 4300 ms.

    Seus dados de exemplo preenchem apenas a tabela com categoria_a entre 1 e 50, portanto, a condição em categoria_a na consulta é redundante, mas presumo que não seja redundante na consulta real. De qualquer forma, como categoria_a é uma chave de partição, o postgres lerá apenas as partições relevantes para a consulta, portanto esta parte já está otimizada.

    Atualmente particionamos por A porque eles mudam após cerca de 200 valores e são eliminados do conjunto de dados. As partições A são subparticionadas por B. Cada uma dessas partições A_B contém cerca de 50-70 milhões de linhas.

    Não está claro se você deseja particionar por A ou A,B. Presumo que você particione apenas por A, pois é isso que está em questão.

    Agora, como Laurenz Albe disse, seu índice de chave primária não é adequado para esta consulta. Você precisaria deste:

    CREATE INDEX ON sales( category_b, product );

    Se você particionou por A, B e não por A, precisará apenas de um índice em (produto) porque a categoria_b seria tratada no nível da partição.

    Com esse índice, a consulta acima realiza uma varredura de índice em 4 ms, o que é 1.000 vezes mais rápido. Apenas citando uma parte do EXPLAIN, há um destes por partição:

     ->  Bitmap Heap Scan on sales_a001 sales_1  (cost=90.60..799.11 rows=197 width=33) (actual time=0.468..0.596 rows=150 loops=1)
       Recheck Cond: (((category_b)::text = ANY ('{001,010,018,019,024}'::text[])) AND ((product)::text = ANY ('{00000000000147,00000000000900,00000000000140,00000000009999}'::text[])))
       Filter: ((category_a >= 1) AND (category_a <= 10))
       Heap Blocks: exact=9
       ->  Bitmap Index Scan on sales_a001_category_b_product_idx  (cost=0.00..90.55 rows=197 width=0) (actual time=0.447..0.447 rows=150 loops=1)
             Index Cond: (((category_b)::text = ANY ('{001,010,018,019,024}'::text[])) AND ((product)::text = ANY ('{00000000000147,00000000000900,00000000000140,00000000009999}'::text[])))

    Isso é ótimo, mas você precisa de um índice extra, que será bem grande. Podemos fazer isso sem o índice? Sim, trapaceando um pouco. Então, primeiro, descarto o índice criado acima e:

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

    Se usarmos uma constante categoria_a = 1 então o postgres será capaz de usar uma varredura de índice.

    O que ele realmente faz é pegar as duas listas de categoria_b e produto, percorrer ambas para enumerar todas as combinações e fazer uma pesquisa de índice para cada combinação. Ainda muito mais rápido que uma varredura sequencial.

    Isso também funciona com:

    select category_a, category_b, product, sum(units), sum(sales)
from sales
where category_a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,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;

    Ao substituir o intervalo por um conjunto de valores conhecidos, é possível usar uma varredura de índice. No entanto, o número de combinações a enumerar é bastante grande. O Postgres não entende que pode pular todos os valores de categoria_a que não existem em uma partição, então ele executa esta pesquisa em cada partição:

    ->  Index Scan using sales_a002_pkey on sales_a002 sales_2  (cost=0.43..8308.58 rows=202 width=33) (actual time=0.035..1.421 rows=150 loops=1)
Index Cond: ((category_a = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100}'::integer[])) AND ((category_b)::text = ANY ('{001,010,018,019,024}'::text[])) AND ((product)::text = ANY ('{00000000000147,00000000000900,00000000000140,00000000009999}'::text[])))

    Portanto, a consulta é muito mais rápida (175ms) do que a consulta all-seq-scan (4300ms), mas também é muito mais lenta do que com o índice em (category_b,product) (4ms). Ainda assim, um meio-termo interessante se esta for uma consulta ocasional e você não quiser carregar um índice enorme apenas para esta.

    Agora, é claro que existe a opção de trapacear ainda mais:

    select category_a, category_b, product, sum(units), sum(sales) from sales_a001
        where category_a = 1 AND category_b in ('001', '010', '018', '019', '024')
        and product in ('00000000000147', '00000000000900', '00000000000140', '00000000009999')
        group by category_a, category_b, product
UNION ALL select category_a, category_b, product, sum(units), sum(sales) from sales_a002
        where category_a = 2 AND 
        ..... repeat for all values of category_a

    Isso combina muitos níveis de design ruim, mas é executado em menos de 20 ms sem o índice extra.

    Eu também tentei clickhouse.

    CREATE TABLE IF NOT EXISTS sales2( 
    category_a  Int16           NOT NULL CODEC(Delta,ZSTD(9)),    -- sequential integer values from 0 - 10000
    category_b  FixedString(3)  NOT NULL CODEC(ZSTD(9)),  -- 3-digit ids (all numeric, padded with zeros)
    product     FixedString(14)     NOT NULL CODEC(ZSTD(9)), -- essentially random 14 character identifiers
    location_id FixedString(5)     NOT NULL CODEC(ZSTD(9)),  -- location id, 5-digit number (left padded with zeros)
    units       Int32           NOT NULL CODEC(ZSTD(9)),         -- value of interest
    sales       Float32         NOT NULL CODEC(ZSTD(9))       -- second value of interest
)   ENGINE=MergeTree -- eliminates duplicates with same (topic,ts) which should not occur
    PARTITION BY (category_a)
    ORDER BY (category_a,category_b,product)
    PRIMARY KEY (category_a,category_b,product)
    SETTINGS index_granularity=256;

    Eu \copio dados do postgres e os carrego. A taxa de compactação é de cerca de 6:1, o que não é ideal devido às colunas de texto (armazenar números como inteiros daria um resultado melhor). Toda a tabela e índice ocupam cerca de 340 MB, contra 4,3 GB do postgres: graças à compactação é como ter 12 vezes mais RAM para cache de disco.

    A consulta na pergunta leva de 20 a 70 ms, dependendo da configuração de granularidade do índice.

    Mudar a ordem de armazenamento das linhas e a chave primária para (categoria_a,produto,categoria_b) parece torná-lo mais rápido, porque o produto é mais seletivo.

    Provavelmente poderia ser otimizado ainda mais, mas este é apenas um teste de 10 minutos.

    • 0

relate perguntas