Questa pagina è stata tradotta dall'API Cloud Translation.

Crea e gestisci patch del piano di query

Questa pagina descrive come creare e gestire le patch del piano di query in AlloyDB per PostgreSQL.

Una patch del piano di query è 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 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.

La patch del piano di query AlloyDB supporta tutti gli hint dell'estensione pg_hint_plan open source.

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 patch del piano sia per le query con parametri sia per le query senza parametri. In questa pagina, le query senza parametri sono chiamate query sensibili ai parametri.

Flusso di lavoro

L'utilizzo di una patch del piano di query prevede i seguenti passaggi:

Identifica la query per cui vuoi creare una patch del piano.
Crea una patch del piano con suggerimenti da applicare alla successiva esecuzione della query.
Verifica l'applicazione della patch del piano.

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 le patch del piano di query che hai creato utilizzando una versione precedente, ricreale seguendo le istruzioni riportate in questa pagina.

Prima di iniziare

Attiva la funzionalità di patch del piano di query sull'istanza. Imposta il flag alloydb.enable_query_plan_patch 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à di patch del piano di query sull'istanza del pool di lettura.

Per verificare che il flag sia abilitato, esegui il comando show alloydb.enable_query_plan_patch;. Se il flag è attivato, l'output restituisce "on".
Per ogni database in cui vuoi utilizzare le patch del piano di query, crea un'estensione nel database dall'istanza primaria AlloyDB come utente alloydbsuperuser o postgres:
```
CREATE EXTENSION google_auto_hints CASCADE;
```

Ruoli obbligatori

Per ottenere le autorizzazioni necessarie per creare e gestire le patch del piano di query, 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 patch del piano, puoi concedere facoltativamente l'autorizzazione di scrittura agli altri utenti o ruoli del database in modo che possano creare patch del piano.

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

Crea una patch del piano di query

Per creare una patch del piano di query, utilizza la funzione google_create_plan_patch(), che crea un'associazione tra la query e gli hint nel database.

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

Sostituisci quanto segue:

PLAN_PATCH_NAME: un nome per la patch del piano. Deve essere univoco all'interno del database.
SQL_ID (facoltativo): ID query della query per cui stai creando la patch del piano.

Puoi utilizzare l'ID query o il testo della query, ovvero il parametro SQL_TEXT, per creare una patch del piano. Tuttavia, ti consigliamo di utilizzare l'ID query per creare una patch del piano perché AlloyDB individua automaticamente il testo della query normalizzato in base all'ID query.
SQL_TEXT (Facoltativo): testo della query per cui stai creando la patch del piano.

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 della patch del piano. Per scoprire come creare patch del piano per le query con valori letterali e costanti, consulta Creare una patch del piano di query sensibile ai parametri.
APPLICATION_NAME (Facoltativo): nome dell'applicazione client di sessione per cui vuoi utilizzare la patch del piano. Una stringa vuota ti consente di applicare la patch del piano 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 la patch del piano inizialmente disattivata.

Esempio:

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

Questa query crea una patch del piano denominata 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 una patch del piano, puoi utilizzare google_query_plan_patch_view per verificare se la patch del piano è stata creata, come mostrato nell'esempio seguente:

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

Dopo aver creato la patch del piano nell'istanza principale, questa viene applicata automaticamente alle query associate nell'istanza del pool di lettura, a condizione che tu abbia attivato la funzionalità di patch del piano di query anche nell'istanza del pool di lettura.

Creare una patch del piano di query sensibile ai parametri

Per impostazione predefinita, quando viene creata una patch del piano 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 ?. La patch del piano viene quindi utilizzata 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 la patch del piano my_hint2 per impostazione predefinita.

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

Quindi, una query come SELECT * FROM t WHERE a = 99; può utilizzare la patch del piano my_hint2 per impostazione predefinita.

AlloyDB ti consente anche di creare una patch del piano 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 una patch al piano sensibile 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 patch di piani separate per ciascuna query. Tuttavia, puoi utilizzare suggerimenti diversi per le due patch del piano.

Per creare una patch del piano sensibile ai parametri, imposta il parametro SENSITIVE_TO_PARAM della funzione google_create_plan_patch() su TRUE, come mostrato nell'esempio seguente:

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

La query SELECT * FROM t WHERE a = 99; non può utilizzare la patch del piano my_hint3, perché il valore letterale "99" non corrisponde a "88".

Quando utilizzi le patch dei piani sensibili ai parametri, tieni presente quanto segue:

Le patch dei piani sensibili ai parametri non supportano una combinazione di valori letterali e costanti e marcatori di parametri nel testo della query.
Quando crei una patch del piano sensibile ai parametri e una patch del piano predefinita per la stessa query, la patch del piano sensibile ai parametri ha la precedenza sulla patch predefinita.
Se vuoi utilizzare l'ID query per creare una patch del piano sensibile 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 la patch del piano.

Verificare l'applicazione della patch del piano di query

Dopo aver creato la patch del piano, 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 cercando 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 le patch del piano di query

AlloyDB ti consente di visualizzare, attivare, disattivare ed eliminare una patch del piano di query.

Visualizzare una patch del piano di query

Per visualizzare le patch del piano esistenti, utilizza la funzione google_query_plan_patch_view, come mostrato nell'esempio seguente:

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

Abilitare una patch del piano di query

Per attivare una patch del piano esistente, utilizza la funzione google_enable_plan_patch(PLAN_PATCH_NAME). Per impostazione predefinita, una patch del piano è abilitata quando la crei.

Ad esempio, per riattivare la patch del piano my_hint1 disattivata in precedenza dal database, esegui la seguente funzione:

SELECT google_enable_plan_patch('my_hint1');

Disattivare una patch del piano di query

Per disattivare una patch del piano esistente, utilizza la funzione google_disable_plan_patch(PLAN_PATCH_NAME).

Ad esempio, per eliminare la patch del piano di esempio my_hint1 dal database, esegui la seguente funzione:

SELECT google_disable_plan_patch('my_hint1');

Eliminare una patch del piano di query

Per eliminare una patch del piano, utilizza la funzione google_delete_plan_patch(PLAN_PATCH_NAME).