Menu

PostgreSQL CREATE INDEX: Indextypen und Beispiele

CREATE INDEX baut standardmäßig einen B-Tree-Index, damit PostgreSQL Zeilen findet, ohne die ganze Tabelle zu lesen. Andere Typen (GIN, GiST, BRIN, Hash) decken JSONB, Arrays, Volltextsuche, Bereiche und sehr große Tabellen ab.

Diese Seite enthält ausführbare Editoren. Bearbeiten, ausführen und Ausgabe sofort sehen.

CREATE INDEX name ON table (column); baut einen Index, mit dem PostgreSQL passende Zeilen findet, ohne die ganze Tabelle zu lesen. Der Standardtyp ist B-Tree, er bedient =, <, >, BETWEEN, IN, IS NULL und ORDER BY. Einen anderen Typ wählst du mit USING: gin, gist, spgist, brin oder hash.

Die erste Zeile des Plans lautet Index Scan using users_email_idx on users: PostgreSQL sucht den Wert im Index und liest nur die passende Zeile. Entferne die Zeile mit CREATE INDEX und führ die Abfrage erneut aus, dann siehst du Seq Scan on users, der alle 10.000 Zeilen liest.

Bei einer Tabelle mit ein paar Dutzend Zeilen wählt der Planer oft einen sequenziellen Scan, auch wenn ein Index existiert: Ein paar Seiten zu lesen ist billiger. Wie du diese Pläne liest, erklärt EXPLAIN ANALYZE.

Einen Indextyp wählen

TypNimm ihn für
B-Tree (Standard)Gleichheit, Bereiche, Sortieren, LIKE 'prefix%' (mit der Collation C oder text_pattern_ops)
GINWerte aus vielen Teilen: jsonb (@>, ?), Arrays (@>, &&), Volltext mit tsvector
GiSTGeometrie, Bereiche (&&, Überschneidungen), Volltext, nächste Nachbarn mit ORDER BY ... <->
SP-GiSTDaten, die sich in überschneidungsfreie Regionen teilen lassen: Punkte, IP-Adressen, Textpräfixe
BRINsehr große Tabellen, deren Werte der physischen Reihenfolge folgen, etwa ein created_at, an das nur angehängt wird
Hashnur Gleichheit; ein B-Tree kann dasselbe und mehr, deshalb wird er selten gewählt

Mit einem GIN-Index auf einer jsonb Spalte nutzen Containment-Abfragen den Index:

Der Plan zeigt einen Bitmap Index Scan auf dem GIN-Index, der einen Bitmap Heap Scan speist, der die passenden Zeilen liest. Die Seite zu JSONB behandelt die Operatoren.

Mehrere Spalten indizieren

Ein mehrspaltiger B-Tree-Index sortiert nach der ersten Spalte und innerhalb jedes Werts der ersten nach der zweiten. Er bedient Abfragen, die auf die führende Spalte filtern oder auf die führende und die folgenden. Ein Filter nur auf die zweite Spalte profitiert kaum davon. Setz also die Spalte nach vorn, auf die du immer mit = filterst.

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);

-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;

INCLUDE (PostgreSQL 11+) speichert zusätzliche Spalten im Index, ohne nach ihnen zu sortieren: CREATE INDEX ON orders (customer_id) INCLUDE (total);. Eine Abfrage, die nur diese Spalten liest, lässt sich dann allein aus dem Index beantworten, mit einem Index Only Scan.

Eindeutige und partielle Indizes: eine Regel für manche Zeilen

CREATE UNIQUE INDEX lehnt doppelte Werte ab. Ein partieller Index ergänzt ein WHERE und indiziert nur die Zeilen, die dazu passen. Er ist kleiner und kann eine Eindeutigkeitsregel auf eine Teilmenge der Zeilen beschränken:

Beide Zeilen werden angenommen, weil nur eine davon nicht gelöscht ist. Eine dritte aktive Zeile mit derselben E-Mail scheitert mit duplicate key value violates unique constraint "accounts_email_live". Eine Abfrage nutzt einen partiellen Index nur, wenn ihr WHERE die Bedingung des Index impliziert, hier WHERE NOT deleted.

Einen Ausdruck indizieren

Ein Index kann das Ergebnis eines Ausdrucks speichern. Eine Abfrage muss denselben Ausdruck wiederholen, um ihn zu nutzen, und ein Index auf email hilft bei WHERE lower(email) = ... nicht:

Der Plan nutzt users_email_lower_idx, weil das WHERE genau lower(email) wiederholt. Der Ausdruck muss immutable sein, now() oder eine Funktion, die von einer Einstellung abhängt, wird also abgelehnt.

Indizes auf einer laufenden Datenbank auflisten, bauen und löschen

pg_indexes listet jeden Index mit der Anweisung auf, die ihn neu anlegen würde:

Der Primärschlüssel und der UNIQUE Constraint haben eigene Indizes angelegt, users_pkey und users_email_key. In psql zeigt \d users dieselbe Liste unter den Spalten der Tabelle.

Bei einer Tabelle, in die geschrieben wird, baust und löschst du Indizes mit CONCURRENTLY, damit Schreibzugriffe nicht blockiert werden. Diese Formen laufen nicht in einem Transaktionsblock. Ein fehlgeschlagener paralleler Aufbau hinterlässt einen INVALID Index, den du löschen und neu anlegen musst:

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx;  -- rebuild a bloated index

Häufig gestellte Fragen

Legt PostgreSQL Indizes automatisch an?

Nur für PRIMARY KEY, UNIQUE und Exclusion Constraints. Eine Foreign-Key-Spalte bekommt keinen Index. Leg also selbst einen an, wenn du über sie joinst oder Zeilen aus der referenzierten Tabelle löschst.

Warum nutzt PostgreSQL meinen Index nicht?

Häufige Gründe: Die Tabelle ist klein, ganz lesen ist also billiger; die Abfrage liefert einen großen Teil der Zeilen; das WHERE packt die Spalte in eine Funktion oder einen Cast, die der Index nicht hat; oder die Statistiken sind veraltet (führ ANALYZE aus). EXPLAIN zeigt, welcher Plan gewählt wurde.

Wie viele Indizes sind zu viele?

Eine feste Zahl gibt es nicht. Jeder Index beschleunigt manche Lesezugriffe, verlangsamt jedes INSERT und die meisten UPDATEs auf der Tabelle und belegt Plattenplatz. pg_stat_user_indexes.idx_scan zeigt, wie oft jeder Index genutzt wird, so kannst du die löschen, die bei null bleiben.

Sperrt CREATE INDEX die Tabelle?

Ein einfaches CREATE INDEX blockiert INSERT, UPDATE und DELETE auf der Tabelle, bis es fertig ist; Lesen geht weiter. CREATE INDEX CONCURRENTLY erlaubt Schreibzugriffe während des Aufbaus, dauert länger und kann nicht in einem Transaktionsblock laufen.

Illustration der Programmiersprachen bei Coddy

Lerne mit Coddy zu programmieren

Los geht's