PostgreSQL Query Profiler: kuidas plaani ja päringut vastandada

Paljuski, kes juba kasutavad explain.tensor.ru — meie PostgreSQL plaanide visualiseerimise teenust, ei pruugi teada selle ühe erilise omaduse üle — muuta keeruliselt loetav serveri logitükk...

...ilusalt vormindatud päringuks kontekstuaalsete vihjete ja vastavate plaanisõlmede kohta:

Selles teises osas oma ettekandest PGConf.Russia 2020 räägin, kuidas me seda tegime.

Esimese osa transkriptsiooniga, mis käsitleb tüüpilisi päringute tulemuslikkuse probleeme ja nende lahendusi, saab tutvuda artiklis „Retseptid haige SQL-päringu jaoks”.

Alustame värvimisest — ja värvime teemat, mitte plaani, oleme selle juba ilusaks ja arusaadavaks teinud.

Meie arvates näeb niimoodi vormindamata „lapp“, logist välja tõmmatud päring, väga kole välja ja seetõttu — ebamugav.


Eriti kui arendajad „kleepivad“ päringu keha koodi sisse (see on muidugi antipattern, aga juhtub) ühte ritta. Kohutav!

Teeme selle kuidagi ilusamalt.


Ja kui me saame selle ilusasti kujundada, st lahti võtta ja tagasi kokku panna päringute keha, siis suudame igale selle päringu objektile "kinnitada" vihje — mis juhtus vastavas planeeringu punktis.

Päringu süntaktiline puu

Selleks tuleb päring kõigepealt analüüsida.


Kuna meil on süsteemi tuumik töötamas NodeJS-is, siis tegime sellele mooduli, mille leiate GitHubist. Tegelikult on see laienenud "sidemed" PostgreSQL-i parseri sisemustega. See tähendab, et grammaatika on lihtsalt binaarselt kompileeritud ja sellele on tehtud sidemed NodeJS-i poolt. Võtsime aluseks teiste moodulid — siin pole suurt saladust.

Söödame päringu keha meie funktsiooni sisendisse — väljundina saame analüüsitud süntaktilise puu JSON-objekti kujul.


Nüüd saab seda puud töödelda vastupidises suunas ja koguda päringut selliste indentide, värvimise ja vormindamisega, nagu me soovime. Ei, seda ei saa seadistada, kuid tundus, et just nii on mugav.

Päringu sõlmede ja plaani vastavus

Nüüd vaatame, kuidas saame ühendada plaani, mille me esimeses etapis lahkasime, ja päringu, mille me teises etapis analüüsisime.

Võtame lihtsa näite — meil on päring, mis genereerib CTE ja loeb seda kaks korda. See genereerib sellise plaani.

CTE

Kui sellele tähelepanelikult vaadata, siis enne 12. versiooni (või alates sellest koos märksõnaga MATERIALIZED) on CTE genereerimine ilmtingimata takistuseks plaanijale..


Seega, kui me näeme kuskil päringus CTE genereerimist ja kuskil plaanis sõlme CTE, siis need sõlmed on üksteisega kindlasti „kokku põrkuvad“, saame need kohe ühendada.

Ülesanne „tärniga“: CTE-d võivad olla sissepoole pandud.

Need võivad olla väga halvasti sisse pandud, isegi sama nimetusega. Näiteks võite sees CTE A teha CTE X, ja samal tasemel sees CTE B teha jälle CTE X:

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

Selle seondumise juures peate seda mõistma. Mõista seda „silmadega“ — isegi plaani nähes, isegi päringu keha nähes — on väga keeruline. Kui teie CTE genereerimine on keeruline, sissepandud, päringud suured — siis on see täiesti alateadlik.

UNION

Kui meie päringus on märksõna UNION [ALL] (ühendusoperaator kahe valimi vahel), siis on sellele plaanis kas sõlm Lisa, või mõni muu Rekursiivne Liit.


See, mis on „üleval“ meie sõlmest, on esimene järglane, mis on „all“ – teine. Kui meil on „kleeps“ mitmest plokist korraga, siis UNION -sõlm on ikkagi vaid üks, aga lapsi on tal mitte kaks, vaid palju – järjestikku, nagu nad käivad: UNION (...) -- #1 UNION ALL (...) -- #2 UNION ALL (...) -- #3 LisaLisa -> ... #1 -> ... #2 -> ... #3

  : rekursiivse valiku genereerimise sees (

) võib olla ka rohkem kui üks

Ülesanne „tärniga“. Kuid alati on rekursiivne ainult kõige viimane plokk pärast viimastWITH RECURSIVE. Kõik, mis on üleval – see on üks, aga teine UNIONWITH RECURSIVE T AS( (...) -- #1 UNION ALL (...) -- #2, siin lõppeb rekursiooni algse seisundi genereerimine UNION ALL (...) -- #3, ainult see plokk on rekursiivne ja võib sisaldada viitamist T-le ) ... UNIONSelliseid näiteid tuleb ka osata „ära kleepida“. Siin näeme, et UNION:

-segmente oli meie päringus 3 tükki. Vastavalt on ühel

-sõlm, ja teisel – UNIONAndmete lugemise ja kirjutamise UNION vastab Lisa-узел, а другому — Rekursiivne Liit.

Чтение-запись данных

Nüüd, kui oleme kõik selgeks teinud, teame, milline päringu osa vastab millisele plaani osale. Nendes osades saame hõlpsalt ja muretult leida need objektid, mis on "loetavad".

Päringu vaatenurgast ei tea me, kas see on tabel või CTE, kuid need on esitatud sama sõlme kaudu. Vahemikud. Plaani osas, mida "loetakse", on see samuti üsna piiratud sõlmede kogum:

• Järje skaneerimine laual [tbl]
• Bitmap Heap Scan laual [tbl]
• Indeksi [ainult] skaneeri [tagurpidi] kasutades [idx] [tbl] peal
• CTE skane ühe peal [cte]
• Sisestage/Uuendus/Delete laual [tbl]

Me teame plaani ja päringu struktuuri, blokke vastavaid, objektide nimesid — teeme ühemõttelise vastenduse.


Taaskord ülesanne "tähega".Võtame päringu, teeme selle täitmise, meil ei ole ühtegi aliasit — me lugesime lihtsalt kaks korda sama CTE-st.


Vaata plaani — mis juhtus? Miks on meil alias? Me ei tellinud seda. Kust see "number" ilmus?

PostgreSQL lisab selle automaatselt. Peame lihtsalt mõistma, et selline alias ei oma meie plaaniga vastendamiseks mingit tähendust, see on lihtsalt siia lisatud. Ärgem pöörakem sellele tähelepanu.

Teine ülesanne "tähega".: kui loeme sektsioonitud tabelist, saame sõlme Lisa või Liitu Lisa, mis koosneb suurest hulgast "lastest", ja igaüks neist on mingisugune Scantabeli-sektsioonist: Järje skaneerimine, Bitmap Heap Scan või Index Scan. Kuid igal juhul on need "lapsed" lihtsad päringud — neid sõlmi saab eristada Lisa päringus UNION.


Selliseid sõlmi me samuti mõistame, kogume „ühte kuhja“ ja ütleme: "kõik, mida sa megatable'ist lugesid — see on siin ja allpool puu sees".

„Lihtsad“ andmete saamise sõlmed

Väärtuste skannimine vastab plaanile VÄÄRTUSED päringus.

Tulemus — see on päring, kus puuduvad FROM näiteks SELECT 1. Või kui teil on ilmselgelt vale avaldis WHERE-plokis (siis tekib atribuut One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- või 0 = 1

Tulemus  (kulu=0.00..0.00 read=0 laius=230) (reaalne aeg=0.000..0.000 read=0 kordused=1)
  One-Time Filter: vale

Funktsionaalne skaneerimine „kaardistatakse“ vastava nimega SRF-idele.

Aga subpäringutega on kõik keerulisem — kahjuks ei pruugi need alati muutuda Alguspakett/Alamplaan. Mõnikord need muutuvad ... Join või ... Anti Join, eriti kui kirjutate midagi sellist nagu WHERE NOT EXISTS .... Ja seal ei õnnestuge alati kombineerida — plaani tekstis vastavad sõlmed operatorite plaanile ei ole.

Taaskord ülesanne "tähega".: mitu VÄÄRTUSED päringus. Sellisel juhul ja plaanis saate mitu sõlme Väärtuste skannimine.


Erilist tähelepanu väärivad „numbrilised“ suffiksid — need lisatakse just vastava leidmise järjekorras VÄÄRTUSED-plokkide kaudu päringu käigus ülalt alla.

Andmete töötlemine

Nagu kõik on meie päringus lahti seletatud — jääb vaid Limit.


Siin on kõik lihtne — sellised sõlmed nagu Limit, Sorteeri, Kogumine, WindowAgg, Unikaalne «kaardistatakse» üks-ühele vastavate operaatoritega päringus, kui need seal on. Siin ei ole mingeid «tähti» ega keerukusi.

JOIN

Keerukused tekivad, kui me tahame neid omavahel ühendada. JOIN Seda ei pruugi alati teha, kuid võimalik on.


Päringu analüsaatori seisukohalt on meil sõlm LiituExpr, millel on täpselt kaks last — vasak ja parem. See on vastavalt see, mis on „üle“ teie JOIN'i ja see, mis on „alla“ selle päringus kirjutatud.

Ja plaani seisukohast on see mõne * Tsükkel/* Liitu-sõlme kaks järglast. Nested Loop, Hash Anti Join,… — midagi sellist.

Kasutame lihtsat loogikat: kui meil on tabelid A ja B, mis omavahel „ühenduvad“ plaanis, siis võisid nad päringus olla paigutatud kas A-JOIN-B, või B-JOIN-A. Proovime neid nii ühendada, proovime neid vastupidi ühendada, ja nii seni, kuni sellised paarid otsa saavad.

Võtame meie süntaktilise puu, võtame meie plaani, vaatame neid… ei sarnane!


Kujundame graafikutena ümber — oh, nüüd näeb see juba välja nagu midagi!


Kasutame tähelepanekut, et meil on sõlmed, millel on samaaegselt lapsed B ja C — meile ei ole oluline, millises järjekorras. Ühendame need ja pöörame sõlme pilti.


Vaadime uuesti. Nüüd on meil sõlmed koos lastega A ja paar (B + C) — need on ühilduvad.


Suurepärane! Tundub, et meil on need kaks JOIN plaanisõlme päringust edukalt ühendatud.

Kahjuks ei lahenda see ülesanne alati.


Näiteks kui päringus on A JOIN B JOIN C, ja plaanis on esmakordselt ühendatud „äärsed” sõlmed A ja C. Ja kui päringus sellist operaatorit pole, ei ole meil midagi rõhutada, pole millele vihjatagi. Sama kehtib „komade” kohta, kui kirjutate A, B.

Kuid enamikul juhtudel õnnestub peaaegu kõik sõlmed „lahustada” ja saada selline ajaprofiil vasakul — täpselt nagu Google Chromes, kui analüüsite JavaScripti koodi. Näete, kui palju aega iga rida ja iga operaator „täideti”.


Ja et teil oleks kõigi nende funktsioonide kasutamine mugav, oleme loonud salvestamise arhivis, kus saate salvestada ja hiljem leida oma plaane koos seotud päringutega või kellegagi jagada linki.

Kui aga peate lihtsalt tooma loetamatud päringud mõistlikku vormi, kasutage meie „normaliseerijat”.

Allikas: habr.com