Velen die er al gebruik van maken - onze PostgreSQL-planvisualisatieservice is zich mogelijk niet bewust van een van zijn superkrachten - het omzetten van een moeilijk leesbaar stuk van het serverlogboek...
... in een prachtig ontworpen query met contextuele hints voor de overeenkomstige planknooppunten:
In deze transcriptie van het tweede deel van zijn Ik zal je vertellen hoe we dit hebben gedaan.
Het transcript van het eerste deel, gewijd aan typische problemen met de prestatie van query's en hun oplossingen, vindt u in het artikel .
Laten we eerst beginnen met kleuren - en we zullen het plan niet langer inkleuren, we hebben het al ingekleurd, we hebben het al mooi en begrijpelijk, maar een verzoek.
Het leek ons dat met zo'n ongeformatteerd "blad" het verzoek uit het logboek er erg lelijk en daarom ongemakkelijk uitziet.
Vooral wanneer ontwikkelaars de hoofdtekst van het verzoek in de code (dit is natuurlijk een antipatroon, maar het gebeurt) op één regel "lijmen". Vreselijk!
Laten we dit op de een of andere manier mooier tekenen.
En als we dit mooi kunnen tekenen, dat wil zeggen, de hoofdtekst van het verzoek demonteren en weer in elkaar zetten, dan kunnen we aan elk object van dit verzoek een hint "bijvoegen" - wat er op het overeenkomstige punt in het plan is gebeurd.
Syntaxisstructuur van query's
Om dit te doen, moet het verzoek eerst worden geparseerd.
Omdat we hebben , dan hebben we er een module voor gemaakt, dat kan . In feite zijn dit uitgebreide ‘bindingen’ naar de interne onderdelen van de PostgreSQL-parser zelf. Dat wil zeggen, de grammatica wordt eenvoudigweg binair gecompileerd en er worden bindingen aan gemaakt vanuit NodeJS. We hebben de modules van anderen als basis genomen - er is hier geen groot geheim.
We voeren de hoofdtekst van het verzoek in als invoer voor onze functie - bij de uitvoer krijgen we een geparseerde syntaxisboom in de vorm van een JSON-object.
Nu kunnen we deze boom in de tegenovergestelde richting doornemen en een verzoek samenstellen met de gewenste inspringingen, kleuren en opmaak. Nee, dit is niet aanpasbaar, maar het leek ons dat dit handig zou zijn.
Query- en planknooppunten in kaart brengen
Laten we nu eens kijken hoe we het plan dat we in de eerste stap hebben geanalyseerd, kunnen combineren met de vraag die we in de tweede stap hebben geanalyseerd.
Laten we een eenvoudig voorbeeld nemen: we hebben een query die een CTE genereert en hier twee keer uit leest. Hij maakt zo'n plan.
CTE
Als je het goed bekijkt, tot en met versie 12 (of vanaf daar met het trefwoord MATERIALIZED) формирование .
Dit betekent dat als we ergens in het verzoek een CTE-generatie zien en ergens in het plan een knooppunt CTE, dan “vechten” deze knooppunten definitief met elkaar, we kunnen ze onmiddellijk combineren.
Probleem met een sterretje: CTE's kunnen worden genest.
Er zijn zeer slecht geneste exemplaren, en zelfs exemplaren met dezelfde naam. Je kunt bijvoorbeeld binnen zitten CTE A сделать CTE X, en op hetzelfde niveau binnenin CTE B doe het opnieuw CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...Als u vergelijkt, moet u dit begrijpen. Dit ‘met je ogen’ begrijpen – zelfs het plan zien, zelfs de inhoud van het verzoek zien – is erg moeilijk. Als uw CTE-generatie complex en genest is en de verzoeken groot zijn, dan is dit volledig onbewust.
UNION
Als we een trefwoord in de zoekopdracht hebben UNION [ALL] (operator voor het samenvoegen van twee monsters), dan komt het in het plan overeen met een knooppunt Append, of sommige Recursive Union.
Dat wat ‘boven’ boven is UNION - dit is de eerste afstammeling van ons knooppunt, die "onder" is - de tweede. Als door UNION we hebben dan meerdere blokken tegelijk “gelijmd”. Append-er zal nog steeds maar één knooppunt zijn, maar het zal niet twee, maar veel kinderen hebben - in de volgorde waarin ze gaan, respectievelijk:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #1
-> ... #2
-> ... #3
Probleem met een sterretje: binnen het genereren van recursieve steekproeven (WITH RECURSIVE) kan ook meer dan één zijn UNION. Maar alleen het allerlaatste blok na het laatste is altijd recursief UNION. Alles hierboven is één, maar dan anders UNION:
WITH RECURSIVE T AS(
(...) -- #1
UNION ALL
(...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
(...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...
Dergelijke voorbeelden moet je ook kunnen ‘uitsteken’. In dit voorbeeld zien we dat UNION-er waren 3 segmenten in ons verzoek. Dienovereenkomstig, een UNION komt overeen met Append-knooppunt, en naar de andere - Recursive Union.
Gegevens lezen en schrijven
Alles is vastgelegd, nu weten we welk deel van de aanvraag overeenkomt met welk deel van het plan. En in deze stukken kunnen we gemakkelijk en op natuurlijke wijze die objecten vinden die “leesbaar” zijn.
Vanuit het oogpunt van een zoekopdracht weten we niet of het een tabel of een CTE is, maar ze worden aangeduid door hetzelfde knooppunt RangeVar. En in termen van “leesbaarheid” is dit ook een vrij beperkte reeks knooppunten:
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]
We kennen de structuur van het plan en de vraag, we kennen de overeenkomst van de blokken, we kennen de namen van de objecten - we maken een één-op-één vergelijking.
Opnieuw taak "met een asterisk". We nemen het verzoek aan, voeren het uit, we hebben geen aliassen - we lezen het gewoon twee keer uit dezelfde CTE.
We kijken naar het plan - wat is het probleem? Waarom hadden we een alias? Wij hebben het niet besteld. Waar haalt hij zo’n “nummer” vandaan?
PostgreSQL voegt het zelf toe. Dat moet je gewoon begrijpen precies zo'n alias voor ons heeft het voor de vergelijking met het plan geen enkele zin, het wordt hier gewoon toegevoegd. Laten we geen aandacht aan hem besteden.
Het tweede taak "met een asterisk": als we lezen vanuit een gepartitioneerde tabel, krijgen we een knooppunt Append of Merge Append, die uit een groot aantal "kinderen" zal bestaan, en die allemaal op de een of andere manier zullen zijn Scan'om uit de tabelsectie: Seq Scan, Bitmap Heap Scan of Index Scan. Maar in ieder geval zullen deze "kinderen" geen complexe vragen zijn - dit is hoe deze knooppunten kunnen worden onderscheiden Append bij UNION.
We begrijpen zulke knopen ook, verzamelen ze “op één stapel” en zeggen: “alles wat je van megatable leest, vind je hier en verderop in de boom".
"Eenvoudige" knooppunten voor het ontvangen van gegevens
Values Scan komt overeen in plan VALUES in het verzoek.
Result is een verzoek zonder FROM zoals SELECT 1. Of wanneer je opzettelijk een valse uitdrukking in je hebt WHERE-block (dan verschijnt het attribuut One-Time Filter):
EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1Result (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 “toewijzen” aan de SRF’s met dezelfde naam.
Maar met geneste zoekopdrachten is alles ingewikkelder - helaas worden ze niet altijd omgezet in InitPlan/SubPlan. Soms veranderen ze in ... Join of ... Anti Join, vooral als je zoiets schrijft WHERE NOT EXISTS .... En hier is het niet altijd mogelijk om ze te combineren - in de tekst van het plan zijn er geen operators die overeenkomen met de knooppunten van het plan.
Opnieuw taak "met een asterisk": sommige VALUES in het verzoek. In dit geval en in het plan krijgt u verschillende knooppunten Values Scan.
"Genummerde" achtervoegsels helpen ze van elkaar te onderscheiden - ze worden precies toegevoegd in de volgorde waarin de overeenkomstige worden gevonden VALUES-blokken langs het verzoek van boven naar beneden.
Gegevensverwerking
Het lijkt erop dat alles in ons verzoek is opgelost - het enige dat overblijft is Limit.
Maar hier is alles eenvoudig - knooppunten zoals Limit, Sort, Aggregate, WindowAgg, Unique één-op-één “toewijzen” aan de overeenkomstige operators in het verzoek, als deze aanwezig zijn. Er zijn hier geen “sterren” of moeilijkheden.
AANMELDEN
Er ontstaan moeilijkheden als we willen combineren JOIN tussen hun zelf. Dit is niet altijd mogelijk, maar het is wel mogelijk.
Vanuit het oogpunt van de query-parser hebben we een knooppunt JoinExpr, die precies twee kinderen heeft - links en rechts. Dit is dus wat er “boven” uw JOIN staat en wat er “onder” in het verzoek staat.
En vanuit het oogpunt van het plan zijn dit twee afstammelingen van sommigen * Loop/* Join-knooppunt. Nested Loop, Hash Anti Join,... - zoiets.
Laten we eenvoudige logica gebruiken: als we tabellen A en B hebben die in het plan met elkaar 'samenvoegen', dan kunnen ze in het verzoek ook worden gelokaliseerd A-JOIN-BOf B-JOIN-A. Laten we proberen op deze manier te combineren, laten we proberen andersom te combineren, enzovoort, totdat we geen dergelijke paren meer hebben.
Laten we onze syntaxisboom nemen, ons plan nemen, ernaar kijken... niet vergelijkbaar!
Laten we het opnieuw tekenen in de vorm van grafieken - oh, het lijkt al ergens op!
Laten we er rekening mee houden dat we knooppunten hebben die tegelijkertijd kinderen B en C hebben - het maakt ons niet uit in welke volgorde. Laten we ze combineren en de afbeelding van het knooppunt omdraaien.
Laten we nog eens kijken. Nu hebben we knooppunten met kinderen A en paren (B + C) - ook compatibel met hen.
Geweldig! Het blijkt dat wij deze twee zijn JOIN uit de aanvraag met de planknooppunten zijn succesvol gecombineerd.
Helaas wordt dit probleem niet altijd opgelost.
Bijvoorbeeld als het om een verzoek gaat A JOIN B JOIN C, en in het plan waren allereerst de "buitenste" knooppunten A en C met elkaar verbonden. Maar zo'n operator zit niet in het verzoek, we hebben niets om te benadrukken, niets om een hint aan te koppelen. Hetzelfde geldt voor de "komma" als je schrijft A, B.
Maar in de meeste gevallen kunnen bijna alle knooppunten 'losgemaakt' worden en kun je dit soort profilering op tijd aan de linkerkant krijgen - letterlijk, zoals in Google Chrome wanneer je JavaScript-code analyseert. U kunt zien hoe lang het duurde voordat elke regel en elke instructie werd 'uitgevoerd'.
En om het u gemakkelijker te maken om dit allemaal te gebruiken, hebben we opslagruimte gemaakt , waar u uw plannen en bijbehorende verzoeken kunt opslaan en later kunt terugvinden, of de link met iemand kunt delen.
Als u alleen maar een onleesbare zoekopdracht in een adequate vorm wilt omzetten, gebruik dan .
Bron: www.habr.com