Verwendung von EXPLAIN ANALYZE zur Messung der tatsächlichen Ausführungszeit von Abfragen

Wenn dir der Befehl EXPLAIN einen Blick in die Glaskugel erlaubt, um zu sehen, wie PostgreSQL „plant“, eine Abfrage auszuführen, dann macht dich EXPLAIN ANALYZE zum echten Detektiv, der herausfindet, was wirklich passiert ist.

Wichtige Unterschiede zwischen EXPLAIN und EXPLAIN ANALYZE:

EXPLAIN – das ist die Theorie und zeigt dir, wie PostgreSQL die Ausführung einer Abfrage plant. Du siehst geschätzte Werte wie die Anzahl der Zeilen (rows) und die Ausführungskosten (cost).

EXPLAIN ANALYZE – das ist die Praxis. PostgreSQL führt die Abfrage wirklich aus und zeigt dir:

• Die tatsächliche Anzahl der verarbeiteten Zeilen auf jedem Schritt.
• Die echte Ausführungszeit jeder Operation.
• Vergleich mit den Annahmen des Plans (rows und cost).

Beispiel: Wenn deine Abfrage eigentlich 100 Zeilen erwartet, aber tatsächlich 10.000 Zeilen verarbeitet, deckt EXPLAIN ANALYZE diesen unschönen Fakt sofort auf!

Grundlegende Syntax und Verwendung

Wie bei EXPLAIN ist EXPLAIN ANALYZE super easy zu benutzen. Einfach das Wort ANALYZE zu deinem EXPLAIN-Befehl hinzufügen.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Das macht PostgreSQL dann:

• Es führt die Abfrage aus.
• Schreibt jeden Schritt im Ausführungsplan auf, inklusive der echten Werte.
• Gibt dir eine komplette Beschreibung des Ausführungsprozesses zurück.

Welche Daten liefert EXPLAIN ANALYZE?

Tatsächliche Ausführungszeit der Operationen:

• Actual Start Time: Wann die Operation gestartet wurde.
• Actual End Time: Wann die Operation beendet wurde.

Gesamtanzahl der verarbeiteten Zeilen:

Das hilft dir einzuschätzen, wie genau die Annahmen des Plans (rows) waren.

Info zu Buffern:

Wie Disk- und Memory-Buffer verwendet wurden.

Beispiel für die Verwendung von EXPLAIN ANALYZE

Schauen wir uns ein konkretes Beispiel an. Wir haben eine Tabelle students, die Daten über Studierende enthält:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    grade FLOAT
);

INSERT INTO students (name, age, grade)
VALUES
('Alice', 22, 4.1),
('Bob', 19, 3.8),
('Charlie', 23, 4.5),
('Diana', 20, 3.9);

Wir führen eine Abfrage aus, um Studierende über 20 Jahre zu finden:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Beispiel für das Ergebnis:

Seq Scan on students  (cost=0.00..14.00 rows=2 width=116) (actual time=0.025..0.026 rows=2 loops=1)
  Filter: (age > 20)
  Rows Removed by Filter: 2
Planning Time: 0.032 ms
Execution Time: 0.048 ms

Schauen wir uns das Ergebnis an:

• Seq Scan – sagt, dass PostgreSQL einen sequentiellen Scan auf der Tabelle macht.
• cost=0.00..14.00 – das sind die geschätzten Kosten der Operation.
• rows=2 – PostgreSQL erwartet, dass die Abfrage 2 Zeilen zurückgibt (und das stimmt!).
• actual time=0.025..0.026 – die echte Ausführungszeit der Operation (in Millisekunden).
• Rows Removed by Filter: 2 – zwei Zeilen wurden durch den Filter entfernt, weil sie nicht zum WHERE-Kriterium gepasst haben.

Theorie vs. Praxis

Hier liegt die Magie von EXPLAIN ANALYZE: Es zeigt dir, wie die Abfrage wirklich ausgeführt wurde und lässt dich das mit dem theoretischen Ausführungsplan vergleichen.

Schauen wir uns ein etwas komplexeres Beispiel an.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20 AND grade > 4.0;

Beispiel für das Ergebnis:

Seq Scan on students  (cost=0.00..14.00 rows=1 width=116) (actual time=0.026..0.027 rows=1 loops=1)
  Filter: ((age > 20) AND (grade > 4.0))
  Rows Removed by Filter: 3
Planning Time: 0.035 ms
Execution Time: 0.057 ms

Was sehen wir hier:

1. PostgreSQL hat die Abfrage in 0.057 Millisekunden ausgeführt.
2. Nur eine Zeile (rows=1) erfüllt die WHERE-Bedingungen.
3. Drei Zeilen wurden herausgefiltert (Rows Removed by Filter: 3).

Fazit

Mit EXPLAIN ANALYZE findest du Flaschenhälse und verstehst, wie du deine Abfragen optimieren kannst. Zum Beispiel:

• Wenn Seq Scan zu „schwer“ ist, ist es vielleicht Zeit für einen Index.
• Wenn die Annahmen von PostgreSQL stark von den echten Daten abweichen, solltest du die Tabellenstatistiken (ANALYZE) oder die Indexstruktur checken.

2

Aufgabe

SQL SELF, Level 41, Lektion 3

Gesperrt

Analyse der Ausführung einer Abfrage mit Filterung und Sortierung

Analyse der Ausführung einer Abfrage mit Filterung und Sortierung

Öffnen