Index in PostgreSQL: B-tree, GIN und wann man sie einsetzt

1. Übersicht

PostgreSQL unterstützt viele Index-Typen, aber B-tree und GIN sind die beiden, denen du in deiner täglichen Arbeit am häufigsten begegnest. Die Wahl des falschen Index-Typs verlangsamt Abfragen oder verbraucht Speicherplatz, ohne zu helfen.

Dieser Artikel erklärt, wie B-tree und GIN auf ausreichendem Niveau funktionieren, wie du Indizes korrekt erstellst, wie du EXPLAIN liest, um zu wissen, ob ein Index verwendet wird, und bietet eine Checkliste zur Wahl des richtigen Index-Typs. Nach dem Lesen wirst du selbstbewusst Indizes zu den Tabellen Order, Product hinzufügen, ohne zu raten.

2. B-tree Index: Standard und am häufigsten verwendet

2.1. Wie B-tree funktioniert

B-tree (balanced tree) speichert Werte in sortierter Reihenfolge und ermöglicht Suchen, Vergleiche (<, >, BETWEEN) und Sortierung (ORDER BY) sehr schnell — mit Komplexität O(log n). Dies ist der Standard-Index-Typ, wenn du CREATE INDEX ohne Angabe einer Methode ausführst.

CREATE TABLE customer_order (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total_amount NUMERIC(12,2)
);

CREATE INDEX idx_order_customer_id ON customer_order (customer_id);

Die CREATE INDEX-Zeile erstellt einen B-tree auf der Spalte customer_id. Bei einer Abfrage WHERE customer_id = 123 durchquert PostgreSQL den Baum, anstatt die gesamte Tabelle zu scannen (sequential scan).

2.2. Überprüfen, ob der Index verwendet wird

EXPLAIN ANALYZE
SELECT * FROM customer_order WHERE customer_id = 123;

Index Scan using idx_order_customer_id on customer_order
  Index Cond: (customer_id = 123)
  Planning Time: 0.123 ms
  Execution Time: 0.045 ms

Index Scan bedeutet, dass der Planner entschieden hat, den Index zu verwenden. Wenn du Seq Scan siehst, obwohl ein Index vorhanden ist, liegt das normalerweise daran, dass die Tabelle zu klein ist (der Planner denkt, dass Scannen günstiger ist) oder die Statistiken veraltet sind — führe ANALYZE customer_order; aus, um sie zu aktualisieren.

2.3. Zusammengesetzter Index und Spaltenreihenfolge

B-tree unterstützt mehrere Spalten, aber die Spaltenreihenfolge bestimmt die Effizienz.

CREATE INDEX idx_order_customer_status
    ON customer_order (customer_id, status);

-- Nutzt den Index vollständig
SELECT * FROM customer_order
WHERE customer_id = 123 AND status = 'PAID';

-- Nutzt nur den customer_id-Teil des Index
SELECT * FROM customer_order WHERE customer_id = 123;

-- KANN diesen Index NICHT verwenden (customer_id fehlt)
SELECT * FROM customer_order WHERE status = 'PAID';

Der Grundsatz: Ein zusammengesetzter Index ist wie ein Adressbuch, das nach (customer_id, status) sortiert ist — du kannst zuerst nach customer_id suchen, aber nicht direkt nur nach status.

Hinweis: Platziere die Spalte mit hoher Selektivität (viele unterschiedliche Werte) zuerst, wenn die Spalten unabhängig in Abfragen verwendet werden. Wenn du immer beide Spalten zusammen abfragst, platziere zuerst die Spalte mit stärkster Filterung, damit der Baum den Bereich schnell eingrenzt.

3. GIN Index: für Daten mit „vielen Werten pro Zeile”

3.1. Wenn B-tree nicht hilft

B-tree ist nicht effizient bei Datentypen, bei denen eine Zeile mehrere „Elemente” enthält, die unabhängig durchsucht werden: array, JSON (jsonb), Volltextsuche (tsvector). GIN (Generalized Inverted Index) wurde für diesen Fall entwickelt — es indiziert jedes Element separat, nicht den gesamten Wert.

3.2. GIN für JSONB

CREATE TABLE product (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    attributes JSONB NOT NULL
);

CREATE INDEX idx_product_attributes ON product USING GIN (attributes);

INSERT INTO product (name, attributes) VALUES
('Laptop Dell', '{"brand": "Dell", "ram_gb": 16, "tags": ["laptop", "office"]}'),
('Laptop HP',   '{"brand": "HP", "ram_gb": 8, "tags": ["laptop", "gaming"]}');

SELECT * FROM product WHERE attributes @> '{"brand": "Dell"}';
SELECT * FROM product WHERE attributes -> 'tags' @> '["gaming"]';

USING GIN (attributes) erstellt einen Index auf der gesamten JSONB-Spalte. Der Operator @> („enthält”) ist das, was GIN am besten optimiert — er prüft, ob das JSON auf der linken Seite die Struktur auf der rechten Seite enthält, ohne das gesamte Dokument jedes Mal neu zu parsen.

Bitmap Heap Scan on product
  Recheck Cond: (attributes @> '{"brand": "Dell"}'::jsonb)
  ->  Bitmap Index Scan on idx_product_attributes
        Index Cond: (attributes @> '{"brand": "Dell"}'::jsonb)

3.3. GIN für Volltextsuche

ALTER TABLE product ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('simple', name)) STORED;

CREATE INDEX idx_product_search ON product USING GIN (search_vector);

SELECT name FROM product
WHERE search_vector @@ to_tsquery('simple', 'laptop & dell');

Die Spalte search_vector ist eine generierte Spalte, die automatisch tsvector aus name erzeugt. Ein GIN-Index auf dieser Spalte hilft dem Operator @@ (match), bei großen Textdaten schnell zu laufen, anstatt dass LIKE '%laptop%' die gesamte Tabelle scannen muss.

3.4. GIN für Array

CREATE TABLE article (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200),
    tag_list TEXT[]
);

CREATE INDEX idx_article_tags ON article USING GIN (tag_list);

SELECT * FROM article WHERE tag_list @> ARRAY['spring-boot'];

Ähnlich wie bei JSONB indiziert GIN jedes Element im Array tag_list separat, deshalb ist die Abfrage „Artikel mit Tag X” bei großen Tabellen um ein Vielfaches schneller als mit B-tree.

4. Vergleich und Kosten

Kriterium	B-tree	GIN
Geeignete Daten	scalar: Zahlen, Strings, Daten	array, jsonb, tsvector
Unterstützte Operatoren	=, <, >, BETWEEN, ORDER BY	@>, ?, @@, &&
Schreibgeschwindigkeit (INSERT/UPDATE)	schnell	langsamer, besonders bei großem JSONB-UPDATE
Index-Größe	kleiner	größer (speichert jedes Element)
Index-Build-Geschwindigkeit	schnell	langsamer bei großen Tabellen

GIN ist beim Schreiben teurer, weil jeder INSERT/UPDATE mehrere Einträge aktualisieren muss (einen für jedes Element im JSON/Array), nicht nur einen Eintrag wie B-tree.

-- Überprüfe die tatsächliche Größe der Indizes
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'product';

5. Index-Test bestätigen (integriert mit Spring Boot)

Wenn du mit Spring Data JPA arbeitest, möchtest du oft bestätigen, dass der Index vor der Bereitstellung korrekt funktioniert. Verwende Testcontainers, um auf echtem PostgreSQL zu testen.

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <version>1.20.1</version>
    <scope>test</scope>
</dependency>

@Testcontainers
@SpringBootTest
class ProductIndexTest {

    @Container
    static PostgreSQLContainer<?> postgres =
            new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void shouldUseGinIndexForJsonbQuery() {
        jdbcTemplate.execute("""
            INSERT INTO product (name, attributes)
            VALUES ('Laptop Dell', '{"brand": "Dell"}')
        """);

        String plan = jdbcTemplate.queryForList(
                "EXPLAIN SELECT * FROM product WHERE attributes @> '{\"brand\": \"Dell\"}'"
        ).stream()
         .map(row -> row.get("QUERY PLAN").toString())
         .reduce("", (a, b) -> a + b);

        assertThat(plan).containsIgnoringCase("idx_product_attributes");
    }
}

Dieser Test führt EXPLAIN aus (kein ANALYZE nötig), concateniert die Ergebniszeilen zu einem String und prüft dann, ob der Plan den erwarteten Index-Namen erwähnt. Dies ist eine Möglichkeit zu bestätigen, dass der Index wirklich am Query-Plan beteiligt ist, nicht nur im Schema existiert.

Hinweis: EXPLAIN führt die tatsächliche Abfrage nicht aus, fragt nur den Planner. Um die tatsächliche Ausführungszeit zu kennen, nutze EXPLAIN ANALYZE — aber denke daran, dass es die Anweisung ausführt. Vermeide die Verwendung mit DELETE/UPDATE, es sei denn, du packst sie in einen Transaction-Rollback.

6. Checkliste zur Wahl des Index-Typs

• Abfrage filtert Spalte nach scalar-Werten (Zahlen, Strings, Daten) mit =, <, >, oder benötigt ORDER BY → B-tree.
• Abfrage filtert mehrere Spalten gleichzeitig mit „AND” → zusammengesetzter B-tree, selektive Spalte oder unabhängig verwendete Spalte zuerst.
• Abfrage auf JSONB-Spalte mit @>, ?, ?&, ?| → GIN.
• Volltextsuche (@@ mit tsvector) → GIN.
• Abfrage auf Array mit @>, &&, <@ → GIN.
• Tabelle mit vielen Schreibvorgängen, wenig Lesevorgängen, JSONB-Daten ändern sich häufig → erwäge, nicht die gesamte Spalte mit GIN zu indizieren, sondern nur auf notwendigen Teil-Ausdrücken (expression index).

-- Expression index: indiziert nur einen spezifischen Key im JSONB, schlanker als GIN auf gesamter Spalte
CREATE INDEX idx_product_brand ON product ((attributes ->> 'brand'));

Dieser expression index ist tatsächlich ein B-tree auf dem Ergebnis von attributes ->> 'brand' (Textwert), geeignet, wenn du immer genau einen festen Key abfragst und nicht die Flexibilität von GIN brauchst.

7. Häufige Fehler

• Index auf Spalte mit niedriger Kardinalität erstellen (z. B. status mit nur 3 Werten bei einer Million Zeilen): Der Planner überspringt normalerweise den Index und wählt Seq Scan, weil es günstiger ist. Nicht jeder Index wird nur weil er existiert verwendet.
• ANALYZE nach Massen-Insert vergessen: Der Planner verlässt sich auf alte Statistiken, schätzt die Zeilenanzahl falsch und wählt einen schlechten Plan.
• GIN auf selten geänderter Spalte verwenden, aber sehr häufige kleine Schreibvorgänge: Die Kosten für Index-Updates summieren sich zu etwas Größerem als der Nutzen der Abfrage — messe mit EXPLAIN ANALYZE zuerst, bevor du entscheidest.
• Zusammengesetzten B-tree mit falscher Spaltenreihenfolge erstellen: Wenn du die selten verwendete Spalte zuerst platzierst, wird der Index für echte Abfragen fast nutzlos.
• Zu viele Indizes auf einer Tabelle erstellen: Jeder INSERT/UPDATE muss alle relevanten Indizes aktualisieren, was den Schreibpfad verlangsamt, während du nicht alle diese Indizes verwendest.

8. Fazit

B-tree ist die Standardwahl, geeignet für die meisten Filterbedingungen und Sortierung bei scalar-Daten. GIN ist für „mehrwertige” Daten reserviert wie JSONB, array, Volltextsuche — wo B-tree nicht effizient darstellen kann.

Bevor du einen Index hinzufügst, überprüfe immer mit EXPLAIN ANALYZE, ob der Planner ihn wirklich verwendet, und messe die Schreibkosten, wenn die Tabelle eine hohe INSERT/UPDATE-Häufigkeit hat. Ein Index ist nicht „einfach hinzufügen zur Sicherheit” — jeder Index ist ein Kompromiss zwischen Lesgeschwindigkeit und Schreibgeschwindigkeit.