Antwort auf die Frage
PostgreSQL führt spaltenweise Histogramme in pg_statistic und geht von statistischer Unabhängigkeit zwischen den Spalten aus, wenn die Selektivität für Mehrspaltenfilter geschätzt wird. Wenn Sie auf stark korrelierte Spalten filtern—wie city und state_code oder make und model in einer Fahrzeugdatenbank—multipliziert der Planner die einzelnen Selektivitäten und schätzt die tatsächliche Zeilenanzahl drastisch zu niedrig. Dieser kardinalitätsfehler führt häufig dazu, dass der Optimierer Nested Loop-Joins gegenüber Hash- oder Merge-Joins bevorzugt, was bei großen Tabellen zu katastrophalen Leistungseinbußen führt.
Um dies zu beheben, erstellen Sie ein erweitertes Statistikobjekt mit CREATE STATISTICS, das multivariate Korrelationsdaten aufbaut. Insbesondere verfolgt der Typ dependencies funktionale Abhängigkeiten zwischen den Spalten, sodass der Planner erkennt, dass das Filtern nach state_code = 'CA' bereits city auf Werte aus Kalifornien einschränkt und den Multiplikationsfehler vermeidet.
-- Erstellen Sie erweiterte Statistiken für korrelierte Spalten CREATE STATISTICS stats_vehicle_make_model ON make, model FROM vehicles; -- Füllen Sie die Statistiken ANALYZE vehicles;
Lebenssituation
Eine Logistikplattform hatte Schwierigkeiten mit einem Dashboard zur Sendungsverfolgung, das eine 50-Millionen-Zeilen shipments-Tabelle mit customers verknüpfte. Die Abfrage filterte nach origin_state und origin_city, wobei 95% der Zeilen für 'Springfield' als Stadt tatsächlich in 'IL' waren, aber der Planner davon ausging, dass nur 2% der Sendungen beide Prädikate unabhängig erfüllten. Er schätzte 500 Zeilen und wählte einen Nested Loop-Join, der Millionen von Kundendatensätzen durchlief und nach 90 Sekunden zeitlich auslief.
Eine in Betracht gezogene Lösung bestand darin, Nested Loop-Joins vollständig über SET enable_nestloop = off in der Sitzung zu deaktivieren. Dies zwang zu einem Hash Join und wurde für diese spezifische Abfrage in 3 Sekunden abgeschlossen, aber es barg erhebliche Risiken: Globale Konfigurationsänderungen wirken sich über Verbindungs-Pools aus und könnten potenziell andere legitime Nested Loop-Pläne, die bei kleinen Tabellen mit indizierten Suchen gut funktionieren, zurückwerfen. Darüber hinaus erforderte diese Umgehung Änderungen auf Anwendungsebene, um den Parameter vor der Abfrage zu setzen.
Eine andere Möglichkeit bestand darin, einen kombinierten Index auf (origin_city, origin_state) zu erstellen. Obwohl dies die Indexauswahl verbesserte, löste es die Unterbewertung der Kardinalität nicht; der Planner dachte immer noch, dass nur wenige Zeilen aus dem Index-Scan hervorgehen würden und behielt die Nested Loop-Strategie bei, indem er sie lediglich schneller über den deckenden Index ausführte. Darüber hinaus verbrauchte der breite kombinierte Index zusätzliche 4 GB Speicherplatz auf der Festplatte und verlangsamte Schreiboperationen auf der schnellen shipments-Tabelle.
Das Team setzte letztendlich erweiterte Statistiken um, indem es CREATE STATISTICS stats_origin_correlation ON origin_city, origin_state FROM shipments ausführte, gefolgt von ANALYZE. Dieser Ansatz erforderte keine Abfrage-Umschreibungen und fügte nur vernachlässigbaren Speicheraufwand hinzu. Nach der Bereitstellung schätzte der Planner korrekt 45.000 Zeilen und wählte einen Hash Join, wodurch die Abfrageverzögerung auf 400 Millisekunden gesenkt wurde, während optimale Pläne für nicht verwandte Arbeitslasten erhalten blieben.
Was Kandidaten oft übersehen
Wie aktualisiert der Befehl ANALYZE erweiterte Statistiken und warum könnte ein Statistikobjekt unmittelbar nach der Erstellung ungenutzt erscheinen?
ANALYZE berechnet erweiterte Statistiken nur, wenn es explizit auf der Ziel-Tabelle aufgerufen wird oder wenn der Autovacuum-Prozess die Tabelle verarbeitet, nachdem das Statistikobjekt existiert. Viele Kandidaten nehmen an, dass CREATE STATISTICS sofortige Auswirkungen auf die Planung hat, aber die Katalogtabellen pg_statistic_ext und pg_statistic_ext_data bleiben bis zum nächsten Analysezyklus leer. Folglich verwendet der Planner weiterhin Einspaltenhistogramme und Unabhängigkeitsannahmen, bis ANALYZE shipments; die multivariaten Daten befüllt. Sie können die Nutzung überprüfen, indem Sie die Ansicht pg_stats_ext auf nicht-null Werte für dependencies oder ndistinct überprüfen.
Was ist der funktionale Unterschied zwischen dependencies und ndistinct in CREATE STATISTICS, und welche Abfragemuster profitieren von jeder?
Dependencies erfassen funktionale Beziehungen, bei denen eine Spalte eine andere bestimmt (z. B. bestimmt zip_code city), was direkt die Schätzungen der Selektivität in der WHERE-Klausel korrigiert. Ndistinct berechnet die genaue Anzahl der einzigartigen Kombinationen für Spaltengruppen, was die Schätzungen für GROUP BY und DISTINCT verbessert, anstatt die Filterselektivität. Kandidaten verwechseln diese häufig und erstellen dependencies, wenn ihre langsame Abfrage ein GROUP BY auf korrelierte Spalten enthält, oder umgekehrt. Für optimale Ergebnisse sollten Sie beide Typen angeben: CREATE STATISTICS stats_complex WITH (dependencies, ndistinct) ON (...).
Warum könnten erweiterte Statistiken hinsichtlich Abfragen mit OR-Bedingungen über korrelierte Spalten versagen?
Erweiterte Statistiken unterstützen derzeit nur UND-Klauseln, bei denen eine Selektivitätsmultiplikation auftritt. Wenn Sie mit ODER filtern (z. B. city = 'Springfield' OR state = 'IL'), berechnet PostgreSQL die Selektivität mit der Formel P(A) + P(B) - P(A ∩ B), und kann keine Abhängigkeitskoeffizienten auf den Schnittmengebegriff anwenden, da die Statistiken die gemeinsame Selektivität für Konjunktionen statt für Disjunktionen nachverfolgen. Kandidaten übersehen häufig diese Einschränkung und versuchen, CREATE STATISTICS zu verwenden, um ODER-basierte Kardinalitätsfehler zu beheben, was Abfrageumschreibungen (z. B. Aufteilung in UNION ALL-Äste) oder partielle Indizes erfordert.