Die Auswahl eines Artikeltexts kann einen reinen Index-Scan ausschließen: PostgreSQL muss eine Spalte abrufen, die der Index nicht enthält. Diese Anleitung zeigt, wie Sie diese Änderung auf einem Mac diagnostizieren und ihre Auswirkungen in Node.js messen. Voraussetzungen für reine Index-Scans.

Warum ändert sich der Plan, wenn sich die ausgewählten Spalten ändern?

Betrachten Sie eine Artikeltabelle mit id, category und body sowie einem B-Baum-Index auf (category, id). Eine Abfrage, die IDs nach Kategorie auswählt, kann sowohl ihren Filterwert als auch ihr Ergebnis aus diesem Index beziehen. Durch das Hinzufügen von body reicht dieser Index für einen reinen Index-Scan nicht mehr aus. PostgreSQL-Indizes sind vom Hauptspeicher der Tabelle getrennt, dem sogenannten Heap; ein gewöhnlicher Index-Scan ruft ebenfalls Tabellendaten ab. Reine Index-Scans und abdeckende Indizes.

Der Planer vergleicht nun Möglichkeiten, die benötigten Tabellenzeilen abzurufen. Er könnte einen Index Scan beibehalten, einen Bitmap Index Scan mit einem Bitmap Heap Scan kombinieren oder einen Seq Scan auswählen. Bitmap-Scans gruppieren Heap-Zugriffe nach physischem Speicherort. Beim Abruf vieler verstreuter Zeilen kann ein Tabellenscan günstiger sein, als sie einzeln abzurufen. Ein Bitmap-Plan verwendet weiterhin einen Index, auch wenn sein oberster Scan-Knoten den Heap erwähnt. EXPLAIN verwenden.

Prüfen Sie auch den ursprünglichen Plan sorgfältig. Ein Index Only Scan kann einen Wert ungleich null bei Heap Fetches ausweisen: PostgreSQL muss manchmal Tabellenzeilen besuchen, um ihre Sichtbarkeit festzustellen. Ob diese Zugriffe vermieden werden können, hängt von der Sichtbarkeitskarte der Tabelle ab und davon, ob der Index die benötigten Spalten enthält. Sichtbarkeitsprüfungen bei reinen Index-Scans.

Den Vergleich auf Ihrem Mac reproduzieren

Verwenden Sie eine lokal laufende PostgreSQL-Datenbank und eine installierte, aktuell unterstützte Node.js-Version mit npm. Das Basisbeispiel nutzt Funktionen, die in PostgreSQL 14–18 verfügbar sind; das abgerufene aktuelle PostgreSQL-Handbuch weist Version 18 aus. Die zusätzliche Serialisierungsdiagnose weiter unten erfordert PostgreSQL 17 oder höher. Die Dokumentation von node-postgres beschreibt die Node.js-Kompatibilität und den Installationsbefehl. PostgreSQL-Dokumentation, Installation und Kompatibilität von node-postgres.

Öffnen Sie Terminal und arbeiten Sie in einem neuen Ordner. Mit diesen Befehlen erstellen Sie das Projekt und installieren den Datenbanktreiber:

mkdir article-plan-lab
cd article-plan-lab
npm init -y
npm install pg

Das Programm verwendet Ihre vorhandenen lokalen Verbindungseinstellungen über PGHOST, PGPORT, PGDATABASE und PGUSER. Konfigurieren Sie sie für eine Datenbank, mit der Sie bereits eine Verbindung herstellen können. node-postgres liest diese Umgebungsvariablen, wenn ein Client ohne explizite Verbindungsoptionen erstellt wird. Verbinden mit Umgebungsvariablen.

Speichern Sie das folgende vollständige Programm als article-plan.cjs. Es erstellt synthetische Zeilen in einer temporären Tabelle, baut einen Index auf, gibt beide Ausführungspläne aus und misst anschließend beide gewöhnlichen Abfragen. Beide Vergleiche verwenden dieselbe Verbindung und denselben Kategorienwert. Die Abfragekonfiguration übergibt SQL über text und den Parameter über values entsprechend der dokumentierten node-postgres-API. Abfragekonfiguration.

const { Client } = require('pg');
const { performance } = require('node:perf_hooks');

async function main() {
  const client = new Client();
  try {
    await client.connect();
    const version = await client.query('SHOW server_version');
    console.log(version.rows[0]);

    await client.query(`CREATE TEMP TABLE article_plan_demo (
      id integer PRIMARY KEY,
      category integer NOT NULL,
      body text NOT NULL
    )`);
    await client.query(`INSERT INTO article_plan_demo
      SELECT g, g % 10, repeat('Synthetic article paragraph. ', 40)
      FROM generate_series(1, 50000) AS s(g)`);
    await client.query(`CREATE INDEX article_plan_demo_category_id
      ON article_plan_demo (category, id)`);
    await client.query('VACUUM (ANALYZE) article_plan_demo');

    const queries = [
      ['IDs', 'SELECT id FROM article_plan_demo WHERE category = $1'],
      ['Bodies', 'SELECT id, body FROM article_plan_demo WHERE category = $1']
    ];

    for (const [label, text] of queries) {
      const plan = await client.query({
        text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE) ${text}`,
        values: [3]
      });
      console.log(label);
      console.log(plan.rows.map(row => row['QUERY PLAN']).join('\n'));

      const start = performance.now();
      const result = await client.query({ text, values: [3] });
      const elapsed = performance.now() - start;
      const bodyBytes = result.rows.reduce((sum, row) =>
        sum + (row.body === undefined ? 0 : Buffer.byteLength(row.body)), 0);
      console.log({
        rows: result.rows.length,
        elapsedMs: elapsed.toFixed(2),
        bodyBytes
      });
    }
  } finally {
    await client.end();
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

Führen Sie das Programm im selben Ordner aus:

node article-plan.cjs

Bei der Einrichtung wird VACUUM (ANALYZE) als eigener Befehl ausgeführt. Dieser Befehl führt zuerst VACUUM und anschließend die Statistikensammlung durch und kann nicht innerhalb eines Transaktionsblocks ausgeführt werden. Lassen Sie ihn außerhalb eines Transaktions-Wrappers, den Sie dem Beispiel hinzufügen. VACUUM.

Die JavaScript-Syntax wurde geprüft, und eine separate deterministische Berechnung bestätigte die Zeilenanzahl und die Gesamtzahl der Body-Bytes der Testdaten. In der Rechercheumgebung war PostgreSQL nicht verfügbar. Daher wurden die Datenbankabfragen hier geprüft, aber nicht ausgeführt. Es wird kein bestimmter Plan und keine Laufzeit als beobachtetes Ergebnis dargestellt.

Pläne sinnvoll auswerten

Beginnen Sie mit den Scan-Namen und vergleichen Sie anschließend geschätzte und tatsächliche Zeilen. Prüfen Sie danach den Tabellenzugriff und die Buffer-Aktivität. So lässt sich unterscheiden, ob sich die verfügbaren Zugriffsmethoden geändert haben, die Schätzung ungenau ist oder das Ergebnis aufwendig abzurufen ist. VERBOSE zeigt außerdem die Ausgabespalten jedes Knotens. EXPLAIN verwenden, EXPLAIN-Optionen.

Beobachtung Interpretation Nächste Prüfung
IDs verwenden Index Only Scan; Texte verwenden Index Scan Die Textabfrage benötigt Tabellendaten Buffer und Laufzeit der gewöhnlichen Abfrage vergleichen
Texte verwenden Bitmap Heap Scan Prüfen Sie beim untergeordneten Knoten, ob ein Index verwendet wird Heap-Blöcke und zurückgegebene Zeilen vergleichen
Texte verwenden Seq Scan Der Planer bevorzugt einen Tabellenscan Geschätzte und tatsächliche Trefferzahlen vergleichen
IDs weisen beträchtliche Heap Fetches auf Auch die reine Index-Ausführung greift auf den Heap zu Prüfen Sie, ob dies auf jüngste Tabellenänderungen zurückzuführen ist
Schätzungen weichen deutlich von den tatsächlichen Zeilenzahlen ab Die Statistiken beschreiben die Daten möglicherweise nicht gut Statistiken erfassen und erneut vergleichen

ANALYZE sammelt Statistiken, die der Planer zur Auswahl von Plänen verwendet. Wenn Sie die synthetische Kategorienverteilung ändern, erfassen Sie erneut Statistiken, bevor Sie den nächsten Vergleich auswerten. Durch das Sampling können die Schätzungen zwischen den Durchläufen leicht variieren. ANALYZE.

Behandeln Sie width als geschätzte Ausgabebytes pro Zeile und nicht als Messung übertragener Bytes. Auch die Planerkosten werden in relativen Einheiten statt in Millisekunden angegeben. Buffer-Treffer zeigen Blöcke an, die im Cache von PostgreSQL gefunden wurden; das bedeutet nicht, dass keine Arbeit angefallen ist. Planschätzungen, Buffer-Berichterstattung.

Bei diesem Testdatensatz sollten beide Abfragen 5.000 Zeilen zurückgeben. Jeder synthetische Text enthält 1.160 ASCII-Bytes, daher liefert die Textabfrage 5.800.000 Text-Bytes zurück, also etwa 5,53 MiB vor IDs und Protokoll-Overhead. Das ist eine Berechnung anhand der Beispieleingaben, keine Messung der Datenbankleistung. Mit dem Feld bodyBytes des Programms können Sie diesen Unterschied überprüfen.

Die gewöhnliche Abfrage läuft nach der Planmessung, daher spiegelt ihre verstrichene Zeit diese Ausführungsreihenfolge mit aufgewärmtem Cache wider. Wiederholen Sie die Vergleiche und wechseln Sie die Reihenfolge, bevor Sie aus kleinen Zeitunterschieden Schlüsse ziehen. Ein einzelner Durchlauf eignet sich für die Diagnose, belegt aber kein verlässliches Leistungsverhältnis.

Warum kann der Abruf von Texten länger dauern, als EXPLAIN vermuten lässt?

Große Textwerte können komprimiert oder über den TOAST-Mechanismus von PostgreSQL außerhalb der Haupttabelle gespeichert werden. Der Abruf des Haupteintrags einer Zeile und die Bereitstellung ihres vollständigen Texts für den Client können daher unterschiedlich viel Arbeit erfordern. Der kurze, repetitive Testdatensatz oben soll die Spaltenabdeckung veranschaulichen; er steht nicht für jedes Muster der Textspeicherung. TOAST-Speicherung.

Standardmäßig verwirft EXPLAIN ANALYZE Abfrageergebnisse, ohne sie für die Übertragung umzuwandeln. Ab PostgreSQL 17 können Sie SERIALIZE TEXT hinzufügen, um die Ausgabeumwandlung zu untersuchen. Dazu gehört bei Bedarf auch der Abruf ausgelagerter Werte. PostgreSQL 17 hat diese Option eingeführt. Serialisierungsverhalten von EXPLAIN, Versionshinweise zu PostgreSQL 17.

Um dies auszuprobieren, ersetzen Sie die vorhandene EXPLAIN-Template-Zeile des Programms durch dieses Fragment und führen Sie das ganze Programm erneut aus:

text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SERIALIZE TEXT) ${text}`,

Verwenden Sie unter PostgreSQL 14–16 weiterhin die ursprüngliche Zeile und messen Sie den Empfang des Ergebnisses mit dem Timer der gewöhnlichen Abfrage. SERIALIZE sendet weiterhin keine Ergebnisdaten an den Client und kann daher weder die Übertragung noch die Verarbeitung durch JavaScript messen. Grenzen von EXPLAIN.

node-postgres sammelt Ergebniszeilen normalerweise in JavaScript-Objekten. Seine Option rowMode: 'array' ändert die Darstellung des Ergebnisses, entfernt aber keine ausgewählten Textwerte aus dem SQL-Ergebnis. Sie kann daher die verlorene Spaltenabdeckung nicht wiederherstellen, wenn body zu dieser Abfrage hinzugefügt wird. Zeilenmodus.

Eine Änderung passend zur Aufgabe mit den Artikeln wählen

Wenn die Aufgabe am Desktop nur eine Liste von IDs benötigt, halten Sie die Projektion schmal. Wenn der vollständige Artikeltext benötigt wird, prüfen Sie die tatsächliche Laufzeit und Ergebnisgröße der Textabfrage. Ziel der Diagnose ist es, unnötige Arbeit zu erkennen und dabei das angeforderte Ergebnis beizubehalten.

Für eine Ansicht, die nur die ersten 50 Artikel einer Kategorie zeigt, ist es eine sinnvolle Alternative, das angeforderte Ergebnis zu begrenzen. Ersetzen Sie im Array queries des Programms die Zeichenfolge der Textabfrage durch dieses SQL:

SELECT id, body
FROM article_plan_demo
WHERE category = $1
ORDER BY id
LIMIT 50

Damit ändert sich die Aufgabe bewusst: Statt aller passenden Artikel wird eine sortierte Seite abgerufen. Bei festgelegter Kategorie bietet der vorhandene Index (category, id) einen möglichen Zugriffspfad in ID-Reihenfolge. Ein passender B-Baum-Index kann ORDER BY mit LIMIT effizient machen, da die Ausführung nach dem Finden genügend vieler Zeilen stoppen kann. Der gewählte Plan sollte weiterhin geprüft werden. Indizes und ORDER BY.

Betrachten Sie INCLUDE (body) nicht als automatische Lösung. Eingeschlossene Spalten duplizieren Tabellendaten, vergrößern den Index und können dazu führen, dass Einfügungen fehlschlagen, wenn ein Indextupel seine Größenbegrenzung überschreitet. Kleine Nutzlastspalten können sich für gemessene Experimente eignen. INCLUDE wurde in PostgreSQL 11 eingeführt; in älteren Versionen ist es nicht verfügbar. Hinweise zu abdeckenden Indizes, Versionshinweise zu PostgreSQL 11.

Schließen Sie die Untersuchung mit drei konkreten Erkenntnissen ab: ob die Auswahl der Texte die Eignung für reine Index-Scans aufgehoben hat, ob der neue Scan eine akzeptable Leistung erbringt und wie lange es dauert, bis das vollständige Ergebnis Node.js erreicht. Anhand dieser Erkenntnisse können Sie entscheiden, ob Sie den Plan beibehalten, weniger Artikel anfordern oder einen anderen Index für die tatsächliche Abfrage testen.

Quellen