Um wiederholte PostgreSQL-Lookups zu diagnostizieren, trennen Sie die Erstellung der Kandidaten von ihrer Verwendung. Lesen Sie die Ausführungsanzahl des inneren Scans ab, prüfen Sie, ob Caching wiederholte Arbeit vermeidet, und vergleichen Sie anschließend gruppierte Wiederverwendung, das Inlining von CTEs oder eine indizierte temporäre Tabelle.

Dieser Leitfaden richtet sich an Entwickler, die auf einem Mac an einer langsamen Artikelabfrage arbeiten: Artikelkandidaten einmal auswählen und anschließend innerhalb des Themas jedes Artikels die höchste Kandidaten-ID ermitteln. Im gesamten Text werden synthetische Zeilen verwendet. Ziel ist es, wiederholte Arbeit aufzuspüren und eine Abfrageform zu wählen, die das Ergebnis beibehält.

Eine reproduzierbare Kandidatenabfrage einrichten

Verwenden Sie eine vorhandene lokale PostgreSQL-Installation, auf der psql verfügbar ist. Öffnen Sie Terminal, führen Sie psql mit Ihren üblichen Verbindungseinstellungen aus und lassen Sie dieselbe Sitzung während aller SQL-Beispiele geöffnet. PostgreSQL beschreibt psql als interaktives Terminal zum Übermitteln von Abfragen und Anzeigen der Ergebnisse. psql

Prüfen Sie zunächst die Serverversion mit SHOW server_version;. In der für diesen Artikel abgerufenen aktuellen Dokumentation wird PostgreSQL 18 als aktuelle Version ausgewiesen. Die Beispiele verwenden Syntax, die ab PostgreSQL 12 verfügbar ist. Das ist eine Funktionsgrenze und keine Empfehlung, eine ältere Version zu verwenden. PostgreSQL 12 führte das CTE-Inlining sowie die Steuerelemente MATERIALIZED und NOT MATERIALIZED ein. WITH-Abfragen, Version 12

Führen Sie diese Einrichtung einmal in einer frischen Sitzung aus. Sie erstellt 20.000 synthetische Artikel und einen Index für Lookups nach Thema und ID. Das unten verwendete Kandidatenprädikat wählt die ersten 2.000 Artikel aus.

CREATE TEMP TABLE article_demo (
    id integer PRIMARY KEY,
    topic_id integer NOT NULL,
    title text NOT NULL
);

INSERT INTO article_demo (id, topic_id, title)
SELECT g, 1 + (g % 50), 'Article ' || g
FROM generate_series(1, 20000) AS s(g);

CREATE INDEX article_demo_topic_id_idx
    ON article_demo (topic_id, id);

ANALYZE article_demo;

Diese temporären Tabellen bleiben standardmäßig während der Sitzung bestehen. Führen Sie ANALYZE nach dem Laden explizit aus: Der Autovacuum-Prozess von PostgreSQL analysiert keine temporären Tabellen. CREATE TABLE

Erfassen Sie nun die Ausgangswerte. Die laterale Unterabfrage berechnet die höchste Kandidaten-ID für das Thema des aktuellen Artikels. Hier ist „höchste ID“ einfach die definierte Aufgabe; daraus folgt keine Aussage über das Veröffentlichungsdatum.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate AS x
    WHERE x.topic_id = c.topic_id
) AS m;

Das explizite MATERIALIZED macht die Wiederverwendungsgrenze des Experiments deutlich. Ein materialisiertes CTE kann einmal erstellt werden, während seine Abnehmer die gespeicherten Zeilen wiederholt lesen. Die PostgreSQL-Dokumentation zeigt, wie ein materialisiertes, mehrfach referenziertes CTE den Vorteil der Indizes der zugrunde liegenden Tabelle verliert, wenn das Zwischenergebnis durchsucht wird. Materialisierung von Common Table Expressions

Die SQL-Beispiele wurden geprüft, aber für diesen Artikel nicht auf einem PostgreSQL-Server ausgeführt. Die Berechnungen zu den synthetischen Zeilenanzahlen wurden separat überprüft; Ausführungszeiten und aufgezeichnete Pläne werden nicht als Messwerte dargestellt.

Den Plan lesen, um wiederholte Arbeit aufzuspüren

Beginnen Sie beim Zweig, der die Kandidaten erzeugt, und verfolgen Sie anschließend die einzelnen Abnehmer. Identifizieren Sie bei einer geschachtelten Schleife die äußere Eingabe und den inneren Lookup. PostgreSQL gibt loops als Ausführungsanzahl eines Knotens aus; tatsächliche Zeilenanzahlen und Knotenlaufzeiten sind Durchschnittswerte pro Ausführung. Eine Version mit korrelierter skalarer Unterabfrage kann stattdessen wiederholte Arbeit unter SubPlan anzeigen. Richten Sie Ihre Diagnose daher nach den ausgeführten Knoten aus, statt einen bestimmten Bezeichner vorauszusetzen. EXPLAIN verwenden

Halten Sie anhand Ihres tatsächlichen Plans vier Beobachtungen fest:

  1. Wie viele Zeilen gehen in den äußeren Lookup ein?
  2. Wie oft wird der zugrunde liegende innere Scan ausgeführt?
  3. Liest dieser Scan Kandidaten ein und verwirft anschließend die meisten durch einen Filter?
  4. Befindet sich zwischen den äußeren Zeilen und der aufwendigen inneren Arbeit ein Memoize-Knoten?

Betrachten Sie die Ebenen getrennt. Ein Aggregat kann pro Ausführung ein Maximum zurückgeben, während sein untergeordnetes Element viele Zeilen scannt, um es zu berechnen. Dass ein Kandidatenerzeuger einmal läuft, bedeutet daher nicht, dass die Verwendung der Kandidaten günstig ist.

In diesem konkreten Beispiel umfassen 2.000 Kandidaten 50 Themen mit jeweils 40 Artikeln. Die folgende Berechnung veranschaulicht mögliche Arbeitsmuster; sie sagt weder einen bestimmten Planauszug noch eine bestimmte Laufzeit voraus:

Verwendungsmuster Veranschaulichende Anzahl untersuchter Kandidatenzeilen
Vollständiger Scan aller Kandidaten für jeden äußeren Artikel 2.000 × 2.000 = 4.000.000
Vollständiger Scan einmal pro Thema, alle späteren Lookups gecacht 50 × 2.000 = 100.000
Kandidaten einmal nach Thema gruppieren 2.000 Zeilen, die in den Gruppierungsschritt eingehen, plus nachfolgende Join-Arbeit

Dieser Unterschied ist relevant für PostgreSQL 14 und höher, wo die Memoisierung von Ergebnissen innerer geschachtelter Schleifen eingeführt wurde. Laut aktueller Dokumentation cached Memoize die Ergebnisse parametrisierter Scans und kann bei einem Cache-Treffer den zugrunde liegenden Scan überspringen; Einträge können auch verdrängt werden. Prüfen Sie den Cache-Schlüssel sowie etwaige gemeldete Treffer, Fehlschläge, Verdrängungen und Überläufe, bevor Sie Tausende äußerer Zeilen ebenso vielen vollständigen Scans zuschreiben. Version 14, Abfrageplanung

Vergleichen Sie außerdem geschätzte und tatsächliche Zeilenanzahlen am frühesten wesentlichen Unterschied. PostgreSQL empfiehlt, diese Schätzungen zu prüfen, und weist darauf hin, dass sich Pläne für Spielzeugtabellen nicht unbedingt auf größere Eingaben übertragen lassen. Addieren Sie nicht die Laufzeiten von Eltern- und Kindknoten: Sie beschreiben überlappende Ausführungsarbeit. EXPLAIN verwenden

Eine Umschreibung wählen, die zur wiederholten Aufgabe passt

Entscheidend ist, wie viel Arbeit jeder Abnehmer mit der Kandidatenmenge hat. Wählen Sie anhand dieser Entscheidungshilfe das erste Experiment aus:

Beobachtete Anforderung Erstes Experiment Bedingung, die erhalten bleiben muss
Jeder Kandidat benötigt dasselbe Maximum pro Thema Einmal gruppieren, dann joinen Eine Zusammenfassungszeile pro Thema
Jeder Abnehmer benötigt einen kleinen indizierten Teil der Quelle NOT MATERIALIZED ausprobieren Das vollständige Kandidatenprädikat beibehalten
Mehrere Anweisungen benötigen eine explizit indizierte Kandidatenrelation Eine indizierte temporäre Tabelle erstellen Dieselbe Sitzung beibehalten und Einrichtungskosten berücksichtigen
Wiederholte Parameter erzielen bereits viele Cache-Treffer Gesamtausführungszeit vergleichen, bevor die Abfrageform geändert wird Sicherstellen, dass die zugrunde liegenden Scans tatsächlich aufwendig sind

Das Ergebnis pro Thema einmal berechnen

Für diese Aufgabe drückt eine gruppierte Zusammenfassung die Wiederverwendung direkt aus. Führen Sie Folgendes nach der Einrichtung in derselben Sitzung aus:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
), topic_max AS (
    SELECT topic_id, max(id) AS highest_id
    FROM candidate
    GROUP BY topic_id
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
JOIN topic_max AS m USING (topic_id);

Diese Umschreibung erzeugt ein Maximum pro Thema und ordnet es jedem Kandidatenartikel zu. PostgreSQL definiert max als den größten Wert unter den Eingabewerten, die nicht null sind. Da die Thema- und ID-Spalten im Beispiel keine Nullwerte enthalten, ist die Gleichwertigkeit einfach: Jeder Kandidat gehört zu einer Gruppe, die ihn selbst enthält. Aggregatfunktionen

Ohne EXPLAIN sind bei der Ausführung der Abfrage 2.000 Ergebniszeilen zu erwarten. Artikel 1 gehört beispielsweise zu Thema 2 und erhält highest_id = 1951; Thema 1 erhält highest_id = 2000. Diese Werte ergeben sich deterministisch aus dem Beispieldatengenerator.

Prüfen Sie die Bedeutung des Ergebnisses, bevor Sie die Abfrage verallgemeinern. Eine Gruppierung nach zusätzlichen Spalten kann mehrere Zusammenfassungszeilen pro Thema erzeugen und die Join-Ausgabe vervielfachen. Wenn Thema-IDs null sein dürfen, muss auch die Gleichheitssemantik erneut geprüft werden. Wenn die Aufgabe den Titel des Artikels mit der höchsten ID erfordert, ist das Zurückverbinden des Maximums mit den Kandidatenzeilen ein zusätzlicher Schritt. Ein separates Maximum des Titels würde eine andere Frage beantworten.

Die Umschreibung ermöglicht einen anderen Plan, erzwingt aber weder einen Hash-Join noch garantiert sie eine Beschleunigung. Vergleichen Sie den gemessenen Aufwand für Gruppierung und Join mit den tatsächlichen inneren Ausführungen der Ausgangsabfrage.

Prüfen, ob Quellindizes Zwischenscans ersetzen können

Ersetzen Sie für ein geeignetes CTE AS MATERIALIZED in der vollständigen Ausgangsabfrage durch AS NOT MATERIALIZED und führen Sie sie erneut aus. So wird eine gemeinsame Optimierung mit der Quellrelation ermöglicht. Dabei können gespeicherte Kandidatenscans durch Lookups in der Quelle ersetzt werden; zugleich kann die Kandidatenberechnung mehrfach erfolgen. Materialisierung von Common Table Expressions

Der Index (topic_id, id) aus dem Beispiel entspricht Gleichheitsbedingungen für die führende Themenspalte und einer Einschränkung der ID-Spalte. Daher ist er ein sinnvoller Index für den Test. Prüfen Sie, ob der tatsächliche Plan ihn verwendet und wie viele Zeilen gescannt werden. PostgreSQL dokumentiert Gleichheit für die führende Spalte und eine Ungleichheitsbedingung für die nächste Spalte als Bedingungen, die einen mehrspaltigen B-Tree-Scan begrenzen. Mehrspaltige Indizes

Behalten Sie id <= 2000 in der Kandidatendefinition bei. Das Maximum über alle 20.000 Quellzeilen zu ermitteln, würde das Ergebnis verändern. Beachten Sie außerdem die dokumentierte Grenze: Bei rekursiven CTEs und solchen mit Seiteneffekten wird NOT MATERIALIZED ignoriert. Verwenden Sie für ein ungeeignetes CTE stattdessen eine unterstützte relationale Umschreibung oder speichern Sie das Ergebnis explizit zwischen. SELECT

Eine indizierte Kandidatenrelation bereitstellen, wenn die Wiederverwendung mehrere Anweisungen umfasst

Ein CTE gilt nur für eine Anweisung. Wenn mehrere Anweisungen dieselben Kandidatenzeilen und explizite Indizes benötigen, bietet eine temporäre Tabelle eine andere Lebensdauer und Indizierungsoption. Führen Sie diese Alternative nach der ursprünglichen Einrichtung aus:

CREATE TEMP TABLE candidate_demo AS
SELECT id, topic_id, title
FROM article_demo
WHERE id <= 2000;

CREATE INDEX candidate_demo_topic_id_idx
    ON candidate_demo (topic_id, id);

ANALYZE candidate_demo;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT c.id, c.title, m.highest_id
FROM candidate_demo AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate_demo AS x
    WHERE x.topic_id = c.topic_id
) AS m;

Indizes für temporäre Tabellen sind ebenfalls temporär, und die Relation gilt für die Sitzung. Berücksichtigen Sie bei der Bewertung der gesamten Aufgabe die Erstellung, den Indexaufbau und die Analyse. Vergleichen Sie nicht nur den abschließenden Lookup mit dem gesamten Vorgang ohne Zwischenspeicherung. CREATE TABLE

Dasselbe Experiment mit node-postgres auf dem Mac erfassen

Um den Abfragepfad der Anwendung zu untersuchen, verwenden Sie eine aktuell unterstützte Node.js-Version und installieren Sie das dokumentierte Paket mit npm install pg. Speichern Sie das vollständige Programm unten in diesem Projekt als diagnose.mjs und führen Sie node diagnose.mjs aus. Das Programm erstellt eigene synthetische Eingaben und hängt daher nicht von der zuvor geöffneten Sitzung psql ab. node-postgres

Das Programm verwendet Ihre vorhandene PostgreSQL-Verbindungsumgebung. node-postgres dokumentiert dieselben Umgebungsvariablen für Verbindungen wie psql, darunter PGHOST, PGPORT und PGDATABASE. Verbindungen herstellen

import pg from 'pg';
const { Client } = pg;
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_demo (
      id integer PRIMARY KEY,
      topic_id integer NOT NULL,
      title text NOT NULL
    );
    INSERT INTO article_demo (id, topic_id, title)
    SELECT g, 1 + (g % 50), 'Article ' || g
    FROM generate_series(1, 20000) AS s(g);
    CREATE INDEX article_demo_topic_id_idx
      ON article_demo (topic_id, id);
    ANALYZE article_demo;
  `);

  const sql = `
    WITH candidate AS MATERIALIZED (
      SELECT id, topic_id, title
      FROM article_demo
      WHERE id <= $1::integer
    )
    SELECT c.id, c.title, m.highest_id
    FROM candidate AS c
    CROSS JOIN LATERAL (
      SELECT max(x.id) AS highest_id
      FROM candidate AS x
      WHERE x.topic_id = c.topic_id
    ) AS m
  `;

  const result = await client.query({
    text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) ${sql}`,
    values: [2000]
  });

  for (const row of result.rows) {
    console.log(row['QUERY PLAN']);
  }
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await client.end();
}

Die Abfragekonfiguration verwendet die dokumentierten Felder text und values. Mit einem name wird auf jeder Verbindung eine vorbereitete Anweisung erstellt; dadurch werden weder Kandidatenzeilen gecacht noch wiederholte innere Ausführungen vermieden. Abfragen

Wenn eine vorhandene Anwendung einen Pool verwendet, führen Sie die Erstellung und Verwendung temporärer Tabellen auf demselben ausgeliehenen Client aus und geben Sie ihn anschließend mit finally frei. Getrennte Aufrufe von pool.query können unterschiedliche Clients verwenden. Ebenso verlangt node-postgres, dass alle Anweisungen einer Transaktion dieselbe Clientinstanz verwenden. Pooling, Transaktionen

Untersuchen Sie bei benannten Anweisungen, deren Verhalten sich mit den Parameterwerten ändert, die Planung separat. PostgreSQL kann generische Pläne oder an bestimmte Parameter angepasste Pläne wählen. Die dokumentierte Prüfmethode ist EXPLAIN EXECUTE für die vorbereitete Anweisung. Eine frisch erklärte, unbenannte Abfrage kann sich daher von einer bereits etablierten Ausführung mit vorbereiteter Anweisung unterscheiden. PREPARE

Feststellen, ob sich die wiederholte Arbeit tatsächlich verringert hat

Verwenden Sie für jeden Vergleich dasselbe Kandidatenprädikat, dieselben ausgewählten Spalten und dieselben Eingabewerte. Führen Sie zunächst einen Durchgang aus und wiederholen Sie vergleichbare Durchgänge, um Schwankungen durch den Cache zu beobachten. Halten Sie Serverversion, Kandidatenanzahl, Ausführungen des inneren Scans, Cache-Verhalten und Gesamtausführungszeit fest. PostgreSQL 17 hat die CTE-Planung mithilfe von Spaltenstatistiken und Sortierreihenfolge verbessert. Halten Sie daher die Version fest, wenn Sie Ergebnisse verschiedener Rechner vergleichen. Version 17

Für einen zweiten Diagnosedurchgang behält EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) die Zeilenanzahlen und die Anweisungsdauer bei, vermeidet dabei aber Zeitmessungen für einzelne Knoten. Die Pufferwerte von Elternknoten umfassen auch die Aktivität der Kindknoten. Addieren Sie daher nicht den gesamten Baum. EXPLAIN ANALYZE führt die Abfrage aus, misst jedoch nicht die Übertragung der Ergebniszeilen an Node.js. EXPLAIN

Behandeln Sie Umschalter des Planers als Experimente. Wenn Sie enable_nestloop ausschalten, werden geschachtelte Schleifen weniger bevorzugt, aber nicht vollständig verhindert. Auch mit enable_material lässt sich nicht jede Materialisierung unterdrücken. Keiner der beiden Schalter belegt, dass die Verwendung der Kandidaten effizienter geworden ist. Abfrageplanung

Schließen Sie mit der Abfrageform ab, die die erforderlichen Zeilen beibehält und den gemessenen Aufwand für die gesamte Aufgabe verringert. Für dieses Maximum pro Thema ist die gruppierte Wiederverwendung das naheliegendste erste Experiment. Behalten Sie indizierte Lookups bei, wenn sie sich als günstig erweisen, und verwenden Sie explizite Zwischenspeicherung, wenn sich ihr zusätzlicher Einrichtungsaufwand bei wiederholter Verwendung lohnt.

Quellen