🥇PostgreSQL Query Profiler: hoe het plan en de query te koppelen

Veel gebruikers van onze PostgreSQL-planningsvisualisatieservice zijn zich misschien niet bewust van een van zijn supercapaciteiten: het omzetten van een moeilijk leesbaar stuk serverlog... explain.tensor.ru ... in een prachtig opgemaakte query met contextuele hints voor de bijbehorende knooppunten van de planning:

In deze ontleding van het tweede deel van mijn

lezing op PGConf.Russia 2020 zal ik uitleggen hoe we dit hebben kunnen doen. De transcriptie van het eerste deel, dat gewijd is aan typische prestatieproblemen van queries en hun oplossingen, is te vinden in het artikel

«Recepten voor zieke SQL-queries» Laten we eerst eens kijken naar de kleurcodering - en we zullen niet de planning kleur coderen, die is al mooi en begrijpelijk, maar de query..

Het leek ons dat deze ongeformatteerde ‘lap’ die uit de log is getrokken er erg lelijk uitziet en daarom - onhandig.

Vooral wanneer ontwikkelaars de body van de query in de code ‘plakken’ (dit is natuurlijk een antipatroon, maar het gebeurt) in één regel. Vreselijk!

Laten we het op een mooiere manier tekenen.

Als we het mooi kunnen tekenen, dat wil zeggen de body van de query kunnen analyseren en weer samenstellen, kunnen we later ook aan elk object van die query een hint ‘hangen’ - wat er op dat moment in het bijbehorende knooppunt van de planning gebeurde.

De syntactische boom van de query

Om dit te doen, moet de query eerst worden geanalyseerd.

Aangezien onze

systeemkern draait op NodeJS , hebben we daar een module voor gemaakt, die jeop GitHub kunt vinden . In feite zijn dit uitgebreide ‘bindings’ naar de interne werking van de PostgreSQL-parser. Dit betekent gewoon dat de grammatica binair is gecompileerd en er bindings zijn gemaakt vanuit NodeJS. We hebben ons gebaseerd op bestaande modules - hier zit geen grote geheim achter.We voeren de body van de query in onze functie in - en krijgen een geanalyseerde syntactische boom in de vorm van een JSON-object als resultaat.

Nu kunnen we over deze boom teruglopen en de query samenstellen met de inspringingen, kleurcoderingen, en opmaak die we willen. Nee, dit kan niet worden ingesteld, maar we geloofden dat dit precies zo handig zou zijn.

De mapping van de knooppunten van de query en de planning

Laten we nu bekijken hoe we de planning, die we in de eerste stap hebben geanalyseerd, kunnen combineren met de query, die we in de tweede stap hebben geanalyseerd.

Laten we nu kijken hoe we het plan, dat we in de eerste stap hebben ontleed, kunnen combineren met de query die we in de tweede stap hebben ontleed.

Laten we een eenvoudig voorbeeld nemen: we hebben een query die een CTE vormt en er twee keer uit leest. Dit genereert zo'n plan.

CTE

Als je er goed naar kijkt, tot en met versie 12 (of te beginnen met deze met het sleutelwoord MATERIALIZED) is de vorming CTE een onoverkomelijke barrière voor de planner..

Dat betekent dat als we ergens in de query een CTE-generatie zien en ergens in het plan een knooppunt CTE, deze knooppunten zeker met elkaar 'conflicteren', we kunnen ze direct samenvoegen.

De taak 'met een ster': CTE's kunnen genest zijn.

Ze kunnen zeer slecht genest zijn, en zelfs met dezelfde naam. Bijvoorbeeld, je kunt binnen CTE A maken CTE X, en op hetzelfde niveau binnen CTE B opnieuw maken CTE X:

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

Bij het vergelijken moet je dit begrijpen. Het begrijpen 'met je ogen' — zelfs het plan zien, zelfs het lichaam van de query zien — is erg moeilijk. Als je een complexe, geneste CTE-generatie hebt, zijn de queries groot — dan is het helemaal niet te beseffen.

UNION

Als we in de query het sleutelwoord hebben UNION [ALL] (de operator voor het samenvoegen van twee selecties), dan komt dit in het plan overeen met een knooppunt Voeg toe, of een andere Recursieve Unie.

Wat 'boven' is, is een eerste afstammeling van ons knooppunt, wat 'onder' is, is een tweede. Als er door meerdere blokken 'bovenop' zijn 'gelijmd', dan UNION - blijft er toch maar één knooppunt, maar het aantal kinderen zal niet twee zijn, maar veel — in volgorde zoals ze komen: UNION (...) -- #1 UNION ALL (...) -- #2 UNION ALL (...) -- #3 Voeg toeVoeg samen -> ... #1 -> ... #2 -> ... #3

  : binnen de generatie van een recursieve selectie (

) kan er ook meer dan één zijn.

De taak 'met een ster'Maar altijd is alleen het laatste blok na de laatste recursief. Alles wat hoger is — is één, maar een andere.WITH RECURSIVEWITH RECURSIVE T AS( (...) -- #1 UNION ALL (...) -- #2, hier eindigt de generatie van de starttoestand van de recursie UNION ALL (...) -- #3, alleen dit blok is recursief en kan verwijzen naar T ) ... UNIONDergelijke voorbeelden moeten ook kunnen worden 'uitgeplakt'. In dit voorbeeld zien we dat UNION-segmenten in onze query waren 3 stuks. Dus aan de ene kant is er één knooppunt, en aan de andere kant — UNION:

Lezen-schrijven van gegevens.

Alles is nu uit elkaar gehaald, nu weten we welk deel van de query aan welk deel van het plan overeenkomt. En in deze stukken kunnen we gemakkelijk en moeiteloos de objecten vinden die 'gelezen' worden. UNIONVanuit het perspectief van de query weten we niet — of dit een tabel is of een CTE, maar ze worden aangeduid met hetzelfde knooppunt. UNION overeenkomt met Voeg toe-knooppunt, en voor de ander — Recursieve Unie.

Lezen-schrijven van gegevens.

Dus, we hebben het opgelost, nu weten we welk stuk van de query bij welk stuk van het plan hoort. En binnen deze stukjes kunnen we gemakkelijk en zonder moeite de objecten vinden die 'gelezen' worden.

Vanuit het perspectief van de query weten we niet — of het een tabel of CTE is, maar ze worden aangeduid met hetzelfde knooppunt. BereikVar. In het plan "is leesbaar" - dit is ook een vrij beperkte set knooppunten:

Sequentiële scan op [tbl]
Bitmap Heap Scan op [tbl]
Index [Only] Scan [Backward] using [idx] on [tbl]
CTE-scan op [cte]
Invoegen/Bijwerken/Delete op [tbl]

We weten de structuur van het plan en de query, we weten de overeenkomsten van de blokken, we kennen de objectnamen - we maken een eenduidige mapping.

Opnieuw de taak "met een ster". We nemen de query, voeren deze uit, we hebben geen aliassen - we hebben gewoon twee keer gelezen uit dezelfde CTE.

We kijken naar het plan - wat is er aan de hand? Waarom verschijnt onze alias? We hebben dit niet besteld. Waar komt deze 'nummer' vandaan?

PostgreSQL voegt dit zelf toe. We moeten gewoon begrijpen dat precies zo'n alias voor ons, voor de doeleinden van de mapping met het plan, geen enkele zin heeft, het is hier gewoon toegevoegd. Laten we er niet op letten.

Tweede de taak "met een ster": als we lezen uit een gesegmenteerde tabel, krijgen we een knoop Voeg toe of Samengevoegd Toevoegen, die zal bestaan uit een groot aantal 'kinderen', en elk van hen zal een Scan' zijn uit de sectietabel: Sequentiële scan, Bitmap Heap Scan of Index Scan. Maar in ieder geval zullen deze 'kinderen' geen complexe queries zijn - zo kunnen deze knopen onderscheiden worden van Voeg toe bij UNION.

Zo'n knooppunten begrijpen we ook, verzamelen we 'op één hoop' en zeggen: "alles wat je leest uit megatable - dat is hier en naar beneden in de boom".

"Eenvoudige" knopen voor gegevensverzameling

Waarden scanner in het plan komt overeen WAARDEN in de query.

Result - het is een query zonder FROM zoals SELECT 1. Of wanneer je een per definitie valse uitdrukking in WAAR-blok hebt (dan ontstaat het attribuut One-Time Filter):

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

Resultaat  (kosten=0.00..0.00 rijen=0 breedte=230) (werkelijke tijd=0.000..0.000 rijen=0 loops=1)
  One-Time Filter: valse

Functiescan "mappen" naar overeenkomstige SRF.

Maar met geneste queries is het ingewikkelder - helaas worden ze niet altijd omgezet in InitPlan/SubPlan. Soms worden ze omgezet in ... Join of ... Anti Join, vooral wanneer je iets schrijft als WAAR NIET BESTAAT .... En daar is het niet altijd mogelijk om te combineren - in de tekst van het plan zijn de bijbehorende knopen van de operatoren niet aanwezig.

Opnieuw de taak "met een ster": meerdere WAARDEN in de query. In dit geval krijg je ook meerdere knooppunten in het plan Waarden scanner.

Ze van elkaar onderscheiden helpt 'nummer' suffixen - ze worden toegevoegd in de volgorde waarin de overeenkomstige WAARDEN-blokken in de query van boven naar beneden worden gevonden.

Gegevensverwerking

Het lijkt erop dat we alles in onze query hebben doorgenomen - alleen nog maar Beperking.

Maar hier is alles eenvoudig - zulke knooppunten worden één-op-één gemapt naar de overeenkomstige operatoren in de query, als ze daar zijn. Hier zijn geen 'sterren' en complicaties. Beperking, Sorteren, Aggeren, WindowAgg, Uniek Complicaties ontstaan wanneer we willen combineren

JOIN

met elkaar. Dit is niet altijd mogelijk, maar het kan. JOIN met elkaar. Dit is niet altijd mogelijk, maar het kan.

Vanuit het perspectief van de query-parser hebben we een knooppunt JoinExpr, dat precies twee kinderen heeft - links en rechts. Dit is respectievelijk wat 'boven' uw JOIN staat en wat 'eronder' in de query is geschreven.

En vanuit het perspectief van het plan zijn dat twee kinderen van een * Loop/* Lid Worden-knooppunt. Geneste lus, Hash Anti Join,… - dat is iets dergelijks.

Laten we een eenvoudige logica gebruiken: als we tabellen A en B hebben die met elkaar 'joinen' in het plan, dan konden ze in de query op de volgende manieren zijn geplaatst A-JOIN-B, ofwel B-JOIN-A. Laten we proberen ze zo te combineren, laten we proberen ze omgekeerd te combineren, en zo verder totdat deze paren op zijn.

Laten we onze syntactische boom bekijken, laten we ons plan bekijken, laten we ze vergelijken… het lijkt niet op elkaar!

Laten we ze opnieuw tekenen als grafieken - oh, het begint al op iets te lijken!

Laten we opmerken dat we knooppunten hebben die tegelijkertijd kinderen B en C hebben - het maakt niet uit in welke volgorde. We combineren ze en draaien de afbeelding van het knooppunt om.

Laten we nog eens kijken. Nu hebben we knooppunten met kinderen A en het paar (B + C) - laten we ook die combineren.

Prima! Het blijkt dat we deze twee JOIN uit de query met de knooppunten van het plan succesvol hebben gecombineerd.

Helaas is deze taak niet altijd oplosbaar.

Bijvoorbeeld, als in de query A JOIN B JOIN C, en in het plan in eerste instantie de 'extreme' knooppunten A en C zijn samengevoegd. En in de query is er geen dergelijke operator, hebben we niets om te markeren, niets om de hint aan te verbinden. Hetzelfde met de 'komma', wanneer je schrijft A, B.

Maar in de meeste gevallen kunnen bijna alle knooppunten worden 'ontknopt' en kunnen we zo'n profiling aan de linkerkant in de tijd krijgen - letterlijk zoals in Google Chrome wanneer je JavaScript-code analyseert. Je ziet hoeveel tijd elke regel en elke operator 'uitgevoerd' werd.

En om het voor jullie gemakkelijker te maken, hebben we opslag gemaakt archief, waar je je plannen kunt opslaan en later kunt vinden, of een link ermee kunt delen.