Creare e gestire suggerimenti denominati

Questa pagina descrive come creare e gestire gli hint denominati in AlloyDB per PostgreSQL.

Gli hint denominati sono un'associazione tra una query e un insieme di hint 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 scansione, come una scansione sequenziale.

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

I suggerimenti denominati 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 della tabella.
NoColumnarScan(table): disattiva la scansione colonnare nella tabella.

AlloyDB consente di creare hint denominati sia per le query con parametri sia per quelle senza parametri. In questa pagina, le query senza parametri sono chiamate query sensibili ai parametri.

Flusso di lavoro

L'utilizzo di suggerimenti denominati prevede i seguenti passaggi:

Identifica la query per cui vuoi creare hint denominati.
Crea hint denominati con gli hint da applicare alla successiva esecuzione della query.
Verifica l'applicazione dei suggerimenti denominati.

Questa pagina utilizza 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 denominati che hai creato utilizzando una versione precedente, ricreali seguendo le istruzioni riportate in questa pagina.

Prima di iniziare

Attiva la funzionalità dei suggerimenti denominati 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 Configurare i flag di database di un'istanza.

Nota: per forzare lo stesso piano di query sull'istanza del pool di lettura, abilita la funzionalità suggerimenti denominati sull'istanza del pool di lettura.

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

Ruoli obbligatori

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

Ruolo alloydbsuperuser

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

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 una query sulla visualizzazione 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;
```

Creare suggerimenti denominati

Per creare suggerimenti denominati, 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 denominati. Deve essere univoco all'interno del database.
(Facoltativo) SQL_ID: ID della query per cui stai creando gli hint denominati.

Puoi utilizzare l'ID query o il testo della query, ovvero il parametro SQL_TEXT, per creare suggerimenti denominati. Tuttavia, ti consigliamo di utilizzare l'ID query per creare hint denominati 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 denominati.

Quando utilizzi il testo della query, questo deve essere uguale alla query prevista, ad eccezione dei valori letterali e costanti della query. Qualsiasi mancata corrispondenza, inclusa la differenza tra maiuscole e minuscole, può comportare la mancata applicazione dei suggerimenti denominati. Per scoprire come creare hint denominati per query con valori letterali e costanti, consulta Creare hint denominati sensibili ai parametri.
APPLICATION_NAME (facoltativo): nome dell'applicazione client di sessione per cui vuoi utilizzare i suggerimenti denominati. Una stringa vuota ti consente di applicare gli hint denominati alla query indipendentemente dall'applicazione client che la esegue.
HINTS: un elenco separato da spazi dei suggerimenti per la query.
DISABLED (facoltativo): BOOL. Se TRUE, inizialmente crea i suggerimenti denominati inizialmente 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 hint denominati my_hint1. Il suggerimento IndexScan(t) viene applicato dallo strumento di pianificazione per forzare una scansione dell'indice nella tabella t alla successiva esecuzione di questa query di esempio.

Dopo aver creato i suggerimenti denominati, puoi utilizzare google_named_hints_view per verificare se il suggerimento denominato è 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

Una volta creati i suggerimenti denominati nell'istanza principale, vengono applicati automaticamente alle query associate nell'istanza del pool di lettura, a condizione che tu abbia attivato la funzionalità di suggerimenti denominati anche nell'istanza del pool di lettura.

Creare suggerimenti denominati sensibili ai parametri

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

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

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

Quindi, una query come SELECT * FROM t WHERE a = 99; può utilizzare gli hint denominati my_hint2 per impostazione predefinita.

AlloyDB consente anche di creare suggerimenti denominati per i testi delle query non parametrizzati, in cui ogni valore letterale e costante nel testo della query è significativo per la corrispondenza delle query.

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

Per creare suggerimenti denominati 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 denominati my_hint3, perché il valore letterale "99" non corrisponde a "88".

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

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

Verifica l'applicazione dei suggerimenti denominati

Dopo aver creato gli hint denominati, 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 lo strumento di pianificazione 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 suggerimenti denominati

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

Visualizzare i suggerimenti denominati

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 denominati esistenti, utilizza la funzione google_enable_named_hints(HINTS_NAME). Per impostazione predefinita, i suggerimenti denominati sono attivati quando li crei.

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

SELECT google_enable_named_hints('my_hint1');

Disattivare i suggerimenti denominati

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

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

SELECT google_disable_named_hints('my_hint1');

Elimina suggerimenti denominati

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