PostgreSQL: Abfrageoptimierung und Verständnis von Index-Operationen

PostgreSQL Index-Optimierung: Wenn Indizes ignoriert werden – Ursachen und Lösungen

Das Anlegen eines Indexes garantiert nicht immer, dass PostgreSQL ihn auch tatsächlich nutzt. Entwickler stoßen häufig auf Szenarien, in denen Abfragen trotz vorhandenem Index langsam ablaufen und stattdessen ein vollständiger Tabellen-Scan (Seq Scan) durchgeführt wird. Das Verständnis der Mechanismen des PostgreSQL-Abfrageplaners, die Interpretation der EXPLAIN ANALYZE-Metriken und das Wissen um die Faktoren, die die Wahl der Ausführungsstrategie beeinflussen, sind entscheidend für die Optimierung der Datenbankleistung. In diesem Artikel werden wir diese Aspekte detailliert beleuchten und dabei praxisnahe Beispiele mit großen Datensätzen verwenden.

Vorbereitung: 4 Millionen Zeilen für Experimente

Um die Funktionsweise von Indizes zu demonstrieren, erstellen wir eine Testtabelle namens t_test ohne Primärschlüssel oder Indizes, die 4 Millionen Datensätze enthält. Dies wird den Leistungsunterschied vor und nach der Indizierung deutlich veranschaulichen.

DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (id serial, name text);
INSERT INTO t_test (name) SELECT 'hans' FROM generate_series(1, 2000000);
INSERT INTO t_test (name) SELECT 'paul' FROM generate_series(1, 2000000);
SELECT name, count(*) FROM t_test GROUP BY name;

Führen wir eine Abfrage ohne Index aus, zum Beispiel EXPLAIN ANALYZE SELECT * FROM t_test WHERE id = 432332;, werden wir einen Seq Scan beobachten, der alle 4 Millionen Zeilen durchläuft und etwa 126 Millisekunden benötigt. Dies ist ein klassisches Beispiel für das Problem, das Indizes lösen sollen.

EXPLAIN und die cost-Metrik verstehen

EXPLAIN ANALYZE ist das primäre Werkzeug zur Analyse von Abfrageausführungsplänen in PostgreSQL. Es liefert detaillierte Informationen darüber, wie der Planer eine Abfrage auszuführen gedenkt, einschließlich der gewählten Operatoren (z.B. Seq Scan, Index Scan), der Ausführungszeit und, ganz entscheidend, der cost-Metrik.

Betrachten wir die Ausgabe cost=0.00..71622.00 aus dem vorherigen Beispiel. Diese Zahl ist keine tatsächliche Zeit in Millisekunden, sondern ein relativer Koeffizient, den PostgreSQL verwendet, um verschiedene Ausführungspläne zu vergleichen. Stellen Sie es sich als „Papageien“ vor – abstrakte Kosteneinheiten. Für ein saubereres Experiment ist es sinnvoll, die Parallelisierung zu deaktivieren:

SET max_parallel_workers_per_gather TO 0;

Die Kosten ergeben sich aus mehreren Komponenten, wie der Anzahl der Disk-Blöcke und den Kosten für die Verarbeitung jeder Zeile:

SELECT pg_relation_size('t_test') / 8192.0; -- ~21622 8KB blocks
SHOW cpu_tuple_cost;    -- 0.01 (cost of processing a row)
SHOW cpu_operator_cost; -- 0.0025 (cost of an operator/function)

Die ungefähre Kostenformel für unseren Seq Scan würde wie folgt aussehen:

SELECT (pg_relation_size('t_test') / 8192.0) * 1
       + count(id) * 0.01
       + count(id) * 0.0025
FROM t_test;

Das Ergebnis wird nahe bei 71622 liegen. Es ist entscheidend zu verstehen, dass cost keine systemspezifischen Hardware-Details berücksichtigt. Daher ist der Vergleich der cost zweier unterschiedlicher Abfragen zur Schätzung der tatsächlichen Ausführungszeit ungenau. Innerhalb einer einzelnen Abfrage ist es jedoch nützlich, um die „teuersten“ Teile des Plans zu identifizieren.

Grundlegende Indexnutzung: BTree und seine Vorteile

Der gebräuchlichste Indextyp in PostgreSQL ist der BTree. Er ermöglicht effizientes Suchen, Sortieren und bietet eine hohe Parallelität. Erstellen wir einen BTree-Index auf der Spalte id:

CREATE INDEX idx_id ON t_test (id);
EXPLAIN SELECT * FROM t_test WHERE id = 43242;

Nach dem Erstellen des Indexes sinken die cost der Abfrage erheblich (von 71.622 auf 8.45), und die Abrufzeit reduziert sich auf Bruchteile einer Millisekunde. BTree-Indizes sind auch effektiv für Sortieroperationen und das Finden von Minimum-/Maximum-Werten:

• Sortierung: PostgreSQL kann einen Index verwenden, um ORDER BY-Operationen durchzuführen, indem es den Index einfach in der gewünschten Richtung durchläuft (Index Scan Backward für DESC) und stoppt, sobald das LIMIT erreicht ist.

```sql

EXPLAIN SELECT * FROM t_test ORDER BY id DESC LIMIT 10;

```

• Min/Max: Um min(id) oder max(id) zu bestimmen, verwendet der Planer einen Index Only Scan, der den ersten oder letzten Eintrag aus dem Index liest.

```sql

EXPLAIN SELECT min(id), max(id) FROM t_test;

```

Umgang mit mehreren Bedingungen: Bitmap Scan

PostgreSQL kann Abfragen mit mehreren OR-Bedingungen auf einem einzigen Index effizient mittels eines Bitmap Scans verarbeiten.

EXPLAIN SELECT * FROM t_test WHERE id = 30 OR id = 50;

In diesem Szenario führt PostgreSQL für jede Bedingung einen Bitmap Index Scan durch, kombiniert dann die Ergebnisse mithilfe von BitmapOr zu einer Bitmap und greift erst danach auf die Haupttabelle (Bitmap Heap Scan) zu, um die vollständigen Zeilen abzurufen. Dies vermeidet mehrere Tabellen-Scans und optimiert den Datenzugriff.

Warum der Planer einen Index ignoriert: Selektivität und Statistiken

Einer der häufigsten Gründe, warum ein Index nicht verwendet wird, ist die geringe Selektivität der Abfragebedingung. Selektivität ist der Anteil der Zeilen in einer Tabelle, die einer bestimmten Bedingung entsprechen. Wenn eine Bedingung einen großen Teil der Tabelle betrifft, könnte der Planer entscheiden, dass ein Seq Scan effizienter ist als ein Index Scan.

Betrachten wir ein Beispiel. Wir erstellen einen Index auf dem Feld name:

CREATE INDEX idx_name ON t_test (name);

Suchen wir nach einem nicht existierenden Namen (EXPLAIN SELECT * FROM t_test WHERE name = 'hans2';), wird der Index verwendet, und rows wird 1 sein, da PostgreSQL immer mindestens eine Zeile erwartet. Wenn die Abfrage jedoch einen großen Teil der Tabelle abdeckt, zum Beispiel 'hans' OR 'paul' (was 100% unserer Testtabelle ausmacht):

EXPLAIN SELECT * FROM t_test WHERE name = 'hans' OR name = 'paul';

In diesem Fall führt PostgreSQL einen Seq Scan durch. Der Grund ist einfach: Das Scannen des gesamten Indexes und der anschließende Zugriff auf die Tabelle für jede der 4 Millionen Zeilen ist teurer, als die gesamte Tabelle sequenziell zu lesen. Der Planer trifft seine Entscheidung basierend auf Datenverteilungsstatistiken. Wenn diese Statistiken veraltet sind (z.B. nach einer großen Anzahl von Änderungen), kann die Entscheidung suboptimal sein.

Auswirkungen der physischen Datenanordnung: Korrelation und CLUSTER

Die Effektivität eines Indexes hängt stark von der physischen Anordnung der Daten auf der Festplatte ab. Wenn die über den Index abgerufenen Daten weit verstreut sind, kann dies den Abruf erheblich verlangsamen. Erstellen wir eine Kopie unserer Tabelle, jedoch mit einer zufälligen Zeilenreihenfolge:

CREATE TABLE t_random AS SELECT * FROM t_test ORDER BY random();
CREATE INDEX idx_random ON t_random (id);
VACUUM ANALYZE t_random;

Vergleichen wir die Leistung einer Abfrage, die die ersten 10.000 Datensätze für die ursprüngliche (geordnete) und die randomisierte Tabelle abruft:

• Originaltabelle (geordnete Daten):

```sql

EXPLAIN (analyze true, buffers true) SELECT * FROM t_test WHERE id < 10000;

```

Hier werden wir eine geringe Anzahl von Pufferzugriffen beobachten (z.B. Buffers: shared hit=3 read=82), was auf sequenzielles Lesen der Daten hindeutet.

• Randomisierte Tabelle (verstreute Daten):

```sql

EXPLAIN (analyze true, buffers true) SELECT * FROM t_random WHERE id < 10000;

```

In diesem Fall wird die Anzahl der Pufferzugriffe deutlich höher sein (z.B. Buffers: shared hit=801 read=7210). Dies geschieht, weil die Daten verstreut sind, was PostgreSQL zwingt, viele zufällige Festplattenzugriffe durchzuführen, was die Ausführungszeit erheblich verlängert. Der Planer könnte sogar zu einem Bitmap Heap Scan wechseln.

Korrelation

PostgreSQL verfolgt den Grad der Datenordnung mithilfe der Korrelation-Metrik, die in pg_stats verfügbar ist:

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename IN ('t_test', 't_random') AND attname = 'id'
ORDER BY 1, 2;

• correlation ~ 1: Daten sind physisch geordnet, was ein sequenzielles Lesen von Disk-Blöcken ermöglicht.
• correlation ~ 0: Daten sind zufällig verstreut, was zu vielen einzelnen Disk-Zugriffen für jede Zeile führt.

CLUSTER

Der CLUSTER-Befehl ermöglicht es Ihnen, Daten in einer Tabelle physisch gemäß einem angegebenen Index zu sortieren:

CLUSTER t_random USING idx_random;
VACUUM ANALYZE t_random;

Nach CLUSTER wird der Abruf wieder schnell. CLUSTER hat jedoch erhebliche Nachteile:

• Tabellensperrung: Der Vorgang sperrt die gesamte Tabelle, einschließlich SELECT-Anweisungen, für seine Dauer.
• Einschränkung: Es funktioniert nur mit einem einzigen Index.
• Keine automatische Wartung: Die Datenreihenfolge wird nicht automatisch beibehalten; nach neuen Einfügungen oder Aktualisierungen können die Daten wieder ungeordnet werden.

Optimierung mittels Index Only Scan und INCLUDE

Wenn eine Abfrage nur auf Spalten zugreift, die vollständig in einem Index enthalten sind, kann PostgreSQL einen Index Only Scan durchführen. Dies vermeidet den Zugriff auf die Haupttabelle (Heap) und beschleunigt die Abfrageausführung erheblich.

EXPLAIN SELECT id FROM t_test WHERE id = 34234;

Hier ist id bereits im idx_id-Index enthalten, sodass ein Index Only Scan möglich ist. Wenn Sie jedoch alle Spalten abfragen, einschließlich name, das nicht in idx_id enthalten ist:

EXPLAIN SELECT * FROM t_test WHERE id = 34234;

PostgreSQL führt einen regulären Index Scan durch, da es für die Spalte name auf die Tabelle zugreifen muss. Um einen Index Only Scan auch für SELECT * zu ermöglichen, können Sie einen Covering Index mit der INCLUDE-Klausel verwenden:

CREATE INDEX idx_random_cover ON t_random (id) INCLUDE (name);
EXPLAIN SELECT * FROM t_random WHERE id = 34234;

Nun ist name im Index enthalten, und es wird erneut ein Index Only Scan durchgeführt, wodurch die Disk-Zugriffe minimiert werden.

Fortgeschrittene Indizierungstechniken: Funktionale und Partielle Indizes

Über die Standard-BTree-Indizes hinaus bietet PostgreSQL weitere spezialisierte Lösungen:

• Funktionale Indizes: Diese ermöglichen es Ihnen, die Ergebnisse von Funktionen zu indizieren. Die einzige Anforderung ist, dass die Funktion deterministisch sein muss (d.h. bei gleichem Input immer das gleiche Ergebnis liefert).

```sql

CREATE INDEX idx_cos ON t_random (cos(id));

EXPLAIN SELECT * FROM t_random WHERE cos(id) = 10;

```

Ein typischer Anwendungsfall ist die Indizierung von lower(email) für nicht-sensible Suchen (case-insensitive).

• Partielle Indizes: Diese Indizes decken nur eine Untermenge von Tabellenzeilen ab, die eine bestimmte WHERE-Bedingung erfüllen.

```sql

CREATE INDEX idx_name ON t_test (name) WHERE name NOT IN ('hans', 'paul');

```

Ein solcher Index wird deutlich kleiner sein und seltener aktualisiert, was vorteilhaft ist, wenn der Großteil der Daten selten in Suchabfragen involviert ist.

Andere Indextypen: GiST und pg_trgm

PostgreSQL unterstützt verschiedene Indextypen, die jeweils für spezifische Aufgaben optimiert sind. Neben BTree gibt es GIN, GiST, SP-GiST, BRIN und Bloom. Zum Beispiel werden GiST-Indizes häufig für Geodaten, Volltextsuche und andere komplexe Datentypen verwendet.

Die pg_trgm-Erweiterung ermöglicht unscharfe Zeichenkettenvergleiche (fuzzy string matching), indem Zeichenketten in Trigramme zerlegt und der Abstand zwischen ihnen berechnet wird. Dies ist nützlich für fehlertolerante Suchen oder partielle Zeichenkettenübereinstimmungen.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT 'abcde' <-> 'abdeacb'; -- a number between 0 and 1
SELECT show_trgm('abcdef');

Wichtige Erkenntnisse

• EXPLAIN ANALYZE ist Ihr bester Freund: Nutzen Sie es, um Abfragepläne zu verstehen und Engpässe zu identifizieren. cost ist eine relative Metrik, nützlich zum Vergleich von Teilen eines einzelnen Plans.
• Selektivität steuert die Wahl: Indizes sind effektiv für hochselektive Abfragen (wenige Zeilen). Bei geringer Selektivität (viele Zeilen) könnte PostgreSQL einen Seq Scan bevorzugen.
• Physische Datenanordnung ist wichtig: Eine hohe Korrelation zwischen logischer und physischer Datenreihenfolge verbessert die Index Scan-Leistung. CLUSTER kann helfen, hat aber erhebliche Nachteile.
• Index Only Scan und INCLUDE: Nutzen Sie diese Mechanismen, um den Zugriff auf die Haupttabelle zu minimieren, wenn alle benötigten Spalten bereits im Index vorhanden sind.
• Fortgeschrittene Indizes: Funktionale und partielle Indizes ermöglichen es Ihnen, spezialisiertere und effizientere Strukturen für spezifische Szenarien zu erstellen.

— Editorial Team