Analyse von langsamen Queries mit pg_stat_statements

Wenn du an echten Projekten arbeitest, können tausende User gleichzeitig mit deiner App interagieren. Sie schicken Queries an die Datenbank, fügen Daten hinzu, lesen sie aus, aktualisieren sie... Und plötzlich merkst du, dass dein Server anfängt zu "stöhnen". Das ist ein Zeichen dafür, dass deine Queries alles andere als optimal sind. Manchmal sieht ein Query "auf dem Papier" ganz nett aus, aber in der Praxis ist er ein Performance-Desaster. Genau hier kommt pg_stat_statements ins Spiel.

pg_stat_statements erlaubt dir:

1. Langsame Queries zu tracken.
2. Zu checken, wie oft bestimmte Queries ausgeführt wurden.
3. Zu sehen, wie viel Zeit sie insgesamt gebraucht haben.
4. Die durchschnittliche Ausführungszeit eines Queries zu sehen.
5. Keine fatale Entscheidung zu treffen und die ganze App umzuschreiben!

Struktur von pg_stat_statements verstehen

Nachdem du das Extension aktiviert hast, gibt es in deiner Datenbank eine spezielle View pg_stat_statements. Hier werden alle Daten zu ausgeführten Queries gespeichert. Lass uns erstmal anschauen, was da so drin steht:

SELECT * FROM pg_stat_statements LIMIT 1;

Das Ergebnis könnte so aussehen (vereinfachte Version):

Kurz erklärt:

• query — der SQL-Query selbst.
• calls — wie oft dieser Query ausgeführt wurde.
• total_time — wie viel Zeit insgesamt für diesen Query draufging.
• rows — wie viele Zeilen der Query zurückgegeben hat.
• shared_blks_read — wie viele Blöcke gelesen wurden (es wird auf die Festplatte zugegriffen, wenn du keinen Cache nutzt).

Analyse der Ergebnisse

Jetzt, wo pg_stat_statements läuft, schauen wir mal, wie man langsame Queries findet.

Die langsamsten Queries

Um rauszufinden, welche Queries am meisten Zeit fressen, kannst du diesen Query nutzen:

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Hier:

• mean_time — das ist die durchschnittliche Ausführungszeit eines Queries (total_time / calls).
• ORDER BY total_time DESC — wir sortieren nach der gesamten Ausführungszeit.

Häufig ausgeführte Queries

Manchmal liegt das Problem nicht bei langsamen Queries, sondern bei Queries, die einfach zu oft laufen. Zum Beispiel:

SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

Query-Optimierung

1. Nutze Indexe

Wenn du siehst, dass Queries auf bestimmte Spalten langsam laufen, check, ob es für diese Spalten einen Index gibt. Angenommen, du hast eine Tabelle studenten mit vielen Zeilen und du greifst oft auf das Feld nachname zu. Dann solltest du einen Index anlegen:

CREATE INDEX idx_studenten_nachname ON studenten (nachname);

2. Schreib den Query um

Angenommen, du siehst, dass ein Query wie SELECT * FROM bestellungen WHERE betrag > 1000 zu lange dauert. Wahrscheinlich solltest du statt "alles von allem" nur die nötigen Spalten auswählen:

SELECT bestellung_id, betrag FROM bestellungen WHERE betrag > 1000;

Statistiken zurücksetzen

Manchmal willst du nur die neuen Ergebnisse sehen (zum Beispiel nach einer Optimierung). Dann solltest du die Daten in pg_stat_statements zurücksetzen. Das geht mit diesem Befehl:

SELECT pg_stat_statements_reset();

Das funktioniert wie der "Zurücksetzen"-Button auf deinem Taschenrechner. Danach werden die Statistiken wieder neu gesammelt.

Problematische Queries finden

Stell dir vor, du bist Datenbank-Admin an einer Uni und die Studenten beschweren sich massenhaft, dass ihr persönlicher Bereich ewig lädt. Du entscheidest dich, pg_stat_statements zu checken:

Schritt 1: Die langsamsten Queries finden

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

Du siehst, dass ein Query wie SELECT * FROM studenten WHERE status = 'aktiv' 30 Sekunden braucht. Oha. Da muss dringend was passieren.

Schritt 2: Index prüfen Nachdem du die Tabelle studenten analysiert hast, merkst du, dass es für die Spalte status keinen Index gibt. Das änderst du:

CREATE INDEX idx_studenten_status ON studenten (status);

Schritt 3: Ergebnis prüfen Nach der Optimierung checkst du pg_stat_statements nochmal und siehst, dass der Query jetzt in 0,5 Sekunden läuft. Sieg!

Typische Fehler bei der Nutzung von pg_stat_statements

Manchmal machen Admins Fehler beim Query-Analyse:

1. Extension nicht aktiviert. Wenn du vergessen hast, pg_stat_statements in shared_preload_libraries zu aktivieren, werden keine Statistiken gesammelt.
2. Indexe werden ignoriert. Auch wenn Queries langsam aussehen, kann das Problem oft durch den richtigen Index gelöst werden.
3. Statistiken nicht zurückgesetzt. Wenn du pg_stat_statements_reset() nicht ausführst, stören alte Daten die Analyse der aktuellen Situation.

Die Nutzung von pg_stat_statements ist wie ein GPS-Navi für deine Datenbank: Es zeigt dir genau, wo du im "Stau" stehst und gibt dir sogar Tipps für eine Umleitung. Wenn du das Tool richtig einstellst, kannst du die Performance deiner Datenbanken ordentlich verbessern.