Início / dba / Perguntas / 342744
Accepted
Justin Lowen
Asked: 2024-10-02 23:51:11 +0800 CST

PostgreSQL - Estimativas de linha que levam a loops aninhados sendo selecionados com muita frequência, como melhorar as estimativas de linha

  • 772

Estou executando o PG10 (em breve para mudar para o PG13) e estou tendo problemas de desempenho com uma consulta específica. Também testei no PG13 e obtive resultados semelhantes. A maioria das junções nesta consulta maior são selecionadas para Nested Joins, mas parece que um Hash Join seria mais apropriado para a maioria.

Vejo que os primeiros nós unidos podem estar direcionando as estatísticas de junções posteriores, onde as estimativas de linha são bem baixas. As primeiras junções são as seguintes: https://explain.dalibo.com/plan/b5045eb7cffdf0dhDalibo Explica Visualização 

SELECT * from workflowable_resource where sample_type_id in (SELECT resource_id from resource where name = 'Test Order')

Embora essa consulta menor seja bem rápida, vejo muitos exemplos em que Nested Loop é selecionado incorretamente, onde um Hash Join pode ter um desempenho melhor. (A estimativa de linha é 1, ou próximo de 1, mas as linhas reais são ordens de magnitude maiores)

Quais são algumas técnicas gerais para melhorar estimativas de linha nesses casos? Tentei aumentar default_statistics_target para 10000 e analisar todas as tabelas novamente, mas não vi nenhuma melhoria nas estimativas de linha.

Na consulta maior, no banco de dados ampliado, há um nó tomando todo o tempo. Índice de varredura de loop aninhado Há uma varredura de índice na tabela, mas ela é realizada em um loop aninhado, pois o lado esquerdo é estimado em 1 linha, mas neste ponto está sendo executado com 178.000 linhas. Parece que erros de estimativas de linhas anteriores estão sendo levados adiante.

postgresql

2 respostas

  1. Best Answer

    Para a primeira consulta, ele não sabe qual resource_id específico está associado ao nome de 'Test Order' até que o planejamento termine. Então, ele não pode usar esse valor específico para estimar o número de linhas de workflowable_resource que ele encontrará. Então, em vez disso, ele tem que usar a estimativa genérica, que basicamente será #number of rows in workflowable_resource / n_distinct for sample_type_id. Não há muito que você possa fazer sobre isso com essa consulta, nenhuma quantidade de estatísticas adicionais vai ajudar você, pois a estimativa é genérica de qualquer maneira (a menos que n_distinct esteja muito errado). Você pode dividi-la em duas consultas, conectando a saída de uma no parâmetro da outra. Mas você tem que fazer isso manualmente, não há como instruir o PostgreSQL a executar a consulta interna e então replanejar a externa com base nos resultados. Além disso, provavelmente não importa, pois não vejo razão para pensar que uma estimativa melhor resultaria em um plano melhor.

    Para a consulta maior, talvez uma estimativa melhor levaria a um plano melhor, mas as informações fornecidas são inadequadas para especular. Não temos nem o plano real nem a consulta real.

    • 1

  2. O problema com sua consulta muito maior é bem diferente do que com a primeira, então estou adicionando uma segunda resposta para discutir esta.

    O primeiro lugar onde a seletividade está muito errada é aqui:

    ->  Hash Join  (cost=542.37..2099.91 rows=1 width=12) (actual time=0.340..20.327 rows=6576 loops=1)                                                                                                         
  ->  Seq Scan on three_echo kilo_five  (cost=0.00..1406.89 rows=57389 width=8) (actual time=0.020..8.036 rows=57395 loops=1)
  ->  Hash  (cost=540.67..540.67 rows=136 width=4) (actual time=0.111..0.121 rows=3 loops=1)

    Então ele pensa que das 60.000 linhas da varredura seq, apenas uma corresponderá a qualquer linha da tabela hash. Mas, em vez disso, 1/10 delas encontra um parceiro na tabela hash. Mesmo que a tabela hash seja 40 vezes menor do que ele pensa que será. Então, eu estou supondo que o filtro aplicado ao construir a tabela hash seletivamente extrai linhas que têm valores que são muito comuns na tabela seq-scanned (então esta parte na verdade é bastante semelhante ao problema na primeira consulta). Talvez você pudesse escrever uma consulta muito mais simples que incorporasse esta junção hash e nada mais, veja se o problema de seletividade ainda existe e, em seguida, explore as entradas pg_stats para as colunas relevantes das tabelas relevantes. Talvez você pudesse compartilhar a versão não ofuscada dessa consulta mais focada, pois a ofuscação realmente torna difícil raciocinar sobre o que está acontecendo.

    No entanto, não tenho certeza se esse problema de estimativa de linha é realmente o principal culpado pelo desempenho lento.

    Se olharmos para a linha que realmente leva todo o tempo:

    -> Index Scan using oscar_mike on echo_four kilo_golf (cost=0.42..1,427.16 rows=11 width=12) (actual time=2.775..3.445 rows=4 loops=178,773)
   Index Cond: (kilo_golf.delta_two = alpha_xray_charlie2.four_kilo)
   Buffers: shared hit=93,849,709 dirtied=1

    Este índice retorna 4*178773 linhas. Por que são necessários 93849709/4/178773 = 131 acessos de buffer para cada linha retornada? Isso é como 60 vezes mais acessos de buffer do que o que parece razoável para mim. Acho que o índice está cheio de tuplas mortas que ainda não foram removidas, ou talvez esteja terrivelmente fragmentado ou algo assim. Eu faria o VACUUM VERBOSE manualmente nesta tabela e veria se isso corrigia (ou melhorava substancialmente) o problema. Se não, eu REINDEXARIA este índice específico e veria se isso resolveu. Se nenhum deles funcionar, compartilhe conosco a saída do VACUUM VERBOSE.

    • 1

relate perguntas