Indizes und Ausführungspläne: erst lesen, dann anlegen

Der Plan ist die Antwort, nicht die Stoppuhr

„Die Abfrage braucht zwölf Sekunden“ ist ein Messwert ohne Erklärung. Zwölf Sekunden können bedeuten, dass zwanzig Millionen Zeilen gelesen wurden, oder dass zweihundert Zeilen gelesen wurden und elf Sekunden lang eine Sperre im Weg stand. Die beiden Fälle haben nichts miteinander zu tun, und keine Maßnahme hilft bei beiden.

Beide Systeme beantworten die Frage über den Ausführungsplan: die Schrittfolge, mit der die Engine an das Ergebnis kommt. Der Plan zeigt, welche Zugriffswege gewählt wurden, in welcher Reihenfolge verbunden wird und wie viele Zeilen dabei erwartet werden.

Sequentieller Scan — Die Tabelle wird vollständig gelesen. Bei kleinen Tabellen oder großen Ergebnismengen ist das der richtige Weg, nicht ein Versagen.

Indexzugriff — Über eine Indexstruktur werden gezielt die passenden Zeilen gesucht. Lohnt sich, wenn nur ein kleiner Teil der Tabelle gebraucht wird.

`EXPLAIN` und `EXPLAIN ANALYZE`: der Unterschied, der Daten kostet

EXPLAIN zeigt den Plan, den der Planer wählen würde, samt geschätzter Kosten und geschätzter Zeilenzahlen. Die Abfrage läuft dabei nicht.

EXPLAIN (ANALYZE) ergänzt die tatsächlichen Werte: die verstrichene Zeit je Planknoten und die Zahl der wirklich zurückgegebenen Zeilen. Das ist die einzige Art herauszufinden, ob die Schätzungen der Wirklichkeit nahekommen — und genau hier liegt eine Falle mit Datenverlustpotenzial.

Auf ein SELECT angewandt ist das harmlos. Auf ein UPDATE, ein DELETE, ein INSERT oder ein MERGE angewandt bedeutet es: Die Änderung findet statt. Wer in der Produktion EXPLAIN ANALYZE DELETE FROM ... eingibt, um „nur mal zu sehen, was das kosten würde“, hat gelöscht.

Schätzung gegen Wirklichkeit lesen

Der eigentliche Erkenntnisgewinn eines Plans mit echten Werten liegt nicht in der Gesamtzeit, sondern im Vergleich zwischen geschätzter und tatsächlicher Zeilenzahl je Knoten. Der Planer wählt seinen Weg anhand der Schätzung; liegt sie daneben, ist der Weg falsch, auch wenn die Engine fehlerfrei arbeitet.

Schätzung ≈ Wirklichkeit

Der Planer ist informiert. Ist die Abfrage trotzdem langsam, liegt es an der Datenmenge oder am Zugriffsweg, nicht an den Statistiken.

Schätzung viel kleiner als Wirklichkeit

Der Planer hielt einen Schritt für billig, der teuer war — typischerweise die Ursache für einen ungeeigneten Verbundweg bei vielen Zeilen.

Schätzung viel größer als Wirklichkeit

Der Planer hat einen Indexzugriff verworfen, der sich gelohnt hätte, weil er mit viel mehr Treffern rechnete.

In beiden Fehlerrichtungen ist die erste Maßnahme dieselbe und kostet fast nichts: die Statistiken auffrischen. Die PostgreSQL-Dokumentation hält ausdrücklich fest, dass ANALYZE regelmäßig laufen muss, damit der Planer fundierte Entscheidungen treffen kann. Erst wenn die Schätzungen danach stimmen und die Abfrage weiterhin langsam ist, lohnt das Nachdenken über einen Index.

Wann ein Index hilft — und wann nicht

Ein Index lohnt sich, wenn er die Menge der anzufassenden Zeilen stark verkleinert. Die Dokumentation beschreibt den Fall genau so: Ohne Index müsste das System die gesamte Tabelle Zeile für Zeile durchsuchen; hat die Abfrage viele Zeilen zur Auswahl und liefert nur wenige zurück, ist das offensichtlich unwirtschaftlich.

Selektivität — Der Anteil der Zeilen, den eine Bedingung übrig lässt. Eine Kundennummer ist hochselektiv — eine Zeile von Millionen. Ein Ja-Nein-Kennzeichen ist es nicht — es halbiert bestenfalls.

Daraus folgt die häufigste Fehleinschätzung: Ein Index auf einer Spalte mit wenigen verschiedenen Werten bringt in der Regel nichts. Die Engine müsste den Index lesen und danach noch die halbe Tabelle besuchen; der sequentielle Scan ist dann der billigere Weg, und der Planer wählt ihn zu Recht. Der Index kostet trotzdem Platz und Schreibzeit.

Die Dokumentation nennt auch die Fälle, die gern vergessen werden: Indizes können ebenso UPDATE- und DELETE-Befehle mit Suchbedingung beschleunigen, und sie können in Verbunden verwendet werden — ein Index auf einer Spalte, die Teil einer Verbundbedingung ist, beschleunigt die Abfrage erheblich.

Warum ein vorhandener Index nicht genommen wird

Ein Index existiert, die Abfrage filtert auf genau diese Spalte — und der Plan zeigt trotzdem einen vollständigen Scan. Das ist meist kein Fehler des Planers, sondern eine nicht erfüllte Bedingung. Die Dokumentation nennt die Form, in der eine Bedingung überhaupt indexfähig ist: indizierte-Spalte indexfähiger-Operator Vergleichswert, in einer WHERE- oder JOIN-Klausel. Daraus ergeben sich drei Bedingungen, die alle gelten müssen.

• Die indizierte Seite muss unverändert dastehen. Der Index ist auf eine Spalte oder einen Ausdruck definiert. Wird die Spalte in der Bedingung erst durch eine Funktion geschickt — etwa in Großbuchstaben umgewandelt oder auf einen Datumsteil gekürzt —, steht dort ein anderer Ausdruck als der indizierte, und der Index passt nicht mehr. Der Ausweg ist ein Index auf genau diesem Ausdruck.
• Der Operator muss zur Operatorklasse des Index passen. Ein Index trägt nicht nur Werte, sondern eine Ordnung samt der Operatoren, die auf ihr arbeiten. Ein Operator, der nicht dazugehört, kann den Index nicht benutzen — klassisch beim Suchen mit einem führenden Platzhalter, das keine Ordnung ausnutzen kann.
• Der Vergleichswert muss stabil und fremd sein. Er darf laut Dokumentation nicht flüchtig sein und nicht auf die Tabelle des Index selbst verweisen. Ein Wert, der sich bei jedem Aufruf ändern kann, taugt nicht als Einstieg in eine sortierte Struktur.

SQL Server: der gruppierte Index **ist** die Tabelle

Bei SQL Server kommt eine Unterscheidung dazu, die es in dieser Form bei PostgreSQL nicht gibt — und die weitreichende Folgen für alle anderen Indizes hat.

Gruppierter Index

Sortiert und speichert die Datenzeilen selbst nach seinen Schlüsselwerten. Es kann laut Dokumentation nur einen je Tabelle geben, weil die Zeilen nur in einer Ordnung liegen können.

Heap

Eine Tabelle ohne gruppierten Index. Die Zeilen liegen dann in einer ungeordneten Struktur.

Nicht gruppierter Index

Eine eigene Struktur neben den Datenzeilen. Jeder Eintrag verweist über einen Zeilenlokalisierer auf die Datenzeile.

Die Zuordnung passiert überdies oft, ohne dass jemand einen Index anlegt. Die Dokumentation hält fest: Bei einem PRIMARY KEY erzeugt die Engine automatisch einen gruppierten Index, sofern nicht bereits einer besteht; bei einer UNIQUE-Bedingung einen nicht gruppierten. Existiert schon ein gruppierter Index, wird der Primärschlüssel über einen nicht gruppierten durchgesetzt. Welche Tabelle wie liegt, ist also oft ein Nebenprodukt der Reihenfolge, in der jemand die Bedingungen definiert hat.

Eingeschlossene Spalten sind das Gegenmittel gegen zu breite Schlüssel: Nichtschlüsselspalten lassen sich auf der Blattebene eines nicht gruppierten Index ergänzen, um vollständig abgedeckte Abfragen zu ermöglichen — die Engine beantwortet die Abfrage dann allein aus dem Index, ohne die Datenzeile zu besuchen.

Was ein Index kostet

Jeder Index ist eine zweite Struktur, die denselben Inhalt abbildet. Sie muss mitgeführt werden, und das ist kein Nebeneffekt, sondern der Preis.

Der letzte Halbsatz ist eine ungewöhnlich klare Empfehlung für eine Referenzdokumentation, und er benennt die Kostenseite doppelt. Neben der reinen Schreibarbeit steht dort ein zweiter, subtilerer Punkt: Wird eine Zeile geändert und liegt die neue Fassung auf derselben Seite, kann PostgreSQL die Indizes unangetastet lassen — aber nur, solange keine indizierte Spalte betroffen ist. Zusätzliche Indizes machen diesen sparsamen Weg seltener möglich.

Messbar wird der Schreibpreis über die WAL-Option von EXPLAIN: Sie zeigt laut Dokumentation die Zahl der erzeugten Protokollsätze, die Menge der erzeugten Protokolldaten in Bytes und wie oft die Protokollpuffer volliefen. Damit lässt sich derselbe UPDATE mit und ohne einen Indexkandidaten vergleichen — und die Antwort ist eine Zahl, keine Meinung.

Auf der Nutzungsseite führen beide Systeme Zähler darüber, wie oft ein Index tatsächlich verwendet wurde. Diese Zähler unterliegen derselben Einschränkung wie alle kumulativen Werte: Ohne Bezugszeitraum sagt „null Verwendungen“ nichts — der Zähler kann seit dem letzten Neustart zählen, und der war gestern.

Den Index anzulegen kostet ein Fenster

Ein Index ist schnell beschlossen und langsam gebaut. Was dabei passiert, unterscheidet sich deutlich von dem, was die meisten erwarten.

Ein CREATE INDEX auf einer 200-Gigabyte-Tabelle ist damit kein Einzeiler, sondern ein Wartungsfenster in der Länge des Aufbaus. Wer es zur Hauptlastzeit ausführt, legt die Schreibseite der Anwendung still, ohne dass irgendwo ein Fehler erscheint — die Schreiber warten einfach.

Die Dokumentation nennt auch den Ausweg: Es ist möglich, Schreibvorgänge parallel zum Indexaufbau zuzulassen. Sie verweist dabei ausdrücklich auf mehrere Einschränkungen, die man kennen muss — dieser Weg dauert länger, belastet das System stärker und kann fehlschlagen, wobei ein unbrauchbarer Index zurückbleibt, der von Hand beseitigt werden muss. Er ist die richtige Wahl für die Produktion und trotzdem keine Kleinigkeit.

Der Vorschlag ist kein Befehl

SQL Server sammelt während der Optimierung Hinweise darauf, welche Indizes gefehlt hätten, und stellt sie in eigenen Sichten bereit. Das ist nützlich — und wird regelmäßig als fertige Empfehlung missverstanden. Die Dokumentation nennt die Einschränkungen selbst.

• Die Angaben überdauern keinen Neustart. Sie werden aktualisiert, wenn eine Abfrage optimiert wird, und nicht dauerhaft gespeichert; sie bleiben nur bis zum Neustart der Engine erhalten. Wer sie behalten will, muss sie regelmäßig wegsichern — und den Startzeitpunkt der Instanz danebenlegen.
• Die Ergebnismenge ist auf 600 Zeilen begrenzt. Bei mehr fehlenden Indizes sieht man die neueren erst, wenn die vorhandenen abgearbeitet sind.
• Die Spaltenreihenfolge liefert die Sicht nicht mit. Laut Dokumentation gehören Gleichheitsspalten vor Ungleichheitsspalten, und innerhalb der Gleichheitsspalten ist nach Selektivität zu ordnen — die selektivste zuerst. Eingeschlossene Spalten gehören in die entsprechende Klausel, nicht in den Schlüssel.
• Vorschläge können einander doppeln. Sie entstehen je Abfrage, ohne Rücksicht darauf, was schon existiert oder was ein anderer Vorschlag bereits abdeckt.

Vom langsamen Befund zur belegten Änderung

Der letzte Schritt wird am häufigsten weggelassen und ist der einzige, der die Änderung belegt. Ein Index, der angelegt und nie gegengemessen wurde, ist eine Vermutung mit Schreibkosten — und er bleibt oft jahrelang liegen, weil niemand belegen kann, dass er nötig ist, und niemand sich traut, ihn zu entfernen.