Google utilizza la tecnologia AI per tradurre i contenuti nella tua lingua preferita. Le traduzioni generate dall'AI potrebbero contenere errori.

Creare e gestire suggerimenti denominati

In questa pagina viene descritto come creare e gestire i suggerimenti con nome in AlloyDB per PostgreSQL.

I suggerimenti con nome sono un'associazione tra una query e un insieme di suggerimenti che ti consentono di specificare i dettagli del piano di query. Un suggerimento specifica informazioni aggiuntive sul piano di esecuzione finale preferito per la query. Ad esempio, quando esegui la scansione di una tabella nella query, utilizza una scansione dell'indice anziché altri tipi di scansioni, come una scansione sequenziale.

Per limitare la scelta del piano finale all'interno della specifica dei suggerimenti, il pianificatore di query applica prima i suggerimenti alla query durante la generazione del piano di esecuzione. I suggerimenti vengono quindi applicati automaticamente ogni volta che la query viene emessa successivamente. Questo approccio ti consente di forzare piani di query diversi dal pianificatore. Ad esempio, puoi utilizzare i suggerimenti per forzare una scansione dell'indice su determinate tabelle o per forzare un ordine di join specifico tra più tabelle.

I suggerimenti con nome di AlloyDB supportano tutti i suggerimenti dell' estensione open source pg_hint_plan.

Inoltre, AlloyDB supporta i seguenti suggerimenti per il motore colonnare:

ColumnarScan(table): forza una scansione colonnare sulla tabella.
NoColumnarScan(table): disabilita la scansione colonnare sulla tabella.

AlloyDB consente di creare suggerimenti con nome sia per le query con parametri sia per le query senza parametri. In questa pagina, le query senza parametri vengono chiamate query sensibili ai parametri.

Workflow

L'utilizzo dei suggerimenti con nome prevede i seguenti passaggi:

Identifica la query per cui vuoi creare suggerimenti con nome.
Crea suggerimenti con nome con i suggerimenti da applicare alla prossima esecuzione della query.
Verifica l'applicazione dei suggerimenti con nome.

In questa pagina vengono utilizzati la seguente tabella e il seguente indice per gli esempi:

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

  DROP EXTENSION IF EXISTS google_auto_hints;

Per continuare a utilizzare i suggerimenti con nome creati con una versione precedente, ricreali seguendo le istruzioni riportate in questa pagina.

Prima di iniziare

Attiva la funzionalità dei suggerimenti con nome sull'istanza. Imposta il flag alloydb.enable_named_hints su on. Puoi attivare questo flag a livello di server o di sessione. Per ridurre al minimo l'overhead che potrebbe derivare dall'utilizzo di questa funzionalità, attiva questo flag solo a livello di sessione.

Per ulteriori informazioni, consulta la pagina Configurare i flag di database di un'istanza.

Nota: per forzare lo stesso piano di query sull'istanza del pool di lettura, attiva la funzionalità dei suggerimenti con nome sull'istanza del pool di lettura.

Per verificare che il flag sia attivato, esegui il comando show alloydb.enable_named_hints;. Se il flag è attivato, l'output restituisce "on".
Per ogni database in cui vuoi utilizzare i suggerimenti con nome, crea un'estensione nel database dall'istanza principale di AlloyDB come utente alloydbsuperuser o postgres:
```
CREATE EXTENSION google_auto_hints CASCADE;
```

Ruoli obbligatori

Per ottenere le autorizzazioni necessarie per creare e gestire i suggerimenti con nome, chiedi all'amministratore di concederti i seguenti ruoli Identity and Access Management (IAM):

alloydbsuperuser ruolo

Sebbene l'autorizzazione predefinita consenta solo all'utente con il ruolo alloydbsuperuser di creare suggerimenti con nome, puoi facoltativamente concedere l'autorizzazione di scrittura agli altri utenti o ruoli del database in modo che possano creare suggerimenti con nome.

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;

Identificare la query

Puoi utilizzare l'ID query per identificare la query il cui piano predefinito deve essere ottimizzato. L'ID query diventa disponibile dopo almeno un'esecuzione della query.

Utilizza i seguenti metodi per identificare l'ID query:

Esegui il comando EXPLAIN (VERBOSE), come mostrato nell'esempio seguente:

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

Nell'output, l'ID query è -6875839275481643436.

Esegui query sulla vista pg_stat_statements.

Se hai attivato l'estensione pg_stat_statements, puoi trovare l'ID query eseguendo query sulla vista pg_stat_statements, come mostrato nell'esempio seguente:
```
select query, queryid from pg_stat_statements;
```

Creare suggerimenti con nome

Per creare suggerimenti con nome, utilizza la funzione google_create_named_hints(), che crea un'associazione tra la query e i suggerimenti nel database.

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);

Sostituisci quanto segue:

HINTS_NAME: un nome per i suggerimenti con nome. Questo nome deve essere univoco all'interno del database.
SQL_ID (facoltativo): ID query della query per cui stai creando i suggerimenti con nome.

Puoi utilizzare l'ID query o il testo della query, il parametro SQL_TEXT, per creare suggerimenti con nome. Tuttavia, ti consigliamo di utilizzare l'ID query per creare suggerimenti con nome perché AlloyDB individua automaticamente il testo della query normalizzato in base all'ID query.
SQL_TEXT (facoltativo): testo della query per cui stai creando i suggerimenti con nome.

Quando utilizzi il testo della query, il testo deve essere uguale alla query prevista, ad eccezione dei valori letterali e costanti nella query. Qualsiasi mancata corrispondenza, inclusa la differenza tra maiuscole e minuscole, può impedire l'applicazione dei suggerimenti con nome. Per scoprire come creare suggerimenti con nome per le query con valori letterali e costanti, consulta Creare suggerimenti con nome sensibili ai parametri.
APPLICATION_NAME (facoltativo): nome dell'applicazione client di sessione per cui vuoi utilizzare i suggerimenti con nome. Una stringa vuota consente di applicare i suggerimenti con nome alla query indipendentemente dall'applicazione client che la emette.
HINTS: un elenco separato da spazi dei suggerimenti per la query.
DISABLED (facoltativo): BOOL. Se TRUE, crea inizialmente i suggerimenti con nome come disabilitati.

Esempio:

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

Questa query crea un suggerimento con nome denominato my_hint1. Il suggerimento IndexScan(t) viene applicato dal pianificatore per forzare una scansione dell'indice sulla tabella t alla prossima esecuzione di questa query di esempio.

Dopo aver creato i suggerimenti con nome, puoi utilizzare google_named_hints_view per verificare se il suggerimento con nome è stato creato, come mostrato nell'esempio seguente:

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

Dopo aver creato i suggerimenti con nome sull'istanza principale, questi vengono applicati automaticamente alle query associate sull'istanza del pool di lettura, a condizione che tu abbia attivato la funzionalità dei suggerimenti con nome anche sull'istanza del pool di lettura.

Creare suggerimenti con nome sensibili ai parametri

Per impostazione predefinita, quando vengono creati suggerimenti con nome per una query, il testo della query associato viene normalizzato sostituendo qualsiasi valore letterale e costante nel testo della query con un indicatore di parametro, ad esempio ?. I suggerimenti con nome vengono quindi utilizzati per la query normalizzata anche con un valore diverso per l'indicatore di parametro.

Ad esempio, l'esecuzione della seguente query consente a un'altra query, ad esempio SELECT * FROM t WHERE a = 99;, di utilizzare i suggerimenti con nome my_hint2 per impostazione predefinita.

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);

Una query, ad esempio SELECT * FROM t WHERE a = 99;, può utilizzare i suggerimenti con nome my_hint2 per impostazione predefinita.

AlloyDB consente anche di creare suggerimenti con nome per i testi delle query senza parametri, in cui ogni valore letterale e costante nel testo della query è significativo per la corrispondenza delle query.

Quando applichi suggerimenti con nome sensibili ai parametri, anche due query che differiscono solo nei valori letterali o costanti corrispondenti vengono considerate diverse. Se vuoi forzare i piani per entrambe le query, devi creare suggerimenti con nome separati per ogni query. Tuttavia, puoi utilizzare suggerimenti diversi per i due suggerimenti con nome.

Per creare suggerimenti con nome sensibili ai parametri, imposta il parametro SENSITIVE_TO_PARAM della funzione google_create_named_hints() su TRUE, come mostrato nell'esempio seguente:

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);

La query SELECT * FROM t WHERE a = 99; non può utilizzare i suggerimenti con nome my_hint3, perché il valore letterale "99" non corrisponde a "88".

Quando utilizzi suggerimenti con nome sensibili ai parametri, tieni presente quanto segue:

I suggerimenti con nome sensibili ai parametri non supportano una combinazione di valori letterali e costanti e indicatori di parametri nel testo della query.
Quando crei suggerimenti con nome sensibili ai parametri e suggerimenti con nome predefiniti per la stessa query, i suggerimenti con nome sensibili ai parametri hanno la precedenza sui suggerimenti con nome predefiniti.
Se vuoi utilizzare l'ID query per creare suggerimenti con nome sensibili ai parametri, assicurati che la query sia stata eseguita nella sessione corrente. I valori dei parametri dell'esecuzione più recente (nella sessione corrente) vengono utilizzati per creare i suggerimenti con nome.

Verificare l'applicazione dei suggerimenti con nome

Dopo aver creato i suggerimenti con nome, utilizza i seguenti metodi per verificare che il piano di query sia forzato di conseguenza.

Utilizza il comando EXPLAIN o il comando EXPLAIN (ANALYZE).

Per visualizzare i suggerimenti che il pianificatore sta tentando di applicare, puoi impostare i seguenti flag a livello di sessione prima di eseguire il comando EXPLAIN:
```
SET pg_hint_plan.debug_print = ON;
SET client_min_messages = LOG;
```
Utilizza l'estensione auto_explain.

Gestire i suggerimenti con nome

AlloyDB consente di visualizzare, attivare, disattivare ed eliminare i suggerimenti con nome.

Visualizzare i suggerimenti con nome

Per visualizzare i suggerimenti con nome esistenti, utilizza la funzione google_named_hints_view, come mostrato nell'esempio seguente:

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

Attivare i suggerimenti con nome

Per attivare i suggerimenti con nome esistenti, utilizza la funzione google_enable_named_hints(HINTS_NAME). Per impostazione predefinita, i suggerimenti con nome vengono attivati quando li crei.

Ad esempio, per riattivare i suggerimenti con nome my_hint1 disattivati in precedenza dal database, esegui la seguente funzione:

SELECT google_enable_named_hints('my_hint1');

Disattivare i suggerimenti con nome

Per disattivare i suggerimenti con nome esistenti, utilizza la funzione google_disable_named_hints(HINTS_NAME).

Ad esempio, per eliminare i suggerimenti con nome di esempio my_hint1 dal database, esegui la seguente funzione:

SELECT google_disable_named_hints('my_hint1');

Eliminare i suggerimenti con nome

Per eliminare i suggerimenti con nome, utilizza la funzione google_delete_named_hints(HINTS_NAME).