Gerenciar planos de consulta

Neste documento, descrevemos 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 rastreia 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 melhora o desempenho da consulta.

Como funciona

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

Como os parâmetros de consulta podem mudar a cada execução, selecionar um plano de consulta dinamicamente nem sempre gera os melhores resultados. 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 geralmente seja a opção mais econômica, pode haver casos em que um plano menos eficiente é escolhido, o que resulta em um desempenho 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 mais conscientes.

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

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 capacidade de observação sobre o desempenho do plano de consulta.
Gerenciamento de planos
Depois de analisar os planos disponíveis, o componente de gerenciamento 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 do 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 e gerenciar planos de consulta.

    1. No console Google Cloud , acesse a página Clusters do AlloyDB.

      Acessar Clusters

    2. Clique na instância necessária.

    3. Clique em AlloyDB Studio e 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 a função.

    5. Clique em Executar.

Ver planos de consulta rastreados

O gerenciamento de planos de consulta oferece uma visualização 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 ver os planos rastreados históricos, execute a seguinte consulta:

SELECT * FROM google_plan_management.tracked_plans_view;

A resposta à 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 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

Você pode conferir todas as consultas e planos gerenciados pelo gerenciamento de planos de consulta, incluindo os aprovados e negados.

Para ver os planos gerenciados, execute a seguinte consulta:

SELECT * FROM google_plan_management.managed_plans_view;

A resposta à 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 de forma dinâmica, o que significa que ele pode escolher planos diferentes para a mesma consulta em momentos diferentes. Para impor uma seleção de plano consistente, use o gerenciamento de plano 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 mais econômico para a execução da consulta.

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

  1. Confira os planos monitorados que o otimizador gerou e identifique o logical_query_id na resposta.

  2. Revise 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 decidir qual é o melhor plano para sua consulta.

    A coluna plan inclui outros detalhes, como o índice usado ou o método de classificação, que podem ajudar você a decidir sobre um plano de consulta.

  3. Aprove o plano que você quer aplicar à consulta executando o seguinte comando:

      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 ser associado a vários IDs de plano.
    • PLAN_ID: o plan_id exclusivo da consulta.

Negar um plano

Você pode negar qualquer plano aprovado para uma consulta e impedir que o gerenciamento de plano de consulta aplique o plano a ela. Os planos negados não são excluídos e ficam 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');

Parar de usar temporariamente os planos aprovados

Se você quiser parar de usar temporariamente os planos aprovados para suas consultas, desative a flag do banco de dados google_plan_management.enable_steer_plans. Essa sinalização é 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 só é compatível com a instância principal.
  • O repositório de planos de consulta pode armazenar até 100 mil planos únicos e não oferece uma política de retenção.

A seguir