Sammeln von Statistiken zur Nutzung von Indizes und Tabellen

Stell dir vor, deine Datenbank ist ein riesiges Lagerhaus. Indizes sind die Kataloge und Listen, die dir helfen, schnell das Richtige zu finden. Tabellen sind die Waren auf den Regalen. Wenn ein Index schlecht genutzt wird, ist das so, als würde der Katalog irgendwo in der Ecke verstauben und niemand schaut rein. Wenn eine Tabelle stark genutzt wird, aber schlecht strukturiert ist oder zu viele unnötige Daten enthält, dann belastet das unser Lager (die Datenbank) und macht alles langsamer.

Wichtige Analyseaufgaben:

1. Bewertung der Effektivität von Indizes. Zum Beispiel: Liegt dein teurer Index nur rum? Weg damit!
2. Bestimmung der Häufigkeit von Lese- und Schreiboperationen. Hilft zu verstehen, welche Tabellen wirklich aktiv genutzt werden.
3. Optimierung von Abfragen. Statistiken zeigen dir, wo du die Datenverarbeitung beschleunigen kannst, indem du Indizes hinzufügst oder änderst.

Views pg_stat_user_indexes und pg_stat_user_tables

In PostgreSQL gibt es zwei richtig nützliche Views für Statistiken: pg_stat_user_indexes und pg_stat_user_tables. Lass uns die mal genauer anschauen.

pg_stat_user_indexes: Wie werden Indizes genutzt?

Wichtige Felder:

• relname — Name der Tabelle, zu der der Index gehört.
• indexrelname — Name des Index.
• idx_scan — Wie oft wurde der Index für Suchen verwendet.
• idx_tup_read — Anzahl der Zeilen, die mit dem Index gelesen wurden.
• idx_tup_fetch — Anzahl der tatsächlich zurückgegebenen Zeilen (nach Filtern).

Beispiel-Query:

SELECT relname AS tabelle_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS index_tuples_gelesen, 
       idx_tup_fetch AS index_tuples_geholt
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Was machen wir hier?

• Wir sortieren die Daten nach der Anzahl der Index-Aufrufe (idx_scan), um zu sehen, welche Indizes am beliebtesten sind.
• Wenn ein Index kaum genutzt wird (idx_scan = 0), solltest du dich fragen: Brauch ich den überhaupt?

Praktischer Nutzen:

Du rollst eine neue Version deiner App aus und hast einen neuen Index hinzugefügt. Mit pg_stat_user_indexes kannst du checken, ob deine Query wirklich den neuen Index nutzt oder ob PostgreSQL immer noch den alten Weg nimmt und dein Optimierungs-Meisterwerk ignoriert.

pg_stat_user_tables: Tabellen-Statistiken anschauen

Wichtige Felder:

• relname — Name der Tabelle.
• seq_scan — Anzahl der sequentiellen Scans (ohne Index).
• seq_tup_read — Anzahl der Zeilen, die bei sequentiellen Scans zurückgegeben wurden.
• idx_scan — Anzahl der Index-Scans für die Tabelle.
• n_tup_ins — Anzahl der eingefügten Zeilen.
• n_tup_upd — Anzahl der aktualisierten Zeilen.
• n_tup_del — Anzahl der gelöschten Zeilen.

Beispiel-Query:

SELECT relname AS tabelle_name, 
       seq_scan AS sequentielle_scans, 
       idx_scan AS index_scans, 
       n_tup_ins AS zeilen_eingefügt, 
       n_tup_upd AS zeilen_aktualisiert, 
       n_tup_del AS zeilen_gelöscht
FROM pg_stat_user_tables
ORDER BY sequentielle_scans DESC;

Was sehen wir hier?

• Tabellen mit vielen sequentiellen Scans (seq_scan) sind ein Hinweis darauf, dass ein Index fehlt.
• Die Anzahl der Insert-, Update- und Delete-Operationen zeigt, wie oft sich die Daten in der Tabelle ändern.

Praktischer Nutzen: Du arbeitest mit der Tabelle users, in der alle User-Daten deiner App gespeichert sind. Mit pg_stat_user_tables stellst du fest, dass die sequentiellen Scans (seq_scan) für diese Tabelle durch die Decke gehen. Das ist ein Zeichen: Zeit, Indizes auf die meistgenutzten Spalten zu setzen, damit die Queries schneller werden.

Beispiel: Analyse von Indizes und Tabellen in einer echten Datenbank

Nehmen wir an, wir haben eine Datenbank mit den Tabellen orders (Bestellungen) und products (Produkte). Wir wollen checken, wie effektiv Tabellen und Indizes genutzt werden.

Index-Analyse:

SELECT relname AS tabelle_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS tuples_gelesen, 
       idx_tup_fetch AS tuples_geholt
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY index_scans DESC;

Du siehst, dass der Index orders_customer_id_idx 50.000 Mal genutzt wurde, aber orders_date_idx nur 5 Mal. Vielleicht brauchst du orders_date_idx gar nicht.

Tabellen-Analyse:

SELECT relname AS tabelle_name, 
       seq_scan AS sequentielle_scans, 
       seq_tup_read AS tuples_gelesen, 
       idx_scan AS index_scans, 
       n_tup_ins AS zeilen_eingefügt, 
       n_tup_upd AS zeilen_aktualisiert, 
       n_tup_del AS zeilen_gelöscht
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'products')
ORDER BY sequentielle_scans DESC;

Die Tabelle products wird ständig sequentiell gescannt. Das ist ein Hinweis: Im Produktkatalog fehlen Indizes.

Typische Fehler und wie du sie vermeidest

Der Standard-Fail für Einsteiger ist, die Statistiken zu ignorieren. Zum Beispiel: Du hast einen neuen Index hinzugefügt und denkst: „Jetzt fliegen die Queries!“, aber PostgreSQL nutzt ihn nicht, weil die Statistiken nicht automatisch aktualisiert wurden. Nach größeren Änderungen an Tabellen solltest du die Statistiken manuell mit dem Befehl ANALYZE updaten.

Ein weiterer häufiger Fehler: Indexe wie verrückt hinzufügen. Denk dran, jeder Index braucht Platz auf der Platte und macht Inserts, Updates und Deletes langsamer. Nutze die Statistik pg_stat_user_indexes, um zu checken, ob ein Index wirklich gebraucht wird oder nur Ballast ist.

Praktischer Nutzen: Wo hilft dir das?

In der echten Entwicklung: Wenn die Datenbank lahmt, suchst du als Erstes nach Problemen mit Tabellen und Indizes.

Im Vorstellungsgespräch: Fragen zur Index-Optimierung sind SQL-Interview-Klassiker. Kannst du pg_stat_user_indexes erklären? Dann bist du schon halb durch.

Im Datenbank-Admin-Alltag: Monitoring ist Daily Business für DBAs. Ohne Statistiken zu Tabellen und Indizes kannst du nichts verbessern.