Menu

PostgreSQL EXPLAIN ANALYZE: Abfrageplan richtig lesen

EXPLAIN zeigt den Plan, den PostgreSQL für eine Abfrage wählt, und EXPLAIN ANALYZE führt die Abfrage aus und ergänzt die echten Zeilenzahlen und Zeiten jedes Schritts. So liest du den Planbaum und die gängigen Knoten.

Diese Seite enthält ausführbare Editoren. Bearbeiten, ausführen und Ausgabe sofort sehen.

EXPLAIN query zeigt den Plan, den PostgreSQL für eine Abfrage wählt: einen Baum aus Schritten (Knoten) mit geschätzten Kosten und Zeilenzahlen. EXPLAIN ANALYZE query führt die Abfrage zusätzlich aus und ergänzt für jeden Knoten die tatsächliche Zeit, die Zeilen und die Durchläufe. Lies den Baum vom am weitesten eingerückten Knoten nach außen: Innere Knoten geben ihre Zeilen an den Knoten darüber weiter.

Auf email gibt es keinen Index, der Plan ist also ein Seq Scan: PostgreSQL liest jede Zeile und behält die, die den Filter bestehen. Rows Removed by Filter zeigt die verschwendete Arbeit. Die Zeiten sind bei jedem Lauf anders; die Form des Plans zählt mehr als die Zahlen.

Eine Planzeile lesen

Jeder Knoten gibt eine Zeile in dieser Form aus:

Seq Scan on users  (cost=STARTUP..TOTAL rows=N width=W) (actual time=FIRST..LAST rows=N loops=L)
TeilBedeutung
cost=STARTUP..TOTALgeschätzter Aufwand bis zur ersten Zeile und für alle Zeilen, in Planer-Einheiten (keine Millisekunden)
rows (erstes)geschätzte Zahl der Zeilen, die der Knoten liefert
widthgeschätzte durchschnittliche Zeilengröße in Bytes
actual timeMillisekunden bis zur ersten und bis zur letzten Zeile, pro Durchlauf
rows (actual)gelieferte Zeilen pro Durchlauf
loopswie oft der Knoten lief; Zeit und Zeilen damit multiplizieren

Die Kosten eines Elternknotens enthalten die seiner Kinder. Planning Time und Execution Time ganz unten sind Summen für die ganze Anweisung.

Die gängigen Planknoten erkennen

EXPLAIN (COSTS OFF) blendet die Zahlen aus, so ist die Form des Plans leichter zu sehen:

Lies von innen nach außen. Beide Tabellen werden gescannt, und die gefilterten Kunden kommen in einen Hash. Der Hash Join prüft ihn mit jeder Bestellung, und das GroupAggregate obendrauf zählt das Ergebnis. Die Knoten, die dir am häufigsten begegnen:

KnotenWas er macht
Seq Scanliest die ganze Tabelle
Index Scanfindet Zeilen über einen Index und liest dann jede Zeile aus der Tabelle
Index Only Scanantwortet aus dem Index, ohne die Tabelle zu besuchen
Bitmap Index Scan + Bitmap Heap Scansammelt die Positionen passender Zeilen aus einem Index und liest sie dann in Tabellenreihenfolge
Nested Loopsucht für jede äußere Zeile Treffer in der inneren Eingabe; gut, wenn die äußere Seite klein ist
Hash Join + Hashbaut aus einer Eingabe eine Hashtabelle und prüft sie mit der anderen
Merge Joinjoint zwei Eingaben, die schon nach dem Join-Schlüssel sortiert sind
Sort, Incremental Sortsortiert Zeilen für ORDER BY, DISTINCT oder einen Merge Join
HashAggregate, GroupAggregateGROUP BY mit einer Hashtabelle oder über sortierte Eingabe
Limithört nach N Zeilen auf
Gathersammelt Zeilen von parallelen Workern ein

Falsche Zeilenschätzungen finden

Die nützlichste Prüfung in EXPLAIN ANALYZE ist der Vergleich der geschätzten rows mit den tatsächlichen rows am selben Knoten. Liegen sie weit auseinander, hat der Planer seinen Plan auf falschen Zahlen aufgebaut, oft weil Statistiken fehlen oder veraltet sind:

Der Scan liefert tatsächlich 10 Zeilen, aber die Schätzung liegt viel höher, weil die Tabelle noch nicht analysiert wurde. Setz ANALYZE tasks; vor das EXPLAIN, und die Schätzung rückt nah an 10. Autovacuum analysiert Tabellen im Hintergrund, aber nach einem großen Import führst du ANALYZE selbst aus (siehe VACUUM und Autovacuum).

Eine Datenänderung erklären, ohne sie zu behalten

EXPLAIN ANALYZE führt die Anweisung aus, ein UPDATE oder DELETE ändert also Zeilen. Pack es in eine Transaktion und roll zurück:

Nach dem Rollback sind alle 1000 Zeilen noch da.

I/O mit BUFFERS und weiteren Optionen zeigen

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;        -- pages read from cache (hit) and disk (read)
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT ...; -- also list changed planner settings
EXPLAIN (FORMAT JSON) SELECT ...;              -- machine-readable, for plan visualizers
EXPLAIN (ANALYZE, TIMING OFF) SELECT ...;      -- row counts without per-node timing overhead

BUFFERS zeigt die I/O: Ein Knoten mit vielen read Seiten musste auf die Platte. PostgreSQL 17 hat SERIALIZE (die Kosten, die Ausgabezeilen in Text umzuwandeln) und MEMORY (Speicher des Planers) hinzugefügt. Der offizielle Leitfaden zu EXPLAIN geht weitere Pläne durch.

Einen Plan mit Planer-Einstellungen testen

Um den Plan zu sehen, den PostgreSQL ohne eine bestimmte Methode nehmen würde, schaltest du diese Methode für deine Sitzung ab. Mach das nur in Experimenten, nie in Produktionscode:

Bei 100 Zeilen bevorzugt der Planer einen Seq Scan; mit enable_seqscan = off zeigt der Plan Index Scan using items_pkey. Vergleiche die beiden Zeiten von EXPLAIN ANALYZE, um zu sehen, ob der Index hilft. RESET enable_seqscan; stellt den Standard wieder her.

Häufig gestellte Fragen

Was ist der Unterschied zwischen EXPLAIN und EXPLAIN ANALYZE?

EXPLAIN plant die Abfrage nur und zeigt Schätzungen. EXPLAIN ANALYZE führt sie außerdem aus und ergänzt für jeden Knoten die tatsächliche Zeit, Zeilenzahl und Anzahl der Durchläufe. Es dauert also so lange wie die Abfrage selbst.

Was bedeutet cost in einem PostgreSQL Plan?

Cost ist die Schätzung des Planers in willkürlichen Einheiten, so skaliert, dass das sequenzielle Lesen einer Seite 1 kostet (seq_page_cost). Der Wert taugt zum Vergleich von Plänen derselben Abfrage, nicht als Zeit in Millisekunden.

Ändert EXPLAIN ANALYZE Daten?

Ja, bei INSERT, UPDATE, DELETE und MERGE führt es die Änderung aus. Lass es zwischen BEGIN und ROLLBACK laufen, um zu messen, ohne das Ergebnis zu behalten.

Warum wird ein Seq Scan gewählt, obwohl es einen Index gibt?

Weil der Planer ihn für günstiger hält: Die Tabelle ist klein, die Abfrage liefert einen großen Teil der Zeilen, oder die Bedingung kann den Index nicht nutzen. Auch veraltete Statistiken können ihn in die Irre führen, das behebt ANALYZE.

Illustration der Programmiersprachen bei Coddy

Lerne mit Coddy zu programmieren

Los geht's