Ausführungspläne lesen: der Baum, die Zahlen, der Ausreißer
Die Operatoren unterscheiden sich je System, das Vorgehen nicht: von innen nach außen lesen und den tiefsten Knoten suchen, an dem Schätzung und Wirklichkeit auseinanderlaufen.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Der Plan wird angesehen, aber nicht gelesen
Fast jede Datenbankoberfläche zeigt heute einen Plan an, oft bunt und mit Prozentangaben. Genau das führt in die Irre. Die Prozentzahlen im SQL Server stammen aus der Schätzung und nicht aus der Messung, und die grafische Darstellung legt nahe, dass der teuerste Kasten der langsamste Schritt ist. Beides stimmt oft nicht, und beides führt dazu, dass Stunden in den falschen Teil der Abfrage gesteckt werden.
Der zweite Fehler ist noch verbreiteter: Es wird ein Plan angesehen, den das System gar nicht ausgeführt hat. EXPLAIN ohne ANALYZE in PostgreSQL, EXPLAIN PLAN FOR in Oracle und der geschätzte Plan im SQL Server zeigen, was der Optimizer vorhat. Was er tatsächlich getan hat, kann davon abweichen, weil Bindewerte, adaptive Entscheidungen zur Laufzeit und wiederverwendete Pläne dazwischenkommen.
Dabei ist der Plan das Werkzeug, das als Erstes beantwortet, womit eine Abfrage ihre Arbeit verbringt. Ohne ihn bleibt nur das Umstellen von SQL nach Gefühl, und der Erfolg ist zufällig. Mit ihm reduziert sich die Suche meist auf einen einzigen Knoten, und wenn im Plan alles unauffällig aussieht, ist das ebenfalls eine Auskunft: Dann liegt die Ursache außerhalb der Abfrage, etwa bei einer Sperre.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Eine Planzeile, Stück für Stück
Das Beispiel stammt aus PostgreSQL, weil dort alle Angaben in einer Zeile stehen. Oracle, SQL Server und MySQL zeigen dieselben Größen an anderer Stelle, die Bedeutung ist überall die gleiche.
Der Aufbau
Hash Join (cost=214.30..1893.71 rows=482 width=64) (actual time=2.114..87.930 rows=51204 loops=1) - 01 Der Operator
Hash JoinDie Verknüpfungsart. Hash Join baut aus der kleineren Seite eine Hashtabelle und liest die größere einmal durch, was bei großen Mengen die richtige Wahl ist. Die Alternativen heißen Nested Loop und Merge Join. Oracle und der SQL Server kennen alle drei, MySQL erst seit 8.0.18 auch den Hash Join.
- 02 Die geschätzten Kosten
cost=214.30..1893.71Zwei Werte: die Kosten bis zur ersten Zeile und die bis zur letzten. Die Einheit ist willkürlich, ein Vergleich mit einem anderen System oder mit Sekunden ist sinnlos. Innerhalb eines Plans zeigt der Unterschied zwischen beiden Werten, ob der Operator sofort liefert oder erst alles einsammeln muss.
- 03 Die Schätzung
rows=482So viele Zeilen erwartet der Optimizer aus diesem Knoten. Die Zahl stammt aus den Statistiken, und auf ihr beruhen alle weiteren Entscheidungen des Plans, von der Verknüpfungsart bis zur Sortierstrategie.
- 04 Die gemessene Zeit
actual time=2.114..87.930Millisekunden bis zur ersten und bis zur letzten Zeile, und zwar je Durchlauf. Diese Angabe gibt es nur mit EXPLAIN ANALYZE, weil die Abfrage dafür wirklich ausgeführt wird. Bei einem UPDATE oder DELETE gehört deshalb eine Transaktion mit ROLLBACK drumherum.
- 05 Die Wirklichkeit
rows=51204So viele Zeilen kamen tatsächlich heraus. Gegen die geschätzten 482 ist das der Faktor hundert, und damit ist dieser Knoten der Ausgangspunkt der Fehlersuche. Oracle nennt die beiden Größen E-Rows und A-Rows, der SQL Server Geschätzte und Tatsächliche Anzahl von Zeilen.
- 06 Die Durchläufe
loops=1Wie oft dieser Knoten ausgeführt wurde. Zeit und Zeilenzahl gelten je Durchlauf und müssen mit diesem Wert multipliziert werden. Steht hier eine hohe Zahl, sitzt der Knoten auf der inneren Seite eines Nested Loop, und genau dort verstecken sich die teuren Pläne.
Wenn es nicht funktioniert
Das siehst du
Der Plan sieht sauber aus, die Abfrage braucht trotzdem Minuten.
Warum
Es ist der geschätzte Plan. EXPLAIN ohne ANALYZE, EXPLAIN PLAN FOR in Oracle und der geschätzte Plan im SQL Server zeigen die Absicht des Optimizers, nicht den Ablauf.
Was hilft
Den gemessenen Plan holen: EXPLAIN (ANALYZE, BUFFERS), in Oracle DBMS_XPLAN.DISPLAY_CURSOR mit dem Format ALLSTATS LAST und dem Hinweis GATHER_PLAN_STATISTICS, im SQL Server den tatsächlichen Ausführungsplan, in MySQL EXPLAIN ANALYZE.
Das siehst du
Im SQL Server zeigt ausgerechnet der langsamste Schritt fast null Prozent der Kosten.
Warum
Die Prozentangaben werden aus den geschätzten Kosten berechnet. Wenn die Schätzung danebenliegt, liegt auch die Verteilung daneben, und der teuerste Operator sieht harmlos aus.
Was hilft
Die tatsächliche Zeilenzahl je Operator ansehen und zusätzlich SET STATISTICS IO, TIME ON einschalten. Die gelesenen Seiten je Tabelle sagen mehr über den Aufwand als jeder Prozentwert.
Das siehst du
Ein untergeordneter Knoten braucht mehr Zeit als der Knoten darüber.
Warum
PostgreSQL gibt Zeit und Zeilen je Durchlauf an. Bei loops=1 fällt das nicht auf, bei mehreren tausend Durchläufen ergibt die ungerechnete Zahl ein völlig falsches Bild.
Was hilft
Zeit und Zeilen mit dem Wert bei loops multiplizieren. Oracle macht dasselbe über Starts mal E-Rows im Vergleich zu A-Rows sichtbar.
Das siehst du
Der Index steht im Plan, trotzdem wird fast die ganze Tabelle gelesen.
Warum
Die Bedingung wirkt als Filter und nicht als Zugriffsbedingung. Der Index liefert einen breiten Bereich, und aussortiert wird erst danach.
Was hilft
Bei PostgreSQL Index Cond gegen Filter und Rows Removed by Filter prüfen, bei Oracle den Abschnitt Predicate Information mit access und filter, im SQL Server die Eigenschaften Seek Predicates gegen Predicate. Danach die Indexspalten so ordnen, dass die einschränkende Bedingung zur Zugriffsbedingung wird.
Das siehst du
Im MySQL-Plan steht Using temporary; Using filesort, und die Abfrage schwankt stark in der Laufzeit.
Warum
Für Sortierung oder Gruppierung reicht kein Index aus, MySQL legt eine temporäre Tabelle an und sortiert nach. Passt sie nicht in den Arbeitsspeicher, geht sie auf die Platte, und die Laufzeit springt.
Was hilft
Einen Index anlegen, der die Sortierreihenfolge bereits liefert, also die Spalten aus ORDER BY in derselben Reihenfolge und Richtung enthält. Wo das nicht geht, die Ergebnismenge vor dem Sortieren verkleinern.
Sechs Fragen an jeden Plan, in dieser Reihenfolge
- 01 Ist das ein gemessener Plan oder nur der geschätzte?
- 02 Welcher Knoten ist der tiefste mit grob falscher Zeilenschätzung?
- 03 Wird auf die Daten über den Index oder über die ganze Tabelle zugegriffen?
- 04 Wie viele Zeilen wirft ein Filter nach dem Lesen wieder weg?
- 05 Passt die Verknüpfungsart zur tatsächlichen Menge auf der äußeren Seite?
- 06 Wie oft läuft der teure Knoten, also wie hoch ist die Zahl der Durchläufe?
Was du nach dieser Seite an jedem Plan erkennst
Es geht nicht darum, alle Operatoren auswendig zu kennen. Eine knappe Handvoll deckt den Alltag ab, und die eigentliche Arbeit besteht darin, zwei Zahlen je Knoten zu vergleichen und die Stelle zu finden, an der die Annahmen des Optimizers zerbrechen.
Den tatsächlichen Plan holen
EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL, GATHER_PLAN_STATISTICS mit DBMS_XPLAN.DISPLAY_CURSOR in Oracle, der tatsächliche Ausführungsplan im SQL Server, EXPLAIN ANALYZE in MySQL. Nur diese Varianten enthalten gemessene Werte.
Von innen nach außen lesen
Der am tiefsten eingerückte Knoten läuft zuerst, sein Ergebnis wandert nach oben. In der grafischen Darstellung des SQL Servers entspricht das der Leserichtung von rechts nach links.
Schätzung gegen Wirklichkeit stellen
Eine Abweichung um den Faktor zwei ist normal, um den Faktor hundert nicht. Der tiefste Knoten mit einer groben Abweichung ist die Ursache, alle Knoten darüber erben den Fehler nur.
Zugriffs- und Filterbedingung unterscheiden
Was als Zugriffsbedingung im Index steht, grenzt die gelesenen Zeilen ein. Was als Filter danebensteht, wird erst nach dem Lesen weggeworfen. PostgreSQL beziffert das als Rows Removed by Filter.
Die Verknüpfungsart bewerten
Nested Loop ist bei wenigen Zeilen auf der äußeren Seite richtig und bei vielen katastrophal. Hash Join lohnt sich bei großen Mengen. Ein falsch gewählter Join ist fast immer die Folge einer falschen Schätzung.
Wiederholungen mitrechnen
PostgreSQL gibt Zeit und Zeilen je Durchlauf an. Bei loops=5000 musst du multiplizieren, sonst wirkt der teuerste Knoten des Plans wie eine Nebensächlichkeit.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Die Leserichtung ist überall dieselbe, die Darstellung nicht
PostgreSQL, Oracle und MySQL zeigen den Plan als eingerückten Text. Was am weitesten eingerückt ist, läuft zuerst, und die Ergebnisse wandern nach oben zur Wurzel. Der SQL Server zeigt denselben Baum grafisch, gedreht um neunzig Grad: Die Blätter liegen rechts, die Wurzel links, gelesen wird also von rechts nach links und innerhalb einer Ebene von oben nach unten.
MySQL fällt aus der Reihe. Das klassische EXPLAIN liefert eine Tabelle mit einer Zeile je zugegriffener Tabelle, in der die Reihenfolge grob der Verknüpfungsreihenfolge entspricht. Die Spalte type ist dabei die wichtigste: ALL bedeutet vollständiger Tabellenzugriff, index bedeutet vollständiges Lesen des Index, range einen Bereich, ref einen Zugriff über einen nicht eindeutigen Index, eq_ref und const die günstigsten Fälle. Seit 8.0.16 liefert EXPLAIN FORMAT=TREE einen Baum, der sich wie ein PostgreSQL-Plan liest.
Bei Oracle lohnt sich der Blick unter die Tabelle. Der Abschnitt Predicate Information nennt für jede Zeilennummer, ob eine Bedingung als access oder als filter gewertet wurde. Das ist die schnellste Antwort auf die Frage, ob ein Index wirklich einschränkt oder nur mitgelesen wird.
Die Handvoll Operatoren, die im Alltag reicht
Der Zugriff auf die Daten kommt in zwei Formen vor. Der vollständige Tabellenzugriff heißt Seq Scan in PostgreSQL, TABLE ACCESS FULL in Oracle, Table Scan oder Clustered Index Scan im SQL Server und type=ALL in MySQL. Er ist nicht per se schlecht, denn für einen großen Teil der Tabelle ist er die günstigste Variante. Der Indexzugriff heißt Index Scan, INDEX RANGE SCAN oder Index Seek und liefert zunächst nur Verweise.
Danach folgt oft ein zweiter Schritt, der leicht übersehen wird. Oracle nennt ihn TABLE ACCESS BY INDEX ROWID, der SQL Server Key Lookup, PostgreSQL zeigt ihn beim Bitmap Heap Scan als eigenen Knoten. Er holt die Spalten, die nicht im Index stehen, und läuft je Trefferzeile einmal. Steht dagegen Index Only Scan oder im SQL Server ein Seek ohne anschließenden Lookup, ist der Index abdeckend und der Zugriff auf die Tabelle entfällt.
Bei den Verknüpfungen genügen drei Namen. Nested Loop geht für jede Zeile der äußeren Seite einmal in die innere und ist damit nur bei wenigen äußeren Zeilen gut. Hash Join baut aus der kleineren Seite eine Hashtabelle und ist die Wahl für große Mengen. Merge Join verlangt beide Seiten sortiert und ist billig, wenn die Sortierung ohnehin vorliegt. Sortieren und Gruppieren erscheinen als Sort, Aggregate, HashAggregate oder als Hash Match Aggregate, und ein Sort-Knoten mit Auslagerung auf die Platte ist ein häufiger Grund für schwankende Laufzeiten.
Geschätzt gegen tatsächlich: das eigentliche Werkzeug
Der Optimizer trifft jede Entscheidung auf Grundlage der erwarteten Zeilenzahl. Erwartet er zwanzig Zeilen und bekommt zwanzigtausend, hat er die Verknüpfungsart falsch gewählt, den Speicher für die Sortierung zu knapp bemessen und womöglich einen Indexzugriff genommen, der jetzt zwanzigtausend Einzelzugriffe auf die Tabelle bedeutet. Die Laufzeit ist die Folge, nicht die Ursache.
Deshalb gilt eine einfache Regel: Such den tiefsten Knoten, an dem Schätzung und Wirklichkeit deutlich auseinandergehen. Alles darüber erbt den Fehler und ist nicht die Ursache. In PostgreSQL vergleichst du rows= aus dem Kostenteil mit rows= aus dem actual-Teil, in Oracle E-Rows mit A-Rows, im SQL Server die geschätzte mit der tatsächlichen Anzahl von Zeilen in den Eigenschaften des Operators, in MySQL die beiden Angaben aus EXPLAIN ANALYZE.
Ein Zusatz für Oracle, der viel Zeit spart: Multipliziere E-Rows mit Starts, bevor du vergleichst. E-Rows gilt je Ausführung, A-Rows summiert über alle. Ohne diese Umrechnung sieht ein Nested Loop, der zehntausendmal läuft, wie ein grober Schätzfehler aus, obwohl die Schätzung je Durchlauf stimmt.
Warum die Schätzung danebenliegt
Die häufigste Ursache sind veraltete Statistiken, besonders nach Massenimporten. Die zweithäufigste ist die Annahme, dass Spalten unabhängig voneinander sind. Bei einer Bedingung auf Postleitzahl und Ort multipliziert der Optimizer zwei Wahrscheinlichkeiten und landet bei einem Bruchteil der wahren Trefferzahl. Dagegen helfen mehrspaltige Statistiken: CREATE STATISTICS in PostgreSQL, erweiterte Statistiken auf Spaltengruppen in Oracle, eine benutzerdefinierte Statistik über beide Spalten im SQL Server.
Die dritte Ursache sind Werte, die der Optimizer beim Planen nicht kennt. Beim SQL Server heißt das Parameter Sniffing: Der Plan wird für den ersten übergebenen Wert gebaut und danach wiederverwendet, auch wenn der nächste Wert eine ganz andere Menge trifft. Oracle nennt dasselbe Bind Peeking und begegnet ihm mit adaptiven Cursorn. In PostgreSQL entsteht die Situation bei vorbereiteten Anweisungen, die nach mehreren Ausführungen auf einen generischen Plan umschalten.
Die vierte Ursache steckt in Konstruktionen, durch die der Optimizer nicht hindurchsieht: benutzerdefinierte Funktionen in der WHERE-Bedingung, verschachtelte Sichten über mehrere Ebenen und Berechnungen über mehrere Tabellen hinweg. Dazu gehörten lange auch Tabellenvariablen im SQL Server, für die der Optimizer pauschal eine einzige Zeile annahm; seit Version 2019 mit Kompatibilitätsgrad 150 schätzt er sie anhand der tatsächlichen Zeilenzahl. Erkennbar sind solche Stellen an einer Schätzung, die verdächtig glatt aussieht, etwa genau eine Zeile oder genau ein Zehntel der Tabelle.
Was der Plan nicht zeigt
Ein Plan erklärt, wie viel Arbeit eine Abfrage macht. Er erklärt nicht, wie lange sie auf andere gewartet hat. Eine Abfrage, die auf eine Sperre wartet, hat einen tadellosen Plan und trotzdem eine Laufzeit von dreißig Sekunden. Wenn die Zahlen im Plan gut aussehen und die Uhr etwas anderes sagt, ist die nächste Frage keine Frage an den Optimizer, sondern eine an das Sperrverhalten.
Auch der Zugriff auf den Speicher steht nicht im Standardplan. In PostgreSQL liefert die Option BUFFERS die Treffer im Cache und die Lesevorgänge von der Platte, und erst dieser Vergleich zeigt, ob eine Abfrage im warmen oder im kalten Zustand gemessen wurde. Im SQL Server übernimmt SET STATISTICS IO ON diese Rolle, in Oracle die Spalten Buffers und Reads im Format ALLSTATS.
Und schließlich zeigt der Plan nicht, ob die Abfrage überhaupt nötig ist. Der teuerste Knoten in vielen Plänen ist eine Sortierung für ein ORDER BY, das niemand liest, oder ein DISTINCT, das eine fehlende Verknüpfungsbedingung überdeckt. Bevor du an Indizes gehst, lohnt der Blick, ob die Abfrage weniger Daten anfordern kann.
Dazu passende Kurse
Der Umgang mit einem Plan lässt sich schlecht nachlesen und gut nachvollziehen, deshalb gehören zu Trainings, in denen du Ausführungspläne gemeinsam auswertest eigene Abfragen aus dem Arbeitsalltag.
Wer täglich mit E-Rows, A-Rows und der Predicate Information arbeitet, findet in die Oracle-Seminare mit eigenem Kapitel zum Optimizer den passenden Rahmen.
Wie sicher bist du beim Thema wirklich?
Lesen fühlt sich schnell nach Können an. Ein kurzer Test zeigt dir, was davon schon sitzt und wo sich ein Kurs lohnt. Kostenlos, ohne Anmeldung, mit einer Erklärung zu jeder Antwort.
Wo du genau das übst
Super Schulung, in der der Trainer auch auf eigene Fragestellungen eingegangen ist.
Super Grundlagenschulung mit allen wichtigen Punkten und hilfreichen Tipps!
Schön, sich 5 Tage ungestört mit Postgres beschäftigen zu können.
Häufige Fragen
Reicht EXPLAIN ohne ANALYZE für eine erste Einschätzung?
Warum sieht der Plan im Werkzeug anders aus als im Betrieb?
Lassen sich Pläne aus zwei verschiedenen Systemen vergleichen?
Was mache ich mit dem Hinweis auf einen fehlenden Index im SQL-Server-Plan?
Passt thematisch dazu
Die Befehle, mit denen du einen Plan in PostgreSQL anforderst, und der Hinweis, dass EXPLAIN ANALYZE ein UPDATE wirklich ausführt, stehen in einer kompakten Einordnung von EXPLAIN und EXPLAIN ANALYZE .
Deine Ansprechpartner
Du bist dir nicht sicher, welcher Kurs oder welches Level zu dir passt? Wir beraten dich persönlich und kostenlos.
Yves Hoppe
Weiterbildung & Beratung
Hilft dir, aus dem Datenbanken-Programm den passenden Kurs für deinen Stand zu finden.
Norbert Jansen
Beratung & Inhouse
Plant mit dir Inhouse-Trainings, die auf eure Abläufe und euren Datenbestand zugeschnitten sind.
Pläne lesen ist eine Fertigkeit, keine Begabung
An eigenen Abfragen und mit einem Blick auf mehrere Systeme wird aus dem bunten Diagramm bei cmt eine Arbeitsanleitung.