Moitos que xa están a usar - É posible que o noso servizo de visualización do plan PostgreSQL non teña coñecemento dun dos seus superpoderes: converter unha peza difícil de ler do rexistro do servidor...
... nunha consulta ben deseñada con suxestións contextuais para os nodos do plano correspondentes:
Nesta transcrición da segunda parte da súa Vouvos contar como conseguimos facelo.
A transcrición da primeira parte, dedicada aos problemas típicos de rendemento das consultas e as súas solucións, pódese atopar no artigo .
En primeiro lugar, imos comezar a colorear - e xa non colorearemos o plano, xa o coloreamos, xa o temos bonito e comprensible, pero unha petición.
Pareceunos que cunha "folla" tan sen formato a solicitude tirada do rexistro parece moi fea e, polo tanto, incómoda.
Especialmente cando os desenvolvedores "pegan" o corpo da solicitude no código (isto é, por suposto, un antipatrón, pero ocorre) nunha soa liña. Horrible!
Imos debuxar isto dalgún xeito máis fermoso.
E se podemos debuxar isto moi ben, é dicir, desmontar e volver a montar o corpo da solicitude, entón podemos "anexar" unha pista a cada obxecto desta solicitude: o que pasou no punto correspondente do plano.
Árbore de sintaxe de consulta
Para iso, primeiro debe analizarse a solicitude.
Porque temos , entón fixemos un módulo para iso, podes . De feito, estes son "enlaces" estendidos aos internos do propio analizador PostgreSQL. É dicir, a gramática é simplemente compilada en binario e as ligazóns realízanse desde NodeJS. Tomamos como base os módulos doutras persoas: aquí non hai ningún gran segredo.
Alimentamos o corpo da solicitude como entrada para a nosa función; na saída obtemos unha árbore de sintaxe analizada en forma de obxecto JSON.
Agora podemos percorrer esta árbore na dirección oposta e montar unha solicitude coas sangrías, cor e formato que queremos. Non, isto non é personalizable, pero pareceunos que sería conveniente.
Consulta de mapas e nodos de planificación
Agora vexamos como podemos combinar o plan que analizamos no primeiro paso e a consulta que analizamos no segundo.
Poñamos un exemplo sinxelo: temos unha consulta que xera un CTE e o le dúas veces. El xera tal plan.
CTE
Se o miras con atención, ata a versión 12 (ou empezando dela coa palabra clave MATERIALIZED) formación .
Isto significa que se vemos unha xeración CTE nalgún lugar da solicitude e un nodo nalgún lugar do plan CTE, entón estes nós definitivamente "loitan" entre si, podemos combinalos inmediatamente.
Problema cun asterisco: os CTE pódense aniñar.
Hainos moi mal aniñados, e incluso co mesmo nome. Por exemplo, podes dentro CTE A facer CTE X, e ao mesmo nivel no interior CTE B facelo de novo CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...Ao comparar, debes entender isto. Entender isto "cos teus ollos" - mesmo ver o plan, mesmo ver o corpo da solicitude - é moi difícil. Se a túa xeración CTE é complexa, aniñada e as solicitudes son grandes, entón está completamente inconsciente.
UNIÓN
Se temos unha palabra clave na consulta UNION [ALL] (operador de unir dúas mostras), entón no plano correspóndese con ou ben un nodo Append, ou algúns Recursive Union.
O que está "enriba" arriba UNION - este é o primeiro descendente do noso nodo, que está "abaixo" - o segundo. Se a través UNION temos varios bloques "pegados" á vez, entón Append-aínda haberá só un nodo, pero non terá dous, senón moitos fillos - na orde na que van, respectivamente:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #1
-> ... #2
-> ... #3
Problema cun asterisco: xeración de mostraxe recursiva dentro (WITH RECURSIVE) tamén pode ser máis dun UNION. Pero só o último bloque despois do último é sempre recursivo UNION. Todo o anterior é un, pero diferente UNION:
WITH RECURSIVE T AS(
(...) -- #1
UNION ALL
(...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
(...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...
Tamén cómpre ser capaz de "salir" tales exemplos. Neste exemplo vemos que UNION-había 3 segmentos na nosa solicitude. En consecuencia, un UNION corresponde a Append-nodo, e ao outro - Recursive Union.
Datos de lectura-escritura
Todo está disposto, agora sabemos que parte da solicitude corresponde a que parte do plan. E nestas pezas podemos atopar de xeito sinxelo e natural aqueles obxectos que son "lexibles".
Dende o punto de vista da consulta, non sabemos se é unha táboa ou un CTE, pero están designados polo mesmo nodo RangeVar. E en termos de "lexibilidade", este tamén é un conxunto 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]
Coñecemos a estrutura do plano e a consulta, coñecemos a correspondencia dos bloques, coñecemos os nomes dos obxectos -facemos unha comparación un a un.
De novo tarefa "cun asterisco". Tomamos a solicitude, executámola, non temos ningún alias; só lemos dúas veces desde o mesmo CTE.
Miramos o plan: cal é o problema? Por que tiñamos un alias? Non o pedimos. De onde saca ese "número de número"?
PostgreSQL engádeo por si mesmo. Só tes que entender iso só ese alias para nós, a efectos de comparación co plan, non ten ningún sentido, simplemente engádese aquí. Non lle fagamos caso.
O segundo tarefa "cun asterisco": se estamos lendo desde unha táboa particionada, entón teremos un nodo Append ou Merge Append, que estará formado por un gran número de "nenos", e cada un dos cales será dalgún xeito Scan'om da sección da táboa: Seq Scan, Bitmap Heap Scan ou Index Scan. Pero, en calquera caso, estes "nenos" non serán consultas complexas; así é como se poden distinguir estes nós de Append en UNION.
Tamén entendemos tales nós, recólleos "nunha pila" e dicimos: "todo o que le de megatable está aquí e abaixo da árbore".
Nodos de recepción de datos "simples".
Values Scan corresponde no plano VALUES na solicitude.
Result é unha solicitude sen FROM como SELECT 1. Ou cando tes unha expresión deliberadamente falsa WHERE-block (aparece entón o atributo One-Time Filter):
EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1Result (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 "mapa" aos SRF do mesmo nome.
Pero coas consultas aniñadas todo é máis complicado; por desgraza, non sempre se converten InitPlan/SubPlan. Ás veces convértense en ... Join ou ... Anti Join, sobre todo cando escribes algo así WHERE NOT EXISTS .... E aquí non sempre é posible combinalos: no texto do plan non hai operadores correspondentes aos nodos do plan.
De novo tarefa "cun asterisco": algunhas VALUES na solicitude. Neste caso e no plano obterás varios nodos Values Scan.
Os sufixos "numerados" axudarán a distinguilos entre si - engádense exactamente na orde na que se atopan os correspondentes. VALUES-bloques ao longo da solicitude de arriba a abaixo.
Tratamento de datos
Parece que se solucionou todo a nosa solicitude; só queda Limit.
Pero aquí todo é sinxelo - tales nodos como Limit, Sort, Aggregate, WindowAgg, Unique “mapa” un a un aos operadores correspondentes na solicitude, se están alí. Non hai "estrelas" nin dificultades aquí.
Rexístrese se
As dificultades xorden cando queremos combinar JOIN entre eles. Isto non sempre é posible, pero é posible.
Desde o punto de vista do analizador de consultas, temos un nodo JoinExpr, que ten exactamente dous fillos: esquerda e dereita. Isto, polo tanto, é o que está "enriba" do teu JOIN e o que está escrito "debaixo" na solicitude.
E dende o punto de vista do plan, trátase de dous descendentes dalgúns * Loop/* Join-nodo. Nested Loop, Hash Anti Join,... - algo así.
Usemos unha lóxica sinxela: se temos táboas A e B que "se unen" entre si no plano, na solicitude poderían situarse ou A-JOIN-BOu B-JOIN-A. Tentemos combinar deste xeito, intentemos combinar ao revés, e así ata quedar sen este tipo de parellas.
Collemos a nosa árbore de sintaxe, collemos o noso plan, miralos... non se parecen!
Redebuxámolo en forma de gráficos, xa parece algo!
Teñamos en conta que temos nós que teñen fillos B e C ao mesmo tempo; non nos importa en que orde. Combinámolos e dámoslle voltas á imaxe do nodo.
Vexamos de novo. Agora temos nós con fillos A e pares (B + C), compatibles tamén con eles.
Genial! Resulta que somos estes dous JOIN a partir da solicitude cos nodos do plan combináronse con éxito.
Por desgraza, este problema non sempre se soluciona.
Por exemplo, se nunha solicitude A JOIN B JOIN C, e no plano, en primeiro lugar, conectáronse os nodos "exteriores" A e C. Pero non hai tal operador na solicitude, non temos nada que destacar, nada ao que engadir unha suxestión. Pasa o mesmo coa "coma" cando escribes A, B.
Pero, na maioría dos casos, case todos os nodos pódense "desatar" e podes obter este tipo de perfiles á esquerda a tempo, literalmente, como en Google Chrome cando analizas o código JavaScript. Podes ver canto tempo tardou cada liña e cada instrución en "executar".
E para que che sexa máis cómodo usar todo isto, fixemos almacenamento , onde podes gardar e máis tarde atopar os teus plans xunto coas solicitudes asociadas ou compartir a ligazón con alguén.
Se só precisa traer unha consulta ilexible nunha forma adecuada, use .
Fonte: www.habr.com