Abfragepläne und Indizes

Die wichtigsten Befehle zum Merken

• EXPLAIN (ANALYZE, BUFFERS) — SELECT ausführen und Arbeit untersuchen.
• ANALYZE — Planerstatistik der Testtabelle aktualisieren.
• CREATE INDEX — einen Zugriffspfad für die gewählte Spalte ergänzen.

Befehle und Optionen

Befehl oder Syntax	Bedeutung
psql -X -d lab	Testdatenbank ohne Startanpassungen öffnen.
CREATE TEMP TABLE … AS SELECT	Sitzungslokale Tabelle aus Abfrageergebnissen erstellen.
generate_series(1, 10000)	Zehntausend ganzzahlige Zeilen einschließlich Grenzen erzeugen.
repeat('x', 100) / AS	100 Zeichen Nutzdaten erzeugen / Ausgabespalten benennen.
ANALYZE lab_plan	Statistik sammeln; allein zeigt dieser Befehl keinen Plan.
EXPLAIN (ANALYZE, BUFFERS)	Abfrage ausführen, Zeiten, Zeilen und Pufferaktivität zeigen.
WHERE id = 5000	Eine erzeugte Zeile auswählen.
CREATE INDEX ON lab_plan (id)	Standardmäßigen B-Tree-Index auf ID erstellen.
; / \q	Anweisung beenden / psql schließen und temporäre Objekte entfernen.

Die entscheidenden Konzepte

1. Die Abfrage nennt das Ergebnis, der Plan wählt die Arbeit

SQL beschreibt gewünschte Daten. Der Optimierer wählt einen Ausführungsplan aus Scans, Joins, Filtern, Sortierungen und weiteren Operationen.

Ähnlich wirkende Abfragen können unterschiedliche Pläne erhalten. Auch dieselbe Abfrage kann bei anderen Daten oder Statistiken wechseln. Lies den Plan als Operationsbaum statt nur seine erste Zeile als vollständige Leistungserklärung.

2. Geschätzte Kosten sind keine vergangenen Millisekunden

Planerkosten sind relative Schätzwerte zum Vergleich von Alternativen. Erwartete Zeilenzahlen und Breiten helfen bei der Aufwandsschätzung. Sie sind keine gemessenen Laufzeiten.

EXPLAIN ANALYZE ergänzt tatsächliche Zeiten und Zeilenzahlen. Große Schätzfehler können ungeeignete Join- oder Scanentscheidungen erklären. Bei mehrfach ausgeführten Knoten musst du Schleifenzahl und Werte pro Durchlauf unterscheiden.

3. Ein Index tauscht Pflegeaufwand gegen einen Zugriffspfad

Ein Index ergänzt eine Struktur, die passende Zeilen ohne vollständigen Tabellenscan finden kann. Besonders hilfreich ist er bei selektiven Bedingungen auf größeren Tabellen und passender Indexstrategie.

Indizes benötigen Speicher und Pflege bei Schreibzugriffen. Für kleine Tabellen oder Abfragen auf große Datenanteile kann ein sequenzieller Scan richtig sein. Ein ungenutzter Index beweist keinen defekten Optimierer.

4. Messungen brauchen Kontext

Pufferangaben beschreiben Datenzugriff jenseits ausgegebener Zeilen. Ein Puffertreffer bedeutet, dass der Block bereits im betreffenden PostgreSQL-Puffer lag. Er ist kein Treffer eines Anwendungsantwortcaches.

EXPLAIN ANALYZE führt tatsächlich aus und erzeugt Messaufwand. Ändernde Anweisungen ändern dabei Daten. Warme Caches, andere Arbeit und kleine Testmengen verzerren Zeitvergleiche. Untersuche hier Zugriffspfade statt produktive Beschleunigung zu behaupten.

Ein kleines Beispiel

Optional: Öffne psql und führe SQL der Reihe nach aus. Daten und Index gehören zu einer temporären Sitzungstabelle. Stoppe bei Aufbaufehlern vor dem Planvergleich.

psql -X -d lab

CREATE TEMP TABLE lab_plan AS
SELECT n AS id, repeat('x', 100) AS payload
FROM generate_series(1, 10000) AS n;
ANALYZE lab_plan;
EXPLAIN (ANALYZE, BUFFERS) SELECT payload FROM lab_plan WHERE id = 5000;
CREATE INDEX ON lab_plan (id);
ANALYZE lab_plan;
EXPLAIN (ANALYZE, BUFFERS) SELECT payload FROM lab_plan WHERE id = 5000;

\q

Vergleiche Scanknoten, geschätzte und tatsächliche Treffer sowie Pufferaktivität. Zunächst ist kein Index verfügbar. Danach kann der Planer ihn wählen, muss es aber nicht.

Bei temporären Tabellen können Daten- und Indexzugriffe unter local statt shared erscheinen. temp bezeichnet dagegen temporäre Arbeitsdateien. Tatsächliche Zeiten sind Millisekunden; leite keine erfundene Verbesserung aus ungemessener Ausgabe ab. Der letzte Befehl entfernt die Testobjekte durch Sitzungsende.

Merke dir: Ein Plan zeigt berührte Zeilen und Blöcke. Ein Index hilft, wenn er die entscheidende Arbeit reduziert.