🥇PostgreSQL Query Profiler: Cum să potriviți un plan și o interogare

Mulți care folosesc deja explica.tensor.ru - este posibil ca serviciul nostru de vizualizare a planului PostgreSQL să nu fie conștient de una dintre superputerile sale - transformarea unei părți greu de citit din jurnalul serverului...

... într-o interogare frumos proiectată, cu indicii contextuale pentru nodurile de plan corespunzătoare:

În această transcriere a celei de-a doua părți a lui raport la PGConf.Russia 2020 Vă spun cum am reușit să facem asta.

Transcrierea primei părți, dedicată problemelor tipice de performanță a interogărilor și soluțiilor acestora, poate fi găsită în articol „Rețete pentru interogări SQL dificile”.

În primul rând, să începem să colorăm - și nu vom mai colora planul, l-am colorat deja, îl avem deja frumos și de înțeles, dar o cerere.

Ni s-a părut că, cu o astfel de „foaie” neformatată, cererea scoasă din jurnal arată foarte urât și, prin urmare, incomod.

Mai ales când dezvoltatorii „lipesc” corpul solicitării în cod (acesta este, desigur, un antimodel, dar se întâmplă) într-o singură linie. Oribil!

Să desenăm asta cumva mai frumos.

Și dacă putem desena acest lucru frumos, adică dezasamblam și punem la loc corpul cererii, atunci putem „atașa” un indiciu fiecărui obiect al acestei cereri - ceea ce s-a întâmplat în punctul corespunzător din plan.

Arborele de sintaxă a interogărilor

Pentru a face acest lucru, cererea trebuie mai întâi analizată.

Pentru că avem nucleul sistemului rulează pe NodeJS, apoi am creat un modul pentru el, poți găsiți-l pe GitHub. De fapt, acestea sunt „legături” extinse la elementele interne ale parserului PostgreSQL însuși. Adică, gramatica este pur și simplu compilată binar și se fac legături către ea din NodeJS. Am luat ca bază modulele altora - nu există un mare secret aici.

Alimentam corpul cererii ca intrare în funcția noastră - la ieșire obținem un arbore de sintaxă analizat sub forma unui obiect JSON.

Acum putem trece prin acest arbore în direcția opusă și asamblam o solicitare cu indentările, colorarea și formatarea pe care le dorim. Nu, acest lucru nu este personalizabil, dar ni s-a părut că ar fi convenabil.

Maparea nodurilor de interogare și plan

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

Să luăm un exemplu simplu - avem o interogare care generează un CTE și citește din el de două ori. El generează un astfel de plan.

CTE

Dacă îl priviți cu atenție, până la versiunea 12 (sau pornind de la ea cu cuvântul cheie MATERIALIZED) formare CTE este o barieră absolută pentru planificator.

Aceasta înseamnă că dacă vedem o generație CTE undeva în cerere și un nod undeva în plan CTE, atunci aceste noduri cu siguranță „se luptă” între ele, le putem combina imediat.

Problemă cu un asterisc: CTE-urile pot fi imbricate.

Există unele foarte prost imbricate și chiar unele cu același nume. De exemplu, poți înăuntru CTE A face CTE X, și la același nivel în interior CTE B Fă-o din nou CTE X:

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

Când comparați, trebuie să înțelegeți acest lucru. Înțelegerea acestui „cu ochii tăi” - chiar și a vedea planul, chiar și a vedea corpul cererii - este foarte dificilă. Dacă generația dvs. CTE este complexă, imbricată și există solicitări mari, atunci este complet inconștientă.

UNIUNE

Dacă avem un cuvânt cheie în interogare UNION [ALL] (operator de îmbinare a două mostre), apoi în plan corespunde fie unui nod Append, sau ceva Recursive Union.

Ceea ce este „sus” deasupra UNION - acesta este primul descendent al nodului nostru, care este „dedesubt” - al doilea. Dacă prin UNION atunci avem mai multe blocuri „lipite” deodată Append-va exista în continuare un singur nod, dar nu va avea doi, ci mulți copii - în ordinea în care merg, respectiv:

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

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

Problemă cu un asterisc: generarea eșantionării recursive în interiorul (WITH RECURSIVE) poate fi, de asemenea, mai mult de unul UNION. Dar numai ultimul bloc după ultimul este întotdeauna recursiv UNION. Totul de mai sus este unul, dar diferit UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
  (...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...

De asemenea, trebuie să fii capabil să „imiți” astfel de exemple. În acest exemplu vedem că UNION-au fost 3 segmente in cererea noastra. În consecință, unul UNION соответствует Append-nod, iar celuilalt - Recursive Union.

Date de citire-scriere

Totul este aranjat, acum știm care parte din cerere corespunde cărei piese din plan. Și în aceste piese putem găsi ușor și natural acele obiecte care sunt „lizibile”.

Din punct de vedere al interogării, nu știm dacă este un tabel sau un CTE, dar sunt desemnate de același nod RangeVar. Și în ceea ce privește „lizibilitatea”, acesta este, de asemenea, un set destul de limitat de noduri:

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]

Cunoaștem structura planului și interogarea, știm corespondența blocurilor, știm numele obiectelor - facem o comparație unu-la-unu.

Din nou sarcină „cu asterisc”. Luăm cererea, o executăm, nu avem alias - o citim de două ori de la același CTE.

Ne uităm la plan - care este problema? De ce am avut un alias? Nu noi l-am comandat. De unde obține un astfel de „număr număr”?

PostgreSQL îl adaugă singur. Trebuie doar să înțelegi asta doar un astfel de alias pentru noi, în scopul comparației cu planul, nu are niciun sens, se adaugă pur și simplu aici. Să nu-i dăm atenție.

Al doilea sarcină „cu asterisc”: dacă citim dintr-un tabel partiționat, atunci vom obține un nod Append sau Merge Append, care va fi formată dintr-un număr mare de „copii”, iar fiecare dintre acestea va fi cumva Scan'om din secțiunea tabel: Seq Scan, Bitmap Heap Scan sau Index Scan. Dar, în orice caz, acești „copii” nu vor fi interogări complexe - așa pot fi distinse aceste noduri de Append la UNION.

Înțelegem și astfel de noduri, le colectăm „într-o grămadă” și spunem: „tot ce citești din megatable este aici și în jos".

Noduri „simple” de primire a datelor

Values Scan corespunde în plan VALUES în cerere.

Result este o cerere fără FROM ca SELECT 1. Sau când ai o expresie în mod deliberat falsă WHERE-block (apare apoi atributul 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 „hartă” către SRF-urile cu același nume.

Dar cu interogări imbricate totul este mai complicat - din păcate, ele nu se transformă întotdeauna în InitPlan/SubPlan. Uneori se transformă în ... Join sau ... Anti Join, mai ales când scrii ceva de genul WHERE NOT EXISTS .... Și aici nu este întotdeauna posibil să le combinați - în textul planului nu există operatori corespunzători nodurilor planului.

Din nou sarcină „cu asterisc”: niste VALUES în cerere. În acest caz și în plan veți obține mai multe noduri Values Scan.

Sufixele „numerotate” vă vor ajuta să le distingeți unele de altele - sunt adăugate exact în ordinea în care sunt găsite cele corespunzătoare VALUES-blocuri de-a lungul cererii de sus în jos.

Procesarea datelor

Se pare că totul din solicitarea noastră a fost rezolvat - tot ce a mai rămas este Limit.

Dar aici totul este simplu - astfel de noduri ca Limit, Sort, Aggregate, WindowAgg, Unique „map” unu-la-unu cu operatorii corespunzători din cerere, dacă aceștia sunt acolo. Nu există „stele” sau dificultăți aici.

JOIN

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

Din punctul de vedere al parserului de interogări, avem un nod JoinExpr, care are exact doi copii - stânga și dreapta. În consecință, acesta este ceea ce este „deasupra” JOIN-ul tău și ceea ce este scris „dedesubt” în cerere.

Și din punct de vedere al planului, aceștia sunt doi descendenți ai unora * Loop/* Join-nodul. Nested Loop, Hash Anti Join,... - ceva de genul.

Să folosim o logică simplă: dacă avem tabelele A și B care se „unesc” între ele în plan, atunci în cerere ar putea fi localizate fie A-JOIN-BSau B-JOIN-A. Să încercăm să combinăm așa, să încercăm să combinăm invers și așa mai departe până nu rămânem fără astfel de perechi.

Să luăm arborele nostru de sintaxă, să ne luăm planul, să ne uităm la ele... nu sunt asemănătoare!

Să-l redesenăm sub formă de grafice - o, deja arată ca ceva!

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

Să ne uităm din nou. Acum avem noduri cu copii A și perechi (B + C) - compatibile și cu acestea.

Grozav! Se dovedește că noi suntem acești doi JOIN din cererea cu nodurile de plan au fost combinate cu succes.

Din păcate, această problemă nu este întotdeauna rezolvată.

De exemplu, dacă într-o cerere A JOIN B JOIN C, iar în plan, în primul rând, nodurile „exterioare” A și C au fost conectate. Dar nu există un astfel de operator în cerere, nu avem nimic de evidențiat, nimic la care să atașăm un indiciu. La fel e cu „virgula” când scrii A, B.

Dar, în cele mai multe cazuri, aproape toate nodurile pot fi „deslegate” și puteți obține acest tip de profilare în stânga în timp - literalmente, ca în Google Chrome când analizați codul JavaScript. Puteți vedea cât a durat fiecare rând și fiecare instrucțiune pentru a „executa”.

Și pentru a vă face mai convenabil să utilizați toate acestea, am făcut spațiu de stocare Arhiva, unde puteți salva și mai târziu să vă găsiți planurile împreună cu solicitările asociate sau să partajați linkul cu cineva.