Langsame Abfragen erkennen und beheben

Dies ist die abschließende Interviewaufgabe: Der Interviewer gibt Ihnen eine langsame Abfrage und einen EXPLAIN ANALYZE-Plan und bittet Sie, die Ursache zu diagnostizieren. Geprüft wird eine Methode, keine auswendig gelernten Tricks.

Eine gute Antwort folgt laut einer Checkliste: messen, den Plan lesen, die dominanten Kosten finden, eine Hypothese aufstellen, eine Lösung vorschlagen und sie überprüfen. Diese Lektion baut diese Checkliste Schritt für Schritt auf.

Bleiben Sie systematisch und erläutern Sie Ihre Überlegungen. Genau das bringt Ihnen die Bewertung auf Senior-Level.

Raten Sie niemals allein anhand des SQL. Ermitteln Sie den tatsächlichen Plan mit EXPLAIN (ANALYZE, BUFFERS).

ANALYZE liefert tatsächliche Zeiten und Zeilenzahlen; BUFFERS zeigt, ob Sie aus dem Cache lesen oder von der Festplatte. Zusammen zeigen diese Informationen, ob die Abfrage CPU-gebunden oder E/A-gebunden ist oder einfach zu viel Arbeit verrichtet.

Führen Sie den Befehl einige Male aus. Beim ersten Durchlauf kann ein leerer Cache die Zeitmessung verfälschen.

Lesen Sie den Plan nicht wahllos von oben nach unten. Suchen Sie den Knoten, an dem tatsächlich die meiste Zeit verbraucht wird.

Berechnen Sie die Eigenzeit jedes Knotens: seine gesamte actual time abzüglich der Zeit seiner untergeordneten Knoten, multipliziert mit loops. Der Knoten mit dem größten Anteil ist Ihr Ziel; alles andere ist nebensächlich.

Sagen Sie im Vorstellungsgespräch: 80 Prozent der Laufzeit entfallen auf diesen einen Seq Scan, deshalb konzentriere ich mich darauf. Alles andere zu optimieren wäre vertane Mühe.

Vergleichen Sie am dominanten Knoten die geschätzten mit den tatsächlichen Zeilen. Eine große Abweichung bedeutet, dass der Planer im Blindflug arbeitet und wahrscheinlich einen falschen Plan gewählt hat (falscher Join-Algorithmus, falsche Zugriffsmethode).

Das Beispiel zeigt eine 1000-fache Unterschätzung. Aktualisieren Sie die Statistiken, bevor Sie etwas neu entwerfen. Dieser einzelne Befehl korrigiert den Plan oft kostenlos.

ANALYZE berechnet die Spaltenstatistiken neu; VACUUM ANALYZE bereinigt außerdem veraltete Tupel und aktualisiert die Visibility Map.

Der häufigste behebbare Fehler: Eine Funktion oder ein Cast umschließt die Spalte in WHERE, sodass der Index nicht verwendet werden kann und die Engine einen Seq Scan ausführt.

Das Beispiel erzwingt einen vollständigen Scan, weil DATE() auf jede Zeile angewendet wird. Formulieren Sie die Bedingung als Bereichsprädikat auf der unveränderten Spalte (sargable Form), dann greift der Index auf created_at.

Dasselbe gilt für WHERE lower(email)=...: Speichern Sie entweder normalisierte Daten, fragen Sie die unveränderte Spalte ab oder erstellen Sie einen Ausdrucksindex.

Wenn der dominante Knoten ein Seq Scan mit einem hochselektiven Filter ist oder ein Nested Loop mit sehr vielen loops auf einem nicht indizierten inneren Schlüssel, ist die Lösung normalerweise ein Index.

Erstellen Sie einen Index auf der gefilterten oder verknüpften Spalte. Das Beispiel erstellt einen auf customer_id, sodass der Join von Seq Scans auf Index Scans wechseln kann und der Planer möglicherweise einen deutlich günstigeren Plan auswählt.

Überprüfen Sie das Ergebnis, indem Sie EXPLAIN ANALYZE erneut ausführen. Nehmen Sie nicht einfach an, dass der Index geholfen hat.

SELECT * liest jede Spalte von der Festplatte und über das Netzwerk und verhindert Index-Only-Scans, weil der Index selten alle Spalten abdeckt.

Wählen Sie nur die benötigten Spalten aus. Dadurch wird die Zeilenbreite kleiner, die E/A-Last sinkt und ein abdeckender Index-Only-Scan kann möglich werden.

Ein Interviewer, der SELECT * gezielt einsetzt, möchte sehen, ob Sie es bemerken. Das Kürzen der Spaltenliste bringt bei breiten Tabellen oft schnell einen echten Vorteil.

Wenn ein Sort- oder Hash-Knoten Festplattennutzung meldet (Sort Method: external merge Disk: 25000kB oder Batches: > 1), hat der Vorgang work_mem überschritten und wurde auf die Festplatte ausgelagert.

Optionen: Erhöhen Sie work_mem für die Sitzung, verringern Sie die Anzahl der Zeilen, die die Sortierung oder den Hash erreichen (früher filtern), oder fügen Sie einen Index hinzu, der die sortierte Reihenfolge liefert, sodass überhaupt keine Sortierung erforderlich ist.

Das ist eine präzise Diagnose auf Senior-Level, die Interviewer anerkennen.

Achten Sie auf Rows Removed by Filter: 9500000. Die Abfrage hat zehn Millionen Zeilen gelesen und fast alle verworfen – ein klassisches Beispiel für unnötige Arbeit.

Mögliche Lösungen: Erstellen Sie einen Index, damit der Filter bereits beim Zugriff angewendet wird (nicht erst danach), formulieren Sie das Prädikat selektiver oder verlagern Sie die Filterung in der Abfrage nach vorne, sodass weniger Zeilen im Baum nach oben weitergereicht werden.

Das Prinzip lautet: Verrichten Sie so wenig Arbeit wie möglich und filtern Sie so früh und so günstig wie möglich.

Gehen Sie diese Punkte im Vorstellungsgespräch durch, dann verlieren Sie nicht den roten Faden:

• Messen Sie mit EXPLAIN (ANALYZE, BUFFERS).
• Lokalisieren Sie den Knoten, der am meisten Zeit verbraucht.
• Vergleichen Sie geschätzte und tatsächliche Zeilen und korrigieren Sie zuerst veraltete Statistiken.
• Prüfen Sie die Sargability und entfernen Sie Funktionen aus gefilterten Spalten.
• Indizieren Sie selektive Filter und Join-Schlüssel.
• Beschränken Sie die Spalten und vermeiden Sie SELECT *.
• Achten Sie auf Auslagerungen auf die Festplatte und das Abrufen überflüssiger Zeilen.
• Überprüfen Sie das Ergebnis, indem Sie den Plan erneut ausführen.

Gehen Sie ein vollständiges Beispiel Schritt für Schritt durch. Der Plan zeigt einen Seq Scan auf einer orders-Tabelle mit 50 Millionen Zeilen, den Filter customer_id = 42 und Rows Removed by Filter nahe 50 Millionen. Die Schätzung stimmt ungefähr mit dem tatsächlichen Wert überein.

Diagnose: selektiver Filter, kein Index, die dominante Kostenstelle ist der Scan. Lösung: CREATE INDEX ON orders(customer_id). Führen Sie die Abfrage erneut aus: Der Plan wechselt zu einem Index Scan, und die Laufzeit sinkt von mehreren Sekunden auf unter eine Millisekunde.

Diese Maßnehmen-diagnostizieren-beheben-überprüfen-Schleife ist das Antwortschema für jede Frage zu einer langsamen Abfrage.

Eine Abfrage filtert mit WHERE YEAR(order_date) = 2026, und der Plan zeigt trotz eines vorhandenen B-Tree-Indexes auf order_date einen vollständigen Seq Scan. Was ist die beste erste Lösung?

Sie verfügen jetzt über eine wiederholbare Methode für Fragen zu langsamen Abfragen:

• Führen Sie immer eine Messung mit EXPLAIN (ANALYZE, BUFFERS) durch und konzentrieren Sie sich auf den dominanten Knoten.
• Beheben Sie zuerst veraltete Statistiken, wenn Schätzungen und tatsächliche Werte voneinander abweichen.
• Formulieren Sie Prädikate sargable, fügen Sie Indizes für selektive Filter und Join-Schlüssel hinzu und vermeiden Sie SELECT *.
• Beheben Sie Spills auf die Festplatte und Over-Fetching und überprüfen Sie anschließend den neuen Plan.

Wenn Sie die Checkliste erläutern, eine konkrete Änderung vorschlagen und den Plan anschließend erneut ausführen, um die Verbesserung zu belegen, ist das eine Antwort auf Senior-Niveau.