AQO - Adaptive Abfrageoptimierung in PostgreSQL

Moderne DBMS verwenden bei der Durchführung von Abfragen das Kostenoptimierungsmodell - basierend auf den in den Konfigurationsdateien gespeicherten Koeffizienten und den gesammelten Statistiken berechnen sie den „Preis“ des Empfangs und das Volumen der resultierenden Zeilensätze. Wenn Abfragen erneut ausgeführt werden, werden Kosten und Selektivität neu berechnet. Sie können eine Abfrage ausführen und die tatsächlichen Werte dieser Parameter anzeigen. Während der (Standard-) Neuplanung verwendet der DBMS-Optimierer diese Informationen jedoch in keiner Weise.



Was aber, wenn der Optimierer die tatsächlichen Werte für Kosten, Selektivität und andere erforderliche Parameter für die Ausführung von Abfragen beibehält und sich bei Wiederholung nicht nur auf die standardmäßig gesammelten Statistiken konzentriert, sondern auch auf diejenigen, die nach der vorherigen Ausführung gespeichert wurden?



Dies wird als adaptive Abfrageoptimierung bezeichnet und ist vielversprechend. Einige DBMS verwenden solche Technologien bereits.



Unternehmen Postgres Professional arbeitet seit mehreren Jahren an der AQO-Erweiterung für PostgreSQL, die (in irgendeiner Form) adaptive Optimierung implementiert. Die Arbeiten sind noch im Gange, aber es gibt bereits etwas zu testen.



Schauen wir uns zunächst den Themenbereich der Abfrageoptimierung genauer an.

Warum ein Planer einen suboptimalen Plan auswählen kann

SQL-Abfragen können auf verschiedene Arten ausgeführt werden. Wenn beispielsweise zwei Tabellen verknüpft sind, kann dies auf verschiedene Arten erfolgen - mithilfe verschachtelter Schleifen, Zusammenführen und Hashing. Je mehr Tabellen an der Abfrage beteiligt sind, desto mehr Variationen ihrer Verknüpfungen. Die Aufgabe des Planers besteht darin, aus vielen Variationen den Ausführungsplan der Abfrage mit den niedrigsten Kosten auszuwählen.



Wie bereits erwähnt, verwenden die Scheduler vieler DBMS in ihrer Arbeit statistische Informationen, die entweder automatisch oder manuell erfasst werden. Der Planer berechnet anhand dieser Statistiken die geschätzten Kosten.



Im Allgemeinen funktionieren die Planer moderner DBMS in den meisten Situationen gut. In einigen Fällen kann der gewählte Plan jedoch weit vom Optimum entfernt sein.



Beispielsweise,Das Fehlen aktueller statistischer Informationen führt dazu, dass sich der Planer bei seiner Arbeit an (höchstwahrscheinlich) falschen Daten zur Anzahl der Zeilen in den zu verbindenden Tabellen orientiert. Übermäßiges Understatement (oder Übertreiben) der Kardinalität führt zur Auswahl nicht optimaler Methoden für den Zugriff auf Daten in Tabellen.



Ein weiterer wichtiger Grund kann das Fehlen notwendiger Indizes sein . In Ermangelung von Indizes hat der Scheduler eine begrenzte Auswahl an Datenzugriffsmethoden.



Verwendung abhängiger (korrelierter) Bedingungenkann sich auch negativ auf den Betrieb des DBMS auswirken. Der Scheduler geht (standardmäßig) davon aus, dass alle Bedingungen in einer Abfrage unabhängig voneinander sind, dh der Wert einer Bedingung wirkt sich in keiner Weise auf die andere aus. Dies ist nicht immer der Fall. Wenn abhängige Bedingungen verwendet werden (z. B. Postleitzahl und Stadt), berechnet der Planer auch die falschen Kosten und die falsche Kardinalität der Verbindungen.



Die Verwendung von Funktionen in Bedingungen kann sich auch auf den Scheduler auswirken . Eine Funktion für den Planer ist eine „Black Box“. Er weiß nicht, wie viele Zeilen die Funktion zurückgeben wird, was auch zu fehlerhaften Plankosten führen kann.

Möglichkeiten, die Arbeit des Schedulers zu beeinflussen

Aktuelle Statistiken sind eine unabdingbare Voraussetzung für die angemessene Arbeit des Schedulers. Stellen Sie zunächst sicher, dass das System so konfiguriert ist, dass regelmäßig statistische Informationen erfasst werden.

Es gibt verschiedene Möglichkeiten, um die oben beschriebenen Situationen zu beheben und dem Planer bei der Auswahl der optimalsten Abfrageausführungspläne zu helfen.



Ohne Indizes hat der Scheduler nur eine Möglichkeit, Daten zu erhalten - das sequentielle Scannen von Tabellen (und dies ist nicht immer schlecht und teuer). In einigen Fällen kann das Erstellen der erforderlichen Indizes den Datenzugriff beschleunigen. Sie müssen nicht die gesamte Tabelle scannen. Die Verwendung von Indizes (Suche nach dem Notwendigen, Erstellung, Pflege) ist jedoch nicht kostenlos. Idealerweise sollten sie genau dort eingesetzt werden, wo sie benötigt werden. Und wo nicht benötigt - nicht verwenden.



Wenn korreliert mit Join - Bedingungen in Abfragen können Sie erzeugen eine erweiterte Statistik- den Optimierer explizit auffordern, dass die Bedingungen miteinander zusammenhängen. Dazu muss der DBA (oder Entwickler) seine Daten gut kennen und abhängige Bedingungen in Abfragen verfolgen, da die Anzahl der Kombinationen abhängiger Spalten im Voraus schwer vorherzusagen ist. Erweiterte Statistiken müssen für jede dieser Optionen manuell erstellt werden.



Beim Erstellen einer Funktion können Sie die ungefähren Ausführungskosten und / oder eine Schätzung der Anzahl der von der Funktion zurückgegebenen Zeilen angeben . In Version 12 wurde es möglich, Hilfsfunktionen zu verwenden, um die Schätzungen des Planers in Abhängigkeit von den Argumenten zu verbessern. Dies ist auch ein manueller Weg, der nicht immer ein optimales Ergebnis liefert.



Wenn alles andere fehlschlägt, können Sie die Abfrage manuell neu schreibenVerwenden Sie beispielsweise materialisierte Ansichten, Common Table Expressions (CTE). Oder um die Anforderungen des Themenbereichs zu klären und möglicherweise die Logik der Anforderung radikal umzuschreiben.



Und es ist eine andere Art von „Hinweise“ an den Scheduler - adaptive Abfrageoptimierung ( a daptive q uery o ptimization). Die Idee dieser Methode ist, dass nach der Ausführung der Abfrage echte statistische Informationen gespeichert werden und der Optimierer sich darauf verlassen kann, wenn die angegebene (oder ähnliche) Abfrage wiederholt wird.



Das DBMS Postgres Pro Enterprise ist eine Erweiterung für die adaptive Abfrageoptimierung mit dem Namen AQO... Diese Erweiterung ist auf github veröffentlicht: github.com/postgrespro/aqo , Sie können sie mit Vanilla PostgreSQL ausprobieren, mehr dazu weiter unten.

Das Funktionsprinzip des Moduls

Das AQO-Modul verwendet in seiner Arbeit maschinelles Lernen. Weitere Informationen zum Funktionsprinzip finden Sie in dem Artikel von Oleg Ivanov über maschinelles Lernen zur Steigerung der PostgreSQL-Leistung und ausführlicher in der Präsentation Adaptive Query Optimization (Bericht auf YouTube ).



Das Wesentliche dieser Methode wird im Folgenden kurz beschrieben:



Um die Kosten zu bewerten, benötigt der Planer eine Bewertung der Kardinalität, und dafür ist wiederum eine Bewertung der Selektivität der Bedingungen erforderlich.



Für einfache Bedingungen (wie „Attribut = Konstante“ oder „Attribut> Konstante“) verfügt der Scheduler über ein Modell, anhand dessen er die Selektivität schätzt. Dazu verwendet er statistische Informationen: die Anzahl der eindeutigen Attributwerte, Histogramme usw.

Für Bedingungen, die aus einfachen Elementen bestehen, die logische Verknüpfungen verwenden, verwendet der Planer einfach zu berechnende Formeln:

• sel (nicht A) = 1 - sel (A)
• sel (nicht A) = 1 - sel (A)
• sel (A und B) = sel (A) * sel (B)
• sel (A oder B) = sel (nicht (nicht A und nicht B)) = 1 - (1 - sel (A)) * (1 - sel (B))

Diese Formeln setzen die Unabhängigkeit (Unkorrelation) der Bedingungen A und B voraus, weshalb wir falsche Schätzungen erhalten, wenn diese Annahme verletzt wird.

AQO verkompliziert die Formel: führt für jede einfache Bedingung einen eigenen Koeffizienten ein. Unter Verwendung von maschinellem Lernen (unter Verwendung der Regression des nächsten Nachbarn) passt AQO diese Koeffizienten so an, dass die durch die Formel berechnete Selektivität am besten mit der tatsächlichen Selektivität übereinstimmt, die AQO zuvor beobachtet hat.



Dazu speichert das Modul:

• , ;
• .

AQO unterscheidet in seiner Arbeit zwischen Bedingungen bis hin zu Konstanten. Dies ermöglicht es, die Komplexität des zu lösenden Problems zu verringern, und außerdem gehen in den meisten Fällen immer noch keine Informationen verloren: AQO "sieht" den Wert der Konstante nicht, sondern "sieht" die Selektivität der Bedingung.

Die Situation, in der der Verlust immer noch auftritt, sind die Bedingungen, die unabhängig von den spezifischen Werten als Konstante bewertet werden. Beispielsweise kann der Planer für einige Bedingungen keine vernünftigen Schätzungen vornehmen und wählt eine Standardkonstante (zum Beispiel wird die Selektivität der Bedingung "Ausdruck1 = Ausdruck2" immer mit 0,005 und "Ausdruck1> Ausdruck2" mit 1/3 bewertet).



Somit kann AQO die Bewertung der Selektivität komplexer Bedingungen verbessern (und infolgedessen die Bewertung der Kosten, was zur Wahl eines angemesseneren Umsetzungsplans führen kann).

Modulinstallation

Um die Funktionalität des Moduls unter Vanilla PostgreSQL zu testen, müssen Sie einen speziellen Patch verwenden und das System dann aus dem Quellcode erstellen. Weitere Informationen finden Sie in der README-Datei auf github.



Wenn Postgres Pro Enterprise verwendet wird, wird die Installation des AQO-Moduls im Standardmodus fortgesetzt:



shared_preload_libraries = 'aqo'



Danach können Sie eine Erweiterung in der erforderlichen Datenbank erstellen.

Datenbankvorbereitung

Schauen wir uns ein konkretes Beispiel an, wie das AQO-Modul in einer Demo-Datenbank funktioniert . Wir werden eine große Datenbank mit Informationen zu Flügen für das Jahr von September 2016 bis September 2017 verwenden.



Erstellen wir zunächst eine Erweiterung:

CREATE EXTENSION aqo;

Deaktivieren Sie als Nächstes die parallele Abfrageverarbeitung, damit die Anzeige paralleler Pläne nicht von der Hauptaufgabe ablenkt:



max_parallel_workers_per_gather = 0;



Damit der PostgreSQL-Scheduler mehr Optionen zum Verknüpfen von Tabellen bietet, erstellen Sie zwei Indizes:

CREATE INDEX ON flights (scheduled_departure );
CREATE INDEX ON ticket_flights (fare_conditions );

Bei der Analyse der Ergebnisse konzentrieren wir uns auf den Wert von BUFFERS als die Anzahl der Seiten, die gelesen werden müssen, um die Aufgabe auszuführen. Schauen wir uns auch die Ausführungszeit an (aber die Zeit auf einem geladenen System und einem Heim-Laptop kann sehr unterschiedlich sein).



Erhöhen wir den Puffercache und work_mem, damit alle Arbeiten im RAM ausgeführt werden:



shared_buffers = '256MB';

work_mem = '256MB';

Verwenden des AQO-Moduls

Wir werden eine Anfrage formulieren: Es ist erforderlich, Passagiere zu empfangen, die ab einem bestimmten Datum eine Business Class geflogen sind und spätestens mit einer Stunde verspätet angekommen sind.

Lassen Sie uns die Anforderung ohne Verwendung von AQO ausführen (im Folgenden wurde ein Teil der Informationen, die das Verständnis der Funktionsweise des Moduls nicht beeinträchtigen, aus den Plänen entfernt):

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT t.ticket_no
  FROM flights f
   	JOIN ticket_flights tf ON f.flight_id = tf.flight_id
   	JOIN tickets t ON tf.ticket_no = t.ticket_no
 WHERE f.scheduled_departure > '2017-08-01'::timestamptz
   AND f.actual_arrival < f.scheduled_arrival + interval '1 hour'
   AND tf.fare_conditions = 'Business';

Und sehen wir uns den resultierenden Plan an: In diesem Fall hat der Planer den optimalen Plan betrachtet, bei dem wir zuerst mithilfe eines Bitmap-Scans ( ) eine Reihe von Zeilen aus der Flugtabelle erhalten , die wir (einen Knoten ) mit einer Reihe von Zeilen aus der Tabelle ticket_flights haben, die unter Verwendung des Index erhalten wurden scan ( ). Das Ergebnis wird als externer Satz von Zeilen für die endgültige Verbindung durch eine verschachtelte Schleife (Knoten ) verwendet. Der innere Satz für diesen Join wird durch einen exklusiven Index-Scan der Tickets ( ) -Tabelle erhalten . Die "sperrigste" Operation besteht darin, das interne Rowset für die verschachtelte Schleife abzurufen - 106 205 Puffer werden darin eingelesen.



Nested Loop (rows=33210) (actual rows=31677)

  Buffers: shared hit=116830 read=1

  ->  Hash Join (rows=33210) (actual rows=31677)

        Hash Cond: (tf.flight_id = f.flight_id)

        ->  Index Scan ... on ticket_flights tf  

              Index Cond: fare_conditions = 'Business'

        ->  Hash

              ->  Bitmap Heap Scan on flights f (rows=8331) (actual rows=7673)

                    Recheck Cond: scheduled_departure > '2017-08-01'

                    Filter: actual_arrival < scheduled_arrival + '01:00:00'::interval

                    ->  Bitmap Index Scan on ... [flights]

                          Index Cond: scheduled_departure > '2017-08-01'

                          Buffers: shared hit=44 read=1

  ->   Index Only Scan  ... on tickets t (rows=1 width=14) (actual rows=1 loops=31677)

        Index Cond: (ticket_no = tf.ticket_no)

        Buffers: shared hit=106205

 Planning Time: 9.326 ms

 Execution Time: 675.836 ms



Bitmap Heap Scan on flightsHash JoinIndex Scan ... on ticket_flightsNested LoopIndex Only Scan ... on tickets





Dieser Plan kann als relativ gut bezeichnet werden, da eine verschachtelte Schleife eine relativ kleine Anzahl von Zeilen in der äußeren Menge verbindet.



Jetzt führen wir ein Experiment durch und sehen, wie sich der vorgeschlagene Plan abhängig von der Änderung der Daten in der Anfrage ändern wird (oder nicht). Daten werden so ausgewählt, dass der Zeilenbereich der Flugtabelle, der die Bedingung erfüllt, konsistent vergrößert wird, was zu einem Planungsfehler bei der Bewertung der Kardinalität des Zugriffs auf diese Tabelle führt. Im obigen Plan können Sie sehen, dass der Optimierer beim ersten Datum fast nicht in der Kardinalität verwechselt wird ( ). Ersetzen Sie die folgenden Daten in der Anfrage:Bitmap Heap Scan on flights f (rows=8331) (actual rows=7673)

• 2017-04-01
• 2017-01-01
• 2016-08-01

Und sehen wir uns das Ergebnis an:





Zusammenfassend funktioniert der Planer ohne Verwendung des AQO-Moduls wie folgt:



Schauen wir uns das Ergebnis noch einmal an:

Wenn AQO aktiviert ist, erkennt der Scheduler schnell, dass er von einer verschachtelten Schleifenverknüpfung und Indexverwendung zu einer Hashverknüpfung und einem sequentiellen Scan wechseln muss.

Zusammenfassen

Die Verwendung des AQO-Moduls zur adaptiven Abfrageoptimierung hat sowohl Vor- als auch Nachteile.



Einer der Vorteile der Verwendung eines Moduls besteht darin, dass Sie abhängige Bedingungen in Abfragen nicht verfolgen müssen. In einigen Fällen kann sich die Geschwindigkeit der Abfrageausführung erhöhen. Es gibt verschiedene Modi für die Verwendung des Moduls. Mit AQO können Sie beispielsweise nur bestimmte Arten von Abfragen optimieren.



Unter den Nachteilen des Moduls können wir zusätzliche Gemeinkosten für die Schulung und das Speichern von Statistiken in den Modulstrukturen herausgreifen. Die vom Modul gesammelten statistischen Informationen werden nicht an Replikate übertragen.



Das AQO-Modul ist keine „Silberkugel“ aller möglichen Probleme des Schedulers. In einigen Situationen kann das Modul beispielsweise erweiterte Statistiken ersetzen (wenn es nicht von Hand erstellt wurde) oder irrelevante Statistiken nicht berücksichtigen. Das Modul erstellt jedoch nicht die erforderlichen Indizes und schreibt darüber hinaus den Abfragetext nicht neu.



Daher sollten Sie das Modul nicht für alle Anforderungen aktivieren. Ideale Optimierungskandidaten für AQO sind Abfragen, bei denen der Fehler des Planers bei der Berechnung der Kardinalität der Knoten zu einem schlechten Plan führt. Aus irgendeinem Grund ist es nicht möglich, die Verfeinerung dieser Schätzung zu beeinflussen.