EXPLAIN-Visualizer — PostgreSQL- & MySQL-Abfragepläne erklärt

Einen Plan lesen

Ein Plan ist ein Baum von Operationen; die Wurzel liefert das Ergebnis, und jeder Knoten zieht Zeilen von seinen Kindern. EXPLAIN allein zeigt die Schätzungen des Planers (Kosteneinheiten und erwartete Zeilen); EXPLAIN ANALYZE führt die Abfrage tatsächlich aus und ergänzt echte Zeiten und Zeilenzahlen, was einen Plan erst lesbar macht. BUFFERS ergänzt, wie viele 8-kB-Seiten aus dem Cache (hit) oder von der Platte (read) kamen. Der übliche Weg durch einen Plan: den Knoten mit der größten exklusiven Zeit finden, dann schauen, warum er da ist: eine falsche Zeilenschätzung darüber, ein fehlender Index oder eine Speichereinstellung.

Die Zahlen an einem Knoten

Feld	Bedeutung
cost=0.29..8.31	Schätzung des Planers in willkürlichen Einheiten: Kosten bis zur ersten Zeile .. Kosten für alle Zeilen. Nur innerhalb eines Plans vergleichbar.
rows=	Geschätzte Zeilen pro Loop. Das tatsächliche Gegenstück ist ebenfalls pro Loop; mit loops multiplizieren für die Summe.
actual time=0.05..12.3	Millisekunden bis zur ersten Zeile .. letzten Zeile, pro Loop, inklusive Kinder. Das Tool zieht die Kinder ab, um die eigene Zeit jedes Knotens zu zeigen.
loops=	Wie oft der Knoten lief. Die innere Seite eines Nested Loop läuft einmal pro äußerer Zeile; unter einem Gather entspricht es der Zahl der Worker.
Buffers: shared hit=.. read=..	Seiten in shared_buffers gefunden gegen vom OS/der Platte gelesen. Reads sind, wo die I/O-Zeit hingeht; `track_io_timing` zeigt sie als I/O Timings.
Rows Removed by Filter	Zeilen, die der Knoten las und dann verwarf: das klarste Zeichen für einen fehlenden Index.

Die fünf üblichen Probleme

Falsche Schätzungen. Erwartet ein Knoten 10 Zeilen und bekommt 100.000, wählte der Planer einen Nested Loop oder einen Sort, der nur für 10 sinnvoll war. ANALYZE ausführen; für Prädikate auf korrelierten Spalten erweiterte Statistiken anlegen; bei CTEs und Funktionen hat der Planer keine Ahnung und rät.
Sequential Scan mit selektivem Filter. Auf kleinen Tabellen in Ordnung. Auf großen ist die Lösung ein Index auf den gefilterten Spalten, idealerweise passend zu WHERE und ORDER BY.
Spills. Sort external merge, Hash Batches > 1, HashAggregate auf Platte, lossy Bitmaps: Die Operation passte nicht in work_mem. Es für die Sitzung oder die Abfrage zu erhöhen ist billig; global erhöht es sich mit der Zahl der Verbindungen.
Nested Loop über einem Scan. Die innere Seite läuft einmal pro äußerer Zeile; ist sie ein Seq Scan oder ein Materialize einer großen Menge, explodiert die Zeit. Die innere Seite braucht einen Index, oder dem Planer muss ein Hash Join erlaubt sein.
Index-Only Scans, die keine sind. Heap Fetches nahe der Zeilenzahl heißt, die Visibility Map ist veraltet: VACUUM der Tabelle.

Pläne bekommen

-- PostgreSQL: die nützlichste Form
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT JSON) SELECT …;
-- für Schreibvorgänge, in einer Transaktion, die Sie zurückrollen
BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE …; ROLLBACK;
-- langsame Pläne automatisch loggen: auto_explain
LOAD 'auto_explain'; SET auto_explain.log_min_duration = '200ms'; SET auto_explain.log_analyze = on;

-- MySQL 8.0.18+
EXPLAIN ANALYZE SELECT …;          -- Baum mit tatsächlichen Zeiten
EXPLAIN FORMAT=JSON SELECT …;      -- Schätzungen mit Kostendetails

Fragen

Warum summieren sich die Knotenzeiten nicht zum Gesamten?

Die tatsächliche Zeit jedes Knotens enthält seine Kinder, also zeigt das Tool die exklusive Zeit (Knoten minus Kinder). Parallele Worker laufen gleichzeitig, also wird ein Knoten unter Gather mit loops=3 einmal gezählt, nicht dreimal; innere Seiten von Nested Loops werden mit loops multipliziert. Planungszeit und Trigger-Zeit werden getrennt aufgeführt.

Was ist ein guter Plan?

Einer, bei dem die Schätzungen innerhalb eines Faktors zehn der Ist-Werte liegen, kein Knoten auf Platte spillt, der teuerste Knoten der erwartete ist (die große Tabelle, die Sie wirklich lesen müssen) und die von Platte gelesenen Buffers klein gegenüber den Hits sind. Für so einen Plan ist die Befundliste leer.

Unterstützt es partitionierte Tabellen und parallele Pläne?

Ja: Append / Merge Append über Partitionen, Gather und Gather Merge mit Workers Planned/Launched, Partial- und Finalize-Aggregate. Aufschlüsselungen pro Worker (VERBOSE) werden geparst, aber als Teil des Knotens gezeigt.

Kann ich einen Plan aus pgAdmin, DataGrip oder psql mit Tabellenrahmen einfügen?

Ja. Zeilen werden von psqls Rahmenzeichen, dem QUERY-PLAN-Header und der „(n rows)“-Fußzeile bereinigt. Aus einer GUI als einzelner String in Anführungszeichen kopiertes JSON sollte zuerst von den Anführungszeichen befreit werden.

Wie steht es um die MySQL-Unterstützung?

Der EXPLAIN-ANALYZE-Baum von MySQL 8 hat dieselbe Form wie ein PostgreSQL-Textplan und wird voll unterstützt; EXPLAIN FORMAT=JSON (nur Schätzungen) wird auf denselben Baum abgebildet: Zugriffstypen werden zu Scan-Arten, nested_loop zu einem Join-Knoten, und filesort- / temporary-table-Flags zu Befunden. MariaDBs ANALYZE FORMAT=JSON wird noch nicht unterstützt.