Melhorar os planos de consulta do Aurora PostgreSQL

Melhore o gerenciamento dos planos de consulta avaliando a performance e corrigindo os planos. Para ter mais informações sobre como melhorar planos de consulta, confira os tópicos a seguir.

Avaliar performance do plano

Depois que o otimizador capturar planos como não aprovados, use a função apg_plan_mgmt.evolve_plan_baselines para comparar planos com base na performance real. Dependendo do resultado dos experimentos de performance, altere o status de um plano de não aprovado para aprovado ou rejeitado. Em vez disso, opte por usar a função apg_plan_mgmt.evolve_plan_baselines para desabilitar temporariamente um plano caso ele não atenda aos requisitos.

Aprovar planos melhores

O exemplo a seguir demonstra como alterar o status de planos gerenciados para aprovados usando a função apg_plan_mgmt.evolve_plan_baselines.

SELECT apg_plan_mgmt.evolve_plan_baselines (
   sql_hash, 
   plan_hash, 
   min_speedup_factor := 1.0, 
   action := 'approve'
) 
FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved';

NOTICE:     rangequery (1,10000)
NOTICE:     Baseline   [ Planning time 0.761 ms, Execution time 13.261 ms]
NOTICE:     Baseline+1 [ Planning time 0.204 ms, Execution time 8.956 ms]
NOTICE:     Total time benefit: 4.862 ms, Execution time benefit: 4.305 ms
NOTICE:     Unapproved -> Approved
evolve_plan_baselines 
-----------------------
0
(1 row)     

A saída mostra um relatório de performance da instrução rangequery com associações de parâmetro 1 e 10.000. O novo plano não aprovado (Baseline+1) é melhor do que o melhor plano aprovado anteriormente (Baseline). Para confirmar se o novo plano já está Approved, verifique a visualização apg_plan_mgmt.dba_plans.

SELECT sql_hash, plan_hash, status, enabled, stmt_name 
FROM apg_plan_mgmt.dba_plans;

sql_hash  | plan_hash |  status  | enabled | stmt_name  
------------+-----------+----------+---------+------------
1984047223 | 512153379 | Approved | t       | rangequery
1984047223 | 512284451 | Approved | t       | rangequery
(2 rows)

O plano gerenciado já inclui dois planos aprovados que são a linha de base do plano da instrução. Também é possível chamar a função apg_plan_mgmt.set_plan_status para definir diretamente o campo de status de um plano como 'Approved', 'Rejected', 'Unapproved' ou 'Preferred'.

Rejeitar ou desabilitar planos mais lentos

Para rejeitar ou desabilitar planos, passe 'reject' ou 'disable' como o parâmetro de ação para a função apg_plan_mgmt.evolve_plan_baselines. Esse exemplo desativa todos os planos Unapproved capturados que sejam pelo menos 10% mais lentos do que o melhor plano Approved para a instrução.

SELECT apg_plan_mgmt.evolve_plan_baselines(
sql_hash,  -- The managed statement ID
plan_hash, -- The plan ID
1.1,       -- number of times faster the plan must be 
'disable'  -- The action to take. This sets the enabled field to false.
)
FROM apg_plan_mgmt.dba_plans
WHERE status = 'Unapproved' AND   -- plan is Unapproved
origin = 'Automatic';       -- plan was auto-captured

Também defina diretamente um plano como rejeitado ou desabilitado. Para definir diretamente o campo habilitado de um plano como true, ou false, chame a função apg_plan_mgmt.set_plan_enabled. Para definir diretamente o campo de status de um plano como 'Approved', 'Rejected', 'Unapproved' ou 'Preferred', chame a função apg_plan_mgmt.set_plan_status.

Para excluir planos que não são válidos e que você espera que permaneçam inválidos, use a função apg_plan_mgmt.validate_plans. Essa função permite excluir ou desabilitar planos inválidos. Para ter mais informações, consulte Validar planos.

Corrigir planos usando pg_hint_plan

O otimizador de consulta foi bem projetado para encontrar um plano ideal para todas as instruções e, na maioria dos casos, o otimizador encontra um plano bom. No entanto, às vezes, talvez você saiba que existe um plano muito melhor do que o gerado pelo otimizador. Duas maneiras recomendadas para fazer o otimizador gerar um plano desejado incluem usar a extensão pg_hint_plan ou definir variáveis GUC em PostgreSQL:

Ao usar uma dessas técnicas para forçar otimizador de consultas a usar um plano, também use o gerenciamento de planos de consultas para capturar impor o uso do novo plano.

Use a extensão pg_hint_plan para alterar a ordem da junção, os métodos da junção ou os caminhos de acesso de uma instrução SQL. Use um comentário SQL com sintaxe pg_hint_plan especial para modificar como o otimizador cria um plano. Por exemplo, suponhamos que a instrução SQL problemática tenha uma junção bidirecional.

SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;            

Depois, suponhamos que o otimizador selecione a ordem de junção (t1, t2), embora você saiba que a ordem de junção (t2, t1) seja mais rápida. A dica a seguir força o otimizador a usar a ordem de junção mais rápida, (t2, t1). Inclua EXPLAIN, de maneira que o otimizador gere um plano para a declaração SQL, mas sem executar a declaração. (Saída não mostrada.)

/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id; 

As etapas a seguir mostram como usar pg_hint_plan.