Verbessern der Aurora-PostgreSQL-Abfrageleistung mithilfe der Unterabfragentransformation Verwenden des Unterabfrage-Caches zur Verbesserung der Aurora-PostgreSQL-Abfrageleistung

Optimieren von korrelierten 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, wird aber 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

Die Transformation einer Unterabfrage und der Unterabfragen-Cache sind in Aurora PostgreSQL ab Version 16.8 verfügbar, während Babelfish für Aurora PostgreSQL diese Funktionen ab Version 4.2.0 unterstützt.
Ab den Versionen 4.6.0 und 5.2.0 von Babelfish für Aurora PostgreSQL steuern die folgenden Parameter diese Funktionen:
- babelfishpg_tsql.apg_enable_correlated_scalar_transform
- babelfishpg_tsql.apg_enable_subquery_cache
Beide Parameter sind standardmäßig deaktiviert.

Verbessern der Aurora-PostgreSQL-Abfrageleistung mithilfe der Unterabfragentransformation

Aurora PostgreSQL kann korrelierte Unterabfragen beschleunigen, indem sie in äquivalente äußere Joins umgewandelt werden. Diese Optimierung gilt für die folgenden zwei Typen korrelierter Unterabfragen:

Unterabfragen, die einen einzelnen Aggregatwert zurückgeben und in der SELECT-Liste aufgeführt sind
```
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 aufgeführt sind
```
SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);
```

Aktivieren der Transformation in der Unterabfrage

Um die Transformation korrelierter Unterabfragen in äquivalente externe 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 die Cluster- oder Instance-Parametergruppe ändern, um diese Parameter festzulegen. Weitere Informationen hierzu finden Sie unter Parametergruppen für Amazon Aurora.

Alternativ können Sie die Einstellung mit dem folgenden Befehl ausschließlich für die aktuelle Sitzung konfigurieren:


SET apg_enable_correlated_scalar_transform TO ON;

Überprüfen der Transformation

Verwenden Sie den Befehl EXPLAIN, um zu überprüfen, ob die korrelierte Unterabfrage im Abfrageplan in einen externen Join umgewandelt wurde.

Wenn die Transformation aktiviert ist, wird der entsprechende korrelierte Teil der Unterabfrage in einen externen 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 umgewandelt, wenn der GUC-Parameter auf OFF gesetzt wird. Der Plan hat keinen externen Join, sondern stattdessen einen untergeordneten Plan (Subplan).


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 der WHERE-Klausel enthalten sein. Andernfalls wird sie nicht umgewandelt.
Die Unterabfrage muss eine Aggregatfunktion zurückgeben. Benutzerdefinierte Aggregatfunktionen unterstützen keine Transformation.
Eine Unterabfrage, deren Return-Ausdruck keine einfache Aggregatfunktion ist, wird nicht umgewandelt.
Die korrelierte Bedingung in den WHERE-Klauseln der Unterabfrage sollte ein einfacher Spaltenverweis sein. Andernfalls wird sie nicht umgewandelt.
Die korrelierte Bedingung in den Where-Klauseln der Unterabfrage muss ein einfaches Gleichheitsprädikat sein.
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 Ihrem Workload ab. Die Ausführung korrelierter Unterabfragen mit Transformation kann die Leistung erheblich verbessern, da die Anzahl der Zeilen, die von der äußeren Abfrage erzeugt werden, zunimmt. Es wird dringend empfohlen, diese Funktion in einer Nicht-Produktionsumgebung mit Ihrem tatsächlichen Schema, Ihren Daten und Ihrem Workload zu testen, ehe Sie sie in einer Produktionsumgebung aktivieren.

Verwenden des Unterabfrage-Caches zur Verbesserung der Aurora-PostgreSQL-Abfrageleistung

Aurora PostgreSQL unterstützt einen Unterabfrage-Cache zum Speichern der Ergebnisse korrelierter Unterabfragen. Diese Funktion überspringt wiederholte Ausführungen korrelierter Unterabfragen, wenn sich die Ergebnisse der Unterabfragen bereits im Cache befinden.

Grundlegendes zu Unterabfragen

Der Memoize-Knoten von PostgreSQL ist der wichtigste Teil des Unterabfrage-Caches. Der Memoize-Knoten verwaltet eine Hash-Tabelle im lokalen Cache, um Eingabeparameterwerte den Abfrageergebniszeilen zuzuordnen. Das Speicherlimit für die Hash-Tabelle ergibt sich aus work_mem und hash_mem_multiplier. Weitere Informationen finden Sie unter Ressourcennutzung.

Während der Ausführung der Abfrage verwendet der Unterabfragen-Cache die Cache-Trefferrate (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. Die CHR gibt das Verhältnis der Anzahl von Cache-Treffern zur Gesamtzahl der Anfragen an. 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 apg_subquery_cache_check_interval-Anzahl von Cache-Fehlern wird der Nutzen des Unterabfrage-Caches ausgewertet, indem geprüft wird, ob die CHR größer ist als apg_subquery_cache_hit_rate_threshold. Wenn nicht, 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 der Cache-Fehler fest, um die Unterabfragen-CHR zu bewerten.	500	0 – 2147483647
apg_subquery_cache_hit_rate_threshold	Legt den Schwellenwert für die Unterabfragen-CHR fest.	0.3	0,0 bis 1,0

Anmerkung

Höhere Werte von apg_subquery_cache_check_interval können die Genauigkeit der CHR-basierten Einschätzung des Cache-Nutzens verbessern, erhöhen aber den Cache-Overhead, da die CHR erst ausgewertet wird, wenn die Cache-Tabelle apg_subquery_cache_check_interval-Zeilen enthält.
Höhere Werte von apg_subquery_cache_hit_rate_threshold bedeuten, dass der Unterabfrage-Cache aufgegeben wurde und zur ursprünglichen, erneuten Ausführung der Unterabfrage ohne Zwischenspeicherung zurückgekehrt wird.

Sie können die Cluster- oder Instance-Parametergruppe ändern, um diese Parameter festzulegen. Weitere Informationen hierzu finden Sie unter Parametergruppen für Amazon Aurora.

Alternativ können Sie die Einstellung mit dem folgenden Befehl ausschließlich für die aktuelle Sitzung konfigurieren:


SET apg_enable_subquery_cache TO ON;

Aktivieren des Unterabfrage-Caches in Aurora PostgreSQL

Wenn der Unterabfrage-Cache aktiviert ist, nutzt Aurora PostgreSQL Cache, um die Ergebnisse der Unterabfrage zu speichern. Der Abfrageplan hat dann einen Memoize-Knoten unterhalb von SubPlan.

Die folgende Befehlssequenz zeigt beispielsweise den geschätzten 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 Aktivieren von apg_enable_subquery_cache enthält der Abfrageplan einen Memoize-Knoten unter dem SubPlan-Knoten, der 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 Ausführungsplan für die Abfrage enthält weitere Details zum Unterabfrage-Cache, einschließlich Cache-Treffer und Cache-Fehler. 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-Fehler beträgt 6. Wenn die Gesamtzahl der Treffer und Fehler geringer ist als die Anzahl der Schleifen im Memoize-Knoten, bedeutet das, dass die CHR-Evaluierung nicht erfolgreich war und der Cache an irgendeinem Punkt bereinigt und aufgegeben wurde. Hinsichtlich der Ausführung der Unterabfrage wurde dann die ursprüngliche, erneute Ausführung ohne Zwischenspeicherung wiederaufgenommen.

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:

Korrelierte IN/EXISTS/ANY/ALL-Unterabfragen
Korrelierte Unterabfragen, die nicht deterministische Funktionen enthalten
Korrelierte Unterabfragen, die auf äußere Tabellenspalten mit Datentypen verweisen, die keine Hashing- oder Gleichheitsoperationen unterstützen.

Warnung JavaScript ist in Ihrem Browser nicht verfügbar oder deaktiviert.

Zur Nutzung der AWS-Dokumentation muss JavaScript aktiviert sein. Weitere Informationen finden auf den Hilfe-Seiten Ihres Browsers.

Dokumentkonventionen

Verbesserung der Abfrageleistung mit Aurora-optimierten Lesevorgängen

Verbessern der Abfrageleistung mit Adaptive Join