Seq Scan, Index Scan und Index-Only

Wenn der Planner Zeilen aus einer Tabelle benötigt, wählt er eine von drei Zugriffsmethoden. Interviewer erwarten, dass Sie alle drei nennen können:

• Seq Scan, liest jede Zeile der Tabelle von Anfang bis Ende.
• Index Scan, durchläuft einen Index, um passende Zeilen zu finden, und ruft anschließend jede Zeile aus der Tabelle ab.
• Index-Only Scan, beantwortet die Abfrage vollständig aus dem Index, ohne die Tabelle überhaupt zu berühren.

Zu wissen, warum der Planner die jeweilige Methode auswählt, ist der Kern dieser Lektion und eine typische Frage für Senior-Entwickler.

Ein Seq Scan liest die Seiten der Tabelle nacheinander und wendet den Filter auf jede Zeile an. Ein Index wird nicht verwendet.

Das klingt zunächst schlecht, ist aber oft die richtige Wahl. Sequentielle Lesevorgänge sind für die Festplatte schnell (keine zufälligen Sprünge). Wenn eine Abfrage einen großen Anteil der Tabelle zurückgibt, ist das vollständige Scannen schneller, als millionenfach über einen Index zu springen.

Das Beispiel: Scannen Sie orders und behalten Sie die Zeilen, für die amount > 100 gilt. Wenn die meisten Bestellungen den Wert 100 überschreiten, ist ein Seq Scan die richtige Wahl.

Ein Index Scan verwendet einen B-Baum, um direkt zu passenden Schlüsseln zu springen, und liest anschließend die zugehörigen Zeilen aus dem Tabellen-Heap.

Seine Stärken zeigt er bei einem selektiven Filter, der nur einen kleinen Teil der Tabelle zurückgibt. Fünf Zeilen über einen Index zu suchen, ist schneller, als 10 Millionen Zeilen zu lesen.

Der Plan nennt den verwendeten Index. Jeder Treffer verursacht eine Indexsuche plus einen Heap-Lesevorgang (einen zufälligen Lesezugriff). Daher verlieren Index Scans ihren Vorteil, sobald sie zu viele Zeilen zurückgeben.

Der zentrale Begriff hinter all dem ist die Selektivität: welcher Anteil der Zeilen von einem Prädikat behalten wird.

• Hohe Selektivität (wenige Zeilen stimmen überein, etwa bei einer eindeutigen ID) spricht für einen Index Scan.
• Niedrige Selektivität (viele Zeilen stimmen überein, etwa bei status IS NOT NULL) spricht für einen Seq Scan.

Eine verbreitete Faustregel: Sobald eine Abfrage mehr als ungefähr 5 bis 10 Prozent einer Tabelle zurückgibt, bevorzugt der Planner häufig einen Sequential Scan, weil die zufälligen Heap-Zugriffe eines Index teurer werden als das Lesen der gesamten Tabelle in Reihenfolge.

Ein Index-Only Scan ist die schnellste der drei Methoden. Wenn jede von der Abfrage benötigte Spalte bereits im Index enthalten ist, greift die Engine überhaupt nicht auf den Tabellen-Heap zu.

Die Beispielabfrage wählt nur customer_id aus und filtert danach. Der Index befindet sich auf customer_id. Alle benötigten Daten liegen im Index, daher meldet Postgres Index Only Scan.

Dadurch werden die zufälligen Heap-Lesevorgänge vermieden, die einen gewöhnlichen Index Scan verlangsamen – ein großer Vorteil bei breiten Tabellen.

Interviewer lieben diese Feinheit. Ein Index-Only Scan muss trotzdem bestätigen, dass jede Zeile für Ihre Transaktion sichtbar ist (MVCC); der Index allein speichert keine Sichtbarkeitsinformationen.

Postgres verwendet die visibility map: Wenn eine Seite als all-visible markiert ist, wird der Heap übersprungen. Andernfalls muss die Heap-Zeile trotzdem abgerufen werden. Der Plan zeigt Heap Fetches: N.

Deshalb kann eine gerade aktualisierte Tabelle viele Heap Fetches aufweisen und Index-Only Scans verlangsamen, bis VACUUM die Visibility Map aktualisiert.

Eine vierte Methode taucht ebenfalls häufig auf: der Bitmap Heap Scan. Der Planner wählt ihn, wenn ein Prädikat mehr Zeilen betrifft, als ein gewöhnlicher Index Scan sinnvoll verarbeiten kann, aber weniger als die gesamte Tabelle.

Zuerst erstellt er aus dem Index eine Bitmap der Fundstellen passender Zeilen (Bitmap Index Scan). Anschließend ruft er die Heap-Seiten in physischer Reihenfolge statt in zufälliger Reihenfolge ab. Geordnete Zugriffe sind deutlich günstiger als die verstreuten Lesevorgänge eines normalen Index Scans.

Eine klassische Frage im Vorstellungsgespräch lautet: Ich habe einen Index hinzugefügt, aber der Plan führt weiterhin einen Seq Scan aus – warum? Häufige Gründe:

• Das Prädikat ist nicht selektiv; ein Scan ist tatsächlich günstiger.
• Eine Funktion umschließt die Spalte: WHERE lower(email) = ... kann keinen gewöhnlichen Index auf email verwenden.
• Eine Typinkongruenz erzwingt eine implizite Typumwandlung, die den Index unbrauchbar macht.
• Veraltete Statistiken; führen Sie ANALYZE aus.
• Die Tabelle ist sehr klein; das Scannen weniger Seiten ist schneller als der Index-Overhead.

Angenommen, orders besitzt einen Index auf created_at, aber diese Abfrage verwendet trotzdem einen Seq Scan:

Der Grund ist DATE(created_at). Wenn die Spalte in eine Funktion eingeschlossen wird, kann der Index auf der unveränderten Spalte created_at nicht verwendet werden. Die Lösung besteht darin, die Abfrage in ein Bereichsprädikat umzuschreiben, das die Spalte unverändert lässt, oder einen Ausdrucksindex auf DATE(created_at) anzulegen.

Behalten Sie diesen Vergleich für das Vorstellungsgespräch im Kopf:

• Seq Scan, am besten bei der Rückgabe eines großen Zeilenanteils; sequentielle I/O.
• Index Scan, am besten für selektive Suchen; Indexdurchlauf plus zufällige Heap-Zugriffe.
• Bitmap Heap Scan, für eine mittlere Anzahl passender Zeilen; Index zur Erstellung einer Bitmap, danach geordnete Heap-Lesevorgänge.
• Index-Only Scan, am schnellsten, wenn der Index jede benötigte Spalte abdeckt und alle Seiten als sichtbar markiert sind.

Der Planner entscheidet anhand der geschätzten Kosten, die hauptsächlich durch Selektivität und Statistiken bestimmt werden.

Um in der Entwicklung eine Annahme zu überprüfen, können Sie den Planner vorübergehend in eine Richtung lenken: SET enable_seqscan = off; zwingt ihn, Indizes zu bevorzugen, sodass Sie Pläne vergleichen können.

Das ist ein diagnostischer Trick und niemals eine Lösung für die Produktion. Erwähnen Sie im Vorstellungsgespräch, dass die echten Lösungen bessere Statistiken, ein geeigneter Index oder das Umschreiben des Prädikats sind – nicht das globale Deaktivieren von Planner-Funktionen.

Eine Abfrage wählt nur email aus und filtert nach email. Außerdem gibt es einen B-Baum-Index auf email. Der Plan zeigt Index Only Scan. Warum ist das schneller als ein gewöhnlicher Index Scan?

Die wichtigsten Erkenntnisse zu Zugriffsmethoden:

• Seq Scan ist bei Abfragen mit niedriger Selektivität im Vorteil; Index Scan bei selektiven Abfragen.
• Index-Only Scan vermeidet den Heap, wenn der Index alle benötigten Spalten abdeckt. Achten Sie auf Heap Fetches und die Visibility Map.
• Bitmap Heap Scan bildet den Mittelweg, indem Heap-Seiten in physischer Reihenfolge abgerufen werden.
• Der Planner entscheidet anhand von Selektivität und Statistiken. Funktionen auf Spalten, Typinkongruenzen und veraltete Statistiken sind die Gründe, warum ein Index ignoriert wird.