PostgreSQL Query Profiler: Wie man Plan und Anfrage abgleicht

Viele, die bereits unseren Visualisierungsdienst für PostgreSQL-Pläne nutzen, wissen möglicherweise nicht, dass er eine super Fähigkeit hat — schwer lesbare Teile der Server-Logs umzuwandeln… explain.tensor.ru … in schön formatierte Abfragen mit kontextuellen Hinweisen zu den entsprechenden Knoten des Plans:

In dieser Ausführung des zweiten Teils meines

Berichts auf der PGConf.Russia 2020 werde ich erzählen, wie wir das erreicht haben. Den Transkript der ersten Teil, der sich den typischen Leistungsproblemen von Abfragen und ihren Lösungen widmet, finden Sie in dem Artikel

„Rezepte für kränkelnde SQL-Abfragen“ Zuerst kümmern wir uns um die Farbgebung — und wir werden nicht den Plan, den wir bereits farbig gestaltet haben, sondern die Abfrage einfärben..

Uns erschien, dass diese unformatierte „Textwüste“, die aus dem Log extrahierte Abfrage, äußerst unschön aussieht und daher — unpraktisch ist.

Besonders, wenn Entwickler im Code den Abfragekörper „kleben“ (das ist zwar ein Anti-Muster, passiert aber trotzdem) in einer Zeile. Grauenhaft!


Lassen Sie uns das irgendwie schöner gestalten.

Wenn wir es schaffen, dies schön zu zeichnen, das heißt, den Abfragekörper zu zerlegen und wieder zusammenzusetzen, dann können wir auch an jedes Objekt dieser Abfrage einen Hinweis „anhängen“ — was an dem entsprechenden Punkt des Plans geschah.


Der syntaktische Baum der Abfrage

Um dies zu erreichen, muss die Abfrage zuerst zerlegt werden.

Da unser


Systemkern auf NodeJS basiert , haben wir ein Modul dafür erstellt, das Sieauf GitHub finden können. Tatsächlich handelt es sich um erweiterte „Bindings“ zu den inneren Abläufen des PostgreSQL-Parsers. Es ist einfach die binary kompilierte Grammatik und dazu wurden Bindings aus der Seite NodeJS gemacht. Wir haben uns auf bestehende Module gestützt — hier gibt es kein großes Geheimnis.Wir füttern den Abfragekörper in unsere Funktion — als Ausgabe erhalten wir den zerlegten syntaktischen Baum in Form eines JSON-Objekts.

Jetzt kann man über diesen Baum in umgekehrter Richtung gehen und die Abfrage mit den gewünschten Einrückungen, Färbungen, Formatierungen, die wir wollen, wieder zusammenstellen. Nein, das ist nicht konfigurierbar, aber es erschien uns so am bequemsten.


Zugehörigkeit der Knoten von Abfrage und Plan

Jetzt sehen wir uns an, wie wir den Plan, den wir im ersten Schritt zerlegt haben, mit der Abfrage, die wir im zweiten Schritt zerlegt haben, kombinieren können.

Jetzt schauen wir, wie wir den Plan, den wir im ersten Schritt analysiert haben, und die Abfrage, die wir im zweiten Schritt zerlegt haben, kombinieren können.

Lassen Sie uns ein einfaches Beispiel nehmen — wir haben eine Abfrage, die CTE erstellt und zweimal daraus liest. Sie generiert so einen Plan.

CTE

Wenn man ihn sich genau anschaut, sieht man, dass bis zur Version 12 (oder beginnend mit ihr mit dem Schlüsselwort MATERIALIZED) die Erstellung der CTE eine uneingeschränkte Hürde für den Planer ist..


Das heißt, wenn wir irgendwo in der Abfrage eine CTE-Generierung sehen und irgendwo im Plan einen Knoten CTE, dann stehen diese Knoten eindeutig miteinander in Beziehung, und wir können sie sofort zusammenfassen.

Eine ‚Aufgabe mit Sternchen‘: CTEs können geschachtelt sein.

Es gibt sehr ungeschickt geschachtelte und sogar gleichnamige. Zum Beispiel können Sie innerhalb von CTE A eine CTE X, und auf der gleichen Ebene innerhalb von CTE B wieder eine CTE X:

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

Bei der Zuordnung müssen Sie dies verstehen. Es ist sehr schwierig, dies ‚mit den Augen‘ zu begreifen — selbst den Plan zu sehen, selbst den Anfragekörper zu sehen — ist sehr schwer. Wenn Sie eine komplexe, geschachtelte CTE-Generierung haben, große Abfragen — dann ist es sogar völlig unbewusst.

UNION

Wenn in unserer Abfrage das Schlüsselwort UNION [ALL] (der Operator zum Verknüpfen zweier Abfragen) vorhanden ist, dann entspricht ihm im Plan entweder ein Knoten Anfügen, oder irgendein Rekursive Vereinigung.


Das, was ‚darüber‘ ist, ist der erste Nachkomme unseres Knotens, was ‚darunter‘ ist — der zweite. Wenn über UNION wir mehrere Blöcke gleichzeitig ‚verkettet‘ haben, dann UNION - der Knoten wird trotzdem nur einer sein, aber er hat nicht zwei Kinder, sondern viele — in der Reihenfolge, wie sie erscheinen: Anfügen(...) -- #1 UNION ALL (...) -- #2 UNION ALL (...) -- #3

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

: innerhalb der Generierung einer rekursiven Abfrage (

Eine ‚Aufgabe mit Sternchen‘) kann es ebenfalls mehr als einen geben.WITH RECURSIVEAber rekursiv ist immer nur der letzte Block nach dem letzten UNION. Alles, was darüber ist — ist eine, aber eine andere. UNIONWITH RECURSIVE T AS( (...) -- #1 UNION ALL (...) -- #2, hier endet die Generierung des Startzustandes der Rekursion UNION ALL (...) -- #3, nur dieser Block ist rekursiv und kann auf T verweisen ) ... UNION:

Solche Beispiele muss man ebenfalls ‚entwirren‘ können. In diesem Beispiel sehen wir, dass

-Abschnitte in unserer Abfrage 3 Stück waren. Dementsprechend entspricht einem UNION-Knoten, und einem anderen — UNION entspricht Anfügen-Knoten, während der andere — Rekursive Vereinigung.

Daten lesen und schreiben.

Alles, aufgeschlüsselt, jetzt wissen wir, welches Stück der Anfrage welchem Stück des Plans entspricht. Und in diesen Stücken können wir leicht und unkompliziert die Objekte finden, die ‚gelesen‘ werden.

Aus der Sicht der Anfrage wissen wir nicht — ob es eine Tabelle oder eine CTE ist, aber sie werden durch denselben Knoten bezeichnet. Bereichsvariable. Und im Hinblick auf ‚lesbar‘ ist dies ebenfalls eine recht begrenzte Menge an Knoten:

• Seq Scan auf [tbl]
• Bitmap-Hub-Scan auf [tbl]
• Index [Nur] Scan [Rückwärts] unter Verwendung von [idx] auf [tbl]
• CTE-Scan auf [cte]
• Einfügen/Update/Delete auf [tbl]

Wir wissen um die Struktur des Plans und der Anfrage, wir kennen die Übereinstimmung der Blöcke, die Namen der Objekte — wir machen eine eindeutige Zuordnung.


Again, die Aufgabe ‚mit Stern‘. Wir nehmen die Anfrage, führen sie aus, wir haben keine Aliase — wir haben einfach zweimal aus einer CTE gelesen.


Wir schauen in den Plan — was ist los? Warum tauchte das Alias auf? Wir haben nicht danach gefragt. Woher kommt es mit ‚Nummer‘?

PostgreSQL fügt es selbst hinzu. Man muss einfach verstehen, dass genau dieses Alias für unsere Zwecke der Zuordnung zum Plan keinerlei Bedeutung hat, es wurde einfach hier hinzugefügt. Lassen wir es unbeachtet.

Zweiter die Aufgabe ‚mit Stern‘: Wenn wir aus einer partitionierten Tabelle lesen, erhalten wir einen Knoten Anfügen oder Zusammenführen Anhängen, der aus einer großen Anzahl von ‚Kindern‘ bestehen wird, und jedes von ihnen wird eine Art Scanaus der Tabellen-Partition sein: Seq Scan, Bitmap-Hub-Scan oder Index Scan. Aber in jedem Fall werden diese ‚Kinder‘ keine komplexen Anfragen sein — so kann man diese Knoten von Anfügen unterscheiden UNION.


Diese Knoten verstehen wir ebenfalls, bündeln sie ‚zu einem Haufen‘ und sagen: "alles, was du aus der megatable gelesen hast, ist hier und nach unten im Baum".

‚Einfache‘ Knoten zur Datenerfassung

Werte scannen entsprechen VALUES in der Anfrage.

Ergebnis — dies ist eine Anfrage ohne FROM wie SELECT 1. Oder wenn Sie ein absichtlich falsches Ausdruck in WHERE-Block haben (dann taucht das Attribut One-Time Filter):

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

Result  (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
  One-Time Filter: false

Funktion Scan ‚mappen‘ zu den gleichnamigen SRF.

Mit Unteranfragen wird es komplizierter — leider verwandeln sie sich nicht immer in InitPlan/SubPlan. Manchmal verwandeln sie sich in ... Join oder ... Anti Join, besonders wenn Sie etwas schreiben wie WHERE NOT EXISTS .... Und dort ist es nicht immer möglich zu kombinieren — im Plantext gibt es für die entsprechenden Knoten der Operatoren keine Operatoren.

Again, die Aufgabe ‚mit Stern‘: mehrere VALUES in der Anfrage. In diesem Fall erhalten Sie auch im Plan mehrere Knoten Werte scannen.


Sie können sie durch ‚nummerierte‘ Suffixe unterscheiden — sie werden genau in der Reihenfolge der entsprechenden VALUES-Blöcke in der Anfrage von oben nach unten hinzugefügt.

Datenverarbeitung

Es scheint, als hätten wir alles in unserer Anfrage durchgearbeitet — nur noch bleibt Begrenzung.


Aber hier ist alles einfach — solche Knoten wie Begrenzung, Sortieren, Aggregat, FensterAggregat, Eindeutig ‚mappen‘ eins zu eins auf die entsprechenden Operatoren in der Anfrage, wenn sie dort vorhanden sind. Hier gibt es keine ‚Sternchen‘ und keine Schwierigkeiten.

JOIN

Schwierigkeiten treten auf, wenn wir versuchen zu kombinieren JOIN untereinander. Das ist nicht immer möglich, aber es kann.


Aus der Sicht des Anfragen-Parsers haben wir einen Knoten JoinExpr, der genau zwei Nachkommen hat – links und rechts. Dies ist entsprechend das, was „über“ Ihrem JOIN steht und das, was „unter“ ihm im Antrag steht.

Und aus der Perspektive des Plans sind das zwei Nachkommen eines * Schleife/* Beitreten-Knotens. Verschachtelte Schleife, Hash Anti Join,… – so etwas in der Art.

Lassen Sie uns einfache Logik anwenden: Wenn wir Tabellen A und B haben, die im Plan „joinen“, dann konnten sie im Antrag entweder so angeordnet sein A-JOIN-B, oder B-JOIN-A. Lassen Sie uns versuchen, diese zu kombinieren, versuchen wir es umgekehrt, und so weiter, bis solche Paare enden.

Nehmen wir unseren Syntaxbaum, nehmen wir unseren Plan, schauen wir sie uns an… sieht nicht aus wie!


Wir zeichnen es in Form von Graphen neu – oh, jetzt sieht es schon nach etwas aus!


Lassen Sie uns darauf achten, dass wir Knoten haben, die gleichzeitig Kinder B und C haben – es ist uns egal, in welcher Reihenfolge. Wir kombinieren sie und drehen das Bild des Knotens um.


Schauen wir uns noch einmal an. Jetzt haben wir Knoten mit Kindern A und Paaren (B + C) – wir kombinieren auch diese.


Ausgezeichnet! Es stellt sich heraus, dass wir diese beiden JOIN aus dem Antrag mit den Knoten des Plans erfolgreich kombiniert haben.

Leider ist diese Aufgabe nicht immer lösbar.


Wenn im Antrag beispielsweise A JOIN B JOIN C, aber im Plan zuerst die „äußeren“ Knoten A und C verbunden wurden. Und im Antrag gibt es keinen solchen Operator, wir haben nichts hervorzuheben, an das wir den Hinweis anhängen können. Dasselbe gilt für das „Komma“, wenn Sie schreiben A, B.

Aber in den meisten Fällen gelingt es, fast alle Knoten „zu lösen“ und eine solche Profilierung links nach der Zeit zu erhalten – buchstäblich wie in Google Chrome, wenn Sie Code in JavaScript analysieren. Sie sehen, wie viel Zeit jede Zeile und jeder Operator „ausgeführt“ wurden.


Und um es für Sie einfacher zu machen, haben wir eine Speicherung eingerichtet Archiv, wo Sie Ihre Pläne zusammen mit den zugehörigen Anfragen speichern und später finden oder einen Link mit jemandem teilen können.

Wenn Sie jedoch einfach eine unleserliche Anfrage in eine angemessene Form bringen müssen, verwenden Sie unseren „Normalisierer“.

Quelle: habr.com