O Google usa tecnologia de IA na tradução de conteúdos para seu idioma de preferência. As traduções com IA podem ter erros.

Criar e gerenciar dicas nomeadas

Nesta página, descrevemos como criar e gerenciar dicas nomeadas no AlloyDB para PostgreSQL.

As dicas nomeadas são uma associação entre uma consulta e um conjunto de dicas que permitem especificar os detalhes do plano de consulta. Uma dica especifica informações adicionais sobre o plano de execução final preferido para a consulta. Por exemplo, ao verificar uma tabela na consulta, use uma verificação de índice em vez de outros tipos de verificações, como uma verificação sequencial.

Para limitar a escolha do plano final na especificação das dicas, o planejador de consultas primeiro aplica as dicas à consulta ao gerar o plano de execução. As dicas são aplicadas automaticamente sempre que a consulta é emitida posteriormente. Essa abordagem permite forçar diferentes planos de consulta do planejador. Por exemplo, é possível usar dicas para forçar uma verificação de índice em determinadas tabelas ou para forçar uma ordem de junção específica entre várias tabelas.

As dicas nomeadas do AlloyDB são compatíveis com todas as dicas da extensão de código aberto pg_hint_plan.

Além disso, o AlloyDB oferece suporte às seguintes dicas para o mecanismo colunar:

ColumnarScan(table): força uma verificação colunar na tabela.
NoColumnarScan(table): desativa a verificação colunar na tabela.

O AlloyDB permite criar dicas nomeadas para consultas parametrizadas e não parametrizadas. Nesta página, as consultas não parametrizadas são chamadas de consultas sensíveis a parâmetros.

Fluxo de trabalho

O uso de dicas nomeadas envolve as seguintes etapas:

Identifique a consulta para a qual você quer criar dicas nomeadas.
Crie dicas nomeadas com dicas a serem aplicadas quando a consulta for executada novamente.
Verifique a aplicação das dicas nomeadas.

Esta página usa a tabela e o índice a seguir para exemplos:

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

  DROP EXTENSION IF EXISTS google_auto_hints;

Para continuar usando as dicas nomeadas que você criou usando uma versão anterior, recrie-as seguindo as instruções nesta página.

Antes de começar

Ative o recurso de dicas nomeadas na instância. Defina a flag alloydb.enable_named_hints como on. É possível ativar essa flag no nível do servidor ou da sessão. Para minimizar o overhead que pode resultar do uso desse recurso, ative essa flag apenas no nível da sessão.

Para mais informações, consulte Configurar flags de banco de dados de uma instância.

**Observação** :para forçar o mesmo plano de consulta na instância do pool de leitura, ative o recurso de dicas nomeadas na instância do pool de leitura.

Para verificar se a flag está ativada, execute o comando show alloydb.enable_named_hints;. Se a flag estiver ativada, a saída vai retornar "on".
Para cada banco de dados em que você quer usar dicas nomeadas, crie uma extensão no banco de dados da instância principal do AlloyDB como o alloydbsuperuser ou o usuário postgres:
```
CREATE EXTENSION google_auto_hints CASCADE;
```

Funções exigidas

Para conseguir as permissões necessárias para criar e gerenciar dicas nomeadas, peça ao administrador que conceda a você as seguintes funções do Identity and Access Management (IAM):

Função alloydbsuperuser

Embora a permissão padrão permita apenas que o usuário com a função alloydbsuperuser crie dicas nomeadas, é possível conceder permissão de gravação aos outros usuários ou funções do banco de dados para que eles possam criar dicas nomeadas.

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;

Identificar a consulta

É possível usar o ID da consulta para identificar a consulta cujo plano padrão precisa ser ajustado. O ID da consulta fica disponível após pelo menos uma execução da consulta.

Use os métodos a seguir para identificar o ID da consulta:

Execute o comando EXPLAIN (VERBOSE), conforme mostrado no exemplo a seguir:

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

Na saída, o ID da consulta é -6875839275481643436.

Consulte a visualização pg_stat_statements.

Se você ativou a extensão pg_stat_statements, é possível encontrar o ID da consulta consultando a visualização pg_stat_statements, conforme mostrado no exemplo a seguir:
```
select query, queryid from pg_stat_statements;
```

Criar dicas nomeadas

Para criar dicas nomeadas, use a função google_create_named_hints(), que cria uma associação entre a consulta e as dicas no banco de dados.

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

Substitua:

HINTS_NAME: um nome para as dicas nomeadas. Ele precisa ser exclusivo no banco de dados.
SQL_ID (opcional): ID da consulta para a qual você está criando as dicas nomeadas.

É possível usar o ID da consulta ou o texto da consulta (o parâmetro SQL_TEXT) para criar dicas nomeadas. No entanto, recomendamos que você use o ID da consulta para criar dicas nomeadas, porque o AlloyDB localiza automaticamente o texto da consulta normalizada com base no ID da consulta.
SQL_TEXT (opcional): texto da consulta para a qual você está criando as dicas nomeadas.

Quando você usa o texto da consulta, ele precisa ser o mesmo da consulta pretendida, exceto pelos valores literais e constantes na consulta. Qualquer incompatibilidade, incluindo diferença de maiúsculas e minúsculas, pode resultar na não aplicação das dicas nomeadas. Para saber como criar dicas nomeadas para consultas com literais e constantes, consulte Criar dicas nomeadas sensíveis a parâmetros.
APPLICATION_NAME (opcional): nome do aplicativo cliente da sessão para o qual você quer usar as dicas nomeadas. Uma string vazia permite aplicar as dicas nomeadas à consulta, independentemente do aplicativo cliente que a emite.
HINTS: uma lista separada por espaços das dicas para a consulta.
DISABLED (opcional): BOOL. Se TRUE, cria as dicas nomeadas inicialmente como desativadas.

Exemplo:

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

Essa consulta cria dicas nomeadas chamadas my_hint1. A dica IndexScan(t) é aplicada pelo planejador para forçar uma verificação de índice na tabela t na próxima execução dessa consulta de exemplo.

Depois de criar dicas nomeadas, é possível usar a google_named_hints_view para confirmar se as dicas nomeadas foram criadas, conforme mostrado no exemplo a seguir:

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

Depois que as dicas nomeadas são criadas na instância principal, elas são aplicadas automaticamente às consultas associadas na instância do pool de leitura, desde que você também tenha ativado o recurso de dicas nomeadas na instância do pool de leitura.

Criar dicas nomeadas sensíveis a parâmetros

Por padrão, quando as dicas nomeadas são criadas para uma consulta, o texto da consulta associada é normalizado substituindo qualquer valor literal e constante no texto da consulta por um marcador de parâmetro, como ?. As dicas nomeadas são usadas para essa consulta normalizada, mesmo com um valor diferente para o marcador de parâmetro.

Por exemplo, a execução da consulta a seguir permite que outra consulta, como SELECT * FROM t WHERE a = 99;, use as dicas nomeadas my_hint2 por padrão.

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

Em seguida, uma consulta, como SELECT * FROM t WHERE a = 99;, pode usar as dicas nomeadas my_hint2 por padrão.

O AlloyDB também permite criar dicas nomeadas para textos de consulta não parametrizados, em que cada valor literal e constante no texto da consulta é significativo ao corresponder a consultas.

Ao aplicar dicas nomeadas sensíveis a parâmetros, duas consultas que diferem apenas nos valores literais ou constantes correspondentes também são consideradas diferentes. Se você quiser forçar planos para ambas as consultas, crie dicas nomeadas separadas para cada consulta. No entanto, é possível usar dicas diferentes para as duas dicas nomeadas.

Para criar dicas nomeadas sensíveis a parâmetros, defina o parâmetro SENSITIVE_TO_PARAM da função google_create_named_hints() como TRUE, conforme mostrado no exemplo a seguir:

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

A consulta SELECT * FROM t WHERE a = 99; não pode usar as dicas nomeadas my_hint3, porque o valor literal "99" não corresponde a "88".

Ao usar dicas nomeadas sensíveis a parâmetros, considere o seguinte:

As dicas nomeadas sensíveis a parâmetros não oferecem suporte a uma combinação de valores literais e constantes e marcadores de parâmetros no texto da consulta.
Ao criar dicas nomeadas sensíveis a parâmetros e dicas nomeadas padrão para a mesma consulta, as dicas nomeadas sensíveis a parâmetros são preferidas em relação às dicas nomeadas padrão.
Se você quiser usar o ID da consulta para criar dicas nomeadas sensíveis a parâmetros, verifique se a consulta foi executada na sessão atual. Os valores de parâmetro da execução mais recente (na sessão atual) são usados para criar as dicas nomeadas.

Verificar a aplicação das dicas nomeadas

Depois de criar as dicas nomeadas, use os métodos a seguir para verificar se o plano de consulta é forçado de acordo.

Use o comando EXPLAIN ou o comando EXPLAIN (ANALYZE).

Para conferir as dicas que o planejador está tentando aplicar, defina as flags a seguir no nível da sessão antes de executar o comando EXPLAIN:
```
SET pg_hint_plan.debug_print = ON;
SET client_min_messages = LOG;
```
Use a extensão auto_explain.

Gerenciar dicas nomeadas

O AlloyDB permite visualizar, ativar, desativar e excluir dicas nomeadas.

Visualizar dicas nomeadas

Para visualizar as dicas nomeadas atuais, use a função google_named_hints_view, conforme mostrado no exemplo a seguir:

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

Ativar dicas nomeadas

Para ativar as dicas nomeadas atuais, use a função google_enable_named_hints(HINTS_NAME). Por padrão, as dicas nomeadas são ativadas quando você as cria.

Por exemplo, para reativar as dicas nomeadas my_hint1 desativadas anteriormente no banco de dados, execute a seguinte função:

SELECT google_enable_named_hints('my_hint1');

Desativar dicas nomeadas

Para desativar as dicas nomeadas atuais, use a função google_disable_named_hints(HINTS_NAME).

Por exemplo, para excluir as dicas nomeadas de exemplo my_hint1 do banco de dados, execute a seguinte função:

SELECT google_disable_named_hints('my_hint1');

Excluir dicas nomeadas

Para excluir dicas nomeadas, use a função google_delete_named_hints(HINTS_NAME).