🥇PostgreSQL Query Profiler: Wie man Plan und Abfrage zuordnet

Viele, die bereits unseren Dienst zur Visualisierung von PostgreSQL-Plänen nutzen, wissen möglicherweise nicht von einer seiner besonderen Fähigkeiten — der Umwandlung eines schwer lesbaren Serverprotokolls … explain.tensor.ru … in ein ansprechend gestaltetes SQL-Statement mit kontextbezogenen Hinweisen zu den entsprechenden Plan-Knoten:

In dieser Ausführung des zweiten Teils meines

Vortrags auf der PGConf.Russia 2020 werde ich erläutern, wie uns dies gelungen ist. Das Transkript des ersten Teils, der sich mit typischen Leistungsproblemen von Anfragen und deren Lösungen beschäftigt, finden Sie in dem Artikel

„Rezepte für kranke SQL-Abfragen“ Zuerst kümmern wir uns um das hervorheben — und wir werden nicht den Plan formatieren, den haben wir bereits hübsch und verständlich gestaltet, sondern die Anfrage..

Uns erschien es, dass ein so unformatierter „Schinken“, der aus dem Protokoll extrahiert wurde, sehr unansehnlich und daher — unpraktisch aussieht.

Besonders, wenn Entwickler im Code den Anfrage-Text (das ist natürlich ein Antipattern, kommt aber vor) in eine Zeile „kleben“. Grauenhaft!

Lassen Sie uns das schöner darstellen.

Lass uns das schöner gestalten.

Und wenn es uns gelingt, das schön darzustellen, das heißt, den Anfragekörper zu analysieren und wieder zusammenzusetzen, können wir später auch jedem Objekt dieser Anfrage einen Hinweis hinzufügen – was an dem entsprechenden Punkt des Plans passiert ist.

Syntaxbaum der Anfrage

Um dies zu tun, muss die Anfrage zuerst analysiert werden.

Da wir das Systemkern auf NodeJS läuft, haben wir ein Modul dafür erstellt, das Sie auf GitHub finden können. Tatsächlich handelt es sich um erweiterte 'Bindings' zu den inneren Strukturen des PostgreSQL-Parsers. Das bedeutet, eine binär kompilierte Grammatik, zu der Bindings aus der NodeJS-Seite erstellt wurden. Wir haben auf bestehenden Modulen aufgebaut – da gibt es kein großes Geheimnis.

Wir füttern den Anfragekörper in unsere Funktion – und erhalten einen analysierten Syntaxbaum als JSON-Objekt zurück.

Jetzt kann man durch diesen Baum rückwärts gehen und die Anfrage mit den Einrückungen, Farben und Formatierungen zusammenstellen, die wir wollen. Nein, das lässt sich nicht anpassen, aber uns schien, dass das so am bequemsten sein würde.

Zuordnung der Knoten der Anfrage und des Plans

Lassen Sie uns nun ansehen, wie wir den Plan, den wir im ersten Schritt besprochen haben, mit der Anfrage, die wir im zweiten Schritt behandelt haben, kombinieren können.

Nehmen wir ein einfaches Beispiel: Wir haben eine Anfrage, die eine CTE erstellt und zweimal daraus liest. Das generiert einen solchen Plan.

CTE

Wenn man genau hinsieht, sieht man, dass bis zur Version 12 (oder ab dieser mit dem Schlüsselwort MATERIALIZED) die Erstellung der CTE eine absolute Barriere für den Planer ist..

Das bedeutet, wenn wir irgendwo in der Anfrage eine CTE-Generierung sehen und irgendwo im Plan einen Knoten CTE, dann stehen diese Knoten eindeutig im Konflikt miteinander, und wir können sie sofort zusammenlegen.

Die „Sternchenaufgabe“: CTE können geschachtelt sein.

Es gibt sehr schlecht geschachtelte, und sogar gleichnamige. Zum Beispiel können Sie innerhalb von CTE A eine CTE Xmachen und auf gleicher Ebene innerhalb von CTE B ein weiteres Mal 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 schwierig, das mit den „Augen“ zu erfassen – selbst beim Betrachten des Plans oder des Anfragetextes. Wenn die CTE-Generierung komplex und geschachtelt ist und die Anfragen groß sind, ist es sogar völlig unbewusst.

UNION

Falls in unserer Anfrage das Schlüsselwort vorhanden ist UNION [ALL] (der Operator, der zwei Abfragen verbindet), entspricht ihm entweder ein Knoten Anhängen, oder irgendein Rekursiver Verband.

Das, was „oben“ über UNION — ist der erste Nachkomme unseres Knotens, und was „unten“ — der zweite. Wenn durch UNION wir gleichzeitig mehrere Blöcke „verkleben“, dann Anhängen-der Knoten wird trotzdem nur einer sein, aber seine Nachkommen werden nicht zwei, sondern viele sein — in der Reihenfolge, wie sie erscheinen:

  (...) -- #1
UNION ALL
  (...) -- #2
UNION ALL
  (...) -- #3

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

Die „Sternchenaufgabe“: in der Generierung einer rekursiven Abfrage (WITH RECURSIVE) kann es auch mehr als einen geben UNION. Aber rekursiv ist immer nur der letzte Block nach dem letzten UNION. Alles, was darüber ist, ist ein, aber ein anderer UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, hier endet die Generierung des Startzustands der Rekursion
UNION ALL
  (...) -- #3, nur dieser Block ist rekursiv und kann auf T verweisen
)
...

Solche Beispiele muss man auch „entkleben“ können. In diesem Beispiel sehen wir, dass UNION-Segmente in unserer Abfrage 3 Stück waren. Entsprechend hat der eine UNION entspricht Anhängen-Knoten, und der andere — Rekursiver Verband.

Lese-Schreibdaten

Alles klar, wir haben es aufgeschlüsselt. Nun wissen wir, welches Stück der Anfrage zu welchem Teil des Plans gehört. In diesen Teilen können wir leicht und mühelos die Objekte finden, die »auslesbar« sind.

Aus der Sicht der Anfrage wissen wir nicht, ob es sich um eine Tabelle oder ein CTE handelt, aber sie werden durch denselben Knoten gekennzeichnet. Bereichsvariable. Im Plan ist »auslesbar« - was ebenfalls einen begrenzten Satz an Knoten darstellt:

Seq-Scan auf [tbl]
Bitmap-Haufen-Scan auf [tbl]
Index [Only] Rückwärts-Scan unter Verwendung von [idx] bei [tbl]
CTE-Scan auf [cte]
Einfügen/Update/Löschen auf [tbl]

Wir kennen die Struktur des Plans und der Anfrage, wissen, welche Blöcke übereinstimmen und welche Objektnamen es gibt - wir stellen eine eindeutige Zuordnung her.

Wiederum eine Aufgabe mit einem besonderen Zeichen. Wir nehmen die Anfrage, führen sie aus, und wir haben keine Aliase - wir haben einfach zweimal aus einem einzigen CTE gelesen.

Sehen wir uns den Plan an - was ist das Problem? Warum taucht unser Alias auf? Den hatten wir nicht bestellt. Woher kommt dieser »nummerierte« Alias?

PostgreSQL fügt ihn automatisch hinzu. Wir müssen einfach verstehen, dass genau dieser Alias für unsere Zuordnungsziele mit dem Plan keine Bedeutung hat, er wurde einfach hier hinzugefügt. Lassen wir ihn außer Acht.

Die zweite eine Aufgabe mit einem besonderen Zeichen: Wenn wir aus einer partitionierten Tabelle lesen, erhalten wir einen Knoten Anhängen oder Zusammenführen Anhängen, der aus einer großen Anzahl von »Kindern« besteht, und jede von ihnen wird irgendeine Scanvon der Abschnittstabelle sein: Seq-Scan, Bitmap-Haufen-Scan oder Index Scan. Aber in jedem Fall werden diese „Kinder“ keine komplexen Anfragen sein – so können diese Knoten unterschieden werden von Anhängen bei UNION.

Solche Knoten verstehen wir ebenfalls, sammeln sie „zu einer Gruppe“ und sagen: "alles, was du aus megatable gelesen hast – das hier und weiter unten im Baum".

„Einfache“ Knoten zur Datenbeschaffung

Werte scannen entspricht WERTE in der Anfrage.

Ergebnis – das ist eine Anfrage ohne FROM wie SELECT 1. Oder wenn du eine absichtlich falsche Bedingung in der WHERE-Block (dann entsteht das Attribut One-Time Filter):

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

Ergebnis (Kosten=0,00..0,00 Zeilen=0 Breite=230) (tatsächliche Zeit=0,000..0,000 Zeilen=0 Schleifen=1)
  One-Time Filter: false

Funktionsscannen „mappen“ auf gleichnamige SRF.

Eingebettete Anfragen sind jedoch komplexer – leider verwandeln sie sich nicht immer in Plan starten/Unterplan. Manchmal verwandeln sie sich in ... Join oder ... Anti Join, besonders wenn du etwas schreibst wie WHERE NOT EXISTS .... Und dabei ist es nicht immer möglich, zu kombinieren – im Textplan der entsprechenden Knoten sind keine Operatoren vorhanden.

Wiederum eine Aufgabe mit einem besonderen Zeichen: mehrere WERTE in der Anfrage. In diesem Fall erhältst du auch mehrere Knoten im Plan Werte scannen.

Sie helfen, diese voneinander zu unterscheiden „nummerierte“ Suffixe – diese werden genau in der Reihenfolge hinzugefügt, in der die entsprechenden WERTE-Blöcke im Verlauf der Anfrage von oben nach unten gefunden werden.

Datenbearbeitung

Wir haben alles in unserer Anfrage durchgesprochen – nur noch Limit.

Aber hier ist alles einfach – solche Knoten wie Limit, Sortieren, Gesamt, Fenster-Aggregat, Eindeutig werden eins zu eins auf die entsprechenden Operatoren in der Anfrage gemappt, sofern diese vorhanden sind. Hier gibt es keine "Sternchen" und keine Komplikationen.

JOIN

Die Schwierigkeiten treten auf, wenn wir kombinieren möchten JOIN untereinander. Das ist nicht immer möglich, aber machbar.

Aus der Sicht des Anfrageparsers haben wir einen Knoten JoinExpr, der genau zwei Nachfolger hat – links und rechts. Das entspricht dem, was "über" Ihrem JOIN steht und dem, was "darunter" in der Anfrage geschrieben ist.

Und aus der Sicht des Plans ist das zwei Nachfolger eines * Schleife/* Treten Sie bei-Knotens. Verschachtelter Loop, Hash Anti Join,… – so etwas in der Art.

Nutzen wir eine einfache Logik: Wenn wir die Tabellen A und B haben, die im Plan "joinen", konnten sie in der Anfrage entweder so positioniert sein: A-JOIN-B, oder B-JOIN-A. Probieren wir es so aus, probieren wir es umgekehrt aus, und so weiter, bis solche Paare erschöpft sind.

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

Lassen Sie es uns in Form von Graphen neu zeichnen – oh, das sieht 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. Lassen Sie uns diese kombinieren und das Bild des Knotens umkehren.

Schauen wir nochmal. Jetzt haben wir Knoten mit den Kindern A und dem Paar (B + C) – sie sind kompatibel.

Ausgezeichnet! Wir haben also diese beiden JOIN aus der Anfrage mit den Plan-Knoten erfolgreich kombiniert.

Leider lässt sich diese Aufgabe nicht immer lösen.

Zum Beispiel, wenn in der Anfrage A JOIN B JOIN C, und im Plan zunächst die "äußeren" Knoten A und C verbunden sind. Wenn solch ein Operator in der Anfrage fehlt, haben wir nichts zu markieren und können keinen Hinweis geben. Dasselbe gilt für das „Komma“, wenn Sie schreiben A, B.

Aber in den meisten Fällen gelingt es, fast alle Knoten "aufzulösen" und wir erhalten ein Profiling auf der linken Seite zeitlich – ähnlich wie in Google Chrome, wenn Sie JavaScript-Code analysieren. Sie sehen, wie lange jede Zeile und jeder Operator „ausgeführt“ wurden.

Um die Nutzung für Sie zu erleichtern, haben wir die Speicherung des Archivs, wo Sie Ihre Pläne zusammen mit den assoziierten Anfragen speichern oder einen Link mit jemandem teilen können.