Wie wählt man den passenden Index-Typ aus

Wir sind schon tief in die Theorie der Indizes eingetaucht, haben ihre Typen kennengelernt, gelernt, wie man sie erstellt und löscht, und auch verstanden, wie man komplexe Datentypen wie Arrays und JSONB indexiert. Jetzt ist es an der Zeit, darüber zu sprechen, wie du genau den Index auswählst, der für deine Aufgaben wirklich effizient arbeitet – denn die falsche Wahl kann zu echten Problemen führen.

Stell dir vor, deine Datenbank ist eine Bibliothek und die Anfragen sind Besucher, die nach Büchern suchen. Wenn die Bücher einfach auf dem Boden herumliegen, wird die Suche zu einem endlosen Herumirren. Indizes sind wie organisierte Regale und Kataloge, die helfen, das Richtige schnell zu finden, ohne alles durchsuchen zu müssen.

Aber wenn du das falsche Regal oder den falschen Katalog aufstellst, zum Beispiel einen HASH-Index verwendest, wo eigentlich ein Index für Bereichssuchen gebraucht wird, ist das so, als würde der Bibliothekar versuchen, Bücher nach Titel zu suchen, aber er hat nur einen Katalog nach Erscheinungsjahr – der Prozess zieht sich und alle fangen an zu meckern. In der Datenbank äußert sich das in langsamen Anfragen und hoher Systemlast.

Heute schauen wir uns an, wie du den richtigen Index auswählst, damit deine Anfragen flitzen und die Datenbank nicht schlappmacht. Das Ergebnis kann sonst echt mies sein: Anfragen sind lahm, Ressourcen werden gefressen, der Bibliothekar (PostgreSQL) ist am Ende seiner Nerven.

Kriterien für die Index-Auswahl: Checkliste

Wenn du einen Index auswählst, beantworte dir ein paar Fragen:

1. Welcher Datentyp steckt in dieser Spalte?
   • Zum Beispiel brauchen Zahlen wie INTEGER, FLOAT oft einen B-TREE-Index, Arrays – GIN, Textfelder – das hängt schon mehr von der Aufgabe ab.

2. Welche Anfragen machst du am häufigsten?

   • WHERE field = value? Direkte Suche? Dann passt meistens B-TREE oder HASH.
   • Suche in Arrays oder JSONB? Schau dir GIN an.
   • Geodaten, Bereiche? Überleg dir GiST.

3. Was passiert mit deinen Daten?

   • Wenn du eine Tabelle mit vielen Inserts und Updates hast, vermeide zu viele Indizes, weil das die Overhead-Kosten erhöht.

4. Muss Eindeutigkeit garantiert werden?

   • Dann musst du einen Index mit dem Attribut UNIQUE verwenden.

Cases: echte Beispiele für die Index-Auswahl

Schauen wir uns ein paar echte Szenarien an.

1. Einfache Suche nach Gleichheit

Du arbeitest mit einer Studentendatenbank und willst einen Studenten schnell per E-Mail finden:

SELECT * FROM students WHERE email = 'student@example.com';

Was ist hier wichtig? Wir suchen nach Gleichheit. Die beste Wahl ist ein B-TREE-Index, weil der super für exakte Treffer ist.

CREATE INDEX idx_students_email ON students (email);

Oder, wenn die E-Mail eindeutig sein muss:

CREATE UNIQUE INDEX idx_students_email_unique ON students (email);

2. Bereichssuche

Nehmen wir an, du willst alle Studenten finden, die älter als 18 sind:

SELECT * FROM students WHERE age > 18;

Für Bereichssuchen passt B-TREE auch super, weil seine Struktur genau für geordnete Suchen gemacht ist.

CREATE INDEX idx_students_age ON students (age);

3. Filtern nach Arrays

Du hast eine Tabelle courses, in der in einer Spalte ein Array mit den IDs der Studenten gespeichert ist, die für den Kurs angemeldet sind. Du willst alle Kurse finden, für die der Student mit der ID 123 angemeldet ist.

SELECT * FROM courses WHERE student_ids @> ARRAY[123];

Für solche Anfragen ist der GIN-Index ideal, weil er für Arrays optimiert ist.

CREATE INDEX idx_courses_students_ids ON courses USING gin (student_ids);

4. Daten aus JSONB extrahieren

Angenommen, du hast eine Tabelle mit JSONB-Daten, in der Infos zu Bestellungen gespeichert sind. Du willst alle Bestellungen finden, bei denen der Kunde aus der Stadt "Moskau" kommt:

SELECT * FROM orders WHERE data->>'city' = 'Moskau';

Hier passt ein GIN-Index, der effiziente Suche nach Schlüsseln und Werten in JSONB ermöglicht.

CREATE INDEX idx_orders_data ON orders USING gin (data);

5. Geodaten

Wenn du mit geografischen Infos arbeitest, zum Beispiel alle Punkte in einem bestimmten Radius finden willst, nutze einen GiST-Index. Dieser Index-Typ ist perfekt für Geometrie und Bereiche.

CREATE INDEX idx_locations_geom ON locations USING gist (geom);

Performance-Vergleich verschiedener Indizes

Nehmen wir ein echtes Beispiel mit der Suche nach Studenten per E-Mail. Die Tabelle hat 1 Million Einträge. Wir probieren die Anfrage mit verschiedenen Indizes und ohne Index:

Fazit: In diesem Fall macht der B-TREE-Index die Anfrage über 500-mal schneller.

Fehler bei der Index-Auswahl

Der häufigste Fehler ist, Indizes "auf Verdacht" zu erstellen. Zum Beispiel indexierst du jede Spalte in deiner Tabelle und stellst dann fest, dass Inserts viel langsamer geworden sind. Merke dir: Ein Index ist kein magisches Tool, das immer und überall hilft. Es ist ein mächtiges Werkzeug in den richtigen Händen, aber falsche Nutzung kann schaden.

Ein anderer typischer Fehler ist die Wahl des falschen Index-Typs. Zum Beispiel verwendest du einen HASH-Index für Bereichssuchen und plötzlich werden deine Anfragen super langsam. Das liegt daran, dass HASH nur für exakte Suchen gemacht ist.

Empfehlungen zur Index-Auswahl

• Wenn du oft Suche nach Gleichheit oder Sortierung machst, nimm B-TREE.
• Für exakte Treffer mit minimalem Speicherverbrauch kannst du HASH nehmen.
• Wenn du mit Arrays oder JSONB arbeitest, ist GIN dein Freund.
• Für Bereiche oder Geodaten nimm GiST.

Und der wichtigste Tipp: Analysiere immer deine Anfragen! Nutze EXPLAIN und EXPLAIN ANALYZE, um zu checken, wie PostgreSQL die Indizes nutzt und was du noch verbessern kannst.

EXPLAIN ANALYZE
SELECT * FROM students WHERE email = 'student@example.com';

Das war's für heute! Jetzt bist du bereit, Indizes auszuwählen wie ein Jedi sein Lichtschwert. Sei vorsichtig, erstelle keine Indizes, wo sie nicht gebraucht werden, und prüfe immer, wie sie die Performance beeinflussen.