Planer-Statistiken in PostgreSQL: Statistikziele, korrelierte Spalten und Fehlschätzungen

Worum es geht

Die Dokumentation beschreibt die Eingaben des Planers: reltuples und relpages in pg_class für die Tabellengrösse sowie pg_statistic (lesbar über die View pg_stats) mit den häufigsten Werten jeder Spalte und ihren Häufigkeiten, einem Histogramm der übrigen Werte, dem Null-Anteil und der Anzahl unterschiedlicher Werte. ANALYZE, manuell oder über Autovacuum, tastet die Tabelle ab, um diese Werte zu füllen. Die Anzahl der Einträge für häufigste Werte und Histogramm pro Spalte ist durch default_statistics_target (standardmässig 100) oder ein spaltenbezogenes ALTER TABLE ... ALTER COLUMN ... SET STATISTICS begrenzt. Für mehrere Bedingungen in einer WHERE-Klausel multipliziert der Planer die Selektivitäten, was laut Dokumentation Unabhängigkeit der Bedingungen voraussetzt.

Warum es wichtig ist

Ein Plan wird anhand von Schätzungen gewählt, nicht anhand von Daten. Liegt eine Schätzung um Grössenordnungen daneben, wählt der Planer einen Nested Loop, wo ein Hash Join nötig gewesen wäre, oder einen Index-Scan, der die halbe Tabelle berührt, und die Abfrage ist langsam, obwohl jeder Index existiert. EXPLAIN ANALYZE zeigt die Abweichung als geschätzte gegenüber tatsächlichen Zeilen an dem Knoten, an dem sie beginnt.

So wird es angewendet

• Zuerst die Aktualität prüfen: last_analyze und last_autoanalyze in pg_stat_user_tables. Eine gerade massenhaft geladene Tabelle hat oft überhaupt keine Statistiken.
• Bei einer schiefen Spalte (Statuscodes, wenige sehr grosse Mandanten unter vielen kleinen), deren Schätzung für die seltenen Werte falsch ist, das Ziel nur für diese Spalte erhöhen, etwa SET STATISTICS 1000, dann ANALYZE; die Dokumentation nennt als Kosten mehr Platz in pg_statistic und etwas mehr Planungszeit.
• Für Bedingungen auf korrelierten Spalten (Stadt und Postleitzahl, Bestelldatum und Versanddatum) erweiterte Statistiken anlegen: CREATE STATISTICS s (dependencies, ndistinct, mcv) ON a, b FROM t, dann ANALYZE. Funktionale Abhängigkeiten gelten nur für Gleichheitsbedingungen und IN-Listen mit Konstanten, mcv-Listen erfassen häufige Kombinationen, ndistinct verbessert Schätzungen für GROUP BY.
• Für einen Filter auf einem Ausdruck (lower(email), date_trunc('day', ts)) Ausdrucksstatistiken mit CREATE STATISTICS ON (expr) FROM t anlegen, was die Dokumentation als Nutzen ähnlich einem Ausdrucksindex beschreibt, jedoch ohne dessen Wartungsaufwand.
• Nach jeder Änderung EXPLAIN ANALYZE erneut ausführen und die Änderung nur behalten, wenn sich Schätzungen den tatsächlichen Werten angenähert haben.

Stolpersteine

default_statistics_target global zu erhöhen kostet Platz in pg_statistic und Schätzzeit für jede Spalte bei geringem Nutzen; gezielt die Spalten wählen, die es brauchen. pg_upgrade übernimmt die meisten Statistiken erst ab PostgreSQL 18 und die erweiterten nie, sodass ein Upgrade ein frisches ANALYZE braucht. Ein Prepared Statement kann einen generischen Plan ausführen, dessen Schätzungen den tatsächlichen Parameterwert ignorieren. Eine Stichprobe kann einer Verteilung, die sich stündlich ändert, nicht folgen.