SQL EXPLAIN-Ausgabe muss nicht wie fremdes Texte aussehen

Sie führen EXPLAIN eine langsame Abfrage aus. Die Datenbank gibt Ihnen etwas wie folgt zurück:

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000

Sie starren darauf zehn Sekunden lang. Sie fügen zufällig einen Index hinzu. Sie starten die Anwendung neu und hoffen. Das ist keine Debugging-Strategie – das ist Superstition mit zusätzlichen Schritten.

EXPLAIN gibt Ihnen tatsächlich etwas Spezifisches und Handlungsorientiertes. Hier ist, wie Sie es lesen können.

EXPLAIN vs EXPLAIN ANALYZE: Welche zu verwenden?

Schmucklos EXPLAIN zeigt Ihnen, was der Planer beabsichtigt zu tun. Es führt die Abfrage nie aus – es zeigt nur den Plan mit geschätzten Kosten. Schnell, aber die Schätzungen können falsch sein.

EXPLAIN ANALYZE führt tatsächlich die Abfrage aus und fügt die tatsächlichen Zeiten hinzu. Sie erhalten sowohl den Plan als auch die tatsächlichen Werte:

-- PostgreSQL: run the query and show real timings
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

-- MySQL equivalent
EXPLAIN ANALYZE SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

Der BUFFERS Option ist PostgreSQL-spezifisch und fügt Buffer-Hit/Miss-Zahlen hinzu – nützlich zur Diagnose von I/O-Problemen, aber ignorieren Sie es für jetzt.

Eine Vorsichtsmaßnahme: EXPLAIN ANALYZE führt die Abfrage tatsächlich aus. Bei einer DELETE oder UPDATEgeben Sie es in einer Transaktion ein und rollen es zurück:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'pending';
ROLLBACK;

Die Kostenzahlen: Was sie bedeuten und was sie nicht bedeuten

Jeder Knoten im Plan zeigt (cost=X..Y rows=N width=W). Menschen sehen diese Zahlen und nehmen an, sie seien Millisekunden. Sie sind es nicht.

• cost=X..Y – X ist die Startkosten (Arbeit, die vor der ersten Zeile zurückgegeben wird), Y ist die Gesamtkosten, um alle Zeilen zu verarbeiten. Die Einheit ist beliebig „Page Cost“ – ungefähr proportional zu Disk-Seiten-Lesungen, aber kalibriert durch PostgreSQL-Kostenkonstanten. Ein Kostenwert von 1,0 entspricht einer sequentiellen Seite-Lese.
• rows=N – die von dem Planer geschätzte Anzahl an Zeilen. Kann stark falsch sein, wenn die Tabellendaten nicht aktuell sind.
• width=W – die durchschnittliche Zeilengröße in Bytes. Breitere Zeilen verlangsamen Joins und Sortierungen.

Wenn Sie EXPLAIN ANALYZEausführen, erhält jeder Knoten auch (actual time=X..Y rows=N loops=L). Diese konsistent sind — geometrisch. Vergleichen Sie das mit Millisekunden. Ein großer Unterschied zwischen geschätzten und tatsächlichen Zeilen ist das zuverlässigste Signal dafür, dass der Planer eine schlechte Entscheidung getroffen hat.

Ein kommentierter EXPLAIN ANALYZE-Überblick

Hier ist die gleiche Abfrage aus dem Einführungsteil, diesmal mit ANALYZE und mit jeder Zeile erklärt:

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
           (actual time=2.451..12.879 rows=3168 loops=1)
-- Top-level node. Joins orders and customers via hashing.
-- cost estimate: 145..578 | actual: 2.4ms startup, 12.9ms total
-- rows estimate: 3200 | actual: 3168 — pretty close here

  Hash Cond: (o.customer_id = c.id)
  -- This is the join key. The planner built a hash table on customers.id
  -- then probed it with every row from orders.

  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
                          (actual time=0.019..3.125 rows=16000 loops=1)
  -- Full table scan on orders. 16,000 rows scanned.
  -- No filter here — we pull all orders and join them below.
  -- This is expected: we need all orders, so no index would help here.

  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
            (actual time=2.412..2.413 rows=1000 loops=1)
            Buckets: 1024  Batches: 1  Memory Usage: 64kB
  -- Builds the in-memory hash table from the customers result.
  -- 1 batch = fits in work_mem. Multiple batches = spilling to disk (bad).

        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
                                   (actual time=0.012..1.345 rows=1000 loops=1)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000
        -- Scanned all 10,000 customer rows. Kept 1,000 where country='US'.
        -- *** This is the expensive part. An index on customers.country
        --     would let PostgreSQL skip the 9,000 discarded rows entirely. ***

Planning Time: 0.187 ms
Execution Time: 13.451 ms

Die Handlungsanweisung fällt sofort auf: Rows Removed by Filter: 9000 auf einem vollständigen Tabellen-Scan ist das häufigste Muster für „Sie brauchen hier einen Index.“

Die Knotentypen, die Sie tatsächlich begegnen werden

Seq Scan

liest jede Zeile in der Tabelle in Heap-Reihenfolge. Menschen sehen das und wollen sofort einen Index hinzufügen. Aber ein Seq Scan ist nicht immer falsch – wenn Ihr Filter 20%+ der Zeilen trifft, entscheidet der Planer korrekt, dass zufällige Indexabfragen langsamer wären. Ein Seq Scan auf einer 500-Zeilen-Tabelle ist in Ordnung. Ein Seq Scan auf einer 10-Millionen-Zeilen-Tabelle mit einem hochselektiven Filter ist ein Problem.

Index Scan

nutzt einen B-Tree-Index, um passende Zeilen zu finden, und holt dann jede Zeile aus dem Heap. Der Heap-Abzug ist der teurere Teil – jede Zeile ist eine zufällige I/O-Operation. Gut, wenn der Filter selektiv ist (d.h. Sie holen nur einen kleinen Bruchteil der Zeilen).

Index Only Scan

Die Abfrage kann vollständig aus dem Index beantwortet werden, ohne den Heap zu berühren. Dazu muss jede Spalte in Ihrem SELECT und WHERE Klausel vom Index abgedeckt sein. Der schnellste Scan-Typ – wenn Sie das sehen, ist das Index-Design auf seine Aufgabe ausgelegt.

Bitmap Heap Scan

Ein Mittelweg. Der Planer erstellt eine Bitmap, die angibt, welche Heap-Seiten Zeilen enthalten (über Bitmap Index Scan), und holt dann nur diese Seiten in Reihenfolge, was die zufällige I/O reduziert. Häufig, wenn ein Index Scan zu vielen zufälligen Lesen führen würde, aber ein Seq Scan zu vielen unnötigen Zeilen führen würde.

Hash Join

Baut eine Hash-Tabelle aus dem kleineren Eingang und untersucht dann jede Zeile aus dem größeren Eingang. Gut für große Joins ohne eine nützliche Sortierreihenfolge. Achten Sie auf Batches: > 1 im Hash-Knoten – das bedeutet, dass die Hash-Tabelle auf die Festplatte geschrieben wurde, weil sie work_memüberschritt. Erhöhen Sie work_mem für diese Sitzung oft, um es zu beheben.

Nested Loop

Für jede Zeile auf der äußeren Seite wird der innere Teil (meistens über einen Index) abgefragt. Sehr gut, wenn eine Seite klein ist – O(n) statt O(n log n). Schlecht, wenn beide Seiten groß sind, weil der innere Scan einmal pro äußeren Zeile ausgeführt wird. Wenn Sie einen Nested Loop mit loops=50000sehen, wird der innere Scan 50.000 Mal ausgeführt.

Merge Join

Beide Eingänge sind bereits sortiert nach dem Join-Schlüssel; der Planer läuft sie parallel ab. Effizient, aber erfordert die Sortierung zuerst. Sie sehen das, wenn beide Seiten bereits einen Index auf dem Join-Schlüssel haben oder wenn der Planer entscheidet, dass eine Sortierknoten kostengünstiger ist als das Hashen.

Warum der Planer einen Seq Scan bevorzugt, statt einen Index Scan

Das verwirrt Menschen am meisten. Sie haben einen Index. Die Abfrage ist langsam. EXPLAIN zeigt trotzdem einen Seq Scan.

Der Planer verwendet Spaltenstatistiken – gespeichert in pg_statistic, aktualisiert durch ANALYZE – um zu schätzen, wie viele Zeilen ein Filter zurückgeben wird. Wenn er schätzt, dass 30% der Tabelle übereinstimmen, ist ein Seq Scan tatsächlich günstiger als 300.000 zufällige Indexabfragen. Zufällige I/O ist teuer.

Die Schwelle, bei der der Planer einen Seq Scan bevorzugt, liegt etwa bei 10–20% Selektivität, abhängig von Ihrem Hardware und Kostenkonstanten. Sie können die Planer-Schätzung mit der Realität vergleichen:

-- Check the planner's selectivity estimate
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Compare with actual count
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders;

Wenn der Planer 8.000 Zeilen geschätzt hat, aber nur 80 vorhanden sind, ist es ein Problem mit veralteten Statistiken. Führen Sie ANALYZE orders; aus und überprüfen Sie erneut den Plan. Dies behebt schlechte Pläne häufiger als die Hinzufügung von Indizes.

Das Lesen von EXPLAIN in JSON-Format

PostgreSQL kann den Plan als JSON ausgeben, was für Programmierungen oder eine Baumansicht leichter zu parsen ist:

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

Das JSON-Ausgabe ist dicht. Wenn Sie es in IO Tools’ JSON Formattereinfügen, wird es zu einer navigierbaren Baumstruktur – nützlich bei komplexen Abfragen mit mehreren Unterabfragen oder CTEs.

Ein praktischer Debugging-Workflow

Wenn eine Abfrage langsam ist, folgen Sie dieser Folge anstatt zu raten:

1. Führen Sie EXPLAIN (ANALYZE, BUFFERS) auf der genauen Abfrage mit realen Produktionsparametern (nicht $1 Platzhalter – verwenden Sie die tatsächlichen Werte).
2. Finden Sie den teuersten Knoten. Suchen Sie nach dem höchsten actual time, nicht dem höchsten Kosten-Schätzung. Diese können sich unterscheiden.
3. Vergleichen Sie geschätzte und tatsächliche Zeilen an diesem Knoten. Ein 10-facher Unterschied bedeutet, dass der Planer mit falschen Informationen arbeitete. Führen Sie ANALYZE <table>; zuerst aus.
4. Suchen Sie nach Seq Scans auf großen Tabellen mit selektiven Filtern. Rows Removed by Filter: <large number> direkt unter einem Seq Scan ist Ihr Indexkandidat.
5. Prüfen Sie Hash-Knoten auf Batches > 1. Wenn vorhanden, ist die Verbindung auf die Festplatte geschrieben. Erhöhen Sie work_mem für diese Sitzung und testen Sie erneut.
6. Prüfen Sie Nested Loop mit hohen Schleifenzahlen. Eine Schleifenzahl in der Tausende bedeutet, dass der innere Scan stark belastet wird. Ein Index auf der Join-Spalte der inneren Tabelle behebt dies normalerweise.

Bevor Sie neue Indizes schreiben, führen Sie auch Ihre Abfragen durch IO Tools’ SQL Formatter – lesbare Abfragen sind einfacher zu analysieren, und manchmal wird ein Umbau das Problem vollständig beseitigen.

Das Verständnis des Plans ist der erste Schritt, nicht der Index. Sobald Sie EXPLAIN-Ausgabe lesen können, werden Sie weniger Zeit damit verbringen, zu raten, und mehr Zeit damit, gezielte Änderungen vorzunehmen, die tatsächlich funktionieren.

Das könnte Ihnen auch gefallen