PostgreSQL EXPLAIN ANALYZE für Anfänger verstehen

1. Überblick

EXPLAIN ANALYZE ist das wichtigste Werkzeug zur Diagnose langsamer Abfragen in PostgreSQL. Dieser Artikel hilft dir, jeden Teil der Ausgabe zu verstehen: Node-Typ, Cost, Rows, Actual Time, und wann eine Abfrage ein Problem hat. Am Ende wirst du selbstbewusst Execution Plans lesen können und wissen, wo du Indizes hinzufügen oder deine Abfrage anpassen solltest.

2. EXPLAIN vs. EXPLAIN ANALYZE

EXPLAIN zeigt nur den geplanten Plan (estimated plan) basierend auf Statistiken an, führt die Abfrage aber nicht aus. EXPLAIN ANALYZE führt die Abfrage tatsächlich aus und misst die reale Zeit.

-- Nur den geplanten Plan anzeigen, Abfrage nicht ausführen
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Abfrage wirklich ausführen und reale Zeit messen
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Hinweis: EXPLAIN ANALYZE führt die Abfrage tatsächlich aus. Wenn es sich um UPDATE oder DELETE handelt, werden die Daten also geändert. Nutze eine Transaktion und ROLLBACK, wenn du nur testen möchtest:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'SHIPPED' WHERE id = 1;
ROLLBACK;

3. Struktur eines Nodes im Ausführungsplan

Jede Zeile in der Ausgabe ist ein Node — ein Verarbeitungsschritt für Daten. Beispiel mit der Tabelle orders:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=8 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 99992
Planning Time: 0.120 ms
Execution Time: 12.465 ms

Erklärung der einzelnen Teile:

• Seq Scan on orders: Node-Typ, hier ein sequenzieller Scan über die gesamte Tabelle.
• cost=0.00..1834.00: geschätzter Cost (Startup Cost .. Total Cost), relative Einheit, nicht in ms.
• rows=10: Anzahl der geschätzten Zeilen, die der Planner zurückgeben soll.
• width=72: durchschnittliche Größe pro Zeile (Bytes).
• actual time=0.015..12.430: tatsächliche Zeit (ms) — erste Zeile ausgeliefert .. Node vollständig abgeschlossen.
• rows=8 loops=1: tatsächliche Anzahl zurückgegebener Zeilen und wie oft dieser Node ausgeführt wurde (loops).
• Rows Removed by Filter: Anzahl der Zeilen, die gescannt aber nicht dem Filter entsprechen — ein hoher Wert ist ein Zeichen fehlender Indizes.

4. Vergleich von Seq Scan und Index Scan

4.1. Ohne Index

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=8 loops=1)
  Filter: (customer_id = 42)
Execution Time: 12.465 ms

PostgreSQL muss die gesamte Tabelle lesen und dann jede Zeile filtern. Bei großen Tabellen ist dies die Hauptursache für Verzögerungen.

4.2. Nach dem Hinzufügen eines Index

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer_id on orders
  (cost=0.29..8.31 rows=10 width=72)
  (actual time=0.018..0.025 rows=8 loops=1)
  Index Cond: (customer_id = 42)
Execution Time: 0.045 ms

Vergleich: Cost sinkt von 1834 auf 8.31, Execution Time sinkt von 12.465 ms auf 0.045 ms. Dies ist ein klassisches Beispiel, das zeigt, wie ein Index PostgreSQL hilft, direkt zu den benötigten Zeilen zu springen, ohne die gesamte Tabelle zu scannen.

Hinweis: PostgreSQL nutzt nicht immer einen Index, auch wenn er vorhanden ist. Wenn eine Abfrage einen großen Teil der Tabelle zurückgibt (z. B. > 20-30% der Zeilen), wählt der Planner oft Seq Scan, da dieser kostengünstiger ist als zufällige Sprünge durch den Index und zurück zur Tabelle (Random I/O).

5. Lesen von Plans mit JOIN

Wenn eine Abfrage einen Join hat, enthält der Plan mehrere verschachtelte Nodes. Innere Nodes werden zuerst ausgeführt, ihre Ergebnisse werden an den Parent-Node weitergegeben.

EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'VN';

Hash Join  (cost=45.50..520.75 rows=200 width=40)
           (actual time=1.200..8.900 rows=195 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o
        (cost=0.00..420.00 rows=10000 width=12)
        (actual time=0.010..3.500 rows=10000 loops=1)
  ->  Hash  (cost=40.00..40.00 rows=440 width=36)
            (actual time=1.100..1.100 rows=440 loops=1)
        ->  Seq Scan on customers c
              (cost=0.00..40.00 rows=440 width=36)
              (actual time=0.005..0.700 rows=440 loops=1)
              Filter: (country = 'VN'::text)
Execution Time: 9.050 ms

So wird es gelesen: Hash Join ist der äußerste Node (Root), er erhält Daten von zwei inneren Nodes (Seq Scan on orders und Hash mit Seq Scan on customers). PostgreSQL erstellt eine Hash-Tabelle aus der gefilterten Tabelle customers, scannt dann orders und matched gegen die Hash-Tabelle. Die actual time des äußeren Nodes beinhaltet bereits die Zeit der inneren Nodes.

6. Warnsignale, auf die man achten sollte

6.1. Estimates unterscheiden sich stark vom tatsächlichen Ergebnis

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=9500 loops=1)

Der Planner erwartet rows=10, aber es werden tatsächlich 9500 zurückgegeben. Eine so große Abweichung ist meist auf veraltete Statistiken zurückzuführen. Beheben Sie dies mit:

ANALYZE orders;

Dieser Befehl aktualisiert die Statistiken über die Datenverteilung, damit der Planner genauer schätzen kann.

6.2. Ungewöhnlich große Loops (Nested Loop)

Nested Loop  (cost=0.43..5000.00 rows=1 width=40)
             (actual time=0.020..450.000 rows=1 loops=1)
  ->  Seq Scan on customers c (actual time=0.010..0.015 rows=1 loops=1)
  ->  Index Scan on orders o
        (actual time=0.002..0.450 rows=1 loops=1000)

loops=1000 bedeutet, dass der Node Index Scan on orders 1000-mal aufgerufen wurde (entsprechend 1000 Zeilen vom äußeren Node). Wenn jeder Aufruf bedeutsame Zeit kostet, kann die Gesamtzeit sehr hoch sein, obwohl die actual time des Nodes isoliert gesehen klein aussieht. Multipliziere actual time mit loops, um die tatsächliche Gesamtzeit des Nodes zu schätzen.

6.3. Mit BUFFERS I/O-Details anzeigen

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer_id on orders
  (cost=0.29..8.31 rows=10 width=72)
  (actual time=0.018..0.025 rows=8 loops=1)
  Index Cond: (customer_id = 42)
  Buffers: shared hit=4
Execution Time: 0.045 ms

shared hit=4 bedeutet, dass 4 Blöcke aus dem Cache (Speicher) gelesen wurden, nicht von der Festplatte. Wenn du ein großes shared read siehst, bedeutet das, dass Daten von der Festplatte gelesen werden — ein Zeichen für Cache-Misses, möglicherweise weil shared_buffers zu klein ist oder die Abfrage zu viele “kalte” Daten scannt.

7. Verwendung von EXPLAIN in Java/Spring Code

Beim Debuggen der Performance in einer Spring Boot-Anwendung kannst du den Plan direkt via JdbcTemplate loggen:

@Service
public class QueryDiagnosticsService {

    private final JdbcTemplate jdbcTemplate;

    public QueryDiagnosticsService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<String> explainAnalyze(String sql) {
        String query = "EXPLAIN (ANALYZE, FORMAT TEXT) " + sql;
        return jdbcTemplate.queryForList(query, String.class);
    }
}

explainAnalyze nimmt die ursprüngliche SQL-Anweisung, umhüllt sie mit EXPLAIN (ANALYZE, FORMAT TEXT) und gibt jede Zeile des Plans als String zurück. Nutze dies in Dev/Staging-Umgebungen, um den Plan zu loggen, wenn du eine langsame Abfrage erkennst.

@Test
void explainAnalyze_shouldReturnPlanWithExecutionTime() {
    QueryDiagnosticsService service = new QueryDiagnosticsService(jdbcTemplate);

    List<String> plan = service.explainAnalyze(
        "SELECT * FROM orders WHERE customer_id = 42"
    );

    assertThat(plan)
        .isNotEmpty()
        .anyMatch(line -> line.contains("Execution Time"));
}

Dieser Test prüft keine konkreten Zahlen (da die Zeit je nach Maschine unterschiedlich ist), sondern bestätigt nur, dass der Plan Execution Time enthält — ausreichend um zu überprüfen, dass die Abfrage erfolgreich ausgeführt wurde und einen gültigen Plan zurückgibt.

Hinweis: Führe EXPLAIN ANALYZE nicht auf Production für Abfragen mit INSERT/UPDATE/DELETE aus, ohne sie in einer Transaktion mit ROLLBACK zu wrappen, da die Abfrage wirklich ausgeführt wird.

8. Wann man EXPLAIN ANALYZE NICHT benötigt

• Abfrage ist bereits schnell und stabil (unter einigen ms, wenig Daten) — keine weitere Optimierung nötig, Aufwand lohnt sich nicht.
• Du befindest dich noch in einer frühen Phase des Schemadesigns, hast noch nicht genug echte Daten, damit Statistiken die Realität widerspiegeln (bei wenigen Zeilen ist Seq Scan immer besser als Index Scan).
• Abfrage läuft auf einer kleinen temporären Tabelle (TEMP TABLE) nur ein einziges Mal — der Overhead der Analyse rechtfertigt sich nicht.

9. Fazit

EXPLAIN ANALYZE zeigt dir den Unterschied zwischen dem, was PostgreSQL erwartet zu passieren (Cost, geschätzte Rows), und dem, was wirklich passiert (Actual Time, echte Rows). Wenn du einen Plan liest, achte auf die Ausführungsreihenfolge (von innen nach außen), den Node-Typ (Seq Scan oder Index Scan) und besonders auf die Abweichung zwischen Estimate und Actual — das ist der Schlüssel um zu wissen, ob deine Abfrage einen Index benötigt, ob du die Tabelle mit ANALYZE aktualisieren musst oder ob du die Logik umschreiben solltest.