Vergleiche PostgreSQL-Pläne, indem du wiederholte Ausführungszeiten den Buffer-Zählern des Wurzelknotens gegenüberstellst. Diese Anleitung für das Mac-Terminal verwendet synthetische Artikelzeilen, um zu prüfen, ob ein Index die Datenbankarbeit reduziert. Dabei werden Cache-Effekte berücksichtigt, durch die ein einzelner Lauf irreführend sein kann.

Was solltest du gemeinsam messen?

Deine Aufgabe ist es, dieselbe Abfrage für eine Artikelliste vor und nach dem Hinzufügen eines Indexes zu vergleichen. Behalte ausgewählte Spalten, Kategorie, Sortierung, Limit und Daten unverändert bei. Erfasse sowohl die verstrichene Ausführungszeit als auch die Buffer-Aktivität, die zum Erzeugen des Ergebnisses erforderlich ist.

Verwende EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON). Die Option ANALYZE führt die Abfrage aus; TIMING OFF deaktiviert die Zeitmessung einzelner Knoten, während die Ausführungszeit der gesamten Anweisung erhalten bleibt. Ein maschinenlesbares Ausgabeformat erleichtert das Erfassen wiederholter Messungen. PostgreSQL: EXPLAIN

Lies diese Messwerte gemeinsam:

Messwert Aussage
Execution Time Ausführungsdauer auf dem Server, ohne Planungszeit
Planning Time Zeit zum Erzeugen und Optimieren des Plans
Shared Hit Blocks Buffer-Zugriffe, die aus dem gemeinsamen Cache von PostgreSQL bedient wurden
Shared Read Blocks Blöcke, die in die Shared Buffers geladen wurden
Shared Hits plus Reads Nützlicher Vergleich des Zugriffsvolumens auf Shared Buffers
Temp Read Blocks und Temp Written Blocks Aktivität temporärer Arbeitsdateien, etwa wenn eine Sortierung den Arbeitsspeicher überschreitet

Shared Blocks gehören zu regulären Tabellen und Indizes. Local Blocks gehören zu temporären Tabellen und Indizes; Blöcke temporärer Arbeitsdateien bilden eine eigene Kategorie. Ein übergeordneter Knoten umfasst die Buffer-Aktivität seiner Kindknoten. Verwende daher den Wurzelknoten für die Ausführungssummen und prüfe die Kindknoten, um die Arbeit zu lokalisieren. Addiere niemals die Zähler aller Knoten. PostgreSQL: EXPLAIN

Buffer-Zähler stehen für nicht eindeutige Zugriffe: Wird erneut auf einen Block zugegriffen, kann er wieder gezählt werden. Außerdem sind die Kosten des Planers willkürliche Einheiten und keine Millisekunden. Vergleiche tatsächliche Ausführungszeiten mit tatsächlichen Buffer-Zählern; verwende geschätzte Kosten, um die Entscheidung des Planers zu verstehen. PostgreSQL: EXPLAIN verwenden

Ein Shared Read beweist nicht, dass das Speichergerät deines Mac einen physischen Lesevorgang ausgeführt hat. Die E/A-Statistiken von PostgreSQL können nicht unterscheiden, ob Daten aus dem Speicher oder aus dem bereits im Betriebssystem-Seitencache verfügbaren Inhalt geladen wurden. Bezeichne die Messung als Shared-Buffer-Lesezugriffe, sofern dir keine separaten Belege zum Speicher vorliegen. PostgreSQL: Das kumulative Statistiksystem

Das Mac-Experiment vorbereiten und Versionsgrenzen prüfen

Du brauchst eine laufende lokale PostgreSQL-Datenbank und Node.js mit npm in Terminal. Diese Anleitung richtet sich an PostgreSQL 18 und verwendet Node.js 22 oder 24. Die Dokumentation von node-postgres führt diese Node.js-Versionen als kompatibel auf und beschreibt die Installation mit npm install pg. node-postgres

Der Vergleichsbefehl ist auch in der Dokumentation von PostgreSQL 16 und 17 enthalten. PostgreSQL 18 gibt Buffer-Ausgaben bei EXPLAIN ANALYZE automatisch aus; BUFFERS ausdrücklich anzugeben, verdeutlicht die Absicht über diese Versionen hinweg. Gehe nicht davon aus, dass neuere optionale Einstellungen auf älteren Servern verfügbar sind. PostgreSQL 16: EXPLAIN, PostgreSQL 17: EXPLAIN, Versionshinweise zu PostgreSQL 18

Installiere den Treiber in einem neuen Arbeitsverzeichnis:

mkdir pg-plan-comparison
cd pg-plan-comparison
npm init -y
npm install pg

Verwende eine vorhandene lokale Übungsdatenbank, auf die du mit deiner üblichen PostgreSQL-Verbindung zugreifen kannst. Das Skript nutzt new Client(), das dieselben Verbindungsumgebungsvariablen wie psql liest. Wenn diese Datenbank beispielsweise plan_lab heißt, führe das Skript mit PGDATABASE=plan_lab aus. Ergänze bei Bedarf deine vorhandenen Einstellungen PGHOST oder PGPORT. node-postgres: Verbinden

Das Programm erstellt 100.000 synthetische Artikel innerhalb einer Transaktion, vergleicht zwei Schema-Zustände und rollt die Transaktion zurück. Es verwendet eine reguläre Tabelle, damit der Scan unter Shared Buffers erscheint. PostgreSQL dokumentiert, dass ROLLBACK die Änderungen der Transaktion verwirft. Wenn der Name der Demonstrationstabelle bereits existiert, schlägt das Erstellen fehl, statt die Tabelle zu ersetzen. PostgreSQL: ROLLBACK

Einen vollständigen Vorher-nachher-Vergleich durchführen

Speichere das folgende vollständige Programm als compare-plans.mjs. Es erzeugt die Eingabedaten selbst, führt pro Phase einen nicht aufgezeichneten Aufwärmlauf durch, behält sieben gemessene Läufe bei und gibt den ersten gemessenen Plan jeder Phase aus. Der Zeilengenerator verwendet die dokumentierte PostgreSQL-Funktion generate_series. PostgreSQL: Mengenrückgabefunktionen

import pg from 'pg';

const { Client } = pg;
const client = new Client();
const query = `
  SELECT article_id, title, published_at
  FROM plan_compare_articles_demo
  WHERE category_id = $1
  ORDER BY published_at DESC, article_id DESC
  LIMIT $2
`;
const values = [7, 50];
const explain = `
  EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON)
  ${query}
`;
const median = values =>
  [...values].sort((a, b) => a - b)[Math.floor(values.length / 2)];

async function measure(label) {
  await client.query(explain, values);
  const runs = [];
  let firstPlan;
  for (let run = 1; run <= 7; run++) {
    const result = await client.query(explain, values);
    const document = result.rows[0]['QUERY PLAN'][0];
    const root = document.Plan;
    firstPlan ??= document;
    const hits = root['Shared Hit Blocks'] ?? 0;
    const reads = root['Shared Read Blocks'] ?? 0;
    runs.push({
      phase: label,
      run,
      execution_ms: document['Execution Time'],
      planning_ms: document['Planning Time'],
      shared_hit: hits,
      shared_read: reads,
      shared_accesses: hits + reads,
      temp_read: root['Temp Read Blocks'] ?? 0,
      temp_written: root['Temp Written Blocks'] ?? 0
    });
  }
  console.table(runs);
  console.log(label, 'median execution ms:',
    median(runs.map(run => run.execution_ms)));
  console.dir(firstPlan, { depth: null });
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows[0]);
  await client.query('BEGIN');
  await client.query(`
    CREATE TABLE plan_compare_articles_demo (
      article_id integer PRIMARY KEY,
      category_id integer NOT NULL,
      title text NOT NULL,
      published_at timestamp NOT NULL,
      body text NOT NULL
    )
  `);
  await client.query(`
    INSERT INTO plan_compare_articles_demo
      (article_id, category_id, title, published_at, body)
    SELECT g, g % 20, 'Article ' || g,
           timestamp '2025-01-01' + g * interval '1 minute',
           repeat('Synthetic article text. ', 12)
    FROM generate_series(1, 100000) AS series(g)
  `);
  await client.query('ANALYZE plan_compare_articles_demo');
  const before = await client.query(query, values);
  await measure('before index');

  await client.query(`
    CREATE INDEX plan_compare_articles_demo_list_idx
    ON plan_compare_articles_demo
      (category_id, published_at DESC, article_id DESC)
  `);
  const after = await client.query(query, values);
  if (JSON.stringify(before.rows) !== JSON.stringify(after.rows)) {
    throw new Error('Result mismatch between schema states');
  }
  await measure('after index');
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  try {
    await client.query('ROLLBACK');
  } finally {
    await client.end();
  }
}

Führe es im selben Verzeichnis aus und ersetze dabei den Namen durch den tatsächlichen Namen deiner Übungsdatenbank:

PGDATABASE=plan_lab node compare-plans.mjs

Das Programm übermittelt SQL und Parameterwerte getrennt über client.query(text, values). Es liest den Plan aus der zurückgegebenen Spalte namens QUERY PLAN; node-postgres ordnet Ergebnisspalten Objekteigenschaften zu und parst PostgreSQL-JSON in JavaScript-Objekte. node-postgres: Abfragen, node-postgres: pg.Result, node-postgres: Datentypen

Der eigenständige Befehl ANALYZE erfasst Tabellenstatistiken für den Planer. Er unterscheidet sich von der Option ANALYZE zur Ausführungsmessung innerhalb von EXPLAIN. Hier sorgt das Erfassen von Statistiken nach dem Laden der synthetischen Zeilen dafür, dass beide Phasen mit denselben Ausgangsinformationen zur Tabelle beginnen. PostgreSQL: ANALYZE

Die Berechnungen des Medians und der Buffer-Summen in JavaScript wurden mit deterministischen synthetischen Eingaben geprüft. Das vollständige Datenbankexperiment wurde während der Vorbereitung nicht ausgeführt, da PostgreSQL in der Forschungsumgebung nicht verfügbar war; der Artikel enthält keine gemessenen Benchmark-Ergebnisse.

Was sollte sich in den beiden Plänen ändern?

Der mögliche Index setzt die nach Gleichheit gefilterte Kategorie an die erste Stelle, gefolgt von den angeforderten Sortierspalten. PostgreSQL dokumentiert, dass Gleichheitsbedingungen auf den führenden Spalten eines B-Tree-Indexes den durchsuchten Indexbereich einschränken. Das macht diesen Index zu einem begründeten Kandidaten für die Abfrage. PostgreSQL: Mehrspaltenindizes

Achte vor dem Indexieren auf einen Scan und eine Sortierung und danach auf einen geordneten Index-Scan unterhalb des Limits. Das sind Erwartungen, die du anhand deiner Ausgabe prüfen solltest, keine garantierten Planformen. Ein passender B-Tree-Index kann die sortierten Zeilen direkt liefern; bei einer Sortierung in Kombination mit einem Limit muss der Rest der Treffer möglicherweise nicht verarbeitet werden. PostgreSQL: Indizes und ORDER BY

Prüfe in den ausgegebenen Plänen drei Dinge:

  1. Bestätige, dass der Wurzelknoten in beiden Phasen 50 Zeilen zurückgibt und das Programm keine Abweichung im Ergebnis meldet.
  2. Finde die Knoten, die für Scan und Sortierung zuständig sind. Vergleiche ihre Zeilenschätzungen mit den tatsächlichen Zeilenzahlen und prüfe, wo Buffer-Aktivität auftritt.
  3. Vergleiche die Shared-Zugriffe des Wurzelknotens und die wiederholten Ausführungszeiten. Eine Änderung der Knotennamen allein belegt keine nützliche Verbesserung.

Dies ist ein Vergleich in Phasen mit warmem Cache. Das Einfügen der Zeilen, die Abfragen zur Korrektheit, die Aufwärmläufe und das Erstellen des Indexes beeinflussen den Cache-Zustand. Behalte während beider Phasen dieselbe Mac-Auslastung und Datenbankkonfiguration bei. Bewahre die sieben Einzelmessungen auf, damit ein ungewöhnlich langsamer Lauf sichtbar bleibt; ein Median allein kann Schwankungen verdecken.

Für eine aussagekräftigere Folgeuntersuchung wiederhole das gesamte Experiment und teste andere Kategorien und Limits. Wenn du ein größeres Limit testest, kennzeichne es als separate Arbeitslast. Der ursprüngliche Vergleich beantwortet, ob dieser Index unter den beobachteten lokalen Bedingungen bei der konkreten Anfrage nach 50 Artikeln hilft.

Wie unterscheidest du weniger Arbeit von wärmeren Buffern?

Die folgenden Zahlen sind veranschaulichende Eingaben und keine aufgezeichneten Ergebnisse. Sie zeigen, warum Reads, Hits und Zeit gemeinsam betrachtet werden sollten:

Veranschaulichender Lauf Ausführung Shared Hits Shared Reads Hits + Reads
A, erste Beobachtung 12 ms 900 100 1,000
A, wiederholte Beobachtung 10 ms 1,000 0 1,000
B, Kandidatenplan 3 ms 80 0 80

Zwischen den beiden Beobachtungen von A verschwinden die Reads, während die Gesamtzahl der Shared-Zugriffe bei 1.000 bleibt. Das passt zu einer Änderung des Cache-Zustands; es belegt keine Verringerung des Zugriffsvolumens auf Shared Buffers. Bei B treten eine kürzere Dauer und weniger Zugriffe zusammen auf. Wiederholt sich dieses Muster bei gleichwertigen Ergebnissen, stützt es die engere Schlussfolgerung, dass B für diese Aufgabe weniger Arbeit mit Shared Buffers ausführt.

Nutze diese Entscheidungshilfe, wenn deine Ergebnisse anders ausfallen:

Beobachtetes Muster Interpretation und nächster Schritt
Kürzere Zeit und weniger Shared-Zugriffe Hinweis auf eine Verbesserung für die getestete Arbeitslast; wiederholen, um die Beständigkeit zu prüfen
Kürzere Zeit, weniger Reads, ähnliche Gesamtzahl an Zugriffen Der Cache-Zustand könnte die Änderung erklären; wiederholte Läufe mit warmem Cache vergleichen
Weniger Zugriffe, aber ähnliche oder längere Zeit Sortierung, Ausdrücke und Zeitschwankungen prüfen, bevor du dich entscheidest
Keine Reads, aber viele Hits Auch eine Ausführung aus dem Cache umfasst Buffer-Zugriffe; Knoten mit hoher Aktivität prüfen
Temporäre Reads oder Writes treten auf Den verantwortlichen Arbeitsvorgang prüfen, bevor du Einstellungen änderst

Vermeide es, die Cache-Hit-Rate zum ausschlaggebenden Kriterium zu machen. Im Beispiel haben sowohl A mit warmem Cache als auch B eine Shared-Buffer-Hit-Rate von 100 %, dennoch unterscheiden sich ihre Zugriffszahlen erheblich. Halte die Rohwerte und die Ergebnisgröße neben den Zeitmessungen fest.

Wie hängen diese Messwerte mit der Anwendungsgeschwindigkeit zusammen?

Die serverseitige Execution Time schließt die Planungszeit aus. Außerdem lässt EXPLAIN ANALYZE standardmäßig die Ergebnisübertragung und die Ausgabekonvertierung aus. Die Instrumentierung selbst verursacht zusätzlichen Aufwand. Daher vergleicht das Skript instrumentierte Datenbankausführungen, statt die gesamte Zeit bis zur Anzeige einer Artikelliste zu messen. PostgreSQL: EXPLAIN verwenden

Um die Ausgabekonvertierung in PostgreSQL 17 oder 18 separat zu untersuchen, füge SERIALIZE TEXT zu denselben EXPLAIN-Optionen hinzu. PostgreSQL 16 führt diese Option nicht auf. Die Serialisierung überträgt die Ergebniszeilen der Abfrage weiterhin nicht und kann daher die Netzwerkübertragung nicht messen. Behalte die Serialisierungseinstellungen bei den verglichenen Läufen unverändert bei. PostgreSQL 17: EXPLAIN, PostgreSQL 16: EXPLAIN

Für eine Anwendungsmessung erfasst du die Zeit für die gewöhnliche Artikelabfrage SELECT bis zum Abschluss des erwarteten Treiberaufrufs, nachdem die Verbindung bereits hergestellt wurde. Halte diesen Wert als separate clientseitige Laufzeitmessung fest. Übernimm die tatsächliche Abfragekonfiguration der Anwendung: Mit name für eine node-postgres-Abfrage wird das Verhalten vorbereiteter Anweisungen pro Verbindung aktiviert und die Vergleichsbedingungen ändern sich. node-postgres: Abfragen

Wähle den Kandidaten, wenn gleichwertige Ergebnisse, wiederholte Zeitmessungen und Buffer-Zähler für die zu bedienende Artikelanfrage ein stimmiges Bild ergeben. Bewahre die vollständigen Pläne, Einzelläufe, die Serverversion und die Abfrageparameter auf, damit die Schlussfolgerung spezifisch und reproduzierbar bleibt.

Quellen