Início / dba / Perguntas / 249969
Accepted
Morris de Oryx
Asked: 2019-10-01 16:04:06 +0800 CST

Melhorando estimativas de valores distintos no Postgres

  • 772

Contagens completas no Postgres podem ser lentas, por razões que são bem compreendidas e muito discutidas. Então, eu tenho usado técnicas de estimativa em vez disso, sempre que possível. Para linhas, pg_stats parece bom, para visualizações, extrair uma estimativa retornada por EXPLAINfunciona bem.

https://www.cybertec-postgresql.com/en/count-made-fast/

Mas e os valores distintos? Aqui, tive muito menos sorte. Às vezes as estimativas estão 100% corretas, às vezes elas estão erradas por fatores de 2 ou 20. Tabelas truncadas parecem ter estimativas muito obsoletas em particular (?).

Acabei de fazer este teste e dei alguns resultados:

analyze assembly_prods; -- Doing an ANLYZE to give pg_stats every help.

select 'count(*) distinct' as method,
        count(*) as count
from (select distinct assembly_id 
      from assembly_prods) d 
union all
select 'n_distinct from pg_stats' as method,
        n_distinct as count
from pg_stats 
where tablename  = 'assembly_prods' and
      attname    = 'assembly_id';

Os resultados:

method                      count
count(*) distinct           28088
n_distinct from pg_stats    13805

Isso é apenas um fator de 2, mas eu pareço muito pior em meus dados. A ponto de não usar estimativas. Existe algo mais que eu possa tentar? Isso é algo que o PG 12 melhora?

Acompanhamento

Eu nunca tinha experimentado SET STATISTICSantes, porque há apenas tantas horas em um dia. Inspirado pela resposta de Laurenz, dei uma olhada rápida. Aqui está um comentário útil da documentação:

https://www.postgresql.org/docs/current/planner-stats.html

A quantidade de informações armazenadas pg_statisticpor ANALYZE, em particular o número máximo de entradas nas most_common_valsmatrizes e histogram_bounds para cada coluna, pode ser definida coluna por coluna usando o ALTER TABLE SET STATISTICScomando ou globalmente definindo a default_statistics_targetvariável de configuração. O limite padrão é atualmente 100 entradas. Aumentar o limite pode permitir que estimativas de planejador mais precisas sejam feitas, principalmente para colunas com distribuições de dados irregulares, ao preço de consumir mais espaço pg_statistice um pouco mais de tempo para calcular as estimativas. Por outro lado, um limite inferior pode ser suficiente para colunas com distribuições de dados simples.

Muitas vezes tenho tabelas com alguns valores comuns e muitos valores raros. Ou o contrário, então o limite certo dependerá. Para aqueles que não usaram SET STATISTICS, ele permite definir a taxa de amostragem como um número alvo de entradas. O padrão é 100, então 1000 deve ser uma fidelidade mais alta. Aqui está o que parece:

ALTER TABLE assembly_prods 
    ALTER COLUMN assembly_id
    SET STATISTICS 1000;

Você pode usar SET STATISTICSem uma tabela ou índice. Aqui está um artigo interessante sobre índices:

https://akorotkov.github.io/blog/2017/05/31/alter-index-weird/

Observe que a documentação atual lista os SET STATISTICSíndices.

Então, experimentei limites de 1, 10, 100, 1000 e 10.000 e obtive esses resultados de uma tabela com 467.767 linhas e 28.088 valores distintos:

Target   Estimate  Difference  Missing
     1   13,657    14,431      51%
    10   13,867    14,221      51%
   100   13,759    14,329      51%
 1,000   24,746     3,342      12%
10,000   28,088         0       0%

Obviamente, você não pode tirar conclusões gerais de um caso, mas SET STATISTICSparece muito útil e ficarei feliz em tê-lo em minha mente. Estou tentado a aumentar um pouco o alvo em geral, pois suspeito que isso ajudaria em muitos dos casos em nosso sistema.

postgresql distinct

1 respostas

  1. Best Answer

    Primeiro, uma observação: sua consulta poderia ser escrita de forma mais simples como

    SELECT count(DISTINCT assembly_id) FROM assembly_prods;

    Além disso, sua consulta de estatísticas está errada, pois n_disticttambém pode ser negativa. Você deve consultar:

    SELECT CASE WHEN s.n_distinct < 0
            THEN - s.n_distinct * t.reltuples
            ELSE s.n_distinct
       END AS n_distinct
FROM pg_class t
   JOIN pg_namespace n ON n.oid = t.relnamespace
   JOIN pg_stats s ON t.relname = s.tablename
                      AND n.nspname = s.schemaname
WHERE s.schemaname = 'public'
  AND s.tablename = 'assembly_prods'
  AND s.attname = 'assembly_id';

    Para uma consulta simples como essa, as estatísticas devem conter uma boa estimativa.

    Se as estimativas estiverem erradas, tente ANALYZEa mesa. Isso também corrigirá os resultados de uma nova TRUNCATEtabela d. TRUNCATEnão faz com que o PostgreSQL analise automaticamente a tabela (pode haver espaço para melhorias aqui).

    Se isso melhorar os resultados, veja que a tabela é analisada com mais frequência configurando

    ALTER TABLE assembly_prods SET (autovacuum_analyze_scale_factor = 0.05);

    Também é possível definir autovacuum_analyze_scale_factorcomo 0 e aumentar autovacuum_analyze_thresholdpara a taxa de alteração diária da tabela.

    Se ANALYZEpor si só não melhorar a estimativa, aumente o tamanho da amostra:

    ALTER TABLE assembly_prods ALTER assembly_id SET STATISTICS 1000;

    Um novo ANALYZEdeve agora produzir melhores estimativas.

    Obter boas n_distinctestimativas para consultas mais complicadas se torna cada vez mais difícil. Às vezes , estatísticas estendidas melhorarão consideravelmente a estimativa.

    Até onde sei, o PostgreSQL v12 não traz nenhuma melhoria nessa área.

    • 5

relate perguntas