Esta página foi traduzida pela API Cloud Translation.

Criar e gerenciar patches de plano de consulta

Nesta página, descrevemos como criar e gerenciar patches de plano de consulta no AlloyDB para PostgreSQL.

Um patch do plano de consulta é 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.

O patch do plano de consulta do AlloyDB é compatível 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.

Com o AlloyDB, é possível criar patches de plano 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 um patch de plano de consulta envolve as seguintes etapas:

Identifique a consulta para a qual você quer criar um patch de plano.
Crie um patch de plano com dicas a serem aplicadas na próxima execução da consulta.
Verifique a aplicação do patch do plano.

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 os patches de plano de consulta que você criou com uma versão anterior, recrie-os seguindo as instruções nesta página.

Antes de começar

Ative o recurso de patch do plano de consulta na sua instância. Defina a flag alloydb.enable_query_plan_patch 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 patch do plano de consulta na instância do pool de leitura.

Para verificar se a flag está ativada, execute o comando show alloydb.enable_query_plan_patch;. Se a flag estiver ativada, a saída vai retornar "on".
Para cada banco de dados em que você quer usar patches de plano de consulta, 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 ter as permissões necessárias para criar e gerenciar patches de plano de consulta, peça ao administrador que conceda 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 a função alloydbsuperuser crie patches de plano, é possível conceder permissão de gravação aos outros usuários ou funções do banco de dados para que eles possam criar patches de plano.

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 da consulta para identificar aquela cujo plano padrão precisa de ajuste. O ID da consulta fica disponível após pelo menos uma execução.

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 um patch de plano de consulta

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

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

Substitua:

PLAN_PATCH_NAME: um nome para o patch do plano. Ele precisa ser exclusivo no banco de dados.
SQL_ID (opcional): ID da consulta para a qual você está criando o patch do plano.

É possível usar o ID ou o texto da consulta (o parâmetro SQL_TEXT) para criar um patch de plano. No entanto, recomendamos que você use o ID da consulta para criar um patch de plano, 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 o patch do plano.

Quando você usa o texto da consulta, ele precisa ser igual à consulta pretendida, exceto pelos valores literais e constantes na consulta. Qualquer incompatibilidade, incluindo diferença de maiúsculas e minúsculas, pode impedir a aplicação do patch do plano. Para saber como criar patches de plano para consultas com literais e constantes, consulte Criar um patch de plano de consulta sensível a parâmetros.
APPLICATION_NAME (opcional): nome do aplicativo cliente da sessão para o qual você quer usar o patch do plano. Uma string vazia permite aplicar o patch do plano à 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, inicialmente criar o patch do plano inicialmente como desativado.

Exemplo:

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

Essa consulta cria um patch de plano chamado 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 um patch de plano, use o google_query_plan_patch_view para confirmar se ele foi criado, conforme mostrado no exemplo a seguir:

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

Depois que o patch de plano é criado na instância principal, ele é aplicado automaticamente às consultas associadas na instância do pool de leitura, desde que você tenha ativado o recurso de patch de plano de consulta também na instância do pool de leitura.

Criar um patch de plano de consulta sensível a parâmetros

Por padrão, quando um patch de plano é criado para uma consulta, o texto associado é normalizado. Isso é feito substituindo qualquer valor literal e constante no texto da consulta por um marcador de parâmetro, como ?. O patch de plano é usado 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 o patch de plano my_hint2 por padrão.

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

Então, uma consulta, como SELECT * FROM t WHERE a = 99;, pode usar o patch my_hint2 do plano por padrão.

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

Quando você aplica um patch de plano sensível 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 patches de plano separados para cada uma delas. No entanto, é possível usar dicas diferentes para os dois patches de plano.

Para criar um patch de plano sensível a parâmetros, defina o parâmetro SENSITIVE_TO_PARAM da função google_create_plan_patch() como TRUE, conforme mostrado no exemplo a seguir:

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

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

Ao usar patches de plano sensíveis a parâmetros, considere o seguinte:

Os patches de plano 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 um patch de plano sensível a parâmetros e um patch de plano padrão para a mesma consulta, o patch de plano sensível a parâmetros tem preferência em relação ao patch padrão.
Se você quiser usar o ID da consulta para criar um patch de plano sensível 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 o patch do plano.

Verificar a aplicação do patch do plano de consulta

Depois de criar o patch do plano, use os métodos a seguir para verificar se o plano de consulta foi 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 patches de plano de consulta

O AlloyDB permite visualizar, ativar, desativar e excluir um patch de plano de consulta.

Ver um patch de plano de consulta

Para conferir os patches de plano atuais, use a função google_query_plan_patch_view, conforme mostrado no exemplo a seguir:

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

Ativar um patch de plano de consulta

Para ativar um patch de plano, use a função google_enable_plan_patch(PLAN_PATCH_NAME). Por padrão, um patch de plano é ativado quando você o cria.

Por exemplo, para reativar o patch de plano my_hint1 desativado anteriormente do banco de dados, execute a seguinte função:

SELECT google_enable_plan_patch('my_hint1');

Desativar um patch de plano de consulta

Para desativar um patch de plano, use a função google_disable_plan_patch(PLAN_PATCH_NAME).

Por exemplo, para excluir o patch de plano de exemplo my_hint1 do banco de dados, execute a seguinte função:

SELECT google_disable_plan_patch('my_hint1');

Excluir um patch de plano de consulta

Para excluir um patch de plano, use a função google_delete_plan_patch(PLAN_PATCH_NAME).