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

Per controllare il modo in cui il query planner di AlloyDB per PostgreSQL genera i piani di esecuzione senza modificare le query dell'applicazione, puoi creare, verificare, visualizzare, attivare, disattivare ed eliminare gli hint con nome. Gli hint con nome associano una query a un insieme di hint che specificano i dettagli del piano di esecuzione preferito. Quando il query planner genera un piano di esecuzione per la query associata, applica i suggerimenti e li riapplica automaticamente alle esecuzioni successive della query. Ad esempio, puoi utilizzare gli hint con nome per forzare una scansione dell'indice anziché una scansione sequenziale su una tabella o per applicare un ordine di join specifico su più tabelle.

I suggerimenti con nome in AlloyDB supportano tutti i suggerimenti dell'estensione open source pg_hint_plan e i seguenti suggerimenti per il motore colonnare:

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

AlloyDB consente di creare hint con nome sia per le query con parametri sia per quelle senza parametri, che in questo documento vengono chiamate query sensibili ai parametri.

Workflow

L'utilizzo degli hint con nome prevede i seguenti passaggi:

Identifica la query per cui vuoi creare hint con nome.
Crea hint con nome con hint da applicare alla successiva esecuzione della query.
Verifica l'applicazione degli hint con nome.

Questa pagina utilizza la tabella e l'indice seguenti 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 gli hint con nome creati utilizzando una versione precedente, ricreali seguendo le istruzioni riportate in questa pagina.

Prima di iniziare

Attiva la funzionalità dei suggerimenti con nome nell'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 saperne di più, consulta Configura i flag di database di un'istanza.

Nota: per forzare lo stesso piano di query sull'istanza del pool di lettura, abilita la funzionalità degli hint con nome sull'istanza del pool di lettura.

Per verificare che il flag sia abilitato, esegui il comando show alloydb.enable_named_hints;. Se il flag è abilitato, l'output restituisce "on".
Per ogni database in cui vuoi utilizzare gli hint 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 gli hint con nome, chiedi all'amministratore di concederti i seguenti ruoli IAM (Identity and Access Management):

Ruolo alloydbsuperuser

Sebbene l'autorizzazione predefinita consenta solo all'utente con il ruolo alloydbsuperuser di creare hint con nome, puoi facoltativamente concedere l'autorizzazione in scrittura agli altri utenti o ruoli del database in modo che possano creare hint 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;

Identifica 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 una query sulla vista pg_stat_statements.

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

Crea hint con nome

Per creare hint con nome, utilizza la funzione google_create_named_hints(), che crea un'associazione tra la query e gli hint 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 gli hint con nome. Deve essere univoco all'interno del database.
SQL_ID (facoltativo): ID della query per cui stai creando gli hint con nome.

Per creare hint con nome, puoi utilizzare l'ID query o il testo della query, ovvero il parametro SQL_TEXT. Tuttavia, ti consigliamo di utilizzare l'ID query per creare hint 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 gli hint con nome.

Quando utilizzi il testo della query, questo 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ò comportare la mancata applicazione degli hint con nome. Per scoprire come creare hint con nome per le query con valori letterali e costanti, consulta Crea hint con nome sensibili ai parametri.
APPLICATION_NAME (facoltativo): il nome dell'applicazione client della sessione per cui vuoi utilizzare gli hint con nome. Una stringa vuota ti consente di applicare gli hint 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, inizialmente crea gli hint con nome come disattivati.

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 hint con nome denominato my_hint1. Il suggerimento IndexScan(t) viene applicato dal planner per forzare una scansione dell'indice sulla tabella t alla successiva esecuzione di questa query di esempio.

Dopo aver creato gli hint con nome, puoi utilizzare google_named_hints_view per verificare se sono stati creati, 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 gli hint con nome nell'istanza principale, questi vengono applicati automaticamente alle query associate nell'istanza del pool di lettura, a condizione che tu abbia attivato la funzionalità degli hint con nome anche nell'istanza del pool di lettura.

Crea hint con nome sensibili ai parametri

Per impostazione predefinita, quando vengono creati hint 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 ?. Gli hint con nome vengono quindi utilizzati per la query normalizzata, anche con un valore diverso per l'indicatore del parametro.

Ad esempio, l'esecuzione della seguente query consente a un'altra query, come SELECT * FROM t WHERE a = 99;, di utilizzare gli hint 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);

In seguito, una query come SELECT * FROM t WHERE a = 99; può utilizzare gli hint con nome my_hint2 per impostazione predefinita.

AlloyDB consente anche di creare hint con nome per testi di query non parametrizzati, in cui ogni valore letterale e costante nel testo della query è significativo quando si abbinano le query.

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

Per creare hint 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 gli hint con nome my_hint3 perché il valore letterale "99" non corrisponde a "88".

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

Gli hint con nome sensibili ai parametri non supportano una combinazione di valori letterali e costanti e indicatori di parametri nel testo della query.
Quando crei hint con nome sensibili ai parametri e hint con nome predefiniti per la stessa query, gli hint con nome sensibili ai parametri hanno la precedenza su quelli predefiniti.
Se vuoi utilizzare l'ID query per creare hint 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 gli hint con nome.

Verifica l'applicazione degli hint con nome

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

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

Per visualizzare i suggerimenti che il planner 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.

Gestisci gli hint con nome

AlloyDB ti consente di visualizzare, attivare, disattivare ed eliminare gli hint con nome.

Visualizza gli hint con nome

Per visualizzare gli hint 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

Abilita gli hint con nome

Per attivare gli hint con nome esistenti, utilizza la funzione google_enable_named_hints(HINTS_NAME). Per impostazione predefinita, gli hint con nome sono attivati quando li crei.

Ad esempio, per riattivare gli hint con nome my_hint1 precedentemente disattivati dal database, esegui la seguente funzione:

SELECT google_enable_named_hints('my_hint1');

Disabilita gli hint con nome

Per disattivare gli hint con nome esistenti, utilizza la funzione google_disable_named_hints(HINTS_NAME).

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

SELECT google_disable_named_hints('my_hint1');

Elimina hint con nome

Per eliminare gli hint con nome, utilizza la funzione google_delete_named_hints(HINTS_NAME).