Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.
Optimierung korrelierter Unterabfragen in Aurora PostgreSQL
Eine korrelierte Unterabfrage verweist auf Tabellenspalten aus der äußeren Abfrage. Sie wird einmal für jede Zeile ausgewertet, die von der äußeren Abfrage zurückgegeben wird. Im folgenden Beispiel verweist die Unterabfrage auf eine Spalte aus der Tabelle ot. Diese Tabelle ist nicht in der FROM-Klausel der Unterabfrage enthalten, aber sie wird in der FROM-Klausel der äußeren Abfrage referenziert. Wenn die Tabelle ot 1 Million Zeilen enthält, muss die Unterabfrage 1 Million Mal ausgewertet werden.
SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);
Anmerkung
Unterabfragetransformation und Unterabfrage-Cache sind in Aurora PostgreSQL ab Version 16.8 verfügbar, während Babelfish für Aurora PostgreSQL diese Funktionen ab 4.2.0 unterstützt.
Verbesserung der Aurora PostgreSQL-Abfrageleistung mithilfe der Unterabfragetransformation
Aurora PostgreSQL kann korrelierte Unterabfragen beschleunigen, indem sie in äquivalente äußere Verknüpfungen umgewandelt werden. Diese Optimierung gilt für die folgenden zwei Typen von korrelierten Unterabfragen:
-
Unterabfragen, die einen einzelnen Aggregatwert zurückgeben und in der SELECT-Liste erscheinen.
SELECT ot.a, ot.b, (SELECT AVG(it.b) FROM it WHERE it.a = ot.a) FROM ot; -
Unterabfragen, die einen einzelnen Aggregatwert zurückgeben und in einer WHERE-Klausel erscheinen.
SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);
Transformation in der Unterabfrage aktivieren
Um die Transformation korrelierter Unterabfragen in äquivalente Outer-Joins zu ermöglichen, setzen Sie den apg_enable_correlated_scalar_transform Parameter auf. ON Der Standardwert für diesen Parameter ist OFF.
Sie können den Cluster oder die Instanzparametergruppe ändern, um die Parameter festzulegen. Weitere Informationen hierzu finden Sie unter Parametergruppen für Amazon Aurora.
Alternativ können Sie die Einstellung nur für die aktuelle Sitzung mit dem folgenden Befehl konfigurieren:
SET apg_enable_correlated_scalar_transform TO ON;
Überprüfung der Transformation
Verwenden Sie den Befehl EXPLAIN, um zu überprüfen, ob die korrelierte Unterabfrage im Abfrageplan in eine äußere Verknüpfung umgewandelt wurde.
Wenn die Transformation aktiviert ist, wird der entsprechende korrelierte Teil der Unterabfrage in einen Outer-Join umgewandelt. Zum Beispiel:
postgres=> CREATE TABLE ot (a INT, b INT); CREATE TABLE postgres=> CREATE TABLE it (a INT, b INT); CREATE TABLE postgres=> SET apg_enable_correlated_scalar_transform TO ON; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);QUERY PLAN -------------------------------------------------------------- Hash Join Hash Cond: (ot.a = apg_scalar_subquery.scalar_output) Join Filter: ((ot.b)::numeric < apg_scalar_subquery.avg) -> Seq Scan on ot -> Hash -> Subquery Scan on apg_scalar_subquery -> HashAggregate Group Key: it.a -> Seq Scan on it
Dieselbe Abfrage wird nicht transformiert, wenn der GUC-Parameter geändert wird. OFF Der Plan wird keine äußere Verbindung haben, sondern stattdessen einen Unterplan.
postgres=> SET apg_enable_correlated_scalar_transform TO OFF; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);QUERY PLAN ---------------------------------------- Seq Scan on ot Filter: ((b)::numeric < (SubPlan 1)) SubPlan 1 -> Aggregate -> Seq Scan on it Filter: (a = ot.a)
Einschränkungen
-
Die Unterabfrage muss in der SELECT-Liste oder in einer der Bedingungen in der WHERE-Klausel enthalten sein. Andernfalls wird sie nicht transformiert.
-
Die Unterabfrage muss eine Aggregatfunktion zurückgeben. Benutzerdefinierte Aggregatfunktionen werden für die Transformation nicht unterstützt.
-
Eine Unterabfrage, deren Rückgabeausdruck keine einfache Aggregatfunktion ist, wird nicht transformiert.
-
Die korrelierte Bedingung in den WHERE-Klauseln der Unterabfrage sollte ein einfacher Spaltenverweis sein. Andernfalls wird sie nicht transformiert.
-
Die korrelierte Bedingung in einer Unterabfrage, bei der es sich bei Klauseln um ein einfaches Gleichheitsprädikat handeln muss.
-
Die Unterabfrage darf weder eine HAVING- noch eine GROUP BY-Klausel enthalten.
-
Die WHERE-Klausel in der Unterabfrage kann ein oder mehrere Prädikate in Kombination mit AND enthalten.
Anmerkung
Die Auswirkungen der Transformation auf die Leistung hängen von Ihrem Schema, Ihren Daten und Ihrer Arbeitslast ab. Die korrelierte Ausführung von Unterabfragen und Transformation kann die Leistung erheblich verbessern, wenn die Anzahl der Zeilen, die von der äußeren Abfrage erzeugt werden, zunimmt. Es wird dringend empfohlen, diese Funktion in einer Produktionsumgebung außerhalb der Produktionsumgebung mit Ihrem aktuellen Schema, Ihren Daten und Ihrer Arbeitslast zu testen, bevor Sie sie in einer Produktionsumgebung aktivieren.
Verwenden des Unterabfrage-Caches zur Verbesserung der Aurora PostgreSQL-Abfrageleistung
Aurora PostgreSQL unterstützt den Unterabfrage-Cache zum Speichern der Ergebnisse korrelierter Unterabfragen. Diese Funktion überspringt wiederholte Ausführungen von korrelierten Unterabfragen, wenn sich die Ergebnisse der Unterabfragen bereits im Cache befinden.
Grundlegendes zum Unterabfrage-Cache
Der Memoize-Knoten von PostgreSQL ist der wichtigste Teil des Unterabfrage-Cache. Der Memoize-Knoten verwaltet eine Hashtabelle im lokalen Cache, um Eingabeparameterwerte den Abfrageergebniszeilen zuzuordnen. Das Speicherlimit für die Hash-Tabelle ist das Produkt aus work_mem und hash_mem_multiplier. Weitere Informationen finden Sie unter Ressourcenverbrauch.
Während der Abfrageausführung verwendet der Unterabfrage-Cache die Cache Hit Rate (CHR), um abzuschätzen, ob der Cache die Abfrageleistung verbessert, und um zur Laufzeit der Abfrage zu entscheiden, ob der Cache weiterhin verwendet werden soll. CHR ist das Verhältnis der Anzahl der Cache-Treffer zur Gesamtzahl der Anfragen. Wenn beispielsweise eine korrelierte Unterabfrage 100 Mal ausgeführt werden muss und 70 dieser Ausführungsergebnisse aus dem Cache abgerufen werden können, beträgt die CHR 0,7.
Für jede Anzahl von Cache-Fehlschlägen in apg_subquery_cache_check_interval wird der Nutzen des Unterabfrage-Caches bewertet, indem geprüft wird, ob die CHR größer als apg_subquery_cache_hit_rate_threshold ist. Andernfalls wird der Cache aus dem Speicher gelöscht und die Abfrageausführung kehrt zur ursprünglichen, nicht zwischengespeicherten erneuten Ausführung der Unterabfrage zurück.
Parameter, die das Verhalten des Unterabfrage-Caches steuern
In der folgenden Tabelle sind die Parameter aufgeführt, die das Verhalten des Unterabfrage-Caches steuern.
|
Parameter |
Beschreibung |
Standard |
Zulässig |
|---|---|---|---|
apg_enable_subquery_cache |
Aktiviert die Verwendung von Cache für korrelierte skalare Unterabfragen. |
OFF |
ON, OFF |
apg_subquery_cache_check_interval |
Legt die Häufigkeit (in Anzahl von Cache-Fehlschlägen) fest, mit der die Cache-Trefferrate von Unterabfragen ausgewertet werden soll. |
500 |
0 – 2147483647 |
apg_subquery_cache_hit_rate_threshold |
Legt den Schwellenwert für die Trefferrate des Unterabfrage-Caches fest. |
0.3 |
0,0—1,0 |
Anmerkung
-
Größere Werte von
apg_subquery_cache_check_intervalkönnen die Genauigkeit der auf CHR basierenden Schätzung des Cache-Nutzens verbessern, erhöhen jedoch den Cache-Overhead, da CHR erst ausgewertet wird, wenn die Cachetabelle Zeilen enthält.apg_subquery_cache_check_interval -
Größere Werte bedeuten, dass
apg_subquery_cache_hit_rate_thresholdder Unterabfrage-Cache verlassen und zur ursprünglichen, nicht zwischengespeicherten erneuten Ausführung der Unterabfrage zurückgekehrt wird.
Sie können den Cluster oder die Instance-Parametergruppe ändern, um die Parameter festzulegen. Weitere Informationen hierzu finden Sie unter Parametergruppen für Amazon Aurora.
Alternativ können Sie die Einstellung nur für die aktuelle Sitzung mit dem folgenden Befehl konfigurieren:
SET apg_enable_subquery_cache TO ON;
Den Unterabfrage-Cache in Aurora PostgreSQL einschalten
Wenn der Unterabfrage-Cache aktiviert ist, wendet Aurora PostgreSQL den Cache an, um Unterabfrageergebnisse zu speichern. Unter dem Abfrageplan befindet sich dann ein Memoize-Knoten. SubPlan
Die folgende Befehlssequenz zeigt beispielsweise den voraussichtlichen Abfrageausführungsplan einer einfachen korrelierten Unterabfrage ohne Unterabfrage-Cache.
postgres=> SET apg_enable_subquery_cache TO OFF; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);QUERY PLAN ------------------------------------ Seq Scan on ot Filter: (b < (SubPlan 1)) SubPlan 1 -> Seq Scan on it Filter: (a = ot.a)
Nach dem apg_enable_subquery_cache Einschalten enthält der Abfrageplan einen Memoize-Knoten unter dem Knoten, der SubPlan darauf hinweist, dass die Unterabfrage beabsichtigt, den Cache zu verwenden.
postgres=> SET apg_enable_subquery_cache TO ON; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);QUERY PLAN ------------------------------------ Seq Scan on ot Filter: (b < (SubPlan 1)) SubPlan 1 -> Memoize Cache Key: ot.a Cache Mode: binary -> Seq Scan on it Filter: (a = ot.a)
Der eigentliche Abfrageausführungsplan enthält weitere Details zum Unterabfrage-Cache, einschließlich Cache-Treffer und Cache-Fehlschläge. Die folgende Ausgabe zeigt den tatsächlichen Abfrageausführungsplan der obigen Beispielabfrage nach dem Einfügen einiger Werte in die Tabellen.
postgres=> EXPLAIN (COSTS FALSE, TIMING FALSE, ANALYZE TRUE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);QUERY PLAN ----------------------------------------------------------------------------- Seq Scan on ot (actual rows=2 loops=1) Filter: (b < (SubPlan 1)) Rows Removed by Filter: 8 SubPlan 1 -> Memoize (actual rows=0 loops=10) Cache Key: ot.a Cache Mode: binary Hits: 4 Misses: 6 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> Seq Scan on it (actual rows=0 loops=6) Filter: (a = ot.a) Rows Removed by Filter: 4
Die Gesamtzahl der Cache-Treffer ist 4, und die Gesamtzahl der Cache-Fehlschläge ist 6. Wenn die Gesamtzahl der Treffer und Fehlschläge geringer ist als die Anzahl der Schleifen im Memoize-Knoten, bedeutet dies, dass die CHR-Evaluierung nicht bestanden hat und der Cache irgendwann bereinigt und verlassen wurde. Die Ausführung der Unterabfrage kehrte dann zur ursprünglichen, nicht zwischengespeicherten Neuausführung zurück.
Einschränkungen
Der Unterabfrage-Cache unterstützt bestimmte Muster korrelierter Unterabfragen nicht. Diese Arten von Abfragen werden ohne Cache ausgeführt, auch wenn der Unterabfrage-Cache aktiviert ist:
-
IN/EXISTS/ANY/ALLkorrelierte Unterabfragen
-
Korrelierte Unterabfragen, die nichtdeterministische Funktionen enthalten.
-
Korrelierte Unterabfragen, die auf äußere Tabellenspalten mit Datentypen verweisen, die keine Hashing- oder Gleichheitsoperationen unterstützen.
