JSONB-Indexe

Das Problem ohne Index

Stellt dir 1 Million Produkte vor, jeder mit einem attrs-jsonb-Feld:

myapp=> EXPLAIN ANALYZE
        SELECT * FROM products
        WHERE attrs @> '{"color": "red"}';
                               QUERY PLAN
----------------------------------------------------------------
 Seq Scan on products  (cost=0.00..…)
   Filter: (attrs @> '{"color": "red"}'::jsonb)
   Rows Removed by Filter: 950000
 Planning Time: 0.123 ms
 Execution Time: 1842.567 ms

Knapp 2 Sekunden für eine simple Containment-Suche. Postgres muss jede Zeile parsen und prüfen.

GIN-Index — die Standardlösung

Generalized Inverted Index — speziell für „enthält”-artige Suchen designt:

CREATE INDEX products_attrs_idx ON products USING gin (attrs);

Damit wird die obige Query Millisekunden-schnell:

myapp=> EXPLAIN ANALYZE
        SELECT * FROM products
        WHERE attrs @> '{"color": "red"}';
                                  QUERY PLAN
----------------------------------------------------------------
 Bitmap Heap Scan on products  (cost=… rows=49423)
   Recheck Cond: (attrs @> '{"color": "red"}'::jsonb)
   ->  Bitmap Index Scan on products_attrs_idx  (cost=…)
         Index Cond: (attrs @> '{"color": "red"}'::jsonb)
 Planning Time: 0.234 ms
 Execution Time: 12.481 ms

Über 100× schneller. GIN-Indexe können viele Operatoren bedienen:

jsonb_ops vs. jsonb_path_ops

Beim GIN gibt’s zwei Operator-Klassen:

CREATE INDEX products_attrs_path_idx ON products
    USING gin (attrs jsonb_path_ops);

Faustregel:

• Du brauchst nur Containment-Queries → jsonb_path_ops (kompakter, schneller).
• Du brauchst auch Schlüssel-Existenz oder JSON-Path-Existence → Default-jsonb_ops.

Bei sehr großen Tabellen lohnt sich oft jsonb_path_ops plus separate Expression-Indexe für die Schlüssel, die mit ? abgefragt werden.

Expression-Index — für einzelne Schlüssel

Wenn du immer dieselbe Spalte abfragst (status, category), ist ein gezielter Expression-Index oft besser als der breite GIN:

CREATE INDEX products_status_idx ON products ((attrs->>'status'));

-- Diese Query nutzt ihn:
SELECT * FROM products WHERE attrs->>'status' = 'active';

Vorteile:

• Klein: nur die Werte einer Spalte werden indiziert, nicht das ganze Dokument.
• Schnell für =-Filter und Sortierungen.
• Funktioniert mit allen B-Tree-Operatoren (=, <, >, IN, …).

Nachteile:

• Funktioniert nur für einen Pfad. Wer status UND category indiziert, braucht zwei Indexe.
• @>-Containment-Queries gehen nicht — dafür weiter GIN.

Häufige Kombination in Production: ein GIN-Index für allgemeine Filter plus 2-3 Expression-Indexe auf die hot-spot-Schlüssel.

Mehrere Expression-Indexe

-- Status (häufig gefilterte Spalte)
CREATE INDEX products_status_idx ON products ((attrs->>'status'));

-- Category für Kategorie-Filter
CREATE INDEX products_category_idx ON products ((attrs->>'category'));

-- Numerischer Vergleich auf 'price'
CREATE INDEX products_price_idx ON products (((attrs->>'price')::numeric));

-- Mehrere Expression-Spalten in einem Index
CREATE INDEX products_status_category_idx ON products
    ((attrs->>'status'), (attrs->>'category'));

Bei numerischen Vergleichen wichtig: explizit casten ((attrs->>'price')::numeric), sonst landet der Wert als Text im Index — Vergleiche funktionieren dann lexikographisch (also '9' > '100', was Bugs auslöst).

Partial Indexe

Wenn nur ein kleiner Teil der Daten relevant ist, lohnt sich ein Partial Index:

CREATE INDEX products_active_attrs_idx
ON products USING gin (attrs)
WHERE attrs->>'status' = 'active';

Der Index ist viel kleiner und schneller, weil er nur ~10 % der Daten umfasst. Postgres nutzt ihn automatisch für Queries, die attrs->>'status' = 'active' als Bedingung enthalten — auch in Kombination mit Containment.

Wann welcher Index?

Index-Pflege

GIN-Indexe sind etwas teurer beim Schreiben als B-Tree-Indexe. Für sehr Insert-lastige Workloads lohnt sich gin_pending_list_limit-Tuning oder das Verzögern via fastupdate = on (Standard ab PG 8.4).

SELECT
    indexrelid::regclass AS index,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'products'
ORDER BY pg_relation_size(indexrelid) DESC;

Wenn ein Index idx_scan = 0 hat (nie genutzt), ist er meistens überflüssig — droppen.

Weiterführende Ressourcen

Externe Quellen

• GIN Indexes – PostgreSQL Documentation
• Built-in GIN Operator Classes
• Expression Indexes
• Partial Indexes
• pg_stat_user_indexes

Verwandte Artikel

• JSONB-Operatoren
• JSONB Pfad-Queries
• json vs. jsonb
• JSONB-Felder updaten