Query-Optimierung mit CTE

Heute tauchen wir in die spannende (und ein bisschen gruselige) Welt der Query-Optimierung mit CTE (Common Table Expressions) ein. Wenn du schon weißt, wie man CTEs baut (das hatten wir in den letzten Vorlesungen), dann ist jetzt der perfekte Zeitpunkt, um über die Feinheiten, „Stolpersteine“ und Tricks zu sprechen, wie du das Maximum an Effizienz rausholst.

Auf den ersten Blick wirken CTEs perfekt: Sie sehen sauber aus, sind easy zu schreiben und helfen, Code in logische Blöcke zu splitten. Aber es gibt einen kleinen (oder nicht so kleinen) Haken. PostgreSQL hat eine spezielle Strategie, wie es mit CTEs umgeht, und das beeinflusst die Performance.

Wenn PostgreSQL ein WITH sieht, materialisiert es normalerweise das Ergebnis des CTE. Das heißt, die vom CTE zurückgegebenen Daten werden erstmal berechnet und als temporäre Tabelle gespeichert, die dann im Hauptquery verwendet wird. Das ist praktisch, wenn man das Ergebnis mehrfach braucht, kann aber ein Problem sein, wenn:

1. Die Datenmenge im CTE riesig ist, aber nur ein Teil davon genutzt wird.
2. Der CTE zu oft aufgerufen wird, was Overhead erzeugt.
3. Wir unnötig komplizierte CTEs bauen, die eigentlich gar nicht nötig sind.

Materialisierung kennenlernen

Materialisierung ist der Prozess, bei dem PostgreSQL das CTE-Ergebnis im Speicher oder auf der Platte speichert (je nach Datenmenge). Das bedeutet, die Daten werden nur einmal geholt, aber wenn du den CTE nur an einer Stelle nutzt, kann Materialisierung überflüssig sein. Zum Beispiel:

WITH large_set AS (
    SELECT *
    FROM students_grades
    WHERE note > 60
)
SELECT student_id, note
FROM large_set
WHERE note > 90;

In diesem Fall erstellt PostgreSQL zuerst eine temporäre Tabelle mit dem kompletten CTE-Ergebnis (note > 60) und filtert dann die Zeilen, wo note > 90. Das ist ein unnötiger Zwischenschritt und kostet Performance.

Wie vermeidet man unnötige Materialisierung?

Seit PostgreSQL 12 kann man Materialisierung von CTEs vermeiden, wenn sie nicht gebraucht wird. Dafür gibt es das Keyword MATERIALIZED (Standard) oder NOT MATERIALIZED. Beispiel:

WITH large_set AS NOT MATERIALIZED (
    SELECT *
    FROM students_grades
    WHERE note > 60
)
SELECT student_id, note
FROM large_set
WHERE note > 90;

Hier sagen wir PostgreSQL, dass es die Daten von large_set nicht materialisieren soll, sondern den Query direkt ins Hauptexpression einbauen soll. Das macht den Query effizienter, weil keine Zwischentabelle gebaut wird.

Wann ist Materialisierung sinnvoll?

Denke nicht, dass Materialisierung immer schlecht ist! Wenn die CTE-Daten mehrfach im Query verwendet werden oder unabhängig berechnet werden müssen, kann Materialisierung nützlich sein. Beispiel:

WITH materialized_example AS (
    SELECT *
    FROM students_grades
    WHERE note > 60
)

SELECT student_id
FROM materialized_example
WHERE note > 90

UNION ALL

SELECT student_id
FROM materialized_example
WHERE note < 70;

Hier sorgt die Materialisierung dafür, dass der Filter note > 60 nicht mehrfach berechnet wird.

Query-Optimierung mit Indizes

Damit CTEs schneller laufen, solltest du Indizes auf den Basistabellen nutzen, aus denen die Daten kommen. Zum Beispiel:

CREATE INDEX idx_students_grades_note ON students_grades(note);

WITH filtered_students AS (
    SELECT student_id, note
    FROM students_grades
    WHERE note > 90
)
SELECT *
FROM filtered_students;

Der Index auf der Spalte note hilft PostgreSQL, schneller die Zeilen zu finden, die note > 90 erfüllen. Das ist besonders bei großen Tabellen wichtig.

Große CTEs in kleinere aufteilen

Wenn ein CTE viele Daten zurückgibt, die danach noch gefiltert oder aggregiert werden, ist es besser, ihn in mehrere Schritte zu splitten. Statt einem fetten CTE lieber mehrere kleine bauen:

Schlecht (großer CTE):

WITH large_query AS (
    SELECT s.student_id, AVG(g.note) AS avg_note
    FROM students s
    JOIN grades g ON s.student_id = g.student_id
    WHERE g.subject_id = 101 AND g.note > 85
    GROUP BY s.student_id
)
SELECT *
FROM large_query
WHERE avg_note > 90;

Besser (in Schritte aufgeteilt):

WITH filtered_grades AS (
    SELECT student_id, note
    FROM grades
    WHERE subject_id = 101 AND note > 85
),
average_grades AS (
    SELECT student_id, AVG(note) AS avg_note
    FROM filtered_grades
    GROUP BY student_id
)
SELECT *
FROM average_grades
WHERE avg_note > 90;

So kann PostgreSQL die Ausführung besser optimieren.

Praxisbeispiel: Struktur analysieren und optimieren

Schauen wir uns ein komplexeres Beispiel an. Wir haben Tabellen für Studenten, Kurse und Noten. Wir wollen Studenten mit hohem Durchschnitt finden und ihre Kurse anzeigen:

WITH high_achievers AS (
    SELECT student_id, AVG(note) AS avg_note
    FROM grades
    GROUP BY student_id
    HAVING AVG(note) > 90
),
student_courses AS (
    SELECT e.student_id, c.kurs_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)
SELECT ha.student_id, ha.avg_note, sc.kurs_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Diesen Query kann man optimieren, indem man Indizes auf grades und enrollments anlegt, damit Filter und Joins schneller laufen.

Monitoring: Performance analysieren

Um zu checken, wie effizient dein Query ist, nutze EXPLAIN oder EXPLAIN ANALYZE. Zum Beispiel:

EXPLAIN ANALYZE
WITH high_achievers AS (
    SELECT student_id, AVG(note) AS avg_note
    FROM grades
    GROUP BY student_id
    HAVING AVG(note) > 90
)
SELECT *
FROM high_achievers;

Dieser Query zeigt dir, wie lange jeder Schritt dauert, und hilft dir zu sehen, wo du noch optimieren kannst.

Mehr zu EXPLAIN ANALYZE machen wir dann in den nächsten Levels :P

Typische Fehler bei der CTE-Optimierung

1. Indizes vergessen. Wenn du im CTE filterst, aber kein Index auf der Basistabelle ist, leidet die Performance.
2. Zu große CTEs verwenden. Wenn ein Query zu viel macht, kann das zur Materialisierung von riesigen Datenmengen führen.
3. NOT MATERIALIZED übertreiben. Manchmal ist Materialisierung doch nötig, um mehrfaches Ausführen des CTE zu vermeiden.
4. Monitoring ignorieren. Ohne EXPLAIN merkst du vielleicht gar nicht, dass deine Queries langsam sind.

Jetzt bist du ready, Queries mit CTEs zu optimieren, Fallen zu vermeiden und die Performance zu boosten! Denk dran: CTEs sind ein Tool, keine Wunderwaffe. Nutze sie mit Köpfchen, dann werden sie deine besten Freunde in PostgreSQL.