Optimierung von SQL-Abfragen

Was ist ein Ausführungsplan in SQL?

Execution Plan, Query-Plan

Der Ausführungsplan ist die Schrittfolge, die eine Datenbank für eine SQL-Abfrage wählt, um an die Daten zu kommen. Du beschreibst in SQL nur das Ergebnis, und der Optimizer entscheidet anhand von Statistiken, welche Tabellen in welcher Reihenfolge gelesen und wie sie verbunden werden.

Zwei Abfragen mit demselben Ergebnis können sich in der Laufzeit um Größenordnungen unterscheiden, und der Plan zeigt dir, an welcher Stelle das passiert.

KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt

Erzeugt von: Query-Optimizer der Datenbank
Abrufbar mit: EXPLAIN und EXPLAIN ANALYZE, im SQL Server über SHOWPLAN
Beruht auf: Statistiken über die Verteilung der Werte
Warnsignal: Geschätzte und echte Zeilenzahl weichen stark ab

So liest du den Plan

In PostgreSQL stellst du EXPLAIN vor die Abfrage und bekommst den Plan als Baum. Gelesen wird er von innen nach außen, die am weitesten eingerückten Knoten laufen zuerst. Jeder Knoten trägt eine Schätzung mit Startkosten, Gesamtkosten, erwarteter Zeilenzahl und Breite der Zeile. Die Kosten sind eine dimensionslose Rechengröße, keine Millisekunden.

Interessant wird es mit EXPLAIN ANALYZE, denn dann wird die Abfrage tatsächlich ausgeführt und der Plan zusätzlich mit den gemessenen Werten ausgegeben. Vergleiche in jedem Knoten die geschätzte mit der tatsächlichen Zeilenzahl. Weichen beide um Größenordnungen ab, hat der Optimizer auf falscher Grundlage entschieden, und alle Folgeentscheidungen im Baum sind ebenfalls fragwürdig.

Ergänze die Ausgabe um BUFFERS, dann siehst du, wie viele Blöcke aus dem Cache und wie viele von der Platte kamen. Eine Abfrage, die beim zweiten Aufruf schnell ist, war oft nicht optimiert, sondern nur warmgelaufen.

Was die häufigsten Knoten bedeuten

Ein Seq Scan liest die Tabelle komplett. Das ist kein Fehler, sondern bei kleinen Tabellen oder bei Abfragen, die ohnehin die meisten Zeilen brauchen, der schnellste Weg. Ein Index Scan springt über den Index in die Tabelle und lohnt sich, wenn nur wenige Zeilen zurückkommen. Beim Index Only Scan reichen die Werte im Index, die Tabelle wird gar nicht mehr angefasst.

Bei Verbindungen entscheidet die Zeilenzahl. Ein Nested Loop durchläuft für jede Zeile der äußeren Seite die innere und ist bei wenigen äußeren Zeilen unschlagbar, wird aber bei falscher Schätzung zur Katastrophe. Hash Join baut eine Hashtabelle im Speicher auf und passt zu großen Mengen ohne passende Sortierung, Merge Join setzt sortierte Eingaben voraus.

Achte auf Sort- und Aggregate-Knoten mit dem Hinweis auf externen Speicher. Reicht der Arbeitsspeicher für die Sortierung nicht, weicht die Datenbank auf die Platte aus, und das kostet mehr Zeit als jeder Indexzugriff.

Die typische Fehlerursache

Wenn die Schätzungen daneben liegen, sind meist die Statistiken veraltet. Nach einer großen Ladung oder einer Massenänderung lohnt ein ANALYZE auf der betroffenen Tabelle, bevor du anfängst, Indizes zu bauen. Autovacuum erledigt das im Normalbetrieb, kommt aber bei Massenoperationen manchmal zu spät.

Der zweite Klassiker ist eine Funktion um die Spalte in der Bedingung. Sobald du eine Spalte in einen Ausdruck steckst, kann der normale Index darauf nicht mehr greifen und die Datenbank liest wieder die ganze Tabelle. Entweder formulierst du die Bedingung so um, dass die Spalte allein steht, oder du legst einen Index auf genau diesen Ausdruck.

Und bedenke, dass EXPLAIN ANALYZE die Abfrage wirklich ausführt. Bei einem UPDATE oder DELETE gehört sie in eine Transaktion, die du anschließend zurückrollst, sonst analysierst du deine Daten nicht, sondern änderst sie.

KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt

Nicht verwechseln

Ausführungsplan und was oft damit gleichgesetzt wird

Ausführungsplan gegen Index

Der Index ist eine Datenstruktur, der Ausführungsplan die Entscheidung, ob sie benutzt wird. Ein vorhandener Index bringt nichts, wenn der Optimizer ihn für teurer hält als den vollständigen Tabellendurchlauf.

Ausführungsplan gegen Query-Optimizer

Der Optimizer ist der Teil der Datenbank, der Pläne erzeugt und bewertet. Der Ausführungsplan ist das Ergebnis dieser Arbeit.

Ausführungsplan gegen Slow-Query-Log

Das Protokoll langsamer Abfragen zeigt, welche Anweisung Zeit kostet. Warum sie das tut, erfährst du erst über den Plan.

KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt

In der Praxis

Fast immer sind es alte Statistiken oder ein Index, der nicht greift

Die Datenbank rät nicht, sie rechnet mit Statistiken über die Verteilung der Werte. Aktualisiert nach einem großen Import niemand diese Statistiken, hält der Optimizer eine Tabelle für klein, die inzwischen groß ist, und wählt ein Verfahren, das dafür nicht taugt. Liegen geschätzte und tatsächliche Zeilenzahl im Plan weit auseinander, ist das die erste Spur.

Der zweite Dauerbrenner: Der Index liegt auf der Spalte, aber die Bedingung steckt die Spalte in eine Funktion, etwa beim Vergleich von Datumsteilen oder beim Kleinschreiben für einen Textvergleich. Dann muss die Datenbank jede Zeile anfassen, obwohl der Index existiert, und im Plan steht statt eines Indexzugriffs ein vollständiger Durchlauf.

Und der Plan aus der Testumgebung sagt wenig, wenn dort nur ein Bruchteil der Daten liegt. Bei kleinen Tabellen ist der vollständige Durchlauf oft die schnellste Wahl, und genau derselbe Plan fällt im Produktivbetrieb auseinander. Deshalb lohnt der Blick auf den tatsächlich ausgeführten Plan mit echten Zeiten statt auf die reine Schätzung.

Ausführungsplan lernen

Wie du vom Plan zur konkreten Maßnahme kommst, statt auf Verdacht Indizes anzulegen, üben die PostgreSQL-Trainings für Entwicklung und Betrieb an eigenen Abfragen.

Kurse, die Ausführungsplan behandeln

Häufige Fragen

Was ist der Unterschied zwischen EXPLAIN und EXPLAIN ANALYZE?

EXPLAIN zeigt nur den geplanten Weg mit Schätzwerten und führt nichts aus. EXPLAIN ANALYZE führt die Abfrage aus und ergänzt gemessene Zeiten und tatsächliche Zeilenzahlen, was den Vergleich von Schätzung und Wirklichkeit erst möglich macht.

Ist ein Seq Scan immer schlecht?

Nein. Bei kleinen Tabellen und bei Abfragen, die einen großen Teil der Zeilen liefern, ist das sequentielle Lesen der günstigste Weg. Kritisch wird es erst, wenn eine große Tabelle vollständig gelesen wird, obwohl nur eine Handvoll Zeilen gebraucht wird.

Warum ändert sich der Plan plötzlich?

Weil sich die Datenmenge oder die Verteilung der Werte geändert hat und der Optimizer daraufhin anders rechnet. Auch neue Indizes, geänderte Speicherparameter und ein Versionswechsel der Datenbank können den Plan kippen.

Persönlich für dich da