Benannte Hinweise erstellen und verwalten

Auf dieser Seite wird beschrieben, wie Sie benannte Hinweise in AlloyDB for PostgreSQL erstellen und verwalten.

Benannte Hinweise sind 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 ausgegeben 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.

Die benannten Hinweise von AlloyDB unterstützen alle Hinweise aus der Open-Source-Erweiterungpg_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.

In AlloyDB können Sie benannte Hinweise 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

So verwenden Sie benannte Hinweise:

Abfrage identifizieren, für die Sie benannte Hinweise erstellen möchten.
Benannte Hinweise erstellen, die angewendet werden sollen, wenn die Abfrage das nächste Mal ausgeführt wird.
Anwendung der genannten Hinweise prüfen

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 benannten Tipps, die Sie mit einer früheren Version erstellt haben, weiterhin verwenden möchten, müssen Sie sie anhand der Anleitung auf dieser Seite neu erstellen.

Hinweise

Aktivieren Sie die Funktion „Benannte Hinweise“ in Ihrer Instanz. Legen Sie das Flag alloydb.enable_named_hints 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 für benannte Hinweise für die Lesepoolinstanz.

Führen Sie den Befehl show alloydb.enable_named_hints; 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 benannte Hinweise verwenden möchten, eine Erweiterung in der Datenbank der primären 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 benannter Hinweise benötigen:

Rolle alloydbsuperuser

Mit der Standardberechtigung kann nur der Nutzer mit der Rolle alloydbsuperuser benannte Hinweise erstellen. Sie können die Schreibberechtigung aber optional auch anderen Nutzern oder Rollen der Datenbank erteilen, damit diese benannte Hinweise 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;
```

Benannte Hinweise erstellen

Verwenden Sie die Funktion google_create_named_hints(), um benannte Hinweise zu erstellen. Dadurch wird eine Verknüpfung zwischen der Abfrage und den Hinweisen in der Datenbank erstellt.

SELECT google_create_named_hints(
HINTS_NAME=>'HINTS_NAME',
SQL_ID=>QUERY_ID,
SQL_TEXT=>QUERY_TEXT,
APPLICATION_NAME=>'APPLICATION_NAME',
HINTS=>'HINTS',
DISABLED=>DISABLED);

Ersetzen Sie Folgendes:

HINTS_NAME: ein Name für die benannten Hinweise. Dieser Wert muss innerhalb der Datenbank eindeutig sein.
SQL_ID (optional): Die Abfrage-ID der Abfrage, für die Sie die benannten Tipps erstellen.

Sie können entweder die Abfrage-ID oder den Abfragetext (den Parameter SQL_TEXT) verwenden, um benannte Hinweise zu erstellen. Wir empfehlen jedoch, die Abfrage-ID zum Erstellen benannter Hinweise zu verwenden, da AlloyDB den normalisierten Abfragetext automatisch anhand der Abfrage-ID findet.
SQL_TEXT (optional): Abfragetext der Abfrage, für die Sie die benannten Tipps 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ß-/Kleinschreibung, werden die genannten Hinweise möglicherweise nicht angewendet. Informationen zum Erstellen benannter Hinweise für Abfragen mit Literalen und Konstanten finden Sie unter Parameterabhängige benannte Hinweise erstellen.
APPLICATION_NAME (optional): Name der Sitzungsclientanwendung, für die Sie die benannten Hinweise verwenden möchten. Mit einem leeren String können Sie die benannten Hinweisen auf die Abfrage anwenden, unabhängig von der Clientanwendung, die die Abfrage ausgibt.
HINTS: Eine durch Leerzeichen getrennte Liste der Hinweise für die Anfrage.
DISABLED (Optional): BOOL. Wenn TRUE, werden die benannten Hinweise anfangs deaktiviert.

Beispiel:

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

Mit dieser Abfrage wird ein benannter Hinweis 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 benannte Hinweise erstellt haben, können Sie mit google_named_hints_view bestätigen, ob der benannte Hinweis erstellt wurde, wie im folgenden Beispiel gezeigt:

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

Nachdem die benannten Hinweisen in der primären Instanz erstellt wurden, werden sie automatisch auf die zugehörigen Abfragen in der Lesepool-Instanz angewendet, sofern Sie die Funktion für benannte Hinweise auch in der Lesepool-Instanz aktiviert haben.

Parameterabhängige benannte Hinweise erstellen

Wenn benannte Hinweise für eine Abfrage erstellt werden, wird der zugehörige Abfragetext standardmäßig normalisiert. Dazu werden alle Literal- und Konstantwerte im Abfragetext durch eine Parametermarkierung wie ? ersetzt. Die benannten Hinweismuster werden dann für diese normalisierte Anfrage verwendet, auch wenn der Parametermarker einen anderen Wert hat.

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

SELECT google_create_named_hints(
  HINTS_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; werden dann standardmäßig die benannten Hinweise my_hint2 verwendet.

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

Wenn Sie parameterabhängige benannte Hinweise 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 benannte Hinweise erstellen. Sie können jedoch unterschiedliche Hinweise für die beiden benannten Hinweise verwenden.

Wenn Sie parameterabhängige benannte Hinweise erstellen möchten, legen Sie den Parameter SENSITIVE_TO_PARAM der Funktion google_create_named_hints() auf TRUE fest, wie im folgenden Beispiel gezeigt:

SELECT google_create_named_hints(
HINTS_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; können die benannten Hinweisen my_hint3 nicht verwendet werden, da der Literalwert „99“ nicht mit „88“ übereinstimmt.

Wenn Sie parameterabhängige benannte Hinweise verwenden, sollten Sie Folgendes beachten:

Bei parameterabhängigen benannten Hinweisen wird keine Mischung aus Literal- und Konstantwerten sowie Parametermarkierungen im Abfragetext unterstützt.
Wenn Sie parameterabhängige benannte Hinweise und einen Standardhinweis für dieselbe Abfrage erstellen, werden die parameterabhängigen benannten Hinweise bevorzugt.
Wenn Sie die Abfrage-ID verwenden möchten, um parameterabhängige benannte Hinweise zu erstellen, muss die Abfrage in der aktuellen Sitzung ausgeführt werden. Die Parameterwerte aus der letzten Ausführung (in der aktuellen Sitzung) werden verwendet, um die benannten Tipps zu erstellen.

Anwendung der genannten Hinweise prüfen

Nachdem Sie die benannten Hinweismuster 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 Planer 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.

Benannte Hinweise verwalten

In AlloyDB können Sie benannte Hinweise ansehen, aktivieren, deaktivieren und löschen.

Benannte Hinweise ansehen

Wenn Sie vorhandene benannte Hinweise aufrufen möchten, verwenden Sie die Funktion google_named_hints_view, wie im folgenden Beispiel gezeigt:

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

Benannte Hinweise aktivieren

Mit der Funktion google_enable_named_hints(HINTS_NAME) können Sie vorhandene benannte Hinweise aktivieren. Standardmäßig sind benannte Hinweise aktiviert, wenn Sie sie erstellen.

Wenn Sie beispielsweise die zuvor deaktivierten benannten Hinweise my_hint1 aus der Datenbank wieder aktivieren möchten, führen Sie die folgende Funktion aus:

SELECT google_enable_named_hints('my_hint1');

Benannte Hinweise deaktivieren

Mit der Funktion google_disable_named_hints(HINTS_NAME) können Sie vorhandene benannte Hinweise deaktivieren.

Wenn Sie beispielsweise die Beispielhinweise mit dem Namen my_hint1 aus der Datenbank löschen möchten, führen Sie die folgende Funktion aus:

SELECT google_disable_named_hints('my_hint1');

Benannte Hinweise löschen

Verwenden Sie zum Löschen benannter Hinweise die Funktion google_delete_named_hints(HINTS_NAME).