Performance-Analyse von Funktionen und Prozeduren: Einsatz von EXPLAIN ANALYZE

EXPLAIN ANALYZE hilft dir zu verstehen, wie PostgreSQL "denkt", wenn es deinen Query ausführt:

• Welche Schritte werden gemacht, um die Daten zu verarbeiten.
• Wie lange dauert jeder Schritt.
• Warum ein bestimmter Query langsam läuft – sei es ein kompletter Table-Scan (engl. Seq Scan) oder ein fehlender Index.

Der Befehl EXPLAIN ANALYZE führt den Query tatsächlich aus und zeigt, wie PostgreSQL die Ausführung optimiert. Stell dir vor, du zerlegst eine Uhr, um zu checken, wie das Uhrwerk funktioniert. Genau das macht EXPLAIN ANALYZE – nur eben mit deinen SQL-Queries.

Syntax von EXPLAIN ANALYZE

Fangen wir easy an. So sieht der Grundbefehl aus:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Dieser Query führt das SELECT aus und zeigt, wie PostgreSQL die Daten verarbeitet.

Das Ergebnis von EXPLAIN ANALYZE ist ein Ausführungsbaum des Queries. Jede Ebene im Baum beschreibt einen Schritt, den PostgreSQL macht:

• Operation Type — Typ der Operation (z.B. Seq Scan, Index Scan).
• Cost — wie "teuer" PostgreSQL die Ausführung dieser Operation einschätzt.
• Rows — wie viele Zeilen erwartet werden und wie viele tatsächlich rauskommen.
• Time — wie lange die Operation gebraucht hat.

Beispielausgabe:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Achte auf Seq Scan on students. Das heißt, PostgreSQL scannt ALLE Zeilen der Tabelle students. Wenn die Tabelle groß ist, kann das RICHTIG LANGSAM werden.

Beispiele für den Einsatz von EXPLAIN ANALYZE

Lass uns ein paar praktische Beispiele anschauen, wo du lernst, Probleme in Queries zu finden und zu beheben.

Beispiel 1: kompletter Table-Scan

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Ausgabe:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Das Problem hier: PostgreSQL macht einen Seq Scan, also geht alle Zeilen der Tabelle durch. Wenn da Millionen Zeilen drin sind, wird das zum Flaschenhals.

Lösung: Wir legen einen Index auf die Spalte age an.

CREATE INDEX idx_students_age ON students(age);

Jetzt führen wir denselben Query nochmal aus:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Ausgabe:

Index Scan using idx_students_age on students  (cost=0.29..12.30 rows=250 width=64) (actual time=0.005..0.014 rows=250 loops=1)
  Index Cond: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

Jetzt sehen wir Index Scan statt Seq Scan. Nice, der Query ist jetzt richtig schnell!

Beispiel 2: Komplexer Query mit JOIN

Stell dir vor, wir haben zwei Tabellen: students und courses. Wir wollen die Namen der Studenten und die Kursnamen wissen, für die sie eingeschrieben sind.

EXPLAIN ANALYZE
SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

Die Ausgabe könnte so aussehen:

Nested Loop  (cost=1.23..56.78 rows=500 width=128) (actual time=0.123..2.345 rows=500 loops=1)
  -> Seq Scan on students s  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.023..0.045 rows=1000 loops=1)
  -> Index Scan using idx_enrollments_student_id on enrollments e  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
  -> Index Scan using idx_courses_id on courses c  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
Execution Time: 2.456 ms

Wie du siehst, hat PostgreSQL alles im Griff: Es werden Indizes auf den Tabellen enrollments und courses genutzt, und die Ausführung ist schnell. Fehlt aber einer der Indizes, siehst du einen Seq Scan – und das macht alles langsamer.

Performance-Optimierung von Funktionen

Stell dir vor, wir haben eine Funktion, die eine Liste von Studenten zurückgibt, die älter als ein bestimmtes Alter sind:

CREATE OR REPLACE FUNCTION get_students_older_than(min_age INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name
  FROM students
  WHERE age > min_age;
END;
$$ LANGUAGE plpgsql;

Wir können die Performance dieser Funktion mit EXPLAIN ANALYZE analysieren:

EXPLAIN ANALYZE
SELECT * FROM get_students_older_than(20);

Funktion schneller machen

Wenn die Funktion lange braucht, liegt das vielleicht an einem kompletten Table-Scan. Was tun?

1. Check, ob die Spalte, die du im Filter nutzt (age), einen Index hat.
2. Schau, wie viele Zeilen in der Tabelle sind und überleg dir Partitionierung, wenn es zu viele Daten sind.

Flaschenhälse und wie du sie loswirst

1. Kompletter Table-Scan (Seq Scan). Nutze Indizes, um die Suche nach Zeilen zu beschleunigen. Aber Achtung: Zu viele Indizes machen Inserts langsamer.

2. Zu viele Zeilen im Ergebnis. Wenn dein Query Millionen Zeilen zurückgibt, denk über Filter (WHERE, LIMIT) oder Pagination (OFFSET) nach.

3. "Teure" Operationen. Manche Sachen wie Sortieren, Aggregieren oder das Joinen großer Tabellen brauchen viele Ressourcen. Nutze Indizes oder teile den Query in mehrere Schritte auf.