Statistiken und Query-Planer-Cache verstehen: warum Pläne veralten

1. Wie der Optimizer Entscheidungen tatsaechlich trifft

Der Query-Optimizer entscheidet nicht anhand des SQL-Textes, sondern anhand einer geschaetzten Kostenrechnung, die auf Statistiken ueber die tatsaechliche Datenverteilung basiert. Fuer jede moegliche Zugriffsstrategie, etwa Index-Scan gegen Full-Table-Scan, schaetzt der Optimizer die erwartete Anzahl zu lesender Zeilen (Cardinality Estimation) und waehlt die Strategie mit den geringsten geschaetzten Gesamtkosten. Diese Schaetzung ist nur so gut wie die zugrunde liegenden Statistiken.

Genau hier entsteht ein haeufig uebersehenes Problem: Zwei identische Queries auf zwei Tabellen mit unterschiedlicher Datenverteilung koennen voellig unterschiedliche, jeweils optimale Plaene erhalten. Und dieselbe Query auf derselben Tabelle kann nach einem massiven Datenwachstum einen anderen Plan bekommen, weil sich die zugrunde liegenden Statistiken veraendert haben, obwohl sich am Query-Text selbst nichts geaendert hat.

Wer Statistiken und Query-Planer-Cache verstehen will, muss deshalb zwei getrennte Konzepte auseinanderhalten: Statistiken beschreiben die Datenverteilung und werden periodisch aktualisiert. Der Plan-Cache speichert einen einmal berechneten Ausfuehrungsplan zur Wiederverwendung. Beide koennen unabhaengig voneinander veralten, und beide Formen der Veraltung erzeugen dasselbe Symptom: eine Query, die ohne erkennbaren Grund ploetzlich langsamer wird.

2. Was Optimizer-Statistiken konkret enthalten

MySQL und PostgreSQL fuehren fuer jede Tabelle und jeden Index eine Reihe von Kennzahlen: die geschaetzte Gesamtzeilenzahl, die durchschnittliche Zeilengroesse, und vor allem die Kardinalitaet, also die Anzahl unterschiedlicher Werte pro Spalte. Eine Spalte mit nur zwei unterschiedlichen Werten (etwa ein Boolean-Flag) liefert dem Optimizer ein voellig anderes Kostenbild als eine Spalte mit einer Million unterschiedlicher Werte (etwa eine E-Mail-Adresse), selbst wenn beide denselben Datentyp haben.

In PostgreSQL liefert ANALYZE diese Statistiken und speichert sie in der Systemtabelle pg_stats. MySQL mit InnoDB berechnet vergleichbare Werte ueber ANALYZE TABLE und speichert sie, sofern innodb_stats_persistent aktiviert ist, dauerhaft statt nur im Arbeitsspeicher. Ohne persistente Statistiken wuerde MySQL nach jedem Neustart oder bei bestimmten internen Ereignissen neu und teils zufaellig stichproben, was zu inkonsistenten Planentscheidungen zwischen zwei Serverneustarts fuehren kann.

-- PostgreSQL: Statistiken einer Spalte einsehen
SELECT
    attname,
    n_distinct,        -- geschaetzte Anzahl unterschiedlicher Werte
    correlation,        -- physische vs. logische Sortierreihenfolge
    most_common_vals,   -- haeufigste Werte
    most_common_freqs   -- deren relative Haeufigkeit
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

-- Statistiken manuell aktualisieren
ANALYZE orders;

-- MySQL: Tabellenstatistiken pruefen
SELECT
    table_name, cardinality
FROM information_schema.statistics
WHERE table_schema = 'shop' AND table_name = 'orders';

ANALYZE TABLE orders;

3. Histogramme: Genauigkeit jenseits einfacher Zaehlwerte

Ein einzelner Kardinalitaets-Wert reicht nicht aus, um schiefe Datenverteilungen korrekt einzuschaetzen. Eine Spalte status mit den Werten "pending", "shipped" und "cancelled" hat vielleicht nur drei unterschiedliche Werte, aber wenn 95 Prozent aller Zeilen "shipped" sind, ist eine Query mit WHERE status = 'cancelled' deutlich selektiver als eine mit WHERE status = 'shipped'. Ohne Histogramm wuerde der Optimizer beide Faelle faelschlich gleich behandeln.

PostgreSQL speichert dafuer most_common_vals und most_common_freqs fuer die haeufigsten Werte sowie ein separates Histogramm fuer die restliche Verteilung. MySQL 8.0 fuehrte mit ANALYZE TABLE ... UPDATE HISTOGRAM ON spalte eine aehnliche Funktion ein, die vorher komplett fehlte und den Optimizer bei schiefen Verteilungen fundamental blind gemacht hat. Wer Statistiken und Query-Planer-Cache verstehen will, sollte gezielt Histogramme fuer Spalten mit bekannt schiefer Verteilung anlegen, nicht nur pauschal ANALYZE ausfuehren.

-- MySQL 8.0+: Histogramm fuer eine schief verteilte Spalte anlegen
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;

-- Histogramm-Daten einsehen
SELECT
    JSON_PRETTY(histogram)
FROM information_schema.column_statistics
WHERE table_name = 'orders' AND column_name = 'status';

-- PostgreSQL: Statistik-Ziel fuer eine einzelne Spalte erhoehen
-- (mehr Buckets im Histogramm, praeziser bei schiefer Verteilung)
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

4. Wie Statistiken veralten und warum das unbemerkt bleibt

Statistiken sind eine Momentaufnahme zum Zeitpunkt der letzten Erhebung, keine Live-Berechnung. Eine Tabelle, die seit der letzten ANALYZE-Ausfuehrung von 10.000 auf 10 Millionen Zeilen gewachsen ist, hat immer noch die alten Kardinalitaets-Werte hinterlegt, bis eine neue Analyse laeuft. In der Zwischenzeit trifft der Optimizer Entscheidungen auf Basis einer Datenwelt, die es nicht mehr gibt, oft mit dramatischen Folgen: Ein Index-Scan, der bei 10.000 Zeilen sinnvoll war, kann bei 10 Millionen Zeilen zu einem ineffizienten Plan fuehren, wenn der Optimizer die tatsaechliche Selektivitaet nicht kennt.

Besonders tueckisch sind Massenimporte und Batch-Loeschungen ausserhalb des normalen Anwendungsbetriebs, etwa ein einmaliges Daten-Migrationsskript. Diese Operationen aendern die Datenverteilung drastisch, loesen aber in vielen Konfigurationen keine automatische Neuberechnung aus, wenn die Schwellwerte fuer automatische Statistiken-Aktualisierung noch nicht erreicht sind. Wer nach einem grossen Import ploetzlich schlechte Plaene sieht, sollte als Erstes pruefen, wann ANALYZE zuletzt gelaufen ist.

5. Automatische Aktualisierung: autovacuum und InnoDB-Persistent-Statistics

PostgreSQL loest die automatische Aktualisierung ueber den autovacuum-Daemon, der bei Ueberschreiten eines konfigurierbaren Schwellwerts an geaenderten Zeilen automatisch ein ANALYZE ausloest. Die relevanten Parameter autovacuum_analyze_threshold und autovacuum_analyze_scale_factor bestimmen zusammen, ab wie vielen geaenderten Zeilen eine Neuanalyse ausgeloest wird. Bei sehr grossen Tabellen kann der prozentuale Schwellwert (Standard 10 Prozent) dazu fuehren, dass Millionen Zeilen geaendert werden muessen, bevor eine Neuanalyse ueberhaupt startet.

MySQL aktualisiert Statistiken standardmaessig bei bestimmten internen Ereignissen automatisch, etwa nach signifikanten Aenderungen der Tabellengroesse, aber das Verhalten ist weniger transparent konfigurierbar als bei PostgreSQL. In beiden Systemen gilt: Bei sehr grossen, schnell wachsenden Tabellen ist es sinnvoll, die automatischen Schwellwerte gezielt zu verkleinern oder ein manuelles, geplantes ANALYZE nach bekannten Massenoperationen in den Wartungsplan aufzunehmen.

-- PostgreSQL: autovacuum-Schwellwerte fuer eine grosse, volatile Tabelle senken
ALTER TABLE orders SET (
    autovacuum_analyze_scale_factor = 0.02,   -- statt Standard 0.1
    autovacuum_analyze_threshold = 500
);

-- Aktuellen autovacuum-Status pruefen
SELECT
    relname,
    last_autoanalyze,
    n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

6. Query-Planer-Cache: wenn der Plan selbst veraltet

Neben veralteten Statistiken existiert ein zweites, unabhaengiges Problem: der Query-Planer-Cache selbst. PostgreSQL cached bei vorbereiteten Anweisungen (Prepared Statements) den einmal berechneten Ausfuehrungsplan zur Wiederverwendung, um die Planungskosten bei wiederholter Ausfuehrung zu sparen. Das Problem: Ein Plan, der fuer den ersten uebergebenen Parameterwert optimal war, bleibt gecacht, auch wenn ein spaeterer Parameterwert eine voellig andere Zugriffsstrategie erfordern wuerde.

MySQL hatte bis Version 8.0 einen globalen Query Cache, der komplette Ergebnismengen (nicht nur Plaene) zwischenspeicherte, wurde aber wegen massiver Skalierungsprobleme unter Schreiblast in 8.0 komplett entfernt. Der relevante moderne Mechanismus in MySQL ist stattdessen der interne Optimizer-Plan-Cache pro Prepared Statement innerhalb einer Session, konzeptionell aehnlich zu PostgreSQLs Verhalten, aber mit anderer Lebensdauer und anderen Invalidierungsregeln.

7. Bind-Variable-Peeking und Parameter Sniffing

Das Phaenomen, dass ein gecachter Plan fuer einen bestimmten Parameterwert optimal war, aber fuer einen anderen katastrophal ist, heisst Parameter Sniffing (in Oracle-Terminologie auch Bind-Variable-Peeking). PostgreSQL entscheidet ab dem sechsten Ausfuehrungsdurchlauf einer vorbereiteten Anweisung automatisch, ob ein generischer, parameterunabhaengiger Plan oder ein spezifischer, auf den konkreten Wert zugeschnittener Plan guenstiger ist, indem es die geschaetzten Kosten beider Varianten vergleicht.

Trotz dieser Heuristik kann Parameter Sniffing weiterhin auftreten, insbesondere bei stark schiefen Datenverteilungen, wo kein einzelner generischer Plan fuer alle Parameterwerte gut funktioniert. Der pragmatische Workaround ist, bei bekannten Problemfaellen PREPARE gezielt zu vermeiden und stattdessen mit direkt eingebetteten Literalen zu arbeiten, oder in PostgreSQL explizit plan_cache_mode = force_custom_plan fuer die betroffene Session zu setzen.

-- PostgreSQL: Plan-Cache-Verhalten pro Session steuern
SET plan_cache_mode = force_custom_plan;  -- immer neu planen, kein generischer Plan
-- Alternative: force_generic_plan (immer denselben Plan wiederverwenden)
-- Standard: auto (Heuristik nach dem 6. Ausfuehrungsdurchlauf)

-- Aktuelle Einstellung pruefen
SHOW plan_cache_mode;

8. Veraltete Statistiken und Plaene diagnostizieren

Der zuverlaessigste Weg, veraltete Statistiken zu erkennen, ist der Vergleich zwischen der vom Optimizer geschaetzten Zeilenanzahl und der tatsaechlich zurueckgegebenen Zeilenanzahl im Explain-Output. PostgreSQLs EXPLAIN ANALYZE zeigt beide Werte nebeneinander: rows=X (geschaetzt) und actual rows=Y (tatsaechlich). Eine grosse Abweichung, etwa Faktor 10 oder mehr, ist ein klares Signal fuer veraltete oder unzureichend granulare Statistiken.

In MySQL liefert EXPLAIN ANALYZE seit Version 8.0.18 ein aehnliches Feature mit geschaetzten gegen tatsaechliche Kosten und Zeilenzahlen pro Planschritt. Wer Statistiken und Query-Planer-Cache verstehen und systematisch pruefen will, sollte diese Abweichung als festen Bestandteil jeder Performance-Analyse etablieren, nicht nur den reinen Ausfuehrungsplan ohne tatsaechliche Werte betrachten.

9. MySQL und PostgreSQL im Vergleich

Beide Systeme loesen Statistiken und Plan-Caching aehnlich, aber mit unterschiedlichen Mechanismen und unterschiedlicher Konfigurierbarkeit.

Trotz unterschiedlicher Implementierung gilt fuer beide Systeme derselbe Grundsatz: Statistiken muessen zur tatsaechlichen Datenverteilung passen, und ein gecachter Plan muss regelmaessig gegen die aktuelle Realitaet validiert werden, sonst driftet die Optimizer-Entscheidung unbemerkt von der optimalen Loesung weg.

10. Zusammenfassung

Statistiken und Query-Planer-Cache zu verstehen bedeutet, den Query-Optimizer nicht als Blackbox zu behandeln, sondern seine Entscheidungsgrundlage zu kennen: geschaetzte Kardinalitaet, Histogramme fuer schiefe Verteilungen, und ein einmal berechneter Plan, der zur Wiederverwendung gecacht wird. Beide Bausteine koennen unabhaengig voneinander veralten, wenn Datenmengen wachsen oder sich Wertverteilungen verschieben, und beide erzeugen dasselbe Symptom: eine unveraenderte Query, die ohne erkennbaren Grund langsamer wird.

Regelmaessiges ANALYZE, sinnvoll konfigurierte automatische Schwellwerte, und der bewusste Vergleich von geschaetzter gegen tatsaechliche Zeilenzahl im Explain-Output sind die wirksamsten Werkzeuge, um diese Drift fruehzeitig zu erkennen. Wer diese Zusammenhaenge einmal verinnerlicht hat, diagnostiziert scheinbar zufaellige Performance-Regressionen deutlich schneller, weil er weiss, wo er zuerst nachsehen muss.

11. FAQ: Statistiken und Query-Planer-Cache verstehen