Usar planos gerenciados do Aurora PostgreSQL

Para fazer o otimizador usar planos capturados para as instruções gerenciadas, defina o parâmetro apg_plan_mgmt.use_plan_baselines como true. Veja uma instância local de exemplo.

SET apg_plan_mgmt.use_plan_baselines = true;

Enquanto a aplicação estiver em execução, essa configuração fará com que o otimizador use o plano de custo mínimo, preferido ou aprovado que seja válido e esteja habilitado em cada instrução gerenciada.

Analisar o plano selecionado pelo otimizador

Quando o parâmetro apg_plan_mgmt.use_plan_baselines é definido como true, use instruções EXPLAIN ANALYZE SQL para fazer o otimizador mostrar o plano que ele usaria se fosse executar a instrução. Veja um exemplo a seguir.

EXPLAIN ANALYZE EXECUTE rangeQuery (1,10000);

                                                    QUERY PLAN           
--------------------------------------------------------------------------
 Aggregate  (cost=393.29..393.30 rows=1 width=8) (actual time=7.251..7.251 rows=1 loops=1)
   ->  Index Only Scan using t1_pkey on t1 t  (cost=0.29..368.29 rows=10000 width=0) (actual time=0.061..4.859 rows=10000 loops=1)
Index Cond: ((id >= 1) AND (id <= 10000))         
         Heap Fetches: 10000
 Planning time: 1.408 ms
 Execution time: 7.291 ms
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: 1984047223, Plan Hash: 512153379

O resultado mostra o plano aprovado que seria executado a partir da linha de base. No entanto, o resultado também mostra que ele encontrou um plano de custo mais baixo. Neste caso, você captura esse novo plano de custo mínimo ativando a captura de planos automáticos conforme descrito em Capturar planos automaticamente.

Novos planos são sempre capturados pelo otimizador como Unapproved. Use a função apg_plan_mgmt.evolve_plan_baselines para comparar planos e alterá-los para aprovados, rejeitados ou desabilitados. Para obter mais informações, consulte Avaliar performance do plano.

Como o otimizador escolhe que plano executar.

O custo de um plano de execução é uma estimativa feita pelo otimizador para comparar planos diferentes. Ao calcular o custo de um plano, o otimizador inclui fatores como operações de CPU e E/S exigidas por esse plano. Para saber mais sobre as estimativas de custo do planejador de consultas PostgreSQL, consulte Planejamento de consultas na documentação do PostgreSQL.

A imagem a seguir mostra como um plano é selecionado para determinada instrução SQL quando o gerenciamento de planos de consulta está ativo e quando não está.

O fluxo é o seguinte:

1. O otimizador gera um plano de custo mínimo para a instrução SQL.

2. Se o gerenciamento do plano de consulta não estiver ativo, o plano do otimizador será executado imediatamente (A. Executar o plano do otimizador). O gerenciamento de planos de consultas está inativo quando os parâmetros apg_plan_mgmt.capture_plan_baselines e apg_plan_mgmt.use_plan_baselines estão em suas configurações padrão (“off” e “false”, respectivamente).

   Caso contrário, o gerenciamento de planos de consultas estará ativo. Nesse caso, a instrução SQL e o plano do otimizador para ela são avaliados em mais detalhes antes que um plano seja escolhido.

   dica

   Usuários do banco de dados com o perfil apg_plan_mgmt podem comparar planos de maneira proativa, alterar o status dos planos e forçar o uso de planos específicos, conforme necessário. Para obter mais informações, consulte Manutenção dos planos de execução do Aurora PostgreSQL.

3. A instrução SQL pode já ter planos que foram armazenados pelo gerenciamento de planos de consultas no passado. Os planos são armazenados em apg_plan_mgmt.dba_plans, junto com informações sobre as instruções SQL que foram usadas para criá-los. As informações sobre um plano incluem seu status. O status de um plano pode determinar se ele é usado ou não, conforme mostrado a seguir.

   1. Se o plano não estiver entre os planos armazenados para a instrução SQL, significa que é a primeira vez que esse plano específico foi gerado pelo otimizador para a instrução SQL fornecida. O plano é enviado para o processamento de captura de plano (4).

   2. Se o plano estiver entre os planos armazenados e seu status for Aprovado ou Preferencial, o plano será executado (A. Executar o plano do otimizador).

      Se o plano estiver entre os planos armazenados, mas não for Aprovado nem Preferencial, o plano será enviado para o processamento de captura de plano (4).

4. Quando um plano é capturado pela primeira vez para determinada instrução SQL, o status do plano é sempre definido como Aprovado (P1). Se o otimizador gerar posteriormente o mesmo plano para a mesma instrução SQL, o status desse plano será alterado para Não aprovado (P1+n).

   Com o plano capturado e seu status atualizado, a avaliação continua na próxima etapa (5).

5. A linha de base de um plano consiste no histórico da instrução SQL e em seus planos em vários estados. O gerenciamento de planos de consultas pode levar em consideração a linha de base ao escolher um plano, dependendo se a opção de usar linhas de base do plano está ativada ou não, conforme mostrado a seguir.

   • A opção de usar linhas de base do plano está “off” quando o parâmetro apg_plan_mgmt.use_plan_baselines está definido com o valor padrão (false). O plano não é comparado com a linha de base antes de ser executado (A. Executar plano do otimizador).

   • A opção de usar linhas de base do plano está “on” quando o parâmetro apg_plan_mgmt.use_plan_baselines está definido como true. O plano é avaliado em mais detalhes usando a linha de base (6).

6. O plano é comparado a outros planos para a instrução na linha de base.

   1. Se o plano do otimizador estiver entre os planos na linha de base, seu status será verificado (7a).

   2. Se o plano do otimizador não estiver entre os planos na linha de base, o plano será adicionado aos planos da instrução como um novo plano Unapproved.

7. O status do plano é verificado somente para determinar se ele é Não aprovado.

   1. Se o status do plano for Não aprovado, o custo estimado do plano será comparado com a estimativa de custo especificada para o limite do plano de execução não aprovado.

      • Se o custo estimado do plano estiver abaixo do limite, o otimizador o usará mesmo sendo um plano Não aprovado (A. Executar o plano do otimizador). Geralmente, o otimizador não executa um plano Não aprovado. No entanto, quando o parâmetro apg_plan_mgmt.unapproved_plan_execution_threshold especifica um valor de limite de custo, o otimizador compara o custo do plano Não aprovado com o limite. Se o custo estimado for menor que o limite, o otimizador executará o plano. Para obter mais informações, consulte apg_plan_mgmt.unapproved_plan_execution_threshold.

      • Se o custo estimado do plano não estiver abaixo do limite, os outros atributos do plano serão verificados (8a).

   2. Se o status do plano for diferente de Não aprovado, seus outros atributos serão verificados (8a).

8. O otimizador não usará um plano desativado. Ou seja, um plano com o atributo enable definido como “f” (false). O otimizador também não usará um plano com status Rejeitado.

   O otimizador não pode usar nenhum plano que não seja válido. Os planos podem se tornar inválidos ao longo do tempo quando os objetos dos quais dependem, como índices e partições de tabelas, são removidos ou excluídos.

   1. Se a instrução tiver planos habilitados e válidos com status Preferencial, o otimizador escolherá o plano de custo mínimo dentre os planos com status Preferencial armazenados para essa instrução SQL. Depois, o otimizador executa o plano Preferencial de custo mínimo.

   2. Se a instrução não tiver nenhum plano Preferencial habilitado e válido, será avaliada na próxima etapa (9).

9. Se a instrução tiver planos habilitados e válidos com status Aprovado, o otimizador escolherá o plano de custo mínimo dentre os planos com status Aprovado armazenados para essa instrução SQL. Depois, o otimizador executa o plano Aprovado de custo mínimo.

   Se a instrução não tiver nenhum plano Aprovado habilitado e válido, o otimizador usará o plano de custo mínimo (A. Executar o plano do otimizador).