Esta página foi traduzida pela API Cloud Translation.

Crie e faça a gestão de patches de planos de consultas

Esta página descreve como criar e gerir patches de planos de consultas no AlloyDB for PostgreSQL.

Um patch do plano de consulta é uma associação entre uma consulta e um conjunto de sugestões que lhe permitem especificar os detalhes do plano de consulta. Uma sugestão especifica informações adicionais sobre o plano de execução final preferido para a consulta. Por exemplo, quando analisa uma tabela na consulta, use uma análise de índice em vez de outros tipos de análises, como uma análise sequencial.

Para limitar a escolha final do plano à especificação das sugestões, o planeador de consultas aplica primeiro as sugestões à consulta enquanto gera o respetivo plano de execução. As sugestões são, em seguida, aplicadas automaticamente sempre que a consulta for emitida posteriormente. Esta abordagem permite-lhe forçar diferentes planos de consulta do planeador. Por exemplo, pode usar sugestões para forçar uma análise de índice em determinadas tabelas ou para forçar uma ordem de junção específica entre várias tabelas.

A correção do plano de consulta do AlloyDB suporta todas as sugestões da extensão de código abertopg_hint_plan.

Além disso, o AlloyDB suporta as seguintes sugestões para o motor colunar:

ColumnarScan(table): força uma análise por colunas na tabela.
NoColumnarScan(table): desativa a leitura em colunas na tabela.

O AlloyDB permite-lhe criar patches de planos para consultas parametrizadas e não parametrizadas. Nesta página, as consultas não parametrizadas são denominadas consultas sensíveis a parâmetros.

Fluxo de trabalho

A aplicação de uma correção de um plano de consulta envolve os seguintes passos:

Identifique a consulta para a qual quer criar uma correção de plano.
Crie uma correção de plano com sugestões a aplicar quando a consulta for executada novamente.
Valide a aplicação da correção do plano.

Esta página usa a tabela e o índice seguintes 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 a usar as correções do plano de consulta que criou com uma versão anterior, recrie-as seguindo as instruções nesta página.

Antes de começar

Ative a funcionalidade de patch do plano de consulta na sua instância. Defina a flag alloydb.enable_query_plan_patch como on. Pode ativar esta flag ao nível de todo o servidor ou ao nível da sessão. Para minimizar a sobrecarga que possa resultar da utilização desta funcionalidade, ative esta flag apenas ao nível da sessão.

Para mais informações, consulte o artigo Configure as flags da base de dados de uma instância.

Nota: para forçar o mesmo plano de consulta na instância do conjunto de leitura, ative a funcionalidade de patch do plano de consulta na instância do conjunto de leitura.

Para verificar se a flag está ativada, execute o comando show alloydb.enable_query_plan_patch;. Se o sinalizador estiver ativado, o resultado devolve "on".
Para cada base de dados na qual quer usar patches do plano de consulta, crie uma extensão na base de dados a partir da instância principal do AlloyDB como o utilizador alloydbsuperuser ou postgres:
```
CREATE EXTENSION google_auto_hints CASCADE;
```

Funções necessárias

Para receber as autorizações necessárias para criar e gerir patches de planos de consulta, peça ao seu administrador para lhe conceder as seguintes funções de gestão de identidade e acesso (IAM):

Função de alloydbsuperuser

Embora a autorização predefinida permita apenas que o utilizador com a função alloydbsuperuser crie correções de planos, pode conceder opcionalmente a autorização de gravação aos outros utilizadores ou funções da base de dados para que possam criar correções de planos.

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;

Identifique a consulta

Pode usar o ID da consulta para identificar a consulta cujo plano predefinido precisa de otimização. O ID da consulta fica disponível após, pelo menos, uma execução da consulta.

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

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

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.

Consultar a vista pg_stat_statements.

Se ativou a extensão pg_stat_statements, pode encontrar o ID da consulta consultando a vista pg_stat_statements, conforme mostrado no exemplo seguinte:
```
select query, queryid from pg_stat_statements;
```

Crie um patch do plano de consulta

Para criar uma correção do plano de consulta, use a função google_create_plan_patch(), que cria uma associação entre a consulta e as sugestões na base 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 o seguinte:

PLAN_PATCH_NAME: um nome para a correção do plano. Este valor tem de ser exclusivo na base de dados.
SQL_ID (Opcional): ID da consulta para a qual está a criar a correção do plano.

Pode usar o ID da consulta ou o texto da consulta, ou seja, o parâmetro SQL_TEXT, para criar uma correção do plano. No entanto, recomendamos que use o ID da consulta para criar uma correção do plano porque o AlloyDB localiza automaticamente o texto da consulta normalizado com base no ID da consulta.
SQL_TEXT (Opcional): texto de consulta da consulta para a qual está a criar a correção do plano.

Quando usa o texto da consulta, o texto tem de ser igual à consulta pretendida, exceto para os valores literais e constantes na consulta. Qualquer incompatibilidade, incluindo a diferença entre maiúsculas e minúsculas, pode fazer com que a correção do plano não seja aplicada. Para saber como criar patches de planos para consultas com literais e constantes, consulte o artigo Crie um patch de plano de consulta sensível a parâmetros.
APPLICATION_NAME (Opcional): nome da aplicação cliente da sessão para a qual quer usar o patch do plano. Uma string vazia permite-lhe aplicar a correção do plano à consulta, independentemente da aplicação cliente que emite a consulta.
HINTS: uma lista separada por espaços das sugestões para a consulta.
DISABLED (Opcional): BOOL. Se TRUE, cria inicialmente a correção do plano como desativada.

Exemplo:

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

Esta consulta cria um patch de plano denominado my_hint1. A sugestão IndexScan(t) é aplicada pelo planeador para forçar uma análise de índice na tabela t na próxima execução desta consulta de exemplo.

Depois de criar um patch do plano, pode usar o comando google_query_plan_patch_view para confirmar se o patch do plano foi criado, conforme mostrado no exemplo seguinte:

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 de o patch do plano ser criado na instância principal, é aplicado automaticamente às consultas associadas na instância do conjunto de leitura, desde que também tenha ativado a funcionalidade de patch do plano de consulta na instância do conjunto de leitura.

Crie uma correção do plano de consulta sensível a parâmetros

Por predefinição, quando é criado um patch de plano para uma consulta, o texto da consulta associado é normalizado através da substituição de qualquer valor literal e constante no texto da consulta por um marcador de parâmetro, como ?. Em seguida, a correção do plano é usada para essa consulta normalizada, mesmo com um valor diferente para o marcador de parâmetro.

Por exemplo, a execução da seguinte consulta permite que outra consulta, como SELECT * FROM t WHERE a = 99;, use a correção do plano my_hint2 por predefiniçã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);

Em seguida, uma consulta, como SELECT * FROM t WHERE a = 99;, pode usar o patch my_hint2 por predefiniçã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 de consulta é significativo quando se encontram consultas correspondentes.

Quando aplica uma correção de plano sensível a parâmetros, duas consultas que apenas diferem nos valores literais ou constantes correspondentes também são consideradas diferentes. Se quiser forçar planos para ambas as consultas, tem de criar patches de planos separados para cada consulta. No entanto, pode usar sugestões diferentes para os dois patches de planos.

Para criar uma correção do plano sensível a parâmetros, defina o SENSITIVE_TO_PARAMparâmetro da função google_create_plan_patch() como TRUE, conforme mostrado no exemplo seguinte:

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 a correção do plano my_hint3, porque o valor literal "99" não corresponde a "88".

Quando usa patches de planos sensíveis a parâmetros, considere o seguinte:

As correções de planos sensíveis a parâmetros não suportam uma combinação de valores literais e constantes, bem como marcadores de parâmetros no texto da consulta.
Quando cria uma correção do plano sensível a parâmetros e uma correção do plano predefinida para a mesma consulta, a correção do plano sensível a parâmetros tem preferência em relação à correção predefinida.
Se quiser usar o ID da consulta para criar uma correção do plano sensível a parâmetros, certifique-se de que a consulta foi executada na sessão atual. Os valores dos parâmetros da execução mais recente (na sessão atual) são usados para criar a correção do plano.

Valide a aplicação da correção do plano de consulta

Depois de criar a correção do plano, use os seguintes métodos para verificar se o plano de consulta é forçado em conformidade.

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

Para ver as sugestões que o planeador está a tentar aplicar, pode definir as seguintes flags ao 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.

Faça a gestão das correções de planos de consultas

O AlloyDB permite-lhe ver, ativar, desativar e eliminar uma correção do plano de consulta.

Veja um patch do plano de consulta

Para ver as correções de planos existentes, use a função google_query_plan_patch_view, como mostrado no exemplo seguinte:

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

Ative uma correção do plano de consulta

Para ativar uma correção de plano existente, use a função google_enable_plan_patch(PLAN_PATCH_NAME). Por predefinição, uma correção do plano é ativada quando a cria.

Por exemplo, para reativar a correção do plano my_hint1 desativada anteriormente na base de dados, execute a seguinte função:

SELECT google_enable_plan_patch('my_hint1');

Desative uma correção do plano de consulta

Para desativar uma correção de plano existente, use a função google_disable_plan_patch(PLAN_PATCH_NAME).

Por exemplo, para eliminar a correção do plano de exemplo my_hint1 da base de dados, execute a seguinte função:

SELECT google_disable_plan_patch('my_hint1');

Elimine uma correção do plano de consulta

Para eliminar uma correção de plano, use a função google_delete_plan_patch(PLAN_PATCH_NAME).