PostgreSQL Query Tuning

PostgreSQL-Abfragen optimieren

Überprüfung der Behauptungen — CHECKS.md. Alles unten bezieht sich auf normales PostgreSQL. Zuerst die Version ermitteln (SHOW server_version): Davon hängen die Standardeinstellungen für BUFFERS (PG18), Skip Scan (PG18) und der Standardwert von hash_mem_multiplier (2.0 ab PG15) ab.

Hauptregel

Nicht blind heilen. Die Reihenfolge ist:

1. Wo die Zeit tatsächlich verbracht wird;
2. Welche Abfrage schuld ist und wie oft sie aufgerufen wird;
3. In welchem Knoten der Ausführungsplan bei der Zeilenschätzung falsch lag;
4. Korrektur;
5. Überprüfung anhand der Seitenanzahl auf Produktionsdatenvolumen.

Index, work_mem und random_page_cost nur ändern, wenn die Diagnose darauf hinweist. Warum: Ohne Profiling wird eine „schreckliche“ Abfrage mit einem Anteil von 1/60 „repariert“ und nichts gewonnen, während ein blind hinzugefügter Index einer langen Abfrage schadet.

Wo die Zeit verbracht wird

• Beschwerde „langsam“: Zuerst die Zeit in der Datenbank mit dem vergleichen, was der Benutzer sieht. Wenn die Abfragen insgesamt ~100 ms dauern, aber über eine Minute geklagt wird, ist die Datenbank unschuldig. Die Abfragen nicht ändern, die Ursache in der Anwendung, im Netzwerk oder Frontend suchen.
• Operation aus vielen Abfragen: Ein Profiling über pg_stat_statements durchführen.
  1. pg_stat_statements.track = all setzen. Standardmäßig steht es auf top, dann sind Abfragen innerhalb von Funktionen nicht sichtbar.
  2. pg_stat_statements_reset() aufrufen. Dafür wird Superuser-Berechtigung oder eine erteilte Berechtigung auf die Funktion benötigt. Der Reset löscht die Statistiken aller, daher nur auf einem Stand oder zu ruhiger Zeit ausführen.
  3. Die Operation einmal ausführen und nach total_exec_time sortieren.
  4. Auf calls, shared_blks_hit + shared_blks_read und toplevel achten. Die Zeit von Zeilen mit toplevel = true enthält bereits die verschachtelten Abfragen. Wenn alles zusammengezählt wird, wird die Zeit doppelt gezählt.
• Nur das korrigieren, was den Hauptanteil ausmacht. Der Gewinn ist nicht größer als dieser Anteil: Eine Teilaufgabe von 1 s aus 60 bringt maximal 1/60.
• Abfragen in pg_stat_statements sind normalisiert: Konstanten werden durch $1 ersetzt, Zeilen können nicht nach Sitzungen getrennt werden, Aggregate (Mittelwert, min/max, stddev) werden gespeichert. Wenn der Eindruck besteht, dass der Plan vom Wert abhängt (großer Kunde vs. kleiner Kunde), die konkreten Werte nehmen und EXPLAIN separat analysieren. Mit pg_stat_activity und EXPLAIN VERBOSE werden Zeilen über queryid verknüpft, dafür wird compute_query_id benötigt (ab PG14; auto aktiviert es, wenn pg_stat_statements geladen ist).
• Über das Log: In log_line_prefix werden %p oder %c und die Zeit %m benötigt. Verschachtelte Abfragen sind nur über auto_explain sichtbar. log_min_duration setzen (Standard -1, also ausgeschaltet), log_analyze und log_nested_statements aktivieren (Standard off; Änderung erfordert Superuser oder GRANT SET, ab PG15). Das Log mit pgBadger analysieren. Für eine einzelne Sitzung reicht SET log_min_duration_statement (Superuser oder GRANT SET ON PARAMETER) und LOAD 'auto_explain' (nur Superuser).

Auf welchen Daten messen

• Keine Schlüsse aus einer Dev-Datenbank mit Spielzeugdaten ziehen. Der Plan hängt von der Tabellengröße und Statistik ab, und eine kleine Datenbank liegt vollständig im Cache. Wenn eine Abfrage auf einer kleinen Datenbank schnell ist, schreiben, dass dies nichts beweist.
• Volumen und Verteilung wie in der Produktion. Wenn in der Produktion personenbezogene Daten vorhanden sind, keine Kopie auf den Stand nehmen. Synthetische Daten gleicher Form generieren: Wie viele Zeilen in den Haupttabellen und wie sie auf Kunden verteilt sind. In die Produktion nur für Statistiken gehen (pg_stat_statements, pg_class).
• Pläne mit Konstanten aus der Produktion nicht in externe Visualisierer einfügen: In den Konstanten können personenbezogene Daten enthalten sein. Lokale Werkzeuge verwenden.

Viele schnelle Aufrufe (N+1)

• Wenn eine Abfrage bereits schnell ist, aber während einer Operation dutzende oder hunderte Male aufgerufen wird, sie nicht beschleunigen. Die Aufgabe in eine einzelne Abfrage für den gesamten Satz umschreiben:

  • Statt einer Schleife von Abfragen über eine Liste von IDs im Code — WHERE id = ANY($1) oder ein Batch;
  • Aufteilungen nach Kategorien — count(*) FILTER (WHERE …);
  • Um Zeilen ohne Daten nicht zu verlieren — LEFT JOIN.

  Warum: Eine Abfrage mit 1 ms ist nicht weiter zu beschleunigen, aber tausend solcher Aufrufe bremsen. Beispielsweise lesen 27 Aufrufe, die in eine Abfrage zusammengefasst wurden, ≈60.000 Seiten statt ≈900.000.

• Wenn es nicht mit einer Abfrage geht, den Datenzugriff im Service überdenken, nicht die Datenbankeinstellungen.

Wie Varianten vergleichen

• Hauptmetrik sind Seiten. EXPLAIN (ANALYZE, BUFFERS) ausführen und shared hit + read im obersten Knoten betrachten: Dort ist bereits die Summe der untergeordneten Knoten enthalten. Hit und read addieren, weil ihr Verhältnis vom Cache abhängt. In PG18 wird BUFFERS mit ANALYZE automatisch aktiviert.
• Zeit ist nur ein Hinweis. Sie wird durch Cache, Last, Hardware und die Messung selbst (ANALYZE) verschoben. Der zweite Durchlauf ist immer schneller und beweist nichts. „Schneller geworden“ nur nach mehreren Durchläufen schreiben.
• Seiten erfassen keine CPU-Last: Sortierung im Speicher, Funktionen, JIT, parallele Pläne. Wenn die Abfrage mehr rechnet als liest, die Zeit separat prüfen.
• Wenn nur Zeilen und Puffer benötigt werden, TIMING FALSE hinzufügen: So entstehen keine Messkosten.

Analyse des Ausführungsplans

1. Abfrageklasse.

   • Kurz: Gibt wenige Zeilen zurück, eine Person wartet auf die Antwort. Beispiel: Objektkarte, Listenseite, Schlüsselsuche. Indexzugriff und Nested Loop erwarten. Für die Dauer der Analyse SET max_parallel_workers_per_gather = 0 setzen.
   • Lang: Liest Tabellen fast vollständig, darüber Aggregation. Beispiel: Berichte, Analysen, Exporte. Seq Scan und Hash Join erwarten, Parallelität ist hier normal.

   Das ist eine Erwartung, kein Gesetz. Wenn der Plan nicht mit der Erwartung übereinstimmt, die Ursache in den Schätzungen suchen, nicht einen Index hinzufügen.

2. Schätzung gegen Tatsache. rows mit actual rows pro Knoten vergleichen. Bei loops > 1 ist der Wert von actual rows pro Durchlauf angegeben, mit loops multiplizieren. Den innersten Knoten reparieren, bei dem die Abweichung zum ersten Mal auftritt. Der Fehler wächst im Plan nach oben und führt zu falschen Zugriffs- und Join-Methoden.

3. Seiten gegen Tabellengröße (für lange Abfragen). ANALYZE ausführen. Dann hit + read des obersten Knotens mit der Summe der relpages der Abfragentabellen aus pg_class vergleichen. Indizes und TOAST separat hinzufügen, wenn die Abfrage sie liest.

   • Vergleichbar oder kleiner: Daten werden einmal gelesen, der Plan ist vernünftig.
   • Um ein Vielfaches größer: Daten werden wiederholt gelesen, z. B. Index Scan innerhalb eines Nested Loop.

   Für kurze Abfragen über einen Index ist dieses Kriterium nicht geeignet.

4. Speicher. Wenn es in BUFFERS temp read/written gibt, Sort auf die Platte geht oder Hash Batches > 1 hat, reicht der Speicher nicht. Was tun — im Abschnitt „Was korrigieren“.

Was korrigieren

Zuerst Schätzungen und die Abfrage selbst, dann den Speicher. Kosten erst als Letztes ändern.

• Funktion über einer Spalte in der Bedingung (starts_at::date = …, date_trunc('day', starts_at) = …, round(col) …). In einen halboffenen Bereich umschreiben starts_at >= $1 AND starts_at < $2. Grenzen für timestamptz in der Zeitzone des Benutzers berechnen, nicht des Servers. Warum: Für einen Ausdruck ohne Statistik nimmt der Optimierer eine feste Selektivität (für = sind das 0,5% der Zeilen) und schätzt stark falsch. Ein Bereich über die Spalte selbst wird anhand der Statistik geschätzt und nutzt einen Index. Einen Index oder eine Statistik für den Ausdruck nur anlegen, wenn das Umschreiben nicht möglich ist.
• Prüfen, welcher Index ausgewählt wurde. Wenn der Index zusammengesetzt ist und die gefilterte Spalte nicht die erste ist, wird er bis PG18 vollständig gelesen. In PG18 hilft Skip Scan (im Plan — Index Searches: N), aber er ist nur vorteilhaft, wenn die erste Spalte wenige verschiedene Werte hat. Sonst wird ein Index benötigt, bei dem diese Spalte die erste ist.
• Zusammengesetzte Fremdschlüssel deklarieren (account_id, user_id), auch wenn die Anwendung alles selbst prüft, und den Join über alle Spalten des Fremdschlüssels mit einfachen Gleichheitsbedingungen schreiben. Der Optimierer schätzt den Join über mehrere Spalten anhand des Fremdschlüssels. Ohne Fremdschlüssel betrachtet er die Spalten als unabhängig und schätzt stark falsch (1000 Zeilen statt 100.000) und wählt dann Nested Loop statt Hash. Wenn der Join den Fremdschlüssel nicht vollständig abdeckt (eine Spalte als Ausdruck oder in WHERE), wird die Schätzung über den Fremdschlüssel nicht angewendet. In der Zieltabelle ist ein eindeutiger Schlüssel über dieselben Spalten erforderlich. Preis: zusätzlicher Schreibaufwand.
• Überflüssiger Join in einer langen Abfrage. Wenn die Tabelle nichts filtert und die Beziehung durch Fremdschlüssel und NOT NULL garantiert ist, sie entfernen: Das bringt oft den größten Gewinn. Nicht darauf verlassen, dass der Optimierer einen inner join zu einer anderen Tabelle selbst entfernt, selbst mit Fremdschlüssel: Er entfernt LEFT JOIN zu einem eindeutigen Schlüssel, dessen Spalten die Abfrage nicht liest, und ab PG18 die Selbstverbindung einer Tabelle über einen eindeutigen Schlüssel. Ohne Garantie durch Fremdschlüssel und NOT NULL einen solchen Join nicht ändern: Das Ergebnis ändert sich.
• Schätzungen sind falsch, und die Abfrage kann nicht umgeschrieben werden: Erweiterte Statistiken anlegen oder ALTER TABLE … ALTER COLUMN … SET STATISTICS n setzen (Standard 100).
• Nicht genügend Speicher (Temp-Dateien, Batches > 1). work_mem oder hash_mem_multiplier für die Sitzung oder Rolle erhöhen (SET, ALTER ROLE … SET), aber nicht global: work_mem wird pro Knoten in jeder Sitzung zugewiesen. Das Limit für Hash ist work_mem × hash_mem_multiplier. Standardmäßig sind das 4MB × 2.0 = 8MB; vor PG15 war der Multiplikator 1.0. Mit SHOW prüfen und das endgültige Limit berechnen.

Hypothese „Der Optimierer hat sich geirrt“

• In einer Transaktion prüfen: BEGIN; SET LOCAL enable_nestloop = off; (oder enable_seqscan), dann EXPLAIN (ANALYZE, BUFFERS), dann ROLLBACK. Nach Seiten vergleichen — so ist zu sehen, ob es einen besseren Plan gibt.
• enable_ weder in der Konfiguration noch im Service-Code belassen.* Sie raten dem Optimierer nur von einem Knoten ab und betreffen alle Abfragen. Die Dokumentation nennt sie ein grobes temporäres Mittel.
• Dann zwei Wege:
  • Zeilenschätzungen sind falsch — Schätzungen korrigieren (siehe „Was korrigieren“).
  • Schätzungen sind richtig, aber der gewünschte Plan wird bei mehreren Abfragen systematisch nicht ausgewählt — Kosten ändern. Bei SSD oder wenn die Datenbank fast vollständig im Cache liegt, random_page_cost von 4.0 auf 1.1–2 senken, aber nicht unter seq_page_cost (1.0). In effective_cache_size eine realistische Schätzung setzen: shared_buffers plus Betriebssystem-Cache (Standard 4GB). Für die gesamte Datenbank nur nach Überprüfung an mehreren Abfragen ändern.

Außerhalb des Skills

• pg_query_state — benötigt einen gepatchten Kernel.
• plprofiler und plpgsql_check — für Logik in PL/pgSQL; hier liegt die Logik in der Anwendung.
• Methoden, die kommerzielle PostgreSQL-Builds erfordern.