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 mais informações 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 dentro da 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 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 é compatível com as 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

Para usar dicas nomeadas, siga estas 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 como 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 com uma versão anterior, recrie-as seguindo as instruções nesta página.

Antes de começar

Ative o recurso de dicas nomeadas na sua 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 a sobrecarga que pode resultar do uso desse recurso, ative a 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 usuário alloydbsuperuser ou 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 para conceder a você os seguintes papéis do Identity and Access Management (IAM):

Papel alloydbsuperuser

Embora a permissão padrão permita apenas que o usuário com o papel alloydbsuperuser crie dicas nomeadas, é possível conceder permissão de gravação aos outros usuários ou papéis do banco de dados para que eles também 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

Use o ID para identificar a consulta cujo plano padrão precisa de ajuste. O ID da consulta fica disponível depois de pelo menos uma execução.

Use os seguintes métodos 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 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.
SQL_TEXT (opcional): texto da consulta para que você está criando as dicas nomeadas.

Quando você usa o texto da consulta, ele precisa ser igual à consulta pretendida, exceto pelos valores literais e constantes. Qualquer incompatibilidade, incluindo diferença de maiúsculas e minúsculas, pode impedir que as dicas nomeadas sejam aplicadas. 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, independente do aplicativo cliente que a emitiu.
HINTS: uma lista separada por espaços das dicas para a consulta.
DISABLED (opcional): BOOL. Se TRUE, cria inicialmente as dicas nomeadas 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 uma dica nomeada chamada 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 desta consulta de exemplo.

Depois de criar dicas nomeadas, use o google_named_hints_view para confirmar se a dica nomeada foi criada, 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 dicas nomeadas são criadas para uma consulta, o texto associado é 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, executar a 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);

Então, 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 quiser forçar planos para as duas consultas, crie dicas nomeadas separadas para cada uma. No entanto, é possível usar dicas diferentes para as duas dicas nomeadas.

Para criar dicas 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 aceitam uma mistura de valores literais e constantes e marcadores de parâmetros no texto da consulta.
Quando você cria dicas nomeadas sensíveis a parâmetros e uma dica nomeada 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 está sendo forçado de acordo.

Use o comando EXPLAIN ou EXPLAIN (ANALYZE).

Para conferir as dicas que o planejador está tentando aplicar, defina as seguintes flags 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

Com o AlloyDB, é possível ver, ativar, desativar e excluir dicas nomeadas.

Ver dicas nomeadas

Para ver 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 dicas nomeadas, 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 do banco de dados, execute a seguinte função:

SELECT google_enable_named_hints('my_hint1');

Desativar dicas nomeadas

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

Por exemplo, para excluir o exemplo chamado hints 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).