Ein Expression-Index indiziert nicht den Spalten-Wert, sondern den Wert einer Funktion auf der Spalte — lower(email), (data->>'event_type'), EXTRACT(year FROM created_at). Damit lassen sich Queries beschleunigen, die die Spalte transformiert filtern, ohne dass man auf Generated Columns gehen muss.
Klassiker: lower() für Case-Insensitivity
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL
);
CREATE UNIQUE INDEX users_email_lower_idx
ON users (lower(email));Damit wird:
SELECT * FROM users WHERE lower(email) = 'hans@example.com';…schnell und sortier-stabil. Außerdem garantiert der Unique-Index Case-Insensitive-Eindeutigkeit (Hans@example.com und hans@example.com kollidieren).
Wichtig: die Query muss exakt die selbe Expression nutzen wie der Index. WHERE lower(email) = ... greift, WHERE email ILIKE ... greift NICHT auf diesen Index.
JSONB-Pfad indizieren
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
data jsonb NOT NULL
);
-- Häufige Filter auf data->>'event_type'
CREATE INDEX events_type_idx ON events ((data->>'event_type'));Die doppelten Klammern um (data->>'event_type') sind Pflicht — Postgres-Syntax-Anforderung für Expressions in CREATE INDEX.
Damit wird der Filter blitzschnell:
SELECT * FROM events WHERE data->>'event_type' = 'click';Mehr Patterns im Artikel JSONB-Indexes.
Datums-Komponenten
CREATE INDEX orders_year_idx
ON orders ((EXTRACT(YEAR FROM created_at)::int));
-- nutzt den Index
SELECT count(*) FROM orders WHERE EXTRACT(YEAR FROM created_at)::int = 2026;Vorsicht: in den meisten Fällen ist WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' mit einem normalen B-tree-Index genauso schnell — und flexibler. Den Expression-Index nur, wenn die Funktion-basierte Filterung wirklich Standard-Pattern in der App ist.
Multi-Spalten-Expression
Expressions können beliebig kombiniert werden:
CREATE INDEX customers_fullname_idx
ON customers ((firstname || ' ' || lastname));
SELECT * FROM customers
WHERE firstname || ' ' || lastname = 'Hans Meier';Funktioniert, aber an Generated Column denken — die ist meist sauberer:
ALTER TABLE customers
ADD COLUMN fullname text
GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED;
CREATE INDEX customers_fullname_idx ON customers (fullname);Anforderungen an die Expression
Die Expression muss immutable sein:
| Erlaubt | Nicht erlaubt |
|---|---|
lower(), upper() | now(), random() |
| Arithmetik | Subqueries |
| Stringfunktionen | Volatile Funktionen |
| JSONB-Operatoren | Funktionen, die andere Tabellen lesen |
| Casts | current_user, current_timestamp |
Wer eine eigene Funktion verwendet:
CREATE FUNCTION normalize_phone(p text) RETURNS text
LANGUAGE sql IMMUTABLE STRICT
AS $$ SELECT regexp_replace($1, '[^0-9+]', '', 'g') $$;
CREATE INDEX users_phone_norm_idx
ON users (normalize_phone(phone));IMMUTABLE ist Pflicht, sonst akzeptiert Postgres die Funktion nicht im Index.
Expression-Index vs. Generated Column
| Expression-Index | Generated Column (STORED) | |
|---|---|---|
| Disk-Speicher | Im Index | Auch in der Tabelle |
| Schreib-Cost | Index-Pflege | Spalten-Wert + Index-Pflege |
| Direkt selektierbar | Nein | Ja |
Indexierbar mit INCLUDE | Nein | Ja |
| Ältere PG-Versionen | Ja, schon lange | PG 12+ |
Faustregel:
- Brauchst du den Wert nur zum Suchen, nicht zum Lesen → Expression-Index
- Brauchst du den Wert auch im SELECT-Result → Generated Column
Besonderheiten
Query-Bedingung muss EXAKT die Index-Expression treffen.
WHERE lower(email) = 'x' greift auf einen Index (lower(email)). WHERE email ILIKE 'x' NICHT — auch wenn semantisch gleich. Postgres ist hier syntaktisch streng. Lieber konsistent in der App formulieren.
IMMUTABLE ist Pflicht für selbst-geschriebene Funktionen.
Eigene Funktionen müssen IMMUTABLE STRICT sein, sonst wird der Index nicht akzeptiert. Wer's vergisst: kryptische Fehlermeldung. Nach einer Logik-Änderung der Funktion: Index neu bauen, sonst ist er inkonsistent.
Doppelte Klammern in CREATE INDEX — Syntax-Anforderung.
CREATE INDEX ... ON t ((data->>'key')) — die zwei Klammern sind nötig. Innen die Expression, außen die Index-Spalten-Liste. Beim Generated-Column-Pendant nicht nötig, da dort die Expression in der Spalten-Definition steht.
Statistiken auf Expression-Indexen oft schlecht.
Postgres' Optimizer hat für indizierte Expressions weniger Statistiken als für reine Spalten — Pläne können schlechter werden. Mit CREATE STATISTICS lassen sich Multi-Column-Stats anlegen. Bei Performance-Problemen prüfen.
Expression auf JSONB-Pfaden ist meistens sauberer als GIN.
Wer 80 % der Queries auf data->>'event_type' macht: gezielter B-tree-Expression-Index ist kompakter und schneller als ein GIN auf der ganzen JSONB-Spalte. GIN nur, wenn viele unterschiedliche Pfade abgefragt werden.
Generated Columns ersetzen oft Expression-Indexe sauberer.
Bei wiederkehrenden Berechnungen Generated Column + normalem Index eleganter — die berechnete Spalte ist auch in SELECT lesbar und kann mit INCLUDE covered werden. Trade-off: Storage-Kosten.