Clever Geek Handbook

PostgreSQL Query Profiler: Como combinar plano e consulta

Muitos dos que já usam o explain.tensor.ru - nosso serviço de visualização de planos para PostgreSQL, podem não estar cientes de uma de suas super-habilidades - transformar uma parte difícil de ler de um log de servidor ...





... em uma consulta bem projetada com dicas contextuais para os nós do plano correspondente:





Nesta transcrição da segunda parte da minha palestra no PGConf.Russia 2020, vou contar como conseguimos fazer isso.

A transcrição da primeira parte, que trata de problemas típicos de desempenho de consultas e suas soluções, pode ser encontrada no artigo "Receitas para consultas SQL em dificuldades" .




Primeiro vamos pintar - e não vamos mais pintar o plano, já pintamos, já temos bonito e compreensível, mas um pedido.



Pareceu-nos que a consulta puxada do log com uma "folha" não formatada parece muito feia e, portanto, desconfortável.





Especialmente quando os desenvolvedores no código "colam" o corpo da solicitação (isso é, claro, um antipadrão, mas acontece) em uma linha. Horror!



Vamos desenhá-lo de alguma forma mais bonita.





E se pudermos desenhá-lo lindamente, ou seja, desmontar e remontar o corpo da solicitação, então podemos anexar uma dica a cada objeto desta solicitação - o que aconteceu no ponto correspondente do plano.



Árvore de consulta de sintaxe



Para fazer isso, a solicitação deve primeiro ser analisada.





Como o núcleo do nosso sistema está rodando em NodeJS , nós criamos módulos para ele, você pode encontrar no GitHub . Na verdade, esses são "vínculos" estendidos para os componentes internos do próprio analisador PostgreSQL. Ou seja, a gramática é simplesmente compilada em binário e as ligações são feitas a partir do lado do NodeJS. Pegamos os módulos de outras pessoas como base - não há grande segredo aqui.



Alimentamos o corpo da solicitação para a entrada de nossa função - na saída, obtemos uma árvore de sintaxe analisada na forma de um objeto JSON.





Agora você pode passar por esta árvore na direção oposta e coletar a solicitação com os recuos, coloração, formatação que desejamos. Não, não é configurável, mas pareceu-nos que seria conveniente.





Consulta de mapeamento e nós do plano



Agora vamos ver como podemos combinar o plano que analisamos na primeira etapa e a consulta que analisamos na segunda.



Vamos dar um exemplo simples - temos uma solicitação que gera uma CTE e a lê duas vezes. Ele gera tal plano.





CTE



Se você olhar com atenção, antes da 12ª versão (ou a partir dela com a palavra-chave MATERIALIZED), a formação do CTE é uma barreira absoluta para o planejador .





Isso significa que se virmos a geração de CTEs em algum lugar da solicitação e em algum lugar do plano um nó CTE, então esses nós definitivamente "lutam" entre si, podemos combiná-los imediatamente.



Problema de asterisco : CTEs podem ser aninhados.



Existem muito mal aninhados e até os mesmos nomes. Por exemplo, você pode CTE Afazer isso por dentro CTE Xe CTE Bfazê-lo novamente no mesmo nível por dentro CTE X: 



WITH A AS (
  WITH X AS (...)
  SELECT ...
)
, B AS (
  WITH X AS (...)
  SELECT ...
)
...


Você deve entender isso ao comparar. É muito difícil entender isso com “olhos” - mesmo vendo o plano, mesmo vendo o corpo da solicitação. Se a sua geração CTE for complexa, aninhada, as solicitações são grandes - então ela é completamente inconsciente.



UNIÃO



Se tivermos uma palavra-chave em nossa consulta UNION [ALL](o operador de juntar duas seleções), então um nó Appendou alguém corresponde a ele no plano Recursive Union.





O que "acima" está acima UNIONé o primeiro filho de nosso nó, o que está "abaixo" é o segundo. Se UNIONvários blocos forem "colados" através de nós ao mesmo tempo, Appendainda haverá apenas um nó, mas não terá dois filhos, mas muitos - na ordem em que avançam, respectivamente: 



  (...) -- #1
UNION ALL
  (...) -- #2
UNION ALL
  (...) -- #3


Append
  -> ... #1
  -> ... #2
  -> ... #3


Problema "com um asterisco" : dentro da geração de uma seleção recursiva ( WITH RECURSIVE) também pode haver mais de um UNION. Mas apenas o último bloco após o último é sempre recursivo UNION. Tudo acima é um, mas diferente UNION: 



WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2,      
UNION ALL
  (...) -- #3,          T
)
...


Você também precisa ser capaz de “colar” esses exemplos. Neste exemplo, vemos que UNIONhavia 3 segmentos em nossa solicitação. Consequentemente, um UNION corresponde a um Append-nó e o outro corresponde a Recursive Union.





Dados de leitura e gravação



É isso, a gente espalhou, agora sabemos qual parte do pedido corresponde a qual parte do plano. E nessas peças podemos encontrar facilmente e naturalmente os objetos que são "legíveis".



Do ponto de vista da consulta, não sabemos se se trata de uma tabela ou CTE, mas são denotados pelo mesmo nó RangeVar. E em termos de "legível" - este também é um conjunto bastante limitado de nós:



  • Seq Scan on [tbl]
  • Bitmap Heap Scan on [tbl]
  • Index [Only] Scan [Backward] using [idx] on [tbl]
  • CTE Scan on [cte]
  • Insert/Update/Delete on [tbl]


Conhecemos a estrutura do plano e da consulta, sabemos a correspondência dos blocos, sabemos os nomes dos objetos - fazemos uma comparação inequívoca.





Novamente, um problema de asterisco . Pegamos a solicitação, executamos, não temos nenhum apelido - apenas lemos duas vezes de um CTE.





Nós olhamos para o plano - qual é o problema? Por que nosso apelido foi divulgado? Nós não pedimos. Onde ele conseguiu essa "placa de carro"?



PostgreSQL adiciona ele mesmo. Você só precisa entender que esse apelido não faz nenhum sentido para nós para fins de comparação com o plano, ele é simplesmente adicionado aqui. Não vamos prestar atenção nele.



A segunda tarefa é "com um asterisco" : se estivermos lendo de uma tabela particionada, obteremos um nó AppendouMerge Append, Que será composto por um grande número de "crianças", e cada um dos quais é de alguma forma Scan'th da seção da tabela: Seq Scan, Bitmap Heap Scanou Index Scan. Mas, em qualquer caso, esses "filhos" não serão consultas complexas - é assim que esses nós podem ser diferenciados de Appendquando UNION.





Também entendemos esses nós, os reunimos "em uma pilha" e dizemos: " tudo o que você lê na megatela está aqui e embaixo da árvore ".



Nós "simples" para receber dados







Values Scanem correspondências de plano VALUESno pedido.



Result- este é um pedido sem FROMgosto SELECT 1. Ou quando você tem uma expressão propositalmente falsa no WHEREbloco (então o atributo ocorre One-Time Filter): 



EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; --  0 = 1


Result  (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
  One-Time Filter: false


Function Scan"Mapear" para o SRF de mesmo nome.



Mas com consultas aninhadas, tudo é mais complicado - infelizmente, elas nem sempre se transformam em InitPlan/ SubPlan. Às vezes, eles se transformam em ... Joinou ... Anti Join, especialmente quando você escreve algo assim WHERE NOT EXISTS .... E nem sempre é possível combinar lá - não há operadores correspondentes aos nós do plano no texto do plano.



Novamente, uma tarefa com um asterisco : vários VALUESna solicitação. Neste caso, e no plano, você receberá vários nós Values Scan.





Sufixos "numerados" ajudarão a distingui-los uns dos outros - é adicionado exatamente na ordem de localização dos VALUESblocos correspondentes ao longo da solicitação de cima para baixo.



Processamento de dados



Parece que tudo em nosso pedido foi resolvido - havia apenas um restante Limit.





Mas tudo é simples - como os nós Limit, Sort, Aggregate, WindowAgg, Unique"mapyatsya" one-to-one com as declarações correspondentes no pedido, se eles estão lá. Não existem "estrelas" e nem dificuldades.





JUNTE-SE



As dificuldades surgem quando queremos combinar JOINuns com os outros. Isso nem sempre é feito, mas você pode.





Do ponto de vista do analisador de solicitação, temos um nó JoinExprque possui exatamente dois filhos - esquerdo e direito. Isso, respectivamente, é o que está "acima" de seu JOIN e o que está "abaixo" dele na solicitação é escrito.



E do ponto de vista do plano, esses são dois descendentes de algum nó * Loop/ * Join. Nested Loop, Hash Anti Join... - isso é alguma coisa.



Vamos usar uma lógica simples: se temos as placas A e B que se "juntam" no plano, então na solicitação elas podem ser localizadas A-JOIN-Bou B-JOIN-A. Vamos tentar combinar dessa forma, tentar combinar ao contrário, e assim por diante até que esses pares acabem.



Pegue nossa árvore de sintaxe, pegue nosso contorno, olhe para eles ... não assim!





Vamos redesenhar na forma de gráficos - oh, já se tornou algo parecido com algo!





Vamos notar que temos nós que têm filhos B e C ao mesmo tempo - não nos importamos em qual ordem. Vamos combiná-los e virar a imagem do nó.





Vamos ver de novo. Agora temos nós com filhos A e pares (B + C) - compatíveis com eles também.





Excelente! Acontece que JOINcombinamos com sucesso esses dois da consulta com os nós do plano.



Infelizmente, essa tarefa nem sempre é resolvida.





Por exemplo, se na consulta A JOIN B JOIN C, mas no plano, os nós "extremos" A e C foram conectados em primeiro lugar E na consulta não existe tal operador, não temos nada a destacar, não há nada para vincular a dica. É o mesmo com a "vírgula" quando você escreve A, B.



Mas, na maioria dos casos, quase todos os nós podem ser “desamarrados” e você obtém esse perfil à esquerda no tempo - literalmente, como no Google Chrome, quando você analisa o código JavaScript. Você pode ver por quanto tempo cada linha e cada instrução foram "executadas".





E para tornar mais conveniente para você usar tudo isso, fizemos um armazenamento de arquivo , onde você pode salvar e depois encontrar seus planos junto com as solicitações associadas ou compartilhar um link com alguém.



Se você só precisa colocar uma consulta ilegível em uma forma adequada, use nosso "normalizador" .





More articles:


All Articles