Molti che lo stanno già utilizzando - il nostro servizio di visualizzazione del piano PostgreSQL potrebbe non essere a conoscenza di uno dei suoi superpoteri: trasformare una parte difficile da leggere del registro del server...
... in una query dal design accattivante con suggerimenti contestuali per i nodi del piano corrispondenti:
In questa trascrizione della seconda parte del suo Ti dirò come siamo riusciti a farlo.
La trascrizione della prima parte, dedicata ai tipici problemi di performance delle query e alle relative soluzioni, è reperibile nell'articolo .
Per prima cosa iniziamo a colorare - e non coloreremo più il piano, lo abbiamo già colorato, lo abbiamo già bello e comprensibile, ma una richiesta.
Ci è sembrato che con un “foglio” così non formattato la richiesta estratta dal log risultasse molto brutta e quindi scomoda.
Soprattutto quando gli sviluppatori “incollano” il corpo della richiesta nel codice (questo è, ovviamente, un antipattern, ma succede) in una riga. Orribile!
Disegniamolo in qualche modo in modo più bello.
E se riusciamo a disegnarlo magnificamente, cioè a smontare e rimontare il corpo della richiesta, allora possiamo "allegare" un suggerimento a ciascun oggetto di questa richiesta - cosa è successo nel punto corrispondente del piano.
Albero della sintassi delle query
Per fare ciò, la richiesta deve prima essere analizzata.
Perché abbiamo , quindi abbiamo creato un modulo per questo, puoi . In realtà, questi sono “legami” estesi alle parti interne del parser PostgreSQL stesso. Cioè, la grammatica è semplicemente compilata in formato binario e i collegamenti vengono effettuati da NodeJS. Abbiamo preso come base i moduli di altre persone: qui non ci sono grandi segreti.
Diamo il corpo della richiesta come input alla nostra funzione: in output otteniamo un albero della sintassi analizzato sotto forma di un oggetto JSON.
Ora possiamo percorrere questo albero nella direzione opposta e assemblare una richiesta con i rientri, la colorazione e la formattazione che desideriamo. No, non è personalizzabile, ma ci è sembrato conveniente.
Mappatura dei nodi delle query e del piano
Vediamo ora come possiamo combinare il piano che abbiamo analizzato nel primo passaggio e la query che abbiamo analizzato nel secondo.
Facciamo un semplice esempio: abbiamo una query che genera un CTE e lo legge due volte. Genera un piano del genere.
CTE
Se lo guardi bene, fino alla versione 12 (o partendo da essa con la parola chiave MATERIALIZED) formazione .
Ciò significa che se vediamo una generazione CTE da qualche parte nella richiesta e un nodo da qualche parte nel piano CTE, quindi questi nodi “combattono” definitivamente tra loro, possiamo immediatamente combinarli.
Problema con un asterisco: Le CTE possono essere nidificate.
Ce ne sono di molto poco annidati e anche con lo stesso nome. Ad esempio, puoi dentro CTE A сделать CTE X, e allo stesso livello all'interno CTE B fallo ancora CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...Quando confronti, devi capirlo. Capirlo “con gli occhi” – anche vedendo il piano, anche vedendo il corpo della richiesta – è molto difficile. Se la tua generazione di CTE è complessa, annidata e le richieste sono grandi, allora è completamente inconscia.
UNION
Se abbiamo una parola chiave nella query UNION [ALL] (operatore di unione di due campioni), allora nel piano corrisponde ad uno qualsiasi dei nodi Append, o qualche Recursive Union.
Ciò che è "sopra" sopra UNION - questo è il primo discendente del nostro nodo, che è “sotto” - il secondo. Se attraverso UNION quindi abbiamo più blocchi “incollati” contemporaneamente Append-ci sarà ancora un solo nodo, ma non avrà due, ma molti figli - nell'ordine in cui vanno, rispettivamente:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #1
-> ... #2
-> ... #3
Problema con un asterisco: all'interno della generazione del campionamento ricorsivo (WITH RECURSIVE) possono essere anche più di uno UNION. Ma solo l'ultimo blocco dopo l'ultimo è sempre ricorsivo UNION. Tutto sopra è uno, ma diverso UNION:
WITH RECURSIVE T AS(
(...) -- #1
UNION ALL
(...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
(...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...
È inoltre necessario essere in grado di “mettere in risalto” tali esempi. In questo esempio lo vediamo UNION-c'erano 3 segmenti nella nostra richiesta. Di conseguenza, uno UNION fiammiferi Append-nodo, e all'altro - Recursive Union.
Lettura-scrittura dei dati
Tutto è predisposto, ora sappiamo quale pezzo della richiesta corrisponde a quale pezzo del piano. E in questi pezzi possiamo ritrovare con facilità e naturalezza quegli oggetti “leggibili”.
Dal punto di vista delle query non sappiamo se si tratta di una tabella o di una CTE, ma sono designate dallo stesso nodo RangeVar. E in termini di “leggibilità”, anche questo è un insieme di nodi abbastanza limitato:
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]
Conosciamo la struttura del piano e della query, conosciamo la corrispondenza dei blocchi, conosciamo i nomi degli oggetti: facciamo un confronto uno a uno.
Ancora compito "con un asterisco". Prendiamo la richiesta, la eseguiamo, non abbiamo alias: la leggiamo semplicemente due volte dallo stesso CTE.
Guardiamo il piano: qual è il problema? Perché avevamo uno pseudonimo? Non l'abbiamo ordinato. Dove prende un tale "numero numero"?
PostgreSQL lo aggiunge da solo. Devi solo capirlo proprio un alias del genere per noi, ai fini del confronto con il piano, non ha alcun senso, viene semplicemente aggiunto qui. Non prestiamo attenzione a lui.
La seconda compito "con un asterisco": se stiamo leggendo da una tabella partizionata, otterremo un nodo Append o Merge Append, che sarà composto da un gran numero di “bambini”, e ognuno dei quali lo sarà in qualche modo Scan'om dalla sezione tabella: Seq Scan, Bitmap Heap Scan o Index Scan. Ma, in ogni caso, questi "figli" non saranno query complesse: è così che è possibile distinguere questi nodi Append a UNION.
Comprendiamo anche tali nodi, li raccogliamo “in una pila” e diciamo: “tutto quello che leggi da megatable è qui e sotto l'albero".
Nodi di ricezione dati "semplici".
Values Scan corrisponde in pianta VALUES nella richiesta.
Result è una richiesta senza FROM come SELECT 1. O quando hai un'espressione deliberatamente falsa WHERE-block (quindi viene visualizzato l'attributo 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 “mappa” alle SRF con lo stesso nome.
Ma con le query nidificate tutto è più complicato: sfortunatamente non sempre si trasformano in InitPlan/SubPlan. A volte si trasformano in ... Join o ... Anti Join, soprattutto quando scrivi qualcosa del genere WHERE NOT EXISTS .... E qui non è sempre possibile combinarli: nel testo del piano non ci sono operatori corrispondenti ai nodi del piano.
Ancora compito "con un asterisco": Alcuni VALUES nella richiesta. In questo caso e nel piano otterrai diversi nodi Values Scan.
I suffissi "numerati" aiuteranno a distinguerli l'uno dall'altro: vengono aggiunti esattamente nell'ordine in cui si trovano quelli corrispondenti VALUES-blocchi lungo la richiesta dall'alto verso il basso.
Elaborazione dati
Sembra che tutto nella nostra richiesta sia stato risolto: tutto ciò che resta lo è Limit.
Ma qui tutto è semplice: nodi come Limit, Sort, Aggregate, WindowAgg, Unique “mappare” uno a uno gli operatori corrispondenti nella richiesta, se presenti. Non ci sono “stelle” o difficoltà qui.
ISCRIVITI
Le difficoltà sorgono quando vogliamo unire JOIN tra loro. Questo non è sempre possibile, ma è possibile.
Dal punto di vista del parser della query, abbiamo un nodo JoinExpr, che ha esattamente due figli: sinistro e destro. Questo, quindi, è ciò che è “sopra” il tuo JOIN e ciò che è scritto “sotto” nella richiesta.
E dal punto di vista del piano, questi sono due discendenti di alcuni * Loop/* Join-nodo. Nested Loop, Hash Anti Join,... - qualcosa del genere.
Usiamo una logica semplice: se abbiamo le tabelle A e B che nella planimetria si “uniscono” tra loro, allora nella richiesta potrebbero essere posizionate indifferentemente A-JOIN-BO B-JOIN-A. Proviamo a combinare in questo modo, proviamo a combinare nel contrario e così via finché non esauriamo tali coppie.
Prendiamo il nostro albero della sintassi, prendiamo il nostro piano, guardiamoli... non sono simili!
Ridisegniamolo sotto forma di grafici: oh, sembra già qualcosa!
Notiamo che abbiamo nodi che hanno contemporaneamente figli B e C: non ci interessa in quale ordine. Combiniamoli e capovolgiamo l'immagine del nodo.
Guardiamo di nuovo. Ora abbiamo nodi con figli A e coppie (B + C) - compatibili anche con loro.
Grande! Si scopre che noi siamo questi due JOIN dalla richiesta con i nodi del piano sono stati combinati con successo.
Ahimè, questo problema non è sempre risolto.
Ad esempio, se in una richiesta A JOIN B JOIN C, e nel piano, prima di tutto, erano collegati i nodi “esterni” A e C. Ma non esiste un operatore del genere nella richiesta, non abbiamo nulla da evidenziare, nulla a cui allegare un suggerimento. È lo stesso con la "virgola" quando scrivi A, B.
Ma, nella maggior parte dei casi, quasi tutti i nodi possono essere "sciolti" e questo tipo di profilazione può essere ottenuta in tempo a sinistra, letteralmente, come in Google Chrome quando si analizza il codice JavaScript. Puoi vedere quanto tempo impiega ogni riga e ogni istruzione per essere "eseguita".
E per rendere più conveniente l'utilizzo di tutto questo, abbiamo creato spazio di archiviazione , dove puoi salvare e successivamente trovare i tuoi piani insieme alle richieste associate o condividere il collegamento con qualcuno.
Se hai solo bisogno di portare una query illeggibile in una forma adeguata, usa .
Fonte: habr.com