Veli Keçeci
Zurück zu allen Beiträgen
·6 Min. Lesezeit

PostgreSQL-Abfragepläne lesen: langsame Abfragen mit dem richtigen Index lösen

Wird eine Abfrage langsam, lautet der erste Reflex meist: „Legen wir einen Index an.“ Manchmal hilft das. Manchmal erhöht es nur die Schreibkosten und behebt nichts. Der Unterschied liegt darin, wirklich zu sehen, wie die Datenbank die Abfrage ausführt. PostgreSQL zeigt das offen über den Abfrageplan – wir müssen ihn nur lesen können.

In diesem Beitrag erkläre ich, wie man die Ausgabe von EXPLAIN ANALYZE liest, welche Indexarten in welchen Situationen sinnvoll sind und welche Fallstricke häufig vorkommen. Die Beispiele nutzen PostgreSQL, aber die Gewohnheit, Pläne zu lesen, lässt sich auf andere Datenbanken übertragen.

Nicht raten, den Plan anfordern

Um einen Plan zu sehen, setzt man EXPLAIN vor die Abfrage. Reines EXPLAIN führt die Abfrage nicht aus, sondern zeigt die Schätzung des Planers. EXPLAIN (ANALYZE, BUFFERS) führt die Abfrage tatsächlich aus und ergänzt echte Laufzeiten sowie die Anzahl gelesener Seiten.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Achtung: Da ANALYZE die Abfrage ausführt, sollte man es bei UPDATE oder DELETE innerhalb einer Transaktion ausprobieren und diese zurückrollen. Bei lesenden Abfragen besteht dieses Risiko nicht.

Worauf man im Plan achtet

Ein Plan ist ein verschachtelter Baum; die innersten Knoten laufen zuerst. An jedem Knoten sind zwei Zahlengruppen wichtig: die vom Planer geschätzte Zeilenzahl (rows) und die tatsächlich gelieferte Zeilenzahl (actual ... rows). Weichen beide stark voneinander ab, sind die Statistiken möglicherweise veraltet. Dann ändert ein ANALYZE tabellenname oft den Plan.

Auch die Knotentypen liefern Hinweise:

  • Seq Scan: Die ganze Tabelle wird gelesen. Bei kleinen Tabellen normal, bei großen Tabellen und einer Abfrage mit wenigen Ergebniszeilen verdächtig.
  • Index Scan / Index Only Scan: Ein Index wird genutzt. Ein Index Only Scan kann antworten, ohne die Tabelle zu besuchen, und ist deshalb meist der günstigste Weg.
  • Bitmap Heap Scan: Bei mittelgroßen Ergebnismengen wird aus dem Index eine Seitenliste erzeugt und die Tabelle gebündelt gelesen.
  • Sort und Hash: Reicht der Speicher nicht, weichen sie auf die Platte aus. Eine Zeile wie Sort Method: external merge deutet auf zu wenig work_mem oder einen fehlenden sortierten Index hin.

Auch die Buffers-Zeilen sind wertvoll: Ein hoher shared read bedeutet, dass Daten von der Platte gelesen werden, ein hoher shared hit, dass sie aus dem Cache kommen. Schwanken die Laufzeiten, liegt es oft genau an diesem Unterschied.

Bei zusammengesetzten Indizes zählt die Reihenfolge

Die obige Abfrage filtert per Gleichheit auf customer_id, per Gleichheit auf status und sortiert nach created_at. Ein einziger zusammengesetzter Index kann das abdecken:

CREATE INDEX idx_orders_customer_status_created
    ON orders (customer_id, status, created_at DESC);

Die Regel: Spalten mit Gleichheitsbedingungen kommen zuerst, die Sortier- oder Bereichsspalte zuletzt. PostgreSQL steigt dann direkt bei den relevanten Zeilen ein und braucht keinen separaten Sort-Schritt, weil die Daten bereits sortiert vorliegen; LIMIT 20 kann nach den ersten zwanzig Einträgen aufhören.

Warum die Reihenfolge wichtig ist, lässt sich so veranschaulichen: Ein Index auf (customer_id, status, created_at) ist wie ein Telefonbuch. Man kann nach dem Nachnamen suchen, aber wer nur den Vornamen kennt, hat nichts davon. Sucht Ihre Abfrage nur nach created_at, ohne status, arbeitet dieser Index nicht effizient.

Partielle und abdeckende Indizes

Wenn Sie nur einen Teil der Daten abfragen, ist es verschwendet, alles zu indizieren. Ein partieller Index enthält nur die Zeilen, die eine Bedingung erfüllen, und ist kleiner und damit schneller:

CREATE INDEX idx_orders_open
    ON orders (customer_id, created_at DESC)
    WHERE status = 'open';

Das eignet sich ideal für eine kleine, häufig abgefragte Teilmenge wie „offene Bestellungen“. Ein abdeckender Index trägt dagegen per INCLUDE zusätzliche Spalten, die die Abfrage braucht, und ermöglicht so einen Index Only Scan:

CREATE INDEX idx_orders_customer_covering
    ON orders (customer_id, created_at DESC)
    INCLUDE (total);

Ob ein Index Only Scan die Tabelle tatsächlich vollständig umgehen kann, hängt davon ab, dass die Visibility Map der Tabelle aktuell ist; dafür sorgt regelmäßiges VACUUM. Bei stark aktualisierten Tabellen sehen Sie den erwarteten Gewinn unter Umständen nicht.

Häufige Fallen, die einen Index unbrauchbar machen

Existiert ein Index, der Plan zeigt aber trotzdem einen Seq Scan, liegt es meist an einem dieser Punkte:

  1. Funktion auf der Spalte: WHERE lower(email) = '[email protected]' kann einen einfachen Index auf email nicht nutzen. Die Lösung ist entweder ein Ausdrucksindex (CREATE INDEX ON users (lower(email))) oder die Daten vorab zu normalisieren.
  2. Typkonflikt: Der Vergleich einer text-Spalte mit einem anderen Typ kann eine implizite Umwandlung erzwingen und den Index außer Kraft setzen.
  3. Wildcard am Anfang: LIKE '%abc' kann einen gewöhnlichen B-Tree-Index nicht verwenden. Für solche Suchen braucht es einen anderen Ansatz, etwa die Erweiterung pg_trgm mit einem GIN-Index.
  4. Geringe Selektivität: Liefert die Bedingung einen großen Teil der Tabelle, kann ein Seq Scan tatsächlich günstiger sein. Der Planer trifft diese Entscheidung meist richtig.
  5. Veraltete Statistiken: Nach einem großen Bulk-Load schätzt der Planer falsch.

Ein Index hat seinen Preis

Jeder Index muss bei jedem INSERT, UPDATE und DELETE aktualisiert werden. Zu viele Indizes bremsen das Schreiben und verbrauchen Speicherplatz und Cache. Ungenutzte Indizes zu entfernen ist deshalb genauso wichtig, wie neue anzulegen. Die Sicht pg_stat_user_indexes zeigt, wie oft ein Index gescannt wurde (idx_scan); Indizes, die lange bei null bleiben, sind Kandidaten. Prüfen Sie vor dem Löschen, wie lange die Statistiken schon gesammelt werden und ob selten laufende Berichte den Index brauchen.

Beim Anlegen eines Index auf einem Live-System verwenden Sie CREATE INDEX CONCURRENTLY, damit keine Zeilen gesperrt werden. Es dauert länger und läuft nicht in einem Transaktionsblock, blockiert aber keine Schreibzugriffe.

Ein praktischer Arbeitsablauf

Bei einer langsamen Abfrage spart diese Reihenfolge Zeit:

  1. Führen Sie die Abfrage mit echten Parametern und realistischen Daten per EXPLAIN (ANALYZE, BUFFERS) aus.
  2. Vergleichen Sie geschätzte und tatsächliche Zeilenzahlen; weichen sie stark ab, führen Sie zuerst ANALYZE aus.
  3. Finden Sie den teuersten Knoten: oft ein Seq Scan, ein Sort oder eine oft wiederholte innere Schleife.
  4. Entwerfen Sie nach Bedingungen und Sortierung der Abfrage den kleinsten sinnvollen Index.
  5. Holen Sie den Plan erneut und bestätigen Sie die Verbesserung mit Zahlen. Hat sie nichts gebracht, nehmen Sie den Index zurück.

Fügen Sie auch auf der Anwendungsseite einen Kontrollpunkt hinzu: Aktivieren Sie das Log langsamer Abfragen (log_min_duration_statement) oder beobachten Sie die zeitintensivsten Abfragen mit pg_stat_statements. So richtet sich die Optimierung nach gemessener Last statt nach gefühlter Langsamkeit.

Fazit

Der Abfrageplan ist die Antwort der Datenbank auf die Frage „Wie erledige ich diese Aufgabe?“; der Index ist eine Abkürzung, die Sie ihr geben. Ob die Abkürzung hilft, sagt nur der Plan. Wird das Lesen von Plänen zur Gewohnheit, legen Sie Indizes mit Belegen statt mit Vermutungen an, erkennen und entfernen überflüssige und halten die Lesegeschwindigkeit hoch, ohne die Schreibleistung zu opfern. Bei der nächsten langsamen Abfrage empfehle ich, zuerst EXPLAIN (ANALYZE, BUFFERS) auszuführen.