Diese Seite wurde von der Cloud Translation API übersetzt.

Patch für Abfragepläne erstellen und verwalten

Auf dieser Seite wird beschrieben, wie Sie Patches für Abfragepläne in AlloyDB for PostgreSQL erstellen und verwalten.

Ein Patch für den Abfrageplan ist eine Verknüpfung zwischen einer Abfrage und einer Reihe von Hinweisen, mit denen Sie die Details des Abfrageplans angeben können. Ein Hinweis enthält zusätzliche Informationen zum bevorzugten endgültigen Ausführungsplan für die Abfrage. Wenn Sie beispielsweise eine Tabelle in der Abfrage scannen, verwenden Sie einen Indexscan anstelle anderer Arten von Scans, z. B. eines sequenziellen Scans.

Um die endgültige Planauswahl innerhalb der Spezifikation der Hinweise zu begrenzen, wendet der Abfrageplaner die Hinweise zuerst auf die Abfrage an, während er den Ausführungsplan generiert. Die Hinweise werden dann automatisch angewendet, wenn die Abfrage später noch einmal gestellt wird. Mit diesem Ansatz können Sie verschiedene Abfragepläne vom Planer erzwingen. Sie können beispielsweise Hinweise verwenden, um einen Indexscan für bestimmte Tabellen oder eine bestimmte Join-Reihenfolge für mehrere Tabellen zu erzwingen.

Der AlloyDB-Patch für den Abfrageplan unterstützt alle Hinweise aus der Open-Source-Erweiterung pg_hint_plan.

Außerdem unterstützt AlloyDB die folgenden Hinweise für die spaltenorientierte Engine:

ColumnarScan(table): Erzwingt einen spaltenweisen Scan der Tabelle.
NoColumnarScan(table): Deaktiviert den spaltenweisen Scan für die Tabelle.

Mit AlloyDB können Sie Plan-Patches sowohl für parametrisierte als auch für nicht parametrisierte Abfragen erstellen. Auf dieser Seite werden nicht parametrisierte Abfragen als parameterabhängige Abfragen bezeichnet.

Workflow

Die Verwendung eines Abfrageplan-Patches umfasst die folgenden Schritte:

Identifizieren Sie die Abfrage, für die Sie einen Plan-Patch erstellen möchten.
Erstellen Sie einen Plan-Patch mit Hinweisen, die beim nächsten Ausführen der Abfrage angewendet werden sollen.
Prüfen Sie, ob der Plan-Patch angewendet wurde.

Auf dieser Seite werden die folgende Tabelle und der folgende Index für Beispiele verwendet:

CREATE TABLE t(a INT, b INT);
CREATE INDEX t_idx1 ON t(a);

  DROP EXTENSION IF EXISTS google_auto_hints;

Wenn Sie die mit einer früheren Version erstellten Query Plan Patches weiterhin verwenden möchten, müssen Sie sie anhand der Anleitung auf dieser Seite neu erstellen.

Hinweise

Aktivieren Sie die Funktion zum Patchen von Abfrageplänen auf Ihrer Instanz. Legen Sie das Flag alloydb.enable_query_plan_patch auf on fest. Sie können dieses Flag auf Server- oder Sitzungsebene aktivieren. Um den Overhead zu minimieren, der durch die Verwendung dieser Funktion entstehen kann, aktivieren Sie dieses Flag nur auf Sitzungsebene.

Weitere Informationen finden Sie unter Datenbank-Flags einer Instanz konfigurieren.

Hinweis: Wenn Sie erzwingen möchten, dass derselbe Abfrageplan für Ihre Lesepoolinstanz verwendet wird, aktivieren Sie die Funktion zum Patchen von Abfrageplänen für die Lesepoolinstanz.

Führen Sie den Befehl show alloydb.enable_query_plan_patch; aus, um zu prüfen, ob das Flag aktiviert ist. Wenn das Flag aktiviert ist, wird „on“ zurückgegeben.
Erstellen Sie für jede Datenbank, in der Sie Patches für Abfragepläne verwenden möchten, eine Erweiterung in der Datenbank über die primäre AlloyDB-Instanz als alloydbsuperuser- oder postgres-Nutzer:
```
CREATE EXTENSION google_auto_hints CASCADE;
```

Erforderliche Rollen

Bitten Sie Ihren Administrator, Ihnen die folgenden IAM-Rollen (Identity and Access Management) zuzuweisen, um die Berechtigungen zu erhalten, die Sie zum Erstellen und Verwalten von Patches für Abfragepläne benötigen:

Rolle alloydbsuperuser

Standardmäßig darf nur der Nutzer mit der Rolle alloydbsuperuser Plan-Patches erstellen. Sie können die Schreibberechtigung aber optional auch anderen Nutzern oder Rollen der Datenbank erteilen, damit diese Plan-Patches erstellen können.

GRANT INSERT,DELETE,UPDATE ON hint_plan.plan_patches, hint_plan.hints TO role_name;
GRANT USAGE ON SEQUENCE hint_plan.hints_id_seq, hint_plan.plan_patches_id_seq TO role_name;

Abfrage identifizieren

Mit der Abfrage-ID können Sie die Abfrage identifizieren, deren Standardplan optimiert werden muss. Die Abfrage-ID ist erst nach mindestens einer Ausführung der Abfrage verfügbar.

Verwenden Sie eine der folgenden Methoden, um die Abfrage-ID zu ermitteln:

Führen Sie den Befehl EXPLAIN (VERBOSE) aus, wie im folgenden Beispiel gezeigt:

EXPLAIN (VERBOSE) SELECT * FROM t WHERE a = 99;
                        QUERY PLAN
----------------------------------------------------------
Seq Scan on public.t  (cost=0.00..38.25 rows=11 width=8)
  Output: a, b
  Filter: (t.a = 99)
Query Identifier: -6875839275481643436

In der Ausgabe lautet die Abfrage-ID -6875839275481643436.

Fragen Sie die Ansicht pg_stat_statements ab.

Wenn Sie die pg_stat_statements-Erweiterung aktiviert haben, können Sie die Abfrage-ID abrufen, indem Sie die Ansicht pg_stat_statements abfragen, wie im folgenden Beispiel gezeigt:
```
select query, queryid from pg_stat_statements;
```

Patch für Abfrageplan erstellen

Verwenden Sie die Funktion google_create_plan_patch(), um einen Patch für den Abfrageplan zu erstellen. Dadurch wird eine Verknüpfung zwischen der Abfrage und den Hinweisen in der Datenbank erstellt.

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'PLAN_PATCH_NAME',
SQL_ID=>QUERY_ID,
SQL_TEXT=>QUERY_TEXT,
APPLICATION_NAME=>'APPLICATION_NAME',
HINTS=>'HINTS',
DISABLED=>DISABLED);

Ersetzen Sie Folgendes:

PLAN_PATCH_NAME: Ein Name für den Plan-Patch. Dieser Wert muss innerhalb der Datenbank eindeutig sein.
SQL_ID (optional): Die Abfrage-ID der Abfrage, für die Sie den Plan-Patch erstellen.

Sie können entweder die Abfrage-ID oder den Abfragetext (den Parameter SQL_TEXT) verwenden, um einen Plan-Patch zu erstellen. Wir empfehlen jedoch, die Abfrage-ID zum Erstellen eines Plan-Patches zu verwenden, da AlloyDB den normalisierten Abfragetext automatisch anhand der Abfrage-ID findet.
SQL_TEXT (Optional): Abfragetext der Abfrage, für die Sie den Plan-Patch erstellen.

Wenn Sie den Abfragetext verwenden, muss er mit der beabsichtigten Abfrage übereinstimmen, mit Ausnahme der Literal- und Konstantenwerte in der Abfrage. Bei Abweichungen, auch bei der Groß- und Kleinschreibung, wird der Plan-Patch möglicherweise nicht angewendet. Informationen zum Erstellen von Plan-Patches für Abfragen mit Literalen und Konstanten finden Sie unter Parameterabhängigen Abfrageplan-Patch erstellen.
APPLICATION_NAME (optional): Name der Sitzungsclientanwendung, für die Sie den Plan-Patch verwenden möchten. Mit einem leeren String können Sie den Plan-Patch unabhängig von der Clientanwendung, die die Abfrage ausgibt, auf die Abfrage anwenden.
HINTS: Eine durch Leerzeichen getrennte Liste der Hinweise für die Anfrage.
DISABLED (Optional): BOOL. Wenn TRUE, wird der Plan-Patch anfangs deaktiviert erstellt.

Beispiel:

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'my_hint1',
SQL_ID=>-6875839275481643436,
SQL_TEXT=>NULL,
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL);

Mit dieser Abfrage wird ein Plan-Patch mit dem Namen my_hint1 erstellt. Der Hinweis IndexScan(t) wird vom Planner angewendet, um beim nächsten Ausführen dieser Beispielabfrage einen Indexscan für die Tabelle t zu erzwingen.

Nachdem Sie einen Plan-Patch erstellt haben, können Sie mit google_query_plan_patch_view prüfen, ob der Plan-Patch erstellt wurde. Das folgende Beispiel zeigt, wie das geht:

postgres=>\x
postgres=>select * from google_query_plan_patch_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
plan_patch_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

Nachdem der Plan-Patch auf der primären Instanz erstellt wurde, wird er automatisch auf die zugehörigen Abfragen in der Lesepool-Instanz angewendet, sofern Sie die Funktion für Plan-Patches für Abfragen auch in der Lesepool-Instanz aktiviert haben.

Parameterabhängigen Patch für Abfrageplan erstellen

Wenn ein Plan-Patch für eine Abfrage erstellt wird, wird der zugehörige Abfragetext standardmäßig normalisiert. Dazu werden alle Literal- und konstanten Werte im Abfragetext durch eine Parametermarkierung wie ? ersetzt. Der Plan-Patch wird dann für diese normalisierte Abfrage verwendet, auch wenn der Wert für die Parametermarkierung ein anderer ist.

Wenn Sie beispielsweise die folgende Abfrage ausführen, kann eine andere Abfrage, z. B. SELECT * FROM t WHERE a = 99;, den Plan-Patch my_hint2 standardmäßig verwenden.

SELECT google_create_plan_patch(
  PLAN_PATCH_NAME=>'my_hint2',
  SQL_ID=>NULL,
  SQL_TEXT=>'SELECT * FROM t WHERE a = ?;',
  APPLICATION_NAME=>'',
  HINTS=>'SeqScan(t)',
  DISABLED=>NULL);

Bei einer Abfrage wie SELECT * FROM t WHERE a = 99; kann dann standardmäßig der Plan-Patch my_hint2 verwendet werden.

Mit AlloyDB können Sie auch einen Plan-Patch für nicht parametrisierte Abfragetexte erstellen, in denen jeder Literal- und Konstantwert im Abfragetext beim Abgleichen von Abfragen eine Rolle spielt.

Wenn Sie einen parameterabhängigen Plan-Patch anwenden, werden zwei Abfragen, die sich nur in den entsprechenden Literal- oder Konstantenwerten unterscheiden, ebenfalls als unterschiedlich betrachtet. Wenn Sie Pläne für beide Abfragen erzwingen möchten, müssen Sie für jede Abfrage separate Plan-Patches erstellen. Sie können jedoch unterschiedliche Hinweise für die beiden Plan-Patches verwenden.

Wenn Sie einen parameterabhängigen Plan-Patch erstellen möchten, setzen Sie den SENSITIVE_TO_PARAM-Parameter der Funktion google_create_plan_patch() auf TRUE, wie im folgenden Beispiel gezeigt:

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'my_hint3',
SQL_ID=>NULL,
SQL_TEXT=>'SELECT * FROM t WHERE a = 88;',
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL,
SENSITIVE_TO_PARAM=>TRUE);

Für die Abfrage SELECT * FROM t WHERE a = 99; kann der Plan-Patch my_hint3 nicht verwendet werden, da der Literalwert „99“ nicht mit „88“ übereinstimmt.

Wenn Sie parameterabhängige Plan-Patches verwenden, sollten Sie Folgendes beachten:

Bei parameterabhängigen Plan-Patches wird keine Mischung aus Literal- und konstanten Werten sowie Parametermarkierungen im Abfragetext unterstützt.
Wenn Sie einen parameterabhängigen Plan-Patch und einen Standardplan-Patch für dieselbe Abfrage erstellen, wird der parameterabhängige Plan-Patch dem Standard-Patch vorgezogen.
Wenn Sie die Abfrage-ID zum Erstellen eines parameterabhängigen Plan-Patches verwenden möchten, muss die Abfrage in der aktuellen Sitzung ausgeführt werden. Die Parameterwerte aus der letzten Ausführung (in der aktuellen Sitzung) werden verwendet, um den Plan-Patch zu erstellen.

Anwendung des Queryplan-Patches prüfen

Nachdem Sie den Plan-Patch erstellt haben, können Sie mit den folgenden Methoden prüfen, ob der Abfrageplan entsprechend erzwungen wird.

Verwenden Sie den Befehl EXPLAIN oder EXPLAIN (ANALYZE).

Wenn Sie die Hinweise sehen möchten, die der Planner anzuwenden versucht, können Sie die folgenden Flags auf Sitzungsebene festlegen, bevor Sie den Befehl EXPLAIN ausführen:
```
SET pg_hint_plan.debug_print = ON;
SET client_min_messages = LOG;
```
Verwenden Sie die Erweiterung auto_explain.

Abfrageplan-Patches verwalten

In AlloyDB können Sie einen Patch für den Abfrageplan aufrufen, aktivieren, deaktivieren und löschen.

Abfrageplan-Patch ansehen

Verwenden Sie die Funktion google_query_plan_patch_view, um vorhandene Plan-Patches aufzurufen, wie im folgenden Beispiel gezeigt:

postgres=>\x
postgres=>select * from google_query_plan_patch_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
plan_patch_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

Patch für Abfrageplan aktivieren

Verwenden Sie die Funktion google_enable_plan_patch(PLAN_PATCH_NAME), um einen vorhandenen Plan-Patch zu aktivieren. Standardmäßig ist ein Plan-Patch aktiviert, wenn Sie ihn erstellen.

Wenn Sie beispielsweise den zuvor deaktivierten Plan-Patch my_hint1 aus der Datenbank wieder aktivieren möchten, führen Sie die folgende Funktion aus:

SELECT google_enable_plan_patch('my_hint1');

Patch für Abfrageplan deaktivieren

Verwenden Sie die Funktion google_disable_plan_patch(PLAN_PATCH_NAME), um einen vorhandenen Plan-Patch zu deaktivieren.

Wenn Sie beispielsweise den Beispiel-Plan-Patch my_hint1 aus der Datenbank löschen möchten, führen Sie die folgende Funktion aus:

SELECT google_disable_plan_patch('my_hint1');

Patch für Abfrageplan löschen

Verwenden Sie die Funktion google_delete_plan_patch(PLAN_PATCH_NAME), um einen Plan-Patch zu löschen.