Início / dba / Perguntas / 311006
Accepted
AndreKR
Asked: 2022-04-16 09:19:54 +0800 CST

Preciso de estatísticas além de um índice?

  • 772

Suponha que eu tenha uma tabela com imagens que devem passar por várias etapas:

CREATE TABLE images (filename text, extracted bool, cropped bool, resized bool);
    
INSERT INTO images (filename, extracted, cropped, resized)
VALUES
    ('foo', false, false, false),
    ('bar', true, false, false),
    ('baz', true, true, false),
    ('qux', true, true, true);

Em algum momento, tenho uma consulta para encontrar todas as imagens cortadas, mas que ainda precisam ser redimensionadas:

SELECT count(*) FROM images WHERE cropped AND NOT resized;

Agora acredito que a melhor maneira de tornar essa consulta rápida é um índice parcial:

CREATE INDEX ON images (cropped, resized) WHERE (cropped AND NOT resized);

Eu o tornaria parcial porque cropped AND NOT resizedé um estado relativamente raro, embora possa haver milhões de imagens que já estão totalmente processadas e também milhões que ainda não foram cortadas.

Minha pergunta agora é, preciso de estatísticas além do índice?

Um desses?

CREATE STATISTICS stat1 (dependencies) ON cropped, resized FROM images;
CREATE STATISTICS stat2 (ndistinct) ON cropped, resized FROM images;
CREATE STATISTICS stat3 (mcv) ON cropped, resized FROM images;
ANALYZE images;

Encontrei o capítulo Como o planejador usa estatísticas que eu havia perdido anteriormente (ou melhor, misturado com Estatísticas usadas pelo planejador ), mas ele fala apenas sobre como as estatísticas são transformadas em estimativas de linha. O que não está claro para mim é como os índices são escolhidos, uma vez que aparentemente não há estatísticas sobre os índices.

postgresql statistics

3 respostas

  1. Você está substancialmente pensando demais nisso. Sua consulta é muito simples e há apenas algumas maneiras de executá-la. Se ele retornará 7.000 linhas ou 2.000 linhas, não importa, porque de qualquer forma o índice parecerá melhor do que as poucas alternativas.

    Se você realmente deseja executar uma variedade maior de consultas que tenham mais oportunidades de fazer a escolha errada do planejador, pode ser importante incluir as estatísticas estendidas da variedade mcv.

    Seus dois exemplos são totalmente incompatíveis. A tabela de contagens em sua pergunta levaria a estimativas de linha muito diferentes das mostradas em sua resposta. Daria cerca de 5.000.000 sem estatísticas MCV estendidas e cerca de 1 com as estatísticas estendidas. Certamente não 6872 vs 1782.

    • 1
  2. Best Answer

    Meus experimentos mostraram que atualmente não pareço precisar dessas estatísticas para que o índice seja usado , mas ainda não estava claro por que isso. Depois de um mergulho na fonte, acho que posso responder à minha própria pergunta.

    Essencialmente, a decisão de usar um índice é feita btcostestimate()por sua vez genericcostestimate().

    Isso ajuda a lembrar quais tipos de estatísticas estão disponíveis para cada tabela:

    • Número de tuplas
    • Para cada coluna: Número de valores distintos (às vezes chamado de "cardinalidade")
    • Para cada coluna: os valores mais comuns
    • Para cada coluna: um histograma dos valores restantes (menos comuns)
    • Se configurado: dependenciesstats ("Quantos valores na Coluna A têm apenas um único valor aparece na coluna B.")
    • Se configurado: ndistinctestatísticas (Número de combinações de valores únicos nas colunas A e B.)
    • Se configurado: mcvestatísticas (combinações de valores mais comuns nas colunas A e B.)

    Para cada índice, o Postgres determina quais condições podem ser verificadas usando o índice (o "Index Conds" ou "indexQuals"). Com base neles, genericcostestimate()(usando clauselist_selectivity()) calcula uma seletividade para o índice, levando em consideração essas estatísticas estendidas. Na verdade, isso se refletiu em meus experimentos, pois obtive melhores estimativas de linha com estatísticas estendidas do mcvtipo:

    captura de tela
    captura de tela
    A diferença no tempo real está no cache.

    O predicado do índice parcial também seria levado em consideração, mas somente se introduzir restrições adicionais, portanto, não é relevante aqui.

    Então o que eu acho que o índice foi escolhido é o seguinte: Primeiro o predicado do índice foi verificado para ver se o índice é mesmo utilizável. Em seguida, foi calculada uma seletividade para as condições particulares, que sem as estatísticas estendidas estariam de fato um pouco erradas. Mas, mais abaixo, quando o custo real é calculado, é tão baixo porque o índice é tão pequeno que, mesmo com a estimativa de linha errada, o custo é muito baixo.

    Portanto, a resposta é sim, teoricamente estatísticas estendidas ainda são necessárias para boas estimativas de linha , mas também não , o índice ainda é escolhido sem estatísticas estendidas porque é muito pequeno.

    • 0

  3. sua consulta não filtra suficientemente a tabela. então o otimizador de consulta postgres não usa esse índice e escolhe a varredura de tabela sequencial (ou varredura completa de tabela ).

    Você pode alterar o design da tabela e o design da consulta. Ou você deve concordar com a varredura sequencial.

    • -3

relate perguntas