🥇Profiler de interogări PostgreSQL: cum să aliniezi planul și cererea

Multe dintre persoanele care folosesc deja explain.tensor.ru — serviciul nostru de vizualizare a planurilor PostgreSQL, posibil nu sunt conștiente de una dintre superputerile sale — transformarea unui segment de jurnal server greu de citit...

… într-o interogare frumos formatată, cu sugestii contextuale pentru nodurile relevante ale planului:

În această deschidere a celei de-a doua părți a prezentării mele de la PGConf.Russia 2020 vă voi povesti cum am reușit să facem acest lucru.

Cu transcrierea primei părți, dedicată problemelor de performanță tipice ale interogărilor și soluțiilor lor, se poate consulta în articolul „Rețete pentru interogările SQL bolnave”.

Mai întâi să ne ocupăm de colorare — și vom colora deja nu planul, pe care l-am colorat deja, ci interogarea.

Ni s-a părut că așa, cu un „cearsaf” neformatat, interogarea extrasă din jurnal arată foarte urât și, prin urmare — incomod.

Mai ales când dezvoltatorii lipesc corpul interogării (aceasta, desigur, este un antipattern, dar se întâmplă) într-o singură linie. Groaznic!

Hai să desenăm asta mai frumos.

Iar dacă putem să-l desenăm frumos, adică să descompunem și să reconstruiem corpul interogării, atunci mai putem „atașa” sugestii la fiecare obiect al acestei interogări — despre ce s-a întâmplat în punctul corespunzător al planului.

Arborele sintactic al interogării

Pentru a face acest lucru, interogarea trebuie mai întâi descompusă.

Deoarece, avem nucleul sistemului care funcționează pe NodeJS, am creat un mic modul pentru el, îl puteți găsi pe GitHub. De fapt, acesta este un binding extins către interiorul parser-ului PostgreSQL. Adică este pur și simplu o gramatică compilată binar și binding-uri pentru NodeJS. Ne-am bazat pe modulele altora — nu este nicio mare taină aici.

Furnizăm corpul interogării ca intrare pentru funcția noastră — la ieșire obținem un arbore sintactic descompus sub formă de obiect JSON.

Acum, putem parcurge acest arbore înapoi și reconstrui interogarea cu indentările, colorarea, formatul dorit. Nu, nu este configurabil, dar ne-a părut că așa va fi convenabil.

Asocierea nodurilor interogării și planului

Acum să vedem cum putem îmbina planul, pe care l-am descompus în primul pas, și interogarea, pe care am descompus-o în al doilea.

Să luăm un exemplu simplu – avem o interogare care formează un CTE și îl citește de două ori. Acesta generează un astfel de plan.

CTE

Dacă ne uităm atent la el, până la versiunea 12 (sau începând cu ea, cu cuvântul cheie MATERIALIZED) formarea CTE este un obstacol necondiționat pentru planificator.

Așadar, dacă vedem undeva în interogare generarea CTE și undeva în plan un nod CTE, atunci aceste noduri sunt cu siguranță „împreunate”, putem imediat să le combinăm.

Sarcina „cu stea”: CTE-urile pot fi imbricate.

Există unele foarte prost imbricate, și chiar cu același nume. De exemplu, poți în interiorul CTE A să faci CTE X, și la același nivel în interior CTE B să faci din nou CTE X:

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

Când faci corespondența trebuie să înțelegi asta. A înțelege asta „cu ochii” – chiar și văzând planul, chiar și văzând corpul interogării – este foarte greu. Dacă ai o generare CTE complexă, imbricată, interogările fiind mari – atunci nici măcar nu este conștient.

UNION

Dacă avem în interogare cuvântul cheie UNION [ALL] (operator care leagă două selecții), atunci în plan îi corespunde fie un nod Adaugă, fie o oarecare Uniune Recursivă.

Ce este „deasupra” de UNION – acesta este primul copil al nodului nostru, iar „sub” – al doilea. Dacă prin UNION avem „lipite” mai multe blocuri simultan, atunci Adaugă-nodul va fi totuși unul singur, dar copiii lui nu vor fi doi, ci mulți – în ordinea în care apar:

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

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

Sarcina „cu stea”: în interiorul generării selecției recursive (WITH RECURSIVE) poate fi de asemenea mai mult de unul. UNION. Dar întotdeauna recursiv este doar ultimul bloc după ultimul UNION. Tot ce este deasupra – este unul, dar altul UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, aici se încheie generarea stării inițiale a recursiei
UNION ALL
  (...) -- #3, doar acest bloc este recursiv și poate conține referințe la T
)
...

Astfel de exemple trebuie să le putem „desface”. În acest exemplu vedem că UNION-segmente din interogarea noastră au fost 3. Așadar, unui UNION corespunde Adaugă-nod, iar altuia – Uniune Recursivă.

Citire-scriere de date

Toate, am desfăcut, acum știm ce bucată din interogare corespunde cărei bucăți din plan. Și în aceste bucăți putem găsi cu ușurință obiectele care sunt „citite”.

Din punctul de vedere al interogării nu știm – este vorba despre o tabelă sau un CTE, dar se marchează cu același nod. Varietate. Și în planul „lecturii” - acesta este tot un set destul de limitat de noduri:

Scan Secvențial pe [tbl]
Scanare Bitmap Heap pe [tbl]
Index [Only] Scan [Backward] using [idx] on [tbl]
Scan CTE pe [cte]
Introduce/Actualizare/Delete pe [tbl]

Cunoaștem structura planului și a interogării, corespondența blocurilor, cunoaștem numele obiectelor - facem o asociere clară.

Din nou sarcina „cu stea”. Luăm interogarea, o executăm, nu avem aliasuri - am citit pur și simplu de două ori din aceeași CTE.

Ne uităm în plan - ce s-a întâmplat? De ce ne-a apărut aliasul? Nu l-am solicitat. De unde este acest „numerotat”?

PostgreSQL îl adaugă singur. Trebuie doar să înțelegem că acest alias nu are niciun sens pentru noi în scopul asocierii cu planul, este pur și simplu adăugat aici. Să nu îi acordăm atenție.

Al doilea sarcina „cu stea”: dacă citim dintr-o tabelă secționată, vom obține un nod Adaugă sau Fuzionare Adăugați, care va fi format dintr-un număr mare de „copii”, fiecare dintre aceștia va fi un fel de Scandin tabela-secțiune: Scan Secvențial, Scanare Bitmap Heap sau Index Scan. Dar, în orice caz, acești „copii” nu vor fi interogări complexe - așa că aceste noduri pot fi diferențiate de Adaugă în UNION.

Aceste noduri le înțelegem de asemenea, le adunăm „într-un loc” și spunem: "tot ce ai citit din megatable - este aici și în jos pe arbore".

Nodurile „simple” de obținere a datelor

Scan de valori corespunzătoare în plan VALUES în interogare.

Rezultat - aceasta este o interogare fără FROM pare că SELECT 1. Sau atunci când aveți o expresie evident falsă în WHERE-bloc (atunci apare atributul One-Time Filter):

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

Scan Funcție sunt „mapate” pe SRF cu același nume.

Dar cu interogările încorporate este mai complicat - din păcate, nu se transformă întotdeauna în InitPlan/SubPlan. Uneori se transformă în ... Join sau ... Anti Join, mai ales când scrieți ceva de genul WHERE NOT EXISTS .... Și acolo combinarea nu este întotdeauna posibilă - în textul planului nu există operatori corespunzători nodurilor planului.

Din nou sarcina „cu stea”: mai multe VALUES în interogare. În acest caz, și în plan veți obține mai multe noduri Scan de valori.

Diferentierea lor unul de celălalt va fi ajutată de sufixele „numerotate” - acesta este adăugat exact în ordinea găsirii corespondențelor VALUES-blocurilor pe parcursul interogării de sus în jos.

Procesarea datelor

Se pare că am analizat tot în interogarea noastră - a mai rămas doar Limit.

Dar aici este simplu - nodurile precum Limit, Sortează, Agregat, WindowAgg, Unic se „mapă” unul-la-unu pe operatorii corespunzători din interogare, dacă există. Aici nu sunt „stele” și dificultăți.

JOIN

Dificultățile apar atunci când vrem să le combinăm JOIN între ele. Acest lucru nu este întotdeauna posibil, dar se poate.

Din perspectiva parserului de cereri, avem un nod JoinExpr, care are exact doi descendenti — stângul și dreptul. Aceasta este, respectiv, ceea ce este "deasupra" JOIN-ului vostru și ceea ce este "sub" acesta în cerere.

Și din perspectiva planului, acestea sunt doi descendenti ai unui * Buclă/* Alătură-te-nod. Ciclul Încheiat, Antijoin Hash,… — cam așa ceva.

Să folosim o logică simplă: dacă avem tabelele A și B, care se "JOIN” între ele în plan, atunci în cerere ele ar putea fi plasate fie A-JOIN-B, sau B-JOIN-A. Să încercăm să le combinăm astfel, să încercăm să le combinăm invers, și așa mai departe, până când aceste perechi se termină.

Să luăm arborele nostru sintactic, să luăm planul nostru, să ne uităm la ele… nu seamănă!

Să redesenăm sub formă de grafuri — oh, acum pare că este ceva asemănător!

Să observăm din nou că avem noduri, care au simultan copii B și C — nu ne pasă în ce ordine. Să le combinăm și să întoarcem imaginea nodului.

Să ne uităm încă o dată. Acum avem noduri cu copii A și pereche (B + C) — să le combinăm și pe ele.

Excelent! Așadar, am combinat cu succes aceste două JOIN din cerere cu nodurile planului.

Din păcate, această sarcină nu se rezolvă întotdeauna.

De exemplu, dacă în cerere este A JOIN B JOIN C, iar în plan, în primul rând, s-au conectat nodurile "marginale" A și C. Iar în cerere nu există un astfel de operator, nu avem ce să evidențiem, nu este nimic la ce să atașăm sugestia. Același lucru se aplică cu "virgula", când scrieți A, B.

Dar, în majoritatea cazurilor, aproape toate nodurile pot fi "desfăcute" și obținute astfel un profilare pe stânga în funcție de timp — literalmente, ca în Google Chrome, când analizezi codul pe JavaScript. Vedeți cât timp a fost executată fiecare linie și fiecare operator.

Iar pentru a fi mai ușor de folosit, am realizat un depozit de arhivă, unde puteți salva și apoi găsi planurile dvs. împreună cu cererile asociate sau partaja un link cu cineva.