Interpretation des Ausführungsplans: Lesen und Analysieren von Knoten (`Seq Scan`, `Index Scan`, `Hash Join`)

Heute schauen wir uns an, was eigentlich Knoten im Ausführungsplan von PostgreSQL sind, wie man sie liest und – ganz wichtig – wie man merkt, wenn etwas schief läuft. Du erfährst, warum deine Datenbank manchmal lieber einen ressourcenhungrigen Seq Scan macht, obwohl es doch schon einen Index gibt, vielleicht sogar mehrere.

Wenn PostgreSQL einen Ausführungsplan für eine Abfrage baut, zerlegt es ihn in Schritte, die man Knoten nennt. Jeder Knoten ist quasi ein "Step", den der Datenbankserver ausführt, um deine Abfrage zu bearbeiten. Die wichtigsten Knotentypen sind:

Sequential Scan (Seq Scan)

Seq Scan oder sequentielles Scannen ist die einfachste Methode, Daten aus einer Tabelle zu holen. PostgreSQL nimmt die Tabelle, liest Zeile für Zeile und prüft, ob sie zu den Bedingungen deiner Abfrage passen.

Wann wird Seq Scan verwendet?
Seq Scan kommt zum Einsatz, wenn:

• Es keinen passenden Index in der Tabelle gibt, um die Abfrage zu beschleunigen.
• Die Filterbedingung zu allgemein ist, sodass ein Index nichts bringt (zum Beispiel, wenn mehr als 50% der Daten geholt werden).
• PostgreSQL meint, dass das sequentielle Lesen der Tabelle schneller ist als die Nutzung eines Index (das passiert manchmal bei sehr kleinen Tabellen).

EXPLAIN SELECT * FROM students WHERE age > 18;

Beispiel für das Ergebnis:

Seq Scan on students  (cost=0.00..35.50 rows=10 width=50)
  Filter: (age > 18)

Achte auf Seq Scan on students – das heißt, PostgreSQL liest die Tabelle "students" komplett durch.

Probleme mit Seq Scan: Wenn die Tabelle riesig ist, kann das sequentielle Scannen richtig lange dauern.

Index Scan

Index Scan ist das Scannen von Daten mit Hilfe eines Index. Wenn du einen Index in PostgreSQL anlegst, ist das so, als würdest du ein Inhaltsverzeichnis für deine Tabelle machen. Wenn die Abfrage den Index nutzen kann, geht PostgreSQL nicht die ganze Tabelle durch, sondern nur die relevanten Teile.

Wann wird Index Scan verwendet?

• In der Abfrage gibt es Filterbedingungen für eine Spalte mit Index (zum Beispiel WHERE).
• Es werden Vergleichsoperatoren wie =, <, >, BETWEEN usw. genutzt.

CREATE INDEX idx_students_age ON students(age);

EXPLAIN SELECT * FROM students WHERE age = 18;

Beispiel für das Ergebnis:

Index Scan using idx_students_age on students  (cost=0.15..8.27 rows=1 width=50)
  Index Cond: (age = 18)

Hier zeigt Index Scan using idx_students_age, dass PostgreSQL den Index idx_students_age nutzt. Das zeilenweise Lesen der Tabelle wird durch einen viel schnelleren Zugriff über den Index ersetzt.

Vorteile von Index Scan:

• Deutliche Beschleunigung von Abfragen bei großen Tabellen.
• Weniger Daten werden von der Festplatte gelesen.

Probleme bei Index Scan:
Wenn deine Abfrage zu viele Daten zurückliefert (zum Beispiel mehr als die Hälfte der Tabelle), kann die Nutzung des Index sogar langsamer sein als ein Seq Scan.

Hash Join

Hash Join wird verwendet, um zwei Tabellen anhand einer Join-Bedingung zu verbinden (zum Beispiel ON students.course_id = courses.id). PostgreSQL baut eine Hash-Tabelle für eine der Tabellen (die kleinere) und nutzt sie, um passende Zeilen in der anderen Tabelle zu finden.

Wann wird Hash Join verwendet?

• Beim Verbinden von Tabellen mit INNER JOIN, LEFT JOIN usw.
• Wenn PostgreSQL meint, dass Hash Join effizienter ist als andere Join-Methoden.

EXPLAIN
SELECT * 
FROM students 
JOIN courses ON students.course_id = courses.id;

Beispiel für das Ergebnis:

Hash Join  (cost=25.00..50.00 rows=10 width=100)
  Hash Cond: (students.course_id = courses.id)
  -> Seq Scan on students  (cost=0.00..20.00 rows=10 width=50)
  -> Hash  (cost=15.00..15.00 rows=10 width=50)
       -> Seq Scan on courses  (cost=0.00..15.00 rows=10 width=50)

Hier verbindet Hash Join zwei Tabellen. Beachte, dass PostgreSQL zuerst Seq Scan für beide Tabellen macht und dann die Hash-Tabelle (Hash) baut.

Vorteile von Hash Join:

• Schnelle Verarbeitung bei mittelgroßen Tabellen.
• Effizient beim Verbinden von Tabellen mit vielen Zeilen.

Probleme bei Hash Join:
Wenn die Hash-Tabelle größer ist als der verfügbare Speicher, nutzt PostgreSQL die Festplatte, was das Joinen deutlich langsamer macht.

Beispiel für die Analyse eines Ausführungsplans

Schauen wir uns ein echtes Beispiel an.

Abfrage:

EXPLAIN ANALYZE
SELECT *
FROM students
JOIN courses ON students.course_id = courses.id
WHERE students.age > 18;

Ergebnis:

Hash Join  (cost=35.00..75.00 rows=5 width=100) (actual time=1.00..2.50 rows=5 loops=1)
  Hash Cond: (students.course_id = courses.id)
  -> Seq Scan on students  (cost=0.00..40.00 rows=10 width=50) (actual time=0.50..1.00 rows=7 loops=1)
        Filter: (age > 18)
        Rows Removed by Filter: 3
  -> Hash  (cost=25.00..25.00 rows=5 width=50) (actual time=0.30..0.30 rows=5 loops=1)
       -> Seq Scan on courses  (cost=0.00..20.00 rows=5 width=50) (actual time=0.20..0.25 rows=5 loops=1)
Planning Time: 0.50 ms
Execution Time: 3.00 ms

Interpretation:

1. Hash Join: Hauptknoten. PostgreSQL verbindet die Tabellen students und courses.
   • actual time: von 1.00 bis 2.50 ms.
   • rows=5: Die Abfrage hat 5 Zeilen zurückgegeben.
2. Verschachtelte Knoten:
   • Seq Scan on students: liest die Tabelle students sequentiell und wendet den Filter (age > 18) an.
   • Rows Removed by Filter = 3: 3 Zeilen haben die Bedingung nicht erfüllt.
   • Hash: PostgreSQL baut eine Hash-Tabelle für die Tabelle courses.

Vergleich und Auswahl von Knoten

Wenn du einen Ausführungsplan analysierst, ist der Schlüssel, zu verstehen, warum PostgreSQL eine bestimmte Methode zur Datenverarbeitung gewählt hat. Manchmal musst du eingreifen, um die Sache zu verbessern, zum Beispiel einen Index hinzufügen oder die Abfrage umschreiben. Hier ein paar Tipps:

• Wenn du Seq Scan auf einer großen Tabelle siehst, denk über Indizes nach.
• Wenn Hash Join zu langsam ist, prüfe den verfügbaren Speicher für PostgreSQL.
• Nutze EXPLAIN ANALYZE, um die geschätzten und die tatsächlichen Werte der Metriken (rows, time) zu vergleichen.

Jetzt hast du schon ein grundlegendes Verständnis davon, wie man Ausführungspläne liest und die Knoten interpretiert. In den nächsten Vorlesungen sprechen wir über typische Optimierungsprobleme und wie man sie löst.