Gerenciar planos de consulta

Este documento descreve como melhorar a performance do plano de consulta usando o gerenciamento de planos de consulta no AlloyDB para PostgreSQL. O gerenciamento de planos de consulta acompanha continuamente todos os planos de consulta e as estatísticas de execução deles no banco de dados. Depois de analisar as consultas e os custos associados a elas, você pode aprovar um plano, que é aplicado de forma consistente a uma determinada consulta. Essa abordagem garante a seleção de um plano de consulta econômico, o que leva a uma melhor performance da consulta.

Como funciona

No PostgreSQL, um otimizador de consultas seleciona um plano de execução para cada consulta com base nos custos estimados. Muitos fatores influenciam o custo da consulta, por exemplo, parâmetros de consulta, complexidade da consulta, tamanho da tabela, índices disponíveis e recursos do sistema.

Como os parâmetros de consulta podem mudar a cada execução, a seleção dinâmica de um plano de consulta nem sempre gera resultados ideais. Ao processar uma consulta, o otimizador avalia vários planos de execução e tenta escolher o mais econômico.

Uma mudança de parâmetro pode resultar em uma mudança de plano. Embora o plano selecionado seja geralmente a opção mais econômica, pode haver instâncias em que um plano menos econômico seja escolhido, o que resulta em uma performance ruim da consulta. O gerenciamento de planos de consulta ajuda você a entender os padrões e planos produzidos pelo otimizador e permite visualizar cada plano para tomar decisões informadas.

Os dois principais componentes do gerenciamento de planos de consulta são os seguintes:

Repositório de planos de consulta: Quando você ativa o gerenciamento de planos de consulta para seu banco de dados, o repositório de planos começa a rastrear planos históricos e estatísticas de execução no banco de dados. O repositório de planos de consulta oferece observabilidade sobre a performance do plano de consulta.
Gerenciamento de planos: Depois de analisar os planos disponíveis, o componente de gerenciamento de planos permite aprovar um ou mais planos para um modelo de consulta específico. O gerenciamento de planos de consulta rastreia esses planos aprovados e garante que, quando a consulta for executada posteriormente, o otimizador de consultas use apenas um dos planos aprovados. Se vários planos forem aprovados, o AlloyDB selecionará e executará o plano com o menor custo estimado.

Antes de começar

Defina a flag de banco de dados google_plan_management.enabled como on. Para mais informações, consulte Configurar flags de banco de dados.
Crie a extensão google_plan_management no banco de dados. Para mais informações, consulte Ativar uma extensão.
Conceda google_plan_management_role aos usuários do banco de dados que querem usar o gerenciamento de plano de consulta e gerenciar planos de consulta.
1. Noconsole, acesse a página Clusters do AlloyDB. Google Cloud
  
  Acessar Clusters
2. Clique na instância necessária.
3. Clique em AlloyDB Studio e depois na guia Editor 1.
4. Digite a seguinte consulta:
```
GRANT google_plan_management_role TO DATABASE_USER;
```
  Substitua DATABASE_USER pelo usuário a quem você quer conceder o papel.
5. Clique em Executar.

Ver planos de consulta rastreados

O gerenciamento de planos de consulta oferece uma visualização de planos de consulta que mostra todos os planos de consulta rastreados, os tempos de execução e outras informações. Um plano de consulta rastreado é um plano de consulta gerado pelo otimizador e armazenado no repositório de planos.

Para visualizar os planos históricos rastreados, execute a seguinte consulta:

SELECT * FROM google_plan_management.tracked_plans_view;

A resposta da consulta é semelhante a esta:

db_id                | 5
db_name              | postgres
user_id              | 16392
user_name            | postgres
logical_query_id     | 15480571796188147798
plan_id              | 4740866759615354783
query                | SELECT c1, c2, c3 FROM t1 WHERE c1 = 1;
plan                 | Seq Scan on public.t1                                                                                                                                                            +
                     |   Output: c1, c2, c3                                                                                                                                                             +
                     |   Filter: (t1.c1 = ?)
total_execution_time | 0.003937501
num_executions       | 1
creation_time        | 2024-11-06 16:52:25.200737+00
last_used_time       | 2024-11-06 16:52:25.200737+00

Desativar o rastreamento de planos de consulta

Se você não quiser que o gerenciamento de planos de consulta rastreie os planos de consulta gerados pelo otimizador, desative a flag de banco de dados google_plan_management.enable_track_plans. Essa flag é ativada por padrão, e recomendamos que você a deixe ativada. Para mais informações, consulte Configurar flags de banco de dados.

Ver planos gerenciados

É possível visualizar todas as consultas e planos gerenciados pelo gerenciamento de planos de consulta, incluindo planos aprovados e negados.

Para visualizar os planos gerenciados, execute a seguinte consulta:

SELECT * FROM google_plan_management.managed_plans_view;

A resposta da consulta é semelhante a esta:

db_id            | 5
db_name          | postgres
user_id          | 16392
user_name        | postgres
logical_query_id | 15480571796188147798
plan_id          | 4740866759615354783
status           | approved

Aprovar um plano

O otimizador seleciona um plano de consulta dinamicamente, o que significa que ele pode escolher planos de consulta diferentes para a mesma consulta em momentos diferentes. Para impor uma seleção de plano consistente, use o gerenciamento de planos de consulta para aprovar um ou mais planos de consulta para uma determinada consulta.

Se você aprovar vários planos, o gerenciamento de planos de consulta vai comparar todos os planos aprovados e selecionar o plano mais econômico para a execução da consulta.

Para avaliar e aprovar um plano para uma consulta, siga estas etapas:

Visualize os planos rastreados que o otimizador gerou e identifique o logical_query_id na resposta.
Analise todos os planos gerados para o logical_query_id. É possível calcular o tempo médio de execução de cada plano usando os valores total_execution_time e num_executions e, em seguida, decidir o melhor plano para sua consulta.

A coluna plan inclui outros detalhes, como o índice usado ou o método de classificação usado, que podem ajudar você a decidir sobre um plano de consulta.
Aprove o plano que você quer aplicar à consulta executando a seguinte consulta:
```
  SELECT google_plan_management.approve_plan(QUERY_ID, PLAN_ID);
```
Substitua:
- QUERY_ID: o logical_query_id exclusivo da consulta. Cada ID de consulta pode estar associado a vários IDs de plano.
- PLAN_ID: o plan_id exclusivo da consulta.

Negar um plano

É possível negar qualquer plano aprovado para uma consulta e impedir que o gerenciamento de planos de consulta aplique o plano à consulta. Os planos negados não são excluídos e estão disponíveis na lista de planos rastreados.

Para negar um plano aprovado, execute a seguinte consulta:

    SELECT google_plan_management.deny_plan(QUERY_ID, PLAN_ID);

Excluir um plano aprovado

É possível excluir um plano aprovado do repositório de planos. Quando você exclui um plano aprovado, ele não aparece mais na lista de planos gerenciados.

Para excluir um plano aprovado, execute a seguinte consulta:

    SELECT google_plan_management.update_plan_status(QUERY_ID, PLAN_ID, 'delete');

Interromper temporariamente o uso de planos aprovados

Se você quiser interromper temporariamente o uso de planos aprovados para suas consultas, desative a flag de banco de dados google_plan_management.enable_steer_plans. Essa flag é ativada por padrão. Para mais informações, consulte Configurar flags de banco de dados.

Limitações

Não é possível usar o gerenciamento de planos de consulta em tabelas particionadas ou conjuntos de agrupamento.
O gerenciamento de planos de consulta é compatível apenas com a instância principal.
O repositório de planos de consulta pode armazenar até 100 mil planos exclusivos e não oferece uma política de retenção.