Query-Optimierung mit Plananalyse: EXPLAIN ANALYZE

Jetzt wird’s ernst: SQL-Queries sind nicht einfach nur Codezeilen, sondern ein echter Dialog mit deiner Datenbank. Wenn du ihr ein sanftes "SELECT *" zuflüsterst, versteht sie dich wahrscheinlich und führt den Befehl ohne Murren aus. Aber wenn du ihr einen unstrukturierten SQL-Roman vorlegst, fängt sie an zu grübeln… und dann wird sie langsam.

Query-Optimierung heißt, mit der Datenbank klar und prägnant zu sprechen. Wenn dein Query sauber und effizient geschrieben ist, läuft er schnell, belastet das System nicht und stört keine anderen Prozesse. Ein schlecht gebauter Query dagegen kann das ganze System ausbremsen: Die Datenbank frisst mehr CPU und RAM, das Disk-Subsystem ist mit unnötigen Reads und Writes beschäftigt, und sogar die Apps, die die DB nutzen, werden träge.

EXPLAIN ANALYZE hilft dir, solche Problemstellen zu finden und zu checken, wo genau dein Query „schwer atmet“. Das ist wie eine Diagnose – ohne die kannst du Performance-Probleme kaum heilen.

Typische Query-Probleme und wie du sie findest

Jetzt wird’s Zeit, die üblichen Verdächtigen für schlechte Performance kennenzulernen. Dafür schnappen wir uns EXPLAIN ANALYZE.

Problem 1: Sequentielles Scannen (Seq Scan)

Seq Scan (sequentielles Scannen) bedeutet, dass PostgreSQL jede Zeile der Tabelle durchgeht, um Daten zu finden. Bei kleinen Tabellen ist das okay, aber bei großen kann das richtig weh tun.

Wie findest du raus, ob Seq Scan genutzt wird? Einfach mit EXPLAIN ANALYZE analysieren. Beispiel:

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE student_id = 123;

Das Ergebnis könnte so aussehen (achte auf Seq Scan):

Seq Scan on students  (cost=0.00..35.50 rows=1 width=72) (actual time=0.010..0.015 rows=1 loops=1)

Wie löst du das?

Erstelle einen Index auf student_id, falls noch keiner existiert:

CREATE INDEX idx_student_id ON students(student_id);

Danach nochmal EXPLAIN ANALYZE laufen lassen. Jetzt solltest du Index Scan statt Seq Scan sehen.

Problem 2: Niedrige Selektivität der Bedingungen

Selektivität heißt: Wie viele Zeilen müssen durchsucht werden, um das Gesuchte zu finden. Wenn dein Filter fast die ganze Tabelle betrifft, bringt dir ein Index nichts.

Beispiel für einen Query mit niedriger Selektivität:

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE program = 'Computer Science';

Wenn 90% der Studenten in der Tabelle Computer Science studieren, macht PostgreSQL vielleicht trotzdem ein Seq Scan, selbst wenn ein Index auf program existiert.

Wie kannst du den Query verbessern?

1. Überdenke die Logik des Queries: Vielleicht solltest du den Filter mit weiteren Bedingungen präzisieren.
2. Stelle sicher, dass die Tabellenstatistiken aktuell sind (das hilft PostgreSQL, die Selektivität richtig einzuschätzen):

ANALYZE students;

3. Wenn der Query fälschlicherweise den Index statt Seq Scan nutzt, kannst du PostgreSQL zwingen, ihn zu verwenden:

SET enable_seqscan = OFF;

Problem 3: Überflüssige Sortier-Operationen

Sortieren (Sort) kann teuer werden, besonders wenn die Daten nicht in den RAM passen. Typisch ist das bei ORDER BY.

Beispiel für das Problem:

EXPLAIN ANALYZE
SELECT * 
FROM students
ORDER BY last_name;

Du siehst dann vielleicht sowas:

Sort  (cost=123.00..126.00 rows=300 width=45) (actual time=1.123..1.234 rows=300 loops=1)

Wie kannst du das Sortieren beschleunigen? Wenn du oft nach einer bestimmten Spalte sortierst, leg einen Index an:

CREATE INDEX idx_last_name ON students(last_name);

Jetzt kann PostgreSQL den Index nutzen, um die Daten schon sortiert zu holen – die extra Sortier-Operation fällt weg.

Problem 4: Fehlende Begrenzung (LIMIT)

Wenn du mit SELECT ohne Begrenzung abfragst, kann der Query die ganze Tabelle durchackern, auch wenn du nur die erste Zeile brauchst.

So sieht das aus:

EXPLAIN ANALYZE
SELECT * 
FROM students
WHERE gpa > 3.5;

Wenn die Datenbank eine Million Zeilen hat und der Filter gpa > 3.5 80% der Tabelle zurückgibt, kannst du dich auf Wartezeit einstellen.

Brauchst du aber nur die Top 10 Studenten, nutze LIMIT:

SELECT *
FROM students
WHERE gpa > 3.5
ORDER BY gpa DESC
LIMIT 10;

Außerdem kannst du mit LIMIT und OFFSET Paging bauen.

Session-Parameter steuern: SET

Mit SET in PostgreSQL kannst du Session-Parameter oder Query-Settings ändern. Das ist wie ein temporäres Tuning, das nur für die aktuelle Verbindung gilt.

Anders gesagt: SET ist ein Weg, das „Mood“ von PostgreSQL on the fly zu steuern, ohne globale Settings zu ändern.

Wofür braucht man das?

• Sprache oder Datumsformat vor einem Report umstellen.
• Für einen schweren Query mehr RAM geben.
• Logging während eines Massenuploads abschalten.
• Temporär den search_path ändern.
• Sicherheit steuern (z.B. kurzzeitig User-Rechte runterdrehen).

Allgemeiner Syntax

SET parameter = wert;

Um den aktuellen Wert eines Parameters zu sehen:

SHOW parameter;

Um den Default-Wert wiederherzustellen:

RESET parameter;

Beispiel für eine umfassende Optimierung

Angenommen, du hast die Aufgabe: Finde die letzten 10 Studenten mit dem höchsten Notendurchschnitt (GPA), die Computer Science studieren. So sieht der ursprüngliche Query aus:

SELECT *
FROM students
WHERE program = 'Computer Science'
ORDER BY gpa DESC
LIMIT 10;

1. Query-Analyse: Erstmal EXPLAIN ANALYZE ausführen:

   EXPLAIN ANALYZE
   SELECT * 
   FROM students
   WHERE program = 'Computer Science'
   ORDER BY gpa DESC
   LIMIT 10;
   

   Wenn du sequentielles Scannen und Sortieren siehst, ist das ein Zeichen für Optimierungsbedarf.

2. Index für Filter und Sortierung:

   Lege einen zusammengesetzten Index auf beide Spalten an:

   CREATE INDEX idx_program_gpa
   ON students(program, gpa DESC);
   

3. Verbesserung prüfen:

   Nochmal EXPLAIN ANALYZE laufen lassen. Jetzt sollte der Query den neuen Index nutzen und ohne Sortieren oder Seq Scan auskommen.

Methodik der Query-Optimierung

1. Starte mit der Analyse des aktuellen Ausführungsplans. Nutze EXPLAIN ANALYZE, um Problem-Operationen zu finden.

2. Finde die Engpässe. Such die Plan-Nodes, die am meisten Zeit oder Ressourcen brauchen.

3. Lege Indexe an. Check, welche Spalten für Filter und Sortierung genutzt werden, und erstelle die nötigen Indexe.

4. Reduziere die Datenmenge. Nutze LIMIT, OFFSET und präzise Filterbedingungen.

5. Halte die Statistiken aktuell. Führe ANALYZE aus, damit PostgreSQL frische Infos über die Datenverteilung hat.

6. Teste deine Änderungen. Nach der Optimierung wieder EXPLAIN ANALYZE laufen lassen, um die Performance zu checken.

Wie geht’s weiter?

Du hast gerade einen Schnellkurs in Query-Optimierung hinter dir. Glückwunsch! Je mehr du mit EXPLAIN ANALYZE experimentierst, desto besser verstehst du, wie PostgreSQL unter der Haube tickt. Und denk dran: Keine magischen Indexe retten dich, wenn dein Query zu kompliziert oder schwammig formuliert ist. SQL – wie jede Sprache – liebt Klarheit.