🥇PostgreSQL Query Profiler: kuidas seostada plaani ja päringut

Paljud, kes juba kasutavad explain.tensor.ru — meie PostgreSQL plaanide visualiseerimise teenust, ei pruugi olla teadlikud ühe tema supervõime — keerulisest serveri logi kärpimise teisendamisest…

… ilusaks vormindatud päringuks kontekstuaalsete vihjetega vastavate plaanide sõlmedele:

Selles oma raportis PGConf.Russia 2020 räägin, kuidas me selle saavutame.

Esimese osa transkriptsiga, mis käsitleb tüüpilisi päringute jõudluse probleeme ja nende lahendusi, saate tutvuda artiklis „Retseptid haigestuvatele SQL-päringutele“.

Alustame värvimisest — ja värvime juba mitte plaani, see on meil juba ilus ja arusaadav, vaid päringut.

Meie arvates näeb eksportimisse logidest välja nähtud „riides“ väga kole välja ja seega — ebamugav.

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

Kujundame selle kuidagi ilusamaks.

Ja kui suudame selle ilusateks kujundada, siis saame päringu iga objekti jaoks „paigaldada“ vihje — mis toimus vastavas plaanipunktis.

Päringu süntaktiline puu

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

Kuna meil süsteemi tuum töötab NodeJS-il, siis tegime sellele mooduli, võite leida selle GitHubist. Tegelikult on see laiemad „sidumised“ PostgreSQL-i parseri sisestruktuuride suhtes. See tähendab lihtsalt binaarselt koostatud grammatika ning sellele on tehtud sidumised NodeJS-i poolt. Kasutasime teiste moodulite baasi — siin pole suurt saladust.

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

Nüüd saab seda puu kaudu tagurpidi joosta ja koguda päringu, mida soovime, mingite sisetõmmete, värvimise, vormindamisega. Ei, see ei ole konfigureeritav, kuid tundus, et just nii oleks mugav.

Päringu ja plaani sõlmede vastavus

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

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

CTE

Kui sellele tähelepanelikult vaadata, siis kuni 12. versioonini (või alates sellest, koos reaalsõnaga MATERIALIZED) on loomine CTE absoluutne barjäär planeerijale.

Seega, kui me näeme kusagil päringus CTE genereerimist ja kusagil plaanis sõlme CTE, siis need sõlmed on kindlasti omavahel seotud, me saame need kohe ühendada.

Ülesanne 'tärniga': CTE-d võivad olla pesakohtadest.

Need võivad olla väga halvasti pesakohtades, ja isegi sama nimega. Näiteks võite sees CTE A moodi 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 ...
)
...

Seda kokkupanekut peab mõistma. Mõista seda 'silmadega' — isegi nähes plaani, isegi nähes päringu keha — on väga raske. Kui teil on CTE genereerimine keeruline, pesakohtades, päringud suured — siis pole see üldse aru saanud.

kogumi operaator, toetavad

Kui meie päringus on reaalsõna UNION [ALL] (kahe valiku ühendamise operaator), vastab sellele plaanis kas sõlm Lisa, või mõni Rekursiivne Liit.

See, mis 'üles' on meie sõlme esimene laps, ja mis 'alla' — teine. Kui läbi kogumi operaator, toetavad meil on 'liimitud' mitu plokki korraga, siis kogumi operaator, toetavad -sõlm on ainult üks, aga 'lapsesid' on tal mitte kaks, vaid palju — vastavalt nende järjekonnale: Lisa(...) -- #1 UNION ALL (...) -- #2 UNION ALL (...) -- #3

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

: sees genereerimise rekurssi valik (

Ülesanne 'tärniga') võib samuti olla rohkem kui üks.WITH RECURSIVEKuid alati on rekursiivne ainult viimane plokk pärast viimast. kogumi operaator, toetavadKõik, mis üleval — see on üks, aga teine kogumi operaator, toetavadWITH RECURSIVE T AS( (...) -- #1 UNION ALL (...) -- #2, siin lõpeb rekursside algseisundi genereerimine UNION ALL (...) -- #3, ainult see plokk on rekursiivne ja võib sisaldada viite T-le ) ... kogumi operaator, toetavad:

Taolisi näiteid peab samuti oskama 'lahutada'. Siin näeme, et

-segmente meie päringus oli 3 tükki. Seega, ühele kogumi operaator, toetavad-sõlm, ja teisele — kogumi operaator, toetavad vastab LisaAndmete lugemine ja kirjutamine Rekursiivne Liit.

Nii, me oleme lahti harutanud, nüüd teame, milline tükk päringust vastab millisele tükk plaanist. Ja nendes tükkides saame lihtsalt ja vaevata leida need objektid, mida 'loetakse'.

Päringu seisukohalt me ei tea — kas see on tabel või CTE, kuid need tähistatakse sama sõlmega.

Küsimuse vaatepunktist ei tea me, kas see on tabel või CTE, kuid neid tähistatakse sama sõlmega. RangeVar. Ja plaan „loetav” - see on samuti üsna piiratud sõlmede kogum:

Järjesta skaneerimine [tbl] peal
Bitmapi virna skaneerimine [tbl] peal
Indeks [Ainult] Skaneeri [Tagasi] kasutades [idx] [tbl] peal
CTE skannimine [cte] peal
Sisestage/Uuenda/Kustutage [tbl] peal

Me teame plaani ja päringu struktuuri, teame plokkide vastavust, teame objektide nimesid - teeme ühemõttelise vastavuse.

Jällegi ülesanne „tähega”. Võtame päringu, täidame selle, meil ei ole mingeid alias'e - me lihtsalt lugesime kaks korda ühest CTE-st.

Vaata plaani - mis häda? Miks meil alias ilmus? Me ei tellinud seda. Kust see „numbriline” tuli?

PostgreSQL lisab selle ise. Tuleb lihtsalt mõista, et selline alias ei oma meile plaaniga vastavuse eesmärkide jaoks mingit mõtet, see on lihtsalt siin lisatud. Ärge pöörake sellele tähelepanu.

Teine ülesanne „tähega”: kui meil on lugemine jaotatud tabelist, saame sõlme Lisa või Ühenda Lisa, mis koosneb suurest hulgast „lastest”, kellest igaüks on mingi Scan'ist jaotustabelist: Järjesta skaneerimine, Bitmapi virna skaneerimine või Index Scan. Kuid igal juhul ei ole need „lapsed” keerulised päringud - neid sõlmi saab eristada Lisa kui kogumi operaator, toetavad.

Selliseid sõlmi mõistame ka, kogume „ühte kuhja” ja ütleme: "kõik, mida sa lugesid megatable'ist - see on siin ja allpool puu".

„Lihtsad” andmete saamise sõlmed

Väärtuste skaneerimine plaanis vastab VÄÄRTUSTE päringus.

Tulemus — see on päring ilma KUST näiliselt SELECT 1. Või kui sul on vale väljend KUS-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) (tode 0.000..0.000 read=0 tsüklid=1)
  One-Time Filter: false

Funktsiooni skanner „maapuvad” vastava SRF-ga.

Kuid sisemiste päringutega on kõik keerulisem - kahjuks ei muutu need alati Algusplaan/Alusplaan. Mõnikord muutuvad nad ... Join või ... Anti Join, eriti kui sa kirjutad midagi sarnast WHERE NOT EXISTS .... Ja seal kombineerimine ei pruugi alati õnnestuda - plaani tekstis vastavaid sõlme operaatoritest pole.

Jällegi ülesanne „tähega”: mitu VÄÄRTUSTE päringus. Sellisel juhul saad plaanis mitu sõlme Väärtuste skaneerimine.

Eristada neid üksteisest aitavad „numbrilised” suffiksid - need lisatakse täpselt plokkide vastavuse leidmise järjekorras päringu ülemisest osast alla. VÄÄRTUSTEAndmete töötlemine

Tundub, et oleme oma päringus kõik lahti seletanud - alles on jäänud vaid

Aga siin on kõik lihtne - sellised sõlmed nagu Piirang.

„maapuvad” üks-ühele vastavatele operaatoritele päringus, kui need seal olemas on. Siin ei ole mingeid „tähekesed” ega raskusi. Piirang, Sorteeri, Kogumine, WindowAgg, Ainulaadne Raskused tekivad, kui me tahame ühendada

JOIN

oma vahel. Seda ei ole alati lihtne teha, kuid võimalik. JOIN oma vahel. Seda ei ole alati võimalik teha, kuid see on võimalik.

Küsimuse parsija seisukohalt on meil sõlm, LiituExpr, millel on täpselt kaks last — vasak ja parem. See on vastavalt see, mis on teie JOIN “üleval” ja see, mis on “all” kirjutatud.

Ja plaani seisukohalt on see kaks last mõnest * Tsükkel/* Liitu-sõlmest. Sisekäik, Hash Anti Join,… — midagi sellist.

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

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

Joonistame uuesti graafide vormis — o, nüüd hakkab midagi sarnast olema!

Pöörame tähelepanu, et meil on sõlmed, millel on samal ajal lapsed B ja C — meile ei ole oluline, mis järjekorras. Ühendame need ja pöörame sõlme pildi ümber.

Vaadakem veel kord. Nüüd on meil sõlmed lastega A ja paar (B + C) — ühendame ka need.

Fantastiline! Tundub, et me oleme need kaks JOIN päringust plaani sõlmedega edukalt kokku viinud.

Kahjuks ei lahene see ülesanne alati.

Näiteks, kui päringus on A JOIN B JOIN C, aga plaanis liitusid alguses “äärmuslikud” sõlmed A ja C. Ja päringus pole sellist operaatorit, meil pole midagi, mida esile tõsta, pole millele vihjet siduda. Sama kehtib “komma” kohta, kui kirjutate A, B.

Kuid enamikul juhtudel õnnestub peaaegu kõik sõlmed “lahti siduda” ja saada midagi sellist profiileerimise järgi vasakul ajale — literally, nagu Google Chrome'is, kui analüüsite JavaScripti koodi. Näete, kui palju aega iga rida ja iga operaator “täideti”.

Ja et teil oleks kõigega mugavam kasutada, oleme loonud salvestuse arhiiv, kus saate salvestada ja hiljem leida oma plaane koos seotud päringutega või kellegagi jagada linki.