Verwendung von pg_stat_statements zur Analyse von Index- und Filter-Nutzung

Indizes sind wie Lesezeichen in einem Buch. Sie helfen dir, schnell die richtigen Daten zu finden. Aber was, wenn wir jede Menge Lesezeichen hinzugefügt haben, aber niemand sie nutzt? Oder noch schlimmer: Schlechte Lesezeichen zwingen uns, das Buch von vorne bis hinten durchzublättern? Genau hier kommt die Analyse der Index-Nutzung ins Spiel.

Schlecht geschriebene Queries können Indizes ignorieren, was zu teuren sequentiellen Scans (Seq Scan) führt. Das macht deine Queries langsam und erhöht die Server-Last. Unser Ziel: Herausfinden, welche Queries keine Indizes nutzen – und warum.

Wie checke ich, ob Indizes genutzt werden?

Schauen wir uns zwei Kernfragen an:

1. Werden die Indizes, die wir erstellt haben, überhaupt genutzt?
2. Und falls ja: Sind sie auch wirklich effektiv?

Dafür analysieren wir die Query-Statistiken in pg_stat_statements und achten auf ein paar Spalten:

• rows: Anzahl der Zeilen, die von der Query verarbeitet wurden.
• shared_blks_hit: Anzahl der Seiten, die aus dem Speicher (statt von der Platte) gelesen wurden.
• shared_blks_read: Anzahl der Seiten, die tatsächlich von der Platte gelesen wurden.

Je weniger Zeilen die Query verarbeitet und je höher der Anteil von shared_blks_hit an allen Seiten ist, desto besser arbeitet unser Index.

Beispiel: Index-Analyse

Angenommen, wir haben eine Tabelle für Studenten:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    grade_level INTEGER
);

-- Wir fügen einen Index auf grade_level hinzu
CREATE INDEX idx_grade_level ON students(grade_level);

Jetzt fügen wir ein paar Daten zum Testen ein:

INSERT INTO students (first_name, last_name, birth_date, grade_level)
SELECT 
    'Student ' || generate_series(1, 100000),
    'LastName',
    '2000-01-01'::DATE + (random() * 3650)::INT,
    floor(random() * 12)::INT
FROM generate_series(1, 100000);

Wir machen eine Query, um Studenten mit einem bestimmten Level zu finden:

SELECT *
FROM students
WHERE grade_level = 10;

Check in pg_stat_statements

Nachdem wir die Query ein paar Mal ausgeführt haben, können wir die Statistik checken:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level = 10%';

Wie interpretiere ich das Ergebnis?

• rows: Gibt die Query zu viele Zeilen zurück, macht der Index überhaupt Sinn? Vielleicht ist er bei wenig selektiven Bedingungen überflüssig.
• shared_blks_hit und shared_blks_read: Werden viele Seiten von der Platte gelesen (shared_blks_read), dann funktioniert die Indexierung nicht oder die Daten sind nicht im Buffer Pool.

Index-Optimierung

Einen Index zu erstellen ist nur die halbe Miete. Wichtig ist, dass PostgreSQL ihn auch wirklich nutzt. Manchmal, trotz aller Mühe, nimmt die DB keinen Index, sondern macht einen sequentiellen Scan. Warum? Lass uns das anschauen.

Erstmal: Warum kann ein Index ignoriert werden, obwohl er eigentlich helfen sollte? Und dann: Welche Tricks helfen, damit die DB „merkt“, dass da ein Index ist und ihn auch nutzt?

Was, wenn der Index nicht genutzt wird?

Manchmal ignoriert PostgreSQL den Index und macht einen sequentiellen Scan (Seq Scan). Das kann verschiedene Gründe haben:

1. Niedrige Selektivität der Bedingung. Gibt die Query mehr als die Hälfte der Tabellendaten zurück, ist ein sequentieller Scan oft schneller.
2. Datentypen oder Funktionen. Nutzt du in der Query eine Funktion auf der indexierten Spalte, wird der Index oft ignoriert. Zum Beispiel:

   SELECT *
   FROM students
   WHERE grade_level + 1 = 11; -- Index wird nicht genutzt

In solchen Fällen kann man die Query umschreiben:

   SELECT * 
   FROM students
   WHERE grade_level = 10; -- Nutzt den Index

3. Falscher Index-Typ. Für Volltextsuche solltest du z.B. GIN oder GiST Indizes nehmen, nicht B-TREE.

4. Fehlerhafte Statistiken. Sind die Statistiken veraltet, trifft der Optimizer vielleicht die falsche Entscheidung. Nutze ANALYZE:

   ANALYZE students;
   

Query verbessern

Zurück zu unserem Beispiel. Wenn die Indexierung nicht greift, kannst du Folgendes tun:

1. Stell sicher, dass die Query Filter nutzt, die den Index verwenden können: Keine Funktionen, keine Typumwandlungen usw.
2. Gibt der Filter viele Werte zurück, überlege, ob der Index überhaupt Sinn macht. Ist es eine häufige Query, probiere, die Tabellenstruktur zu ändern oder Materialized Views zu nutzen.
3. Wird wegen großer Datenmengen ein Seq Scan gemacht, teile die Tabelle in Partitionen (PARTITION BY).

Effizienz der Indexierung prüfen

Nach der Optimierung führe die Query nochmal aus und check die Statistik:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level%';

Vergleiche die Metriken vorher und nachher. Du solltest weniger Plattenzugriffe (shared_blks_read) und mehr Treffer im Speicher (shared_blks_hit) sehen.

Echte Beispiele

1. Falsche Index-Nutzung

Wir haben eine Produkttabelle mit einem Textfeld description:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
);

-- Index für Volltextsuche
CREATE INDEX idx_description ON products USING GIN (to_tsvector('english', description));

Wenn wir diese Query machen:

SELECT *
FROM products
WHERE description ILIKE '%smartphone%';

Der Index wird nicht genutzt! Grund: ILIKE ist nicht kompatibel mit GIN. Um den Index zu nutzen, muss die Query so umgeschrieben werden:

SELECT *
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('smartphone');

2. Kein Index, wo einer gebraucht wird

Angenommen, diese Query:

SELECT *
FROM students
WHERE birth_date BETWEEN '2001-01-01' AND '2003-01-01';

macht einen sequentiellen Scan (Seq Scan). Das liegt vielleicht daran, dass es keinen Index auf birth_date gibt. Erstelle einen Index:

CREATE INDEX idx_birth_date ON students(birth_date);

und führe ANALYZE students aus – damit wird die Query deutlich schneller.