ZrobDev/sql-tuning

SQL-Tuning (PostgreSQL zuerst)

Messen, bevor Sie etwas ändern. Eine Tuning-Maßnahme ohne Vorher/Nachher-Plan und Zeitmessung ist eine Vermutung.

Vorgehen

1. Das Ziel finden: sortieren nach Gesamtkosten, nicht nach dem einzelnen langsamsten Aufruf (siehe Triage-Schleife).
2. Die Baseline erfassen: EXPLAIN (ANALYZE, BUFFERS) <query> auf produktionsnahen Daten. Kleine Entwicklungsdaten wählen andere Pläne aus.
3. Den Knoten identifizieren, an dem Zeit oder Zeilen falsch liegen (siehe Leseregeln unten). Nur diesen Knoten beheben.
4. Die gleiche Planerfassung erneut durchführen. Die Änderung nur behalten, wenn sie sowohl bei Zeit als auch bei Buffers gewinnt.
5. Den Index oder die Umformulierung und den Grund neben der Schema-Migration dokumentieren.

EXPLAIN ANALYZE von INSERT/UPDATE/DELETE in BEGIN; ... ROLLBACK; einschließen, weil ANALYZE die Anweisung tatsächlich ausführt.

Plan lesen

• Von unten nach oben lesen, innere Knoten zuerst. Die actual time pro Knoten ist pro Schleifendurchlauf; mit loops multiplizieren, um die tatsächlichen Kosten zu erhalten.
• rows (Schätzung) mit actual rows vergleichen. Ein Unterschied vom Faktor 10 oder mehr ist weitaus häufiger die Ursache als ein fehlender Index: ANALYZE <table> ausführen, das Spaltenstatistik-Ziel erhöhen oder erweiterte Statistiken (CREATE STATISTICS ... (dependencies, ndistinct)) für korrelierte Spalten hinzufügen.
• Rows Removed by Filter viel größer als zurückgegebene Zeilen bedeutet, dass der Index (oder dessen Fehlen) für dieses Prädikat nicht selektiv ist.
• Buffers: shared read ist Festplatte oder OS-Cache; shared hit sind shared_buffers. Hohe Reads bei wiederholten Ausführungen bedeutet, dass der Working Set nicht passt.
• Sort Method: external merge Disk: bedeutet, dass work_mem für diese Sortierung zu klein war. Es pro Sitzung oder pro Rolle erhöhen, nicht global, weil es sich pro Sortierknoten pro Verbindung multipliziert.
• Nested Loop mit einer großen inneren loops-Anzahl und einer nicht indizierten inneren Seite ist der klassische langsame Join. Hash Join mit Spilling (Batches: >1) ist die gleiche work_mem-Problematik.
• Seq Scan ist korrekt, wenn die Abfrage einen großen Teil der Tabelle liest. Fügen Sie keinen Index hinzu, um einen Seq Scan zu entfernen, der 20 % der Zeilen zurückgibt.
• Heap Fetches bei einem Index Only Scan bedeutet, dass die Visibility Map veraltet ist; führen Sie VACUUM auf der Tabelle aus.

Index auswählen

Nur den gemessenen Prädikats-, Join- oder ORDER-BY-Index erstellen. Jeder Index kostet Schreibzeit, WAL und Vakuum-Arbeit.

• btree: Standard für Gleichheit, Bereich, Sortierung. Spaltenreihenfolge: Gleichheitsspalten zuerst, dann die Bereichs- oder Sortierspalte. Skip Scan in PG 18 hilft in einigen Fällen mit fehlender führender Spalte, aber planen Sie nicht darum herum.
• Partial (WHERE status = 'open'): wenn Abfragen auf einen kleinen, stabilen Bereich abzielen (offene Elemente, noch nicht verarbeitete Zeilen). Das WHERE der Abfrage muss das Index-Prädikat implizieren.
• Covering (INCLUDE (col)): wenn ein Index-Only-Scan Heap-Fetches für eine heiße Abfrage entfernen würde. Die eingebundenen Spalten sind keine durchsuchbaren Schlüssel.
• Expression (ON (lower(email)), ON ((payload->>'id'))): wenn die Abfrage eine Funktion auf die Spalte anwendet. Die Abfrage muss den identischen Ausdruck verwenden.
• BRIN: für riesige Append-Only-Tabellen, bei denen die Spalte mit der physischen Reihenfolge korreliert (Zeitstempel, Serien-IDs). Winziger Index, grobe Beschneidung. Prüfen Sie pg_stats.correlation nahe 1; ist sie niedrig, beschneidet BRIN nicht.
• GIN: jsonb-Enthaltensein, Arrays, Volltext. GiST: Bereiche, Geometrie, Ausschluss-Constraints.
• Index auf Live-Tabellen mit CREATE INDEX CONCURRENTLY erstellen; dies kann nicht in einem Transaktionsblock ausgeführt werden, und ein fehlgeschlagener Lauf hinterlässt einen INVALID-Index, der gelöscht werden muss.
• Ungenutzte Indizes nur löschen, nachdem pg_stat_user_indexes.idx_scan über einen vollständigen Geschäftszyklus (Monatsende für Finanzworkloads) und auch auf Replicas geprüft wurde.

Sargability

Ein Prädikat kann einen Index nur verwenden, wenn die indizierte Spalte allein auf einer Seite steht.

• WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' statt WHERE extract(year from created_at) = 2026 oder date_trunc(...) = ....
• WHERE col = $1 mit passenden Typen; numeric_col = '5'::text-artige Casts auf der Spaltenseite blockieren den Index. Casten Sie den Parameter, nicht die Spalte.
• Führendes Wildcard LIKE '%x' vermeiden; pg_trgm GIN oder Volltext-Suche verwenden.
• OR über verschiedene Spalten verlieren oft den Index; versuchen Sie UNION ALL aus zwei indizierten Zweigen.
• NOT IN (Unterabfrage) ist NULL-feindlich und langsam; NOT EXISTS verwenden.
• EXISTS für Semi-Joins gegenüber DISTINCT nach einem Join bevorzugen, weil es beim ersten Treffer stoppt und keine Zeilen dupliziert.

Window-Funktionen vs. Self-Joins

Verwenden Sie eine Window-Funktion, wenn das Ziel ein Wert pro Zeile aus verwandten Zeilen derselben Menge ist: laufende Summen, Rang, Lag/Lead, „letzte Zeile pro Schlüssel“ (row_number() OVER (PARTITION BY k ORDER BY ts DESC) = 1), weil ein Scan und eine Sortierung einen Join der Tabelle mit sich selbst ersetzen. Verwenden Sie einen Self-Join oder LATERAL nur, wenn die zweite Seite einen eigenen Filter oder eine Index-Abfrage benötigt (Top-N pro Schlüssel aus einer großen Tabelle: JOIN LATERAL (SELECT ... ORDER BY ts DESC LIMIT 3) mit einem Index auf (k, ts DESC)). DISTINCT ON (k) ... ORDER BY k, ts DESC ist die kurze PostgreSQL-Form für „letzte pro Schlüssel“.

CTEs

Seit PG 12 wird ein nicht-rekursives, nebenwirkungsfreies, einmal referenziertes CTE wie eine Unterabfrage inlined. Schreiben Sie standardmäßig für Lesbarkeit.

• AS MATERIALIZED hinzufügen, wenn das CTE teuer ist und mehrfach referenziert wird, oder wenn verhindert werden muss, dass der Optimierer ein schlechtes Prädikat hineindrückt.
• AS NOT MATERIALIZED hinzufügen, um das Inlining eines mehrfach referenzierten CTE zu erzwingen, wenn der äußere Filter es billig macht.
• Datenmodifizierende CTEs werden immer einmal ausgeführt; sie werden nicht wegoptimiert.

pg_stat_statements Triage-Schleife

Erfordert pg_stat_statements in shared_preload_libraries (Neustart) und CREATE EXTENSION.

1. Sortieren nach total_exec_time DESC (auch shared_blks_read, temp_blks_written, rows prüfen). Eine 5-ms-Abfrage, die 40 Millionen Mal aufgerufen wird, schlägt eine 30-Sekunden-Nacht-Abfrage.
2. Die Top 3 nehmen. Für jede einen echten Parametersatz besorgen (die Statistiken sind normalisiert) und Schritt 2 des Vorgehens ausführen.
3. Beheben, deployen, dann SELECT pg_stat_statements_reset(<userid>, <dbid>, <queryid>) nur für diese Abfrage ausführen, ein repräsentatives Fenster abwarten, vergleichen.
4. Wiederholen, bis der oberste Eintrag ein gerechtfertigter Kostenpunkt ist. Dann aufhören. Kombinieren mit auto_explain (log_min_duration, log_analyze sparsam), um Pläne für Abfragen zu erfassen, die Sie nicht reproduzieren können.

PL/pgSQL nur, wenn es sich lohnt

Verwenden Sie eine Funktion oder Prozedur, wenn sie Roundtrips für zeilenweise Logik vermeidet, die nicht set-basiert umsetzbar ist, eine Trigger-Invariante erzwingt oder eine Multi-Statement-Transaktion kapselt. Verwenden Sie sie nicht, um über Zeilen zu loopen und jeweils ein UPDATE auszuführen: schreiben Sie es als ein UPDATE ... FROM oder INSERT ... SELECT um. Markieren Sie reine Funktionen korrekt als IMMUTABLE/STABLE (ein falsches Label führt zu falschen gecachten Ergebnissen oder blockiert das Inlining). Bevorzugen Sie einfache SQL-Funktionen, wo möglich, damit der Optimierer sie inlinen kann.

N+1 und Paginierung

• N+1: eine Abfrage pro übergeordneter Zeile aus Anwendungs- oder ORM-Code. Beheben mit einem einzelnen WHERE id = ANY($1)-Batch, einem Join oder der Eager-Load-Option des ORMs. Erkennen über pg_stat_statements: eine normalisierte Anweisung mit calls nahe der Anzahl der übergeordneten Zeilen.
• OFFSET-Paginierung liest und verwirft alle übersprungenen Zeilen, daher kostet Seite N O(N). Verwenden Sie Keyset: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50 mit einem Index auf (created_at DESC, id DESC). Die Tiebreaker-Spalte ist zwingend erforderlich, sonst werden Zeilen übersprungen oder wiederholt. Keyset kann nicht zu Seite N springen; verwenden Sie OFFSET nur für kleine, begrenzte Mengen.
• count(*) auf großen Tabellen ist ein vollständiger Scan; zeigen Sie ungefähre Anzahlen (reltuples) oder deckeln Sie mit SELECT count(*) FROM (SELECT 1 ... LIMIT 10001).

Bulk-ETL-Hinweise

Batch-Läden mit COPY (oder INSERT ... SELECT) statt Zeilen-Inserts; sekundäre Indizes nur bei großen Ersteinladungen löschen oder zurückstellen, dann ANALYZE nach dem Laden. Große UPDATEs blähen Tabellen auf: nach Schlüsselbereichen batchen, pro Batch committen und autovacuum nachkommen lassen.

Andere Dialekte (kurz)

• BigQuery: Kosten sind abgerechnete Bytes, nicht Zeilen. Partitionieren Sie nach einem Datums-/Zeitstempel-Spalte und filtern Sie mit einem Literal oder Parameter, damit das Pruning greift; clustern Sie auf den nächsthäufig gefilterten Spalten. SELECT * berechnet jede Spalte; nennen Sie Spalten. Verwenden Sie den Dry Run, um Bytes vor der Ausführung zu sehen. LIMIT reduziert nicht die abgerechneten Bytes.
• Snowflake: Pruning verwendet Min-/Max-Metadaten von Mikropartitionen, filtern Sie daher nach Spalten, deren Werte beim Laden natürlich sortiert sind; prüfen Sie „Partitions scanned vs total“ im Query-Profil. Fügen Sie einen Clustering-Key nur für sehr große Tabellen mit einem stabilen Filtermuster hinzu, da Reclustering Credits kostet. Vergrößern Sie die Warehouse-Größe bei Spilling (local/remote spill im Profil), für Warteschlangen/Concurrency skaliert man horizontal (multi-cluster). Auto-Suspend sollte kurz sein.

Aktuelle Versionen und Dokumentationslinks: references/landscape-2026-11.md. Lehren aus der Praxis: references/gotchas.md.