Der B-tree-Index ist Postgres' Default und der richtige Index für 90 % aller Use-Cases — Gleichheits-Vergleiche, Bereichsabfragen, Sortierung. Er funktioniert auf praktisch jedem Datentyp mit definierter Ordnung. Hier wann er greift, wann nicht und worauf bei zusammengesetzten Indexen zu achten ist.
Erstellen
-- Default-Index = B-tree
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
-- explizit
CREATE INDEX orders_total_idx ON orders USING btree (total);
-- mehrere Spalten (zusammengesetzter Index)
CREATE INDEX orders_customer_status_idx
ON orders (customer_id, status);USING btree ist der Default — kann weggelassen werden. Bei jedem CREATE INDEX ohne Typangabe legt Postgres einen B-tree an.
Wofür B-tree greift
| Operator | Greift |
|---|---|
= | Ja — der Klassiker |
<, <=, >, >= | Ja — Range-Queries |
BETWEEN | Ja |
IN (...) | Ja, bei kleiner Liste |
IS NULL / IS NOT NULL | Ja |
ORDER BY col | Ja — kein extra Sort nötig |
LIKE 'prefix%' | Ja — wenn rechts von % |
LIKE '%suffix' | Nein — Trigram-Index nötig |
~ (Regex) | Meist nein |
Beispiel-Query, die einen B-tree-Index nutzt:
-- nutzt orders_total_idx
SELECT * FROM orders WHERE total BETWEEN 100 AND 500
ORDER BY total DESC LIMIT 10;Postgres geht zum richtigen Punkt im Index, scannt rückwärts (DESC) und stoppt bei 10 Treffern — kein Sort, kein Tabellen-Scan.
Zusammengesetzte Indexe — Reihenfolge ist wichtig
Ein Index auf (customer_id, status) kann genutzt werden für:
WHERE customer_id = 42WHERE customer_id = 42 AND status = 'paid'ORDER BY customer_id, status
NICHT genutzt werden kann er für:
WHERE status = 'paid'(führende Spalte fehlt)
Faustregel: ein zusammengesetzter Index (a, b, c) hilft bei Bedingungen, die mit a beginnen — a, (a, b), (a, b, c). Nicht für b oder c allein.
-- Wenn du häufig filtierst nach customer_id alleine
-- UND nach customer_id + status:
CREATE INDEX ON orders (customer_id, status);
-- Wenn du häufig nach status alleine filtierst:
-- separater Index oder andere Reihenfolge
CREATE INDEX ON orders (status, customer_id);Index für ORDER BY
Ein B-tree ist sortiert — Postgres kann sortierte Reihenfolge ohne separaten Sort-Schritt liefern:
CREATE INDEX orders_created_idx ON orders (created_at);
EXPLAIN ANALYZE
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100;Plan-Auszug:
Limit
-> Index Scan Backward using orders_created_idx on ordersIndex Scan Backward — Postgres liest den Index rückwärts. Kein Sort, schnell.
Bei mehreren Sortier-Spalten den Index passend definieren:
-- für ORDER BY customer_id, created_at DESC
CREATE INDEX ON orders (customer_id, created_at DESC);Wann lohnt ein Index — wann nicht?
Indexe sind nicht gratis:
- Speicher: jeder Index ist eine eigene Datenstruktur, oft 10-30 % der Tabellen-Größe
- Schreib-Performance: jedes INSERT/UPDATE/DELETE muss den Index pflegen
- Vacuum: Indexe werden mit-vakuumiert, alte Tupel müssen aufgeräumt werden
Faustregel:
- Tabelle < 1000 Zeilen: kein Index nötig (Seq Scan ist eh schneller)
- Spalten-Selektivität niedrig (
status = 'active'trifft 95 %): Index hilft kaum - Spalten in WHERE/JOIN/ORDER BY mit hoher Selektivität: Index lohnt
- Häufiges INSERT, selten gelesen: zurückhaltend mit Indexen
Index-Größe überprüfen
SELECT
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS size,
idx_scan AS scans,
idx_tup_read AS tuples_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;Output zeigt: welcher Index wie groß ist und wie oft er genutzt wird. Indexe mit idx_scan = 0 über lange Zeit sind Kandidaten zum Löschen.
Interessantes
B-tree ist der Default — andere Typen nur bei Spezialfall.
Wer „CREATE INDEX" ohne USING ... schreibt, kriegt B-tree. Das ist 90 % der Zeit richtig. GIN, GiST, BRIN sind für spezifische Use-Cases (JSONB, Volltext, Range, Zeitreihen). Hash hat heute kaum Vorteile gegenüber B-tree.
Reihenfolge in Composite-Indexen entscheidet.
(a, b) hilft bei a = ? und (a, b) = (?, ?), nicht bei b = ?. Wer nach beiden Spalten unabhängig filtert: zwei Indexe, oder die Reihenfolge nach Häufigkeit der Single-Column-Queries wählen. Postgres kann Indexe auch kombinieren (Bitmap Index Scan), aber zusammengesetzte sind effizienter.
ORDER BY kann ohne Sort durch B-tree.
Postgres erkennt einen Index, dessen Sortierung der ORDER-BY-Klausel entspricht, und überspringt den Sort. Bei LIMIT ist das deutlicher Gewinn — die Top-N werden direkt aus dem Index gelesen, statt erst alles sortieren zu müssen.
Funktion auf Spalte verhindert Index-Nutzung.
WHERE lower(email) = 'hans@example.com' nutzt KEINEN Index auf email. Lösung: Expression-Index auf lower(email) (siehe Expression-Index).
Implicit Cast bricht den Index.
WHERE id = '42' (string) statt WHERE id = 42 (int) kann zu Sequential Scan führen, weil Postgres die Spalte zur Cast-Zeit konvertiert. Im Zweifel typsauber arbeiten oder Cast auf den Konstanten machen: WHERE id = '42'::int.
idx_scan = 0 über lange Zeit = Wegwerf-Kandidat.
Indexe, die nie genutzt werden, kosten Speicher und Schreib-Performance, ohne zu helfen. pg_stat_user_indexes zeigt's. Vorsicht bei Constraint-stützenden Indexen (Unique, PK) — die zählen nicht zu den Wegwerf-Kandidaten.