🥇Profilatore di query PostgreSQL: come abbinare piano e query

Molti di coloro che già utilizzano explain.tensor.ru — il nostro servizio di visualizzazione dei piani PostgreSQL, potrebbero non essere a conoscenza di una delle sue super capacità: trasformare un pezzo difficile da leggere del log del server…

… in una query ben formattata con suggerimenti contestuali sui relativi nodi del piano:

In questa spiegazione della seconda parte del mio rapporto al PGConf.Russia 2020 racconterò come siamo riusciti a farlo.

Con il trascrittore della prima parte, dedicata ai problemi tipici di prestazioni delle query e alle relative soluzioni, è possibile consultare l'articolo «Ricette per query SQL malate».

Iniziamo a occuparci della colorazione — e coloreremo non il piano, che abbiamo già colorato, ma la query.

Ci è sembrato che una 'stesa' non formattata estratta dal log apparisse davvero brutta e quindi — poco pratica.

Soprattutto quando gli sviluppatori 'incollano' il corpo della query nel codice (è ovviamente un antipattern, ma succede) in un'unica riga. Terribile!

Facciamo in modo che venga disegnata in modo più bello.

E se riuscissimo a disegnarla bene, cioè a scomporre e ricomporre il corpo della query, allora potremmo anche 'attaccare' un suggerimento a ciascun oggetto di questa query — cosa stava accadendo nel punto corrispondente del piano.

Albero sintattico della query

Per fare ciò, la query deve essere prima scomposta.

Poiché il nostro nucleo del sistema funziona su NodeJS, abbiamo creato un modulo per esso, puoi trovarlo su GitHub. In realtà, si tratta di binding avanzati alle interiora del parser di PostgreSQL stesso. In altre parole, è stata semplicemente compilata in binario la grammatica e sono stati creati binding dal lato di NodeJS. Ci siamo basati su moduli di terzi — non c'è un grande segreto qui.

Passiamo il corpo della query alla nostra funzione — all'uscita otteniamo un albero sintattico scomposto sotto forma di oggetto JSON.

Ora possiamo percorrere questo albero all'indietro e ricostruire la query con gli spazi, i colori e il formato che desideriamo. No, non si può personalizzare, ma ci è sembrato che in questo modo sarebbe comodo.

Corrispondenza dei nodi della query e del piano

Ora vediamo come possiamo combinare il piano che abbiamo analizzato nel primo passaggio e la query che abbiamo analizzato nel secondo.

Prendiamo un semplice esempio: abbiamo una query che forma un CTE e legge da essa due volte. Genera un piano del genere.

CTE

Se lo guardiamo attentamente, fino alla versione 12 (o a partire da essa con la parola chiave MATERIALIZZATO) la formazione di CTE è una barriera incondizionata per il pianificatore..

Quindi, se vediamo da qualche parte nella query la generazione di CTE e da qualche parte nel piano un nodo CTE, allora questi nodi si "scontrano" chiaramente, possiamo immediatamente combinarli.

Il compito "con l'asterisco": i CTE possono essere annidati.

Possono essere annidati molto male e persino avere lo stesso nome. Ad esempio, puoi dentro CTE A creare CTE X, e allo stesso livello dentro CTE B creare di nuovo CTE X:

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

Nella corrispondenza devi comprendere questo. Capirlo "con gli occhi" — anche vedendo il piano, anche vedendo il corpo della query — è molto difficile. Se hai una generazione di CTE complessa, annidata, con query grandi — allora non è nemmeno consapevole.

UNIONE

Se nella nostra query c'è la parola chiave UNION [ALL] (operatore di unione di due set di risultati), allora nel piano corrisponde o a un nodo Aggiungi, oppure a qualche Unione Ricursiva.

Quello che "sta sopra" rispetto UNIONE è il primo discendente del nostro nodo, ciò che "sta sotto" — il secondo. Se tramite UNIONE abbiamo "incollato" più blocchi insieme, allora Aggiungi-il nodo sarà comunque solo uno, ma avrà non due, ma molti figli — in ordine come si presentano, rispettivamente:

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

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

Il compito "con l'asterisco": all'interno della generazione di un set di dati ricorsivo (WITH RECURSIVE) può anche esserci più di uno. UNIONE. Ma solo l'ultimo blocco dopo l'ultimo è sempre ricorsivo. UNIONETutto ciò che è sopra — è uno, ma diverso. UNIONE:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, qui finisce la generazione dello stato iniziale della ricorsione
UNION ALL
  (...) -- #3, solo questo blocco è ricorsivo e può contenere un riferimento a T
)
...

Anche questi esempi devono essere in grado di "essere analizzati". In questo esempio vediamo che UNIONE-i segmenti nella nostra query erano 3. Di conseguenza, uno UNIONE corrisponde a Aggiungi-nodo, e l'altro — Unione Ricursiva.

Lettura-scrittura dei dati

Tutto chiaro, ora sappiamo quale pezzo della query corrisponde a quale pezzo del piano. E in questi pezzi possiamo facilmente e senza sforzo trovare quegli oggetti che "vengono letti".

Dal punto di vista della query non sappiamo — se si tratta di una tabella o di un CTE, ma sono indicati dallo stesso nodo. IntervalloVar. E in termini di «lettura» — è anche un insieme piuttosto limitato di nodi:

Scansione Sequenziale su [tbl]
Scansione della Bitmap Heap su [tbl]
Indicizzazione [Solo] Scansione [All'indietro] utilizzando [idx] su [tbl]
Scanner CTE su [cte]
Inserisci/Aggiornamento/Elimina su [tbl]

Conosciamo la struttura del piano e della query, conosciamo la corrispondenza dei blocchi, conosciamo i nomi degli oggetti — facciamo un abbinamento univoco.

Ancora una volta il compito «con asterisco». Prendiamo la query, la eseguiamo, non abbiamo alias — abbiamo semplicemente letto due volte dalla stessa CTE.

Guardiamo nel piano — qual è il problema? Perché abbiamo un alias apparso? Non lo avevamo richiesto. Da dove viene questo «numerato»?

PostgreSQL lo aggiunge automaticamente. Bisogna semplicemente capire che proprio questo alias per noi, ai fini della corrispondenza con il piano, non ha alcun senso, è semplicemente aggiunto qui. Non dobbiamo prestare attenzione ad esso.

Secondo il compito «con asterisco»: se stiamo leggendo da una tabella partizionata, otterremo un nodo Aggiungi o Unisci Aggiungi, che consisterà in un gran numero di «figli», ognuno dei quali sarà un qualche tipo di Scan' dalla tabella-sezione: Scansione Sequenziale, Scansione della Bitmap Heap o Index Scan. Ma, in ogni caso, questi «figli» non saranno query complesse — così si possono distinguere questi nodi da Aggiungi nella UNIONE.

Comprendiamo anche questi nodi, li raccogliamo «in un unico mucchio» e diciamo: "tutto ciò che hai letto da megatable — è qui e giù per l'albero".

Nodi «semplici» per la ricezione dei dati

Scansione valori nel piano corrisponde a VALUES nella query.

Risultato — è una query senza DA come SELECT 1. Oppure quando hai un'espressione manifestamente falsa nel DOVE-blocco (allora appare l'attributo One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- o 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

Funzione Scan si mappano su SRF con lo stesso nome.

Ma qui le cose si complicano con le sottoquery — sfortunatamente, non sempre si trasformano in PianoIniziale/Piano di Sottoscrizione. A volte si trasformano in ... Join o ... Anti Join, specialmente quando scrivi qualcosa del tipo WHERE NOT EXISTS .... E là combinare non sempre si riesce — nel testo del piano non ci sono operatori corrispondenti ai nodi del piano.

Ancora una volta il compito «con asterisco»: diversi VALUES nella query. In questo caso, nel piano otterrai diversi nodi Scansione valori.

Distinguere l'uno dall'altro aiuteranno i suffissi «numerati» — viene aggiunto proprio nell'ordine in cui si trovano i blocchi corrispondenti dal basso verso l'alto. VALUESSembra che abbiamo esaminato tutto nella nostra query — rimane solo

Elaborazione dei dati

Ma qui è tutto semplice — nodi come Limit.

si mappano uno a uno sugli operatori corrispondenti nella query, se ci sono. Qui non ci sono «asterischi» e complessità. Limit, Ordina, Aggregare, FinestraAgg, Unique Le complessità sorgono quando vogliamo combinare

JOIN

tra loro. Non è sempre possibile, ma si può fare. JOIN tra loro. Non è sempre possibile, ma si può fare.

Dal punto di vista del parser della query, abbiamo un nodo UnisciEspressione, che ha esattamente due discendenti: sinistro e destro. Questo, rispettivamente, è ciò che è "sopra" il vostro JOIN e ciò che è "sotto" di esso nella query.

E dal punto di vista del piano, questi sono due discendenti di qualche * Ciclo/* Unisciti-nodo. Ciclo annidato, Join anti-hash,… — qualcosa del genere.

Utilizziamo una logica semplice: se abbiamo delle tabelle A e B, che "si uniscono" nel piano, allora nella query potrebbero essere posizionate o A-JOIN-B, oppure B-JOIN-A. Proviamo a combinarle così, proviamo a combinarle al contrario, e così via fino a quando queste coppie non finiscono.

Prendiamo il nostro albero sintattico, prendiamo il nostro piano, diamo un'occhiata… non sembrano simili!

Ridisegniamo in forma di grafi — oh, adesso inizia a sembrare qualcosa!

Facciamo attenzione che abbiamo nodi che hanno contemporaneamente figli B e C — non ci interessa in quale ordine. Combinamoli e capovolgiamo l’immagine del nodo.

Diamo un'altra occhiata. Ora abbiamo nodi con figli A e coppie (B + C) — combiniamo anche quelli.

Ottimo! Risultato, che abbiamo combinato con successo questi due JOIN dalla query con i nodi del piano.

Purtroppo, questa operazione non è sempre risolvibile.

Ad esempio, se nella query abbiamo A JOIN B JOIN C, e nel piano i nodi "estremi" A e C si sono uniti prima. E nella query non abbiamo un operatore del genere, non abbiamo nulla da evidenziare, nulla a cui legare il suggerimento. Lo stesso vale per la "virgola", quando scrivete A, B.

Ma, nella maggior parte dei casi, quasi tutti i nodi possono essere "slegati" e ottenere un profiling del tempo a sinistra — letteralmente, come in Google Chrome, quando analizzate il codice Javascript. Vedi quanto tempo ogni riga e ogni operatore "sono stati eseguiti".

E per rendere tutto ciò più facile da utilizzare, abbiamo creato uno storage un archivio, dove puoi salvare e poi trovare i tuoi piani insieme alle query associate o condividere un link con qualcuno.