Bei einer Tabelle mit einer Million Zeilen kann dieselbe SELECT-Abfrage fünfzig Millisekunden dauern oder deutlich unter einer. Der einzige Unterschied ist, ob die Datenbank jede Zeile liest, um die gesuchten zu finden, oder direkt zu ihnen springt. Ein Index macht diesen Sprung möglich, und Sie bezahlen dafür bei jedem Schreibvorgang.

Konkret ist ein Index eine zweite, sortierte Kopie einer oder mehrerer Spalten, die neben der Tabelle gespeichert und bei jeder Änderung der Tabelle automatisch aktualisiert wird. Die Datenbank nutzt ihn, um passende Zeilen zu erreichen, ohne den Rest anzufassen.

Warum eine Abfrage ohne Index die ganze Tabelle liest

Ohne Index zwingt WHERE customer_id = 42 Postgres dazu, jede Zeile zu lesen und die Bedingung an jeder einzelnen zu prüfen. Das ist ein sequenzieller Scan (Seq Scan). Bei tausend Zeilen merken Sie das nicht. Bei einer Million Zeilen, bei der fast nichts passt, lesen und verwerfen Sie 999.989 Zeilen, um 11 zu bekommen.

Sie können sich das selbst ansehen. Starten Sie einen Postgres-Container, konfiguriert wie in Was Ist Docker Compose und Wie Setzt Man Es Ein:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

services:
  db:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
    volumes:
      - db-data:/var/lib/postgresql/data

volumes:
  db-data:

1
2

docker compose up -d db
docker compose exec db psql -U postgres

Legen Sie dann eine Tabelle an, die groß genug ist, damit sich der Unterschied zeigt:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  total_cents integer NOT NULL
);

INSERT INTO orders (customer_id, status, created_at, total_cents)
SELECT
  (random() * 50000)::int,
  (ARRAY['pending', 'paid', 'shipped', 'refunded'])[(floor(random() * 4) + 1)::int],
  now() - (random() * interval '365 days'),
  (random() * 20000)::int
FROM generate_series(1, 1000000);

ANALYZE orders;

Eine Million Zeilen, kein Index auf customer_id. Fragen Sie die Bestellungen eines Kunden ab. Die folgenden Beispiele deaktivieren zunächst die parallelen Worker (SET max_parallel_workers_per_gather = 0), damit der Plan bei einem Knoten bleibt — auf einer Mehrkernmaschine würde Postgres denselben Scan sonst eventuell auf ein Gather und mehrere Parallel Seq Scan-Worker aufteilen, was dieselbe Idee mit mehr gleichzeitig laufenden Prozessen ist:

1
2

SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

1
2
3
4
5

 Seq Scan on orders  (cost=0.00..20834.00 rows=21 width=31) (actual time=1.066..53.238 rows=11 loops=1)
   Filter: (customer_id = 42)
   Rows Removed by Filter: 999989
 Planning Time: 0.301 ms
 Execution Time: 53.284 ms

Rows Removed by Filter: 999989 ist die Rechnung. Postgres hat die ganze Tabelle gelesen, um 11 Zeilen zurückzugeben.

Was ein B-Tree-Index wirklich speichert

Eine Tabelle ohne Index ist ein Schuhkarton voller Quittungen, in der Reihenfolge, in der sie ankamen, übereinandergeworfen. Alle Quittungen eines Kunden zu finden bedeutet, den Karton zu leeren.

Ein B-Tree-Index sind dieselben Quittungen, abgelegt in einem Aktenschrank, sortiert nach Kunde. Die unterste Ebene, die Leaf Pages, enthält jeden indizierten Wert zusammen mit einem Zeiger auf seine Zeile auf der Festplatte. Über den Blättern liegen wenige interne Seiten, jede mit dem niedrigsten Wert eines Bereichs und einem Zeiger darauf. Postgres-Seiten sind standardmäßig 8 KB groß, und eine interne Seite kann auf Hunderte Kinder zeigen, sodass drei oder vier Ebenen Dutzende Millionen Zeilen abdecken. Eine Suche liest eine Seite pro Ebene und landet auf dem richtigen Blatt. Ein paar Seitenzugriffe statt eines Durchlaufs durch die gesamte Tabelle.

Fügen Sie den Index hinzu und führen Sie dieselbe Abfrage erneut aus:

1

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

1
2

SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

1
2
3
4
5
6
7

 Bitmap Heap Scan on orders  (cost=4.59..85.69 rows=21 width=31) (actual time=0.217..0.295 rows=11 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=11
   ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..4.58 rows=21 width=0) (actual time=0.090..0.090 rows=11 loops=1)
         Index Cond: (customer_id = 42)
 Planning Time: 0.979 ms
 Execution Time: 0.449 ms

Von 53 ms auf 0,45 ms, rund 120-mal schneller. Die geschätzten Kosten fallen von 20834 auf 86, der Seq Scan ist verschwunden, und es gibt keine Rows Removed by Filter-Zeile mehr: Es gab nichts zu verwerfen.

Postgres hat sich für einen Bitmap Heap Scan statt eines einfachen Index Scan entschieden. Bei rund 20 passenden, über die Tabelle verstreuten Zeilen ist es günstiger, zuerst alle passenden Einträge aus dem Index zu holen (die Bitmap Index Scan-Zeile), sie nach physischer Seite zu sortieren und dann jede Heap-Seite genau einmal zu besuchen (das ist Heap Blocks: exact=11), statt für jede einzelne Zeile separat in den Heap zu springen. Filtern Sie auf eine einzelne Zeile (ein Gleichheitsvergleich auf einer eindeutigen Spalte, etwa id = 42), sehen Sie stattdessen einen einfachen Index Scan. In beiden Fällen macht der Index dieselbe Arbeit: passende Zeilen erreichen, ohne die zu lesen, die nicht passen.

Wie die Spaltenreihenfolge bei einem zusammengesetzten Index funktioniert

Ein Index kann mehr als eine Spalte abdecken, und die Reihenfolge, in der Sie sie angeben, entscheidet, welche Abfragen er bedienen kann. CREATE INDEX ON orders (customer_id, status) sortiert zuerst nach customer_id, dann innerhalb jeder customer_id nach status. Er kann also beantworten:

  • WHERE customer_id = 42, über die erste Spalte.
  • WHERE customer_id = 42 AND status = 'paid', über beide.

WHERE status = 'paid' allein kann er nicht effizient beantworten. status ist nur innerhalb der Gruppe jedes Kunden sortiert, es gibt also keine einzige sortierte Folge von 'paid'-Zeilen, zu der der Baum springen könnte. Das ist die Leftmost-Prefix-Regel: Ein zusammengesetzter Index hilft einer Abfrage nur, wenn diese ihre Spalten von links, in Reihenfolge und ohne Lücke einschränkt.

Zwei Regeln für die Reihenfolge. Erstens: Stellen Sie die Spalte, nach der Ihre Abfragen allein filtern, nach vorn, denn nur sie kann der Index allein bedienen. Zweitens: Stellen Sie Spalten, die Sie mit = vergleichen, vor eine Spalte, die Sie mit einem Bereich (>, <, BETWEEN) vergleichen, denn alles nach der Bereichsspalte schränkt die Suche nicht mehr weiter ein. Für diese Tabelle steht customer_id (rund 50.000 unterschiedliche Werte) vor status (vier Werte): Sie suchen ständig nach Kunden, und Sie wollen nie alle 'paid'-Bestellungen ohne Kundenbezug durchforsten.

Brauchen Sie außerdem schnelle Suchen allein nach status, legen Sie einen zweiten Index an. Ordnen Sie den ersten nicht um.

Wann ein Index-Only-Scan die Tabelle überspringt

Der Bitmap Heap Scan von oben greift trotzdem auf die Tabelle zu — genau das macht Heap Blocks: exact=11. Der Index enthält customer_id und einen Zeiger auf jede Zeile; SELECT * will auch status, total_cents und created_at, und die gibt es nur in der Tabelle. Eine INCLUDE-Klausel packt zusätzliche Spalten in den Index selbst:

1
2
3

CREATE INDEX idx_orders_customer_covering
  ON orders (customer_id)
  INCLUDE (status, total_cents);

status und total_cents reisen als Payload mit. Sie sind nicht Teil des Sortierschlüssels, ein WHERE status = ... kann sie also nicht zur Suche nutzen. Fragen Sie aber nur die Spalten ab, die der Index jetzt mitführt:

1

EXPLAIN ANALYZE SELECT customer_id, status, total_cents FROM orders WHERE customer_id = 42;

1
2
3
4
5

 Index Only Scan using idx_orders_customer_covering on orders  (cost=0.42..4.79 rows=21 width=15) (actual time=0.071..0.073 rows=11 loops=1)
   Index Cond: (customer_id = 42)
   Heap Fetches: 0
 Planning Time: 0.442 ms
 Execution Time: 0.108 ms

Index Only Scan und Heap Fetches: 0: Jede Zeile kommt direkt aus dem Index, kein Zugriff auf die Tabelle. Das ist ein Index-Only-Scan.

Heap Fetches: 0 hängt von der Visibility Map der Tabelle ab, die Seiten markiert, deren Zeilen für jede Transaktion sichtbar sind — ein Scan überspringt den Heap nur bei markierten Seiten. VACUUM setzt diese Markierungen (normalerweise Autovacuum, das nach eigenem Zeitplan läuft und nicht von Hand angestoßen wird), und jeder Schreibvorgang auf eine Seite löscht sie wieder. Bei einer Tabelle mit vielen aktuellen Schreibvorgängen sind viele Seiten nicht markiert, Heap Fetches steigt über null, und Postgres besucht für diese Zeilen trotzdem den Heap. Covering-Indizes zahlen sich am meisten bei Tabellen aus, die viel öfter gelesen als geschrieben werden.

Was ein Index kostet und warum Postgres ihn ignoriert

Jeder INSERT schreibt einen Eintrag in jeden Index der Tabelle, und ein UPDATE, das eine indizierte Spalte ändert, schreibt ebenfalls neue Einträge. Fünf Indizes bedeuten fünf zusätzliche Strukturen, die bei jedem dieser Schreibvorgänge gepflegt werden müssen. Indizes belegen außerdem Speicherplatz. Vergleichen Sie die beiden Größen direkt:

1
2

SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size,
       pg_size_pretty(pg_relation_size('idx_orders_customer_id')) AS index_size;

Und manchmal legen Sie den Index an, und der Planner nutzt ihn nicht. Das ist selten ein Postgres-Bug. Meist liegt einer dieser Fälle vor:

  • Eine Funktion umschließt die Spalte. WHERE lower(email) = '[email protected]' kann keinen einfachen Index auf email nutzen, weil der Index Rohwerte speichert, nicht lower(email). Indizieren Sie den Ausdruck (CREATE INDEX ON users (lower(email))) oder filtern Sie auf der Rohspalte.
  • Ein führendes Wildcard. WHERE name LIKE '%smith' gibt dem Baum kein Präfix, nach dem er suchen kann. LIKE 'smith%' schon.
  • Geringe Selektivität. Ein Index auf einem 50/50 verteilten Booleschen Wert spart nichts. Postgres würde trotzdem die Hälfte des Index und die meisten Seiten der Tabelle lesen, der Planner wählt also zu Recht einen sequenziellen Scan.
  • Veraltete Statistiken. Der Planner wählt zwischen Scan-Typen anhand der Statistiken, die ANALYZE in pg_statistic sammelt. Nach einem Massenimport ohne ANALYZE können seine Zeilenschätzungen weit daneben liegen. Führen Sie es nach jedem großen Import aus, wie im obigen Setup.

Sehen Sie einen Seq Scan, wo Sie einen Index Scan erwartet haben, prüfen Sie die Abfrage anhand dieser Liste, bevor Sie am Index etwas ändern.

Wann Sie einen Index anlegen sollten und wann nicht

Legen Sie einen an, wenn eine Spalte in einer WHERE, einem JOIN oder einem ORDER BY auf einer Tabelle auftaucht, die groß genug ist, dass ein Scan echte Zeit kostet, und der Filter selektiv ist: Er verengt das Ergebnis auf einen kleinen Teil der Tabelle, nicht auf die Hälfte. Verzichten Sie darauf, wenn die Tabelle klein genug ist, dass ein Scan bereits schnell ist, wenn die Tabelle viele Schreibvorgänge und wenige Lesevorgänge bekommt, oder wenn die Spalte nichts eingrenzen kann.

Legen Sie ihn nicht auf Verdacht an. Führen Sie zuerst EXPLAIN ANALYZE auf der langsamen Abfrage aus. Zeigt sie bereits einen Index Scan und ist trotzdem langsam, ist der Index nicht Ihr Problem: Schreiben Sie die Abfrage um, oder cachen Sie das Ergebnis mit Redis. Zeigt sie einen Seq Scan auf einer großen Tabelle mit selektivem Filter, legen Sie den Index genau auf die Spalten an, die die WHERE nutzt, in der von der Leftmost-Prefix-Regel geforderten Reihenfolge, und führen Sie EXPLAIN ANALYZE erneut aus, um zu bestätigen, dass sich der Plan geändert hat.

Verwandte Artikel