PostgreSQL Query Profiler: kako uskladiti plan i upit

Mnogi koji već koriste explain.tensor.ru — našu uslugu vizualizacije planova PostgreSQL, možda nisu upoznati s jednom od njenih supermoći — pretvaranjem teško čitljivog dela loga servera…

PostgreSQL Query Profiler: kako uskladiti plan i upit
… u lepo formatiran upit s kontekstualnim savjetima o relevantnim čvorovima plana:

PostgreSQL Query Profiler: kako uskladiti plan i upit
U ovoj obradi druge polovice svog izlaganja na PGConf.Russia 2020 ispričaću kako smo uspeli to da učinimo.

Sa transkriptom prve polovine, posvećene tipičnim problemima performansi upita i njihovim rešenjima, možete se upoznati u članku „Recepti za bolesne SQL upite”.


Igraj video

Prvo ćemo se pozabaviti obojanjem — i obojati ćemo već ne plan, on je već obojen, lep i jasan, već upit.

S obzirom na to da nam se čini da tako neformatirana "plahta" izvučena iz loga izgleda veoma ružno i zbog toga — nezgodno.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Pogotovo kada programeri u kodu "lepe" telo upita (to je, naravno, antipattern, ali se dešava) u jedan red. Strašno!

Hajde da to nacrtamo na neki lepši način.
PostgreSQL Query Profiler: kako uskladiti plan i upit

A ako možemo to nacrtati lepo, to jest razbiti i ponovo sastaviti telo upita, onda možemo i svakom objektu ovog upita "priključiti" savjet — šta se dešavalo u odgovarajućem trenutku plana.

Sintaktičko stablo upita

Da bismo to postigli, upit prvo treba da razbivimo.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Pošto je naš jezgro sistema napravljeno na NodeJS, napravili smo modul za njega, možete naći ga na GitHubu. U stvarnosti, ovo su prošireni "bindingi" za unutrašnjost samog parsera PostgreSQL. To jest, jednostavno je binarno kompajlirana gramatika i na nju su napravljeni bindingi sa strane NodeJS. Uzeli smo u obzir tuđe module — ovde nema velike tajne.

Dajemo telo upita kao ulaz u našu funkciju — na izlazu dobijamo razloženo sintaktičko stablo u obliku JSON objekta.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Sad možemo proći kroz ovo stablo u obrnutom smeru i sastaviti upit s tim razmacima, obojanjem, formatiranjem, koje želimo. Ne, to nije podešeno, ali nam se učinilo da bi to upravo tako bilo zgodno.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Mapiranje čvorova upita i plana

Sada ćemo pogledati kako možemo kombinovati plan koji smo razbili u prvom koraku i upit koji smo razbili u drugom.

Uzmimo jednostavan primjer — imamo upit koji formira CTE i čita iz njega dva puta. Generira takav plan.
PostgreSQL Query Profiler: kako uskladiti plan i upit

CTE

Ako se pažljivo pogleda, do 12. verzije (ili počevši od nje s ključnom riječi MATERIALIZED) formiranje CTE je apsolutna prepreka za planer.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Ako vidimo negdje u upitu generaciju CTE i negdje u planu čvor CTE, ti čvorovi se sigurno 'sudaraju', odmah ih možemo spojiti.

Zadatak 's zvjezdicom': CTE mogu biti ugniježdeni.
PostgreSQL Query Profiler: kako uskladiti plan i upit
Mogu biti vrlo loše ugniježdeni, pa čak i s istim nazivom. Na primjer, možete unutar CTE A napraviti CTE X, i na istoj razini unutar CTE B opet napraviti CTE X:

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

Pri usporedbi morate to razumjeti. Razumjeti 'očima' — čak i gledajući plan, čak i gledajući tijelo upita — vrlo je teško. Ako je vaša generacija CTE složena, ugniježdena, upiti veliki — tada to postaje potpuno neosviješteno.

UNION

Ako imamo u upitu ključnu riječ UNION [ALL] (operator za spajanje dviju selekcija), tada mu u planu odgovara ili čvor Dodaj, ili neki Rekurzivna Unija.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Ono što je 'iznad' našeg čvora je prvi potomak, a 'ispod' je drugi. Ako imamo nekoliko blokova 'zalijepljenih' preko UNION , čvor će i dalje biti samo jedan, ali će imati više djece — redom kako idu: UNION (...) -- #1 UNION ALL (...) -- #2 UNION ALL (...) -- #3 DodajAppend -> ... #1 -> ... #2 -> ... #3

  : unutar generacije rekurzivne selekcije (

) također može biti više od jednog

Zadatak 's zvjezdicom'. Ali uvijek je rekurzivni samo posljednji blok nakon posljednjegWITH RECURSIVE. Sve što je iznad — to je jedan, ali drugi UNIONWITH RECURSIVE T AS( (...) -- #1 UNION ALL (…) -- #2, ovdje završava generacija početnog stanja rekurzije UNION ALL (...) -- #3, samo ovaj blok je rekurzivan i može sadržavati poziv na T ) ... UNIONTakve primjere također treba znati 'razdvojiti'. U ovom primjeru vidimo da UNION:

-segmenta u našem upitu je bilo 3. Odnosno, jednom

-čvor, a drugom — UNIONČitanje-pisanje podataka UNION odgovara DodajSve, razložili smo, sada znamo koji dio upita odgovara kojem dijelu plana. I u tim djelovima lako možemo pronaći objekte koji se 'čitaju'. Rekurzivna Unija.
PostgreSQL Query Profiler: kako uskladiti plan i upit

S gledišta upita ne znamo — je li to tabela ili CTE, ali označavaju se istim čvorom.

Sve, rasporedili smo, sada znamo koji deo zahteva odgovara kojem delu plana. I u tim delovima lako možemo pronaći objekte koji se 'čitaju'.

Iz perspektive zahteva ne znamo da li je to tabela ili CTE, ali se obeležavaju istim čvorom RasponVar. A u planu "pročita" — to je također prilično ograničen skup čvorova:

  • Sekvencijalno pretraživanje na [tbl]
  • Bitmap heap skeniranje na [tbl]
  • Indeks [Samo] Skeniranje [Unazad] koristeći [idx] na [tbl]
  • CTE Skener na [cte]
  • Umetni/Ažuriranje/Obriši na [tbl]

Znamo strukturu plana i upita, znamo usklađenost blokova, znamo imena objekata — radimo jasnu mapu.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Ponovno zadatak "sa zvjezdicom". Uzimamo upit, izvršavamo ga, nemamo nikakve alias-e — jednostavno dva puta čitamo iz jedne CTE.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Pogledajmo u plan — koja je to šteta? Zašto nam se pojavila alias? Nismo ga tražili. Odakle on "numerički"?

PostgreSQL ga sam dodaje. Treba jednostavno razumjeti da takav alias za naše svrhe usklađivanja s planom nema nikakvog smisla, on je jednostavno ovdje dodat. Nećemo ga obraćati pažnju.

Drugi zadatak "sa zvjezdicom": ako čitamo iz particionirane tabele, dobit ćemo čvor Dodaj ili Spoji Dodaj, koji će se sastojati od velikog broja "djece", i svako od njih će biti neki Scan' iz tabele-particije: Sekvencijalno pretraživanje, Bitmap heap skeniranje ili Index Scan. Ali, u svakom slučaju, ta "djeca" neće biti složeni upiti — tako se ti čvorovi mogu razlikovati od Dodaj tokom UNION.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Takve čvorove također razumijemo, okupljamo ih "u jednu gomilu" i kažemo: "sve što si čitao iz megatable — to je ovdje i niz drveća".

"Prosti" čvorovi za dobijanje podataka

PostgreSQL Query Profiler: kako uskladiti plan i upit

Skener vrijednosti u planu odgovara VALUES u upitu.

Rezultat — to je upit bez FROM neka vrsta SELECT 1. Ili kada imate unaprijed lažan izraz u GDJE-bloku (onda se javlja atribut One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- ili 0 = 1

Rezultat  (cena=0.00..0.00 redova=0 širina=230) (aktuelno vreme=0.000..0.000 redova=0 petlji=1)
  One-Time Filter: false

Funkcija Skeniranja "mapiraju" se na istoimene SRF.

A sa ugniježdenim upitima je sve složenije — nažalost, oni se ne pretvaraju uvek u InicijalniPlan/SubPlan. Ponekad se pretvaraju u ... Join ili ... Anti Join, posebno kada pišete nešto poput WHERE NOT EXISTS .... I tu se ne može uvek kombinovati — u tekstu plana nema odgovarajućih čvorova plan operatera.

Ponovno zadatak "sa zvjezdicom": nekoliko VALUES u upitu. U ovom slučaju i u planu dobit ćete nekoliko čvorova Skener vrijednosti.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Razlikovati ih jedne od drugih pomoći će "numerički" sufiksi — oni se dodaju upravo u redosledu pronalaska odgovarajućih VALUES- blokova tokom upita odozgo prema dolje.

Obrada podataka

Izgleda da smo sve u našem upitu razjasnili — ostao je samo Ograničenje.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Ali ovdje je sve jednostavno — takvi čvorovi se "mapiraju" jedan-na-jedan na odgovarajuće operatore u upitu, ako ih tamo ima. Ovdje nema "zvjezdica" i komplikacija. Ograničenje, Sortiraj, Agregirati, WindowAgg, Jedinstven Teškoće se javljaju kada želimo spojiti
PostgreSQL Query Profiler: kako uskladiti plan i upit

JOIN

između sebe. To nije uvek moguće, ali može. JOIN među sobom. To nije uvek moguće, ali je izvodljivo.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Iz perspektive parsera upita, imamo čvor PridružiSe, koji ima tačno dva potomka — lijevi i desni. To su, redom, ono što je "iznad" vašeg JOIN-a i ono što je "ispod" njega u upitu.

A iz perspektive plana to su dva potomka nekog * Petlja/* Pridružite se-čvora. Ugniježdena petlja, Hash Anti Join,… — nešto tako.

Koristimo jednostavnu logiku: ako imamo tabele A i B, koje se "spajaju" jedna s drugom u planu, onda su u upitu mogle biti postavljene ili A-JOIN-B, or B-JOIN-A. Pokušaćemo ih spojiti tako, pokušaćemo ih spojiti obrnuto, i tako dok takvi parovi ne nestanu.

Uzmimo naše sintaktičko drvo, uzmimo naš plan, pogledajmo ih... ne liči!
PostgreSQL Query Profiler: kako uskladiti plan i upit

Ponovo ćemo ih nacrtati u obliku grafova — oh, već počinje da liči na nešto!
PostgreSQL Query Profiler: kako uskladiti plan i upit

Obratimo pažnju da imamo čvorove koji imaju djecu B i C — nije nam važno kojim redoslijedom. Spojit ćemo ih i okrenuti sliku čvora.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Pogledajmo još jednom. Sada imamo čvorove sa djecom A i parovima (B + C) — spojimo i njih.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Odlično! Ispostavlja se da smo ova dva JOIN iz upita s čvorovima plana uspješno spojili.

Nažalost, ova zadaća se ne može uvijek riješiti.
PostgreSQL Query Profiler: kako uskladiti plan i upit

Na primjer, ako u upitu A JOIN B JOIN C, a u planu su se prvo spojili "krajnji" čvorovi A i C. A u upitu nema takvog operatora, nemamo šta osvijetliti, nema na šta vezati savjet. Isto to važi i za "zarez", kada pišete A, B.

Ali, u većini slučajeva, skoro svi čvorovi se mogu "raspetljati" i dobiti ovakav profil vrijeme — doslovno, kao u Google Chrome-u, kada analizirate kod na JavaScriptu. Vidite koliko je vremena svaka linija i svaki operator "izvršavan".
PostgreSQL Query Profiler: kako uskladiti plan i upit

A kako biste svemu ovome lakše pristupili, napravili smo skladištenje arhive, gdje možete sačuvati i kasnije pronaći svoje planove zajedno sa asociranim upitima ili podijeliti link s nekim.

Ako trebate samo da pretvorite nečitljiv upit u razumljiv oblik, upotrijebite naš "normalizator".

PostgreSQL Query Profiler: kako uskladiti plan i upit

Izvor: habr.com

Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere 🔥 Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere | ProHoster