EMZETT.
Login

Emzett Wiki

PostgreSQL-Indizes: B-Tree, GIN und wann sie helfen

Properties1
tagsglossar, postgresql, performance

2 Min. Lesezeit

PostgreSQL-Indizes: B-Tree, GIN und wann sie helfen

Kurz: Ein Index ist ein zusätzlicher Suchbaum, der Abfragen beschleunigt, aber Schreibvorgänge und Speicher kostet; man legt ihn gezielt für häufige Filter, Sortierungen und Verknüpfungen an.

Genauer: Ohne Index liest PostgreSQL bei einer Abfrage die ganze Tabelle (Seq Scan). Ein Index findet Zeilen schnell wie ein Stichwortverzeichnis im Buch.

Im Detail

Index-Arten

  • B-Tree (Standard): Gleichheit, Bereiche (<, >, BETWEEN), Sortierung (ORDER BY), Präfix-LIKE 'abc%'.
  • Hash: nur Gleichheit.
  • GIN: Mehrfachwerte (Arrays, JSONB, Volltext tsvector).
  • GiST/SP-GiST: Geodaten, Bereiche, Ähnlichkeit.
  • BRIN: sehr große, nach Zeit sortierte Tabellen, winzig.
  • pgvector (HNSW/IVFFlat): Vektorsuche.
CREATE INDEX idx_nutzer_email ON nutzer (email);
CREATE UNIQUE INDEX uq_nutzer_email ON nutzer (lower(email));
CREATE INDEX idx_best_kunde_datum ON bestellung (kunde_id, erstellt DESC);
CREATE INDEX idx_aktiv ON nutzer (id) WHERE aktiv;       -- partiell
CREATE INDEX idx_tags ON artikel USING gin (tags);

Wann lohnt ein Index?

  • Spalten in WHERE, JOIN (Fremdschlüssel!), ORDER BY.
  • Selektive Bedingungen (wenige Treffer).
  • Zusammengesetzte Indizes: Reihenfolge wichtig; (kunde_id, erstellt) hilft bei WHERE kunde_id = … und … AND erstellt …, nicht bei Filter nur auf erstellt (Linkspräfix-Regel).
  • Kein Index bei winzigen Tabellen, bei Spalten mit wenigen Werten (Ja/Nein) allein, oder wenn die Tabelle ständig massiv beschrieben wird.
  • Kosten: Jeder Index verlangsamt INSERT/UPDATE/DELETE, belegt Speicher und Wartung.

Prüfen mit EXPLAIN

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM bestellung WHERE kunde_id = 42 ORDER BY erstellt DESC LIMIT 10;

Seq Scan bei großer Tabelle = Verdacht; Index Scan/Index Only Scan/Bitmap Heap Scan = Index genutzt. Der Planer wählt nach Statistiken (ANALYZE); manchmal ist ein Seq Scan bei vielen Treffern schneller. pg_stat_user_indexes zeigt nie genutzte Indizes (löschen). CREATE INDEX CONCURRENTLY baut ohne lange Sperre.

Typische Fehler

Funktionen auf Spalten (WHERE lower(email) = …) verhindern normalen Index (→ Funktionsindex). Typ-Umwandlungen, führendes % bei LIKE, OR über verschiedene Spalten. Fehlender Index auf Fremdschlüssel macht Joins und Löschungen langsam. Zu viele Indizes. Bei serverlosen Datenbanken zählt außerdem Rechenzeit: weniger Seq Scans = weniger CPU-Stunden.

Siehe auch: PostgreSQL, Abfrageplaner und EXPLAIN, Abfragen optimieren mit EXPLAIN, N+1-Problem, Vektordatenbanken

Backlinks