Melhorar os planos de consulta do Aurora PostgreSQL - Amazon Aurora
Avaliar performance do planoCorrigir planos usando pg_hint_plan

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 obter 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:

  • Extensão pg_hint_plan – especifique uma "dica" para modificar como o planejador funciona usando a extensão pg_hint_plan PostgreSQL. Para instalar e saber mais sobre com usar a extensão pg_hint_plan, consulte a documentação pg_hint_plan.

  • Variáveis GUC – Substitua um ou mais parâmetros de modelo de custo ou outros parâmetros de otimizador, como o from_collapse_limit ou o GEQO_threshold.

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.

Para modificar o plano gerado do otimizador e capturar o plano usando pg_hint_plan

  1. Ative o modo de captura manual.

    SET apg_plan_mgmt.capture_plan_baselines = manual;

  2. Especifique uma dica para a instrução SQL de interesse. 

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

    Depois dessa execução, o otimizador captura o plano na visualização apg_plan_mgmt.dba_plans. O plano capturado não inclui a sintaxe do comentário especial pg_hint_plan porque o gerenciamento de planos de consultas normaliza a instrução removendo os principais comentários.

  3. Veja os planos gerenciados usando a visualização apg_plan_mgmt.dba_plans.

    SELECT sql_hash, plan_hash, status, sql_text, plan_outline 
FROM apg_plan_mgmt.dba_plans;

  4. Defina o status do plano como Preferred. Isso garante que o otimizador optará por executá-lo, em vez de selecioná-lo no conjunto de planos aprovados quando o plano de custo mínimo ainda não for Approved ou Preferred.

    SELECT apg_plan_mgmt.set_plan_status(sql-hash, plan-hash, 'preferred' );

  5. Desative a captura de plano manual e imponha o uso de planos gerenciados.

    SET apg_plan_mgmt.capture_plan_baselines = false;
SET apg_plan_mgmt.use_plan_baselines = true;

    Agora, quando a instrução SQL original for executada, o otimizador escolherá um plano Approved ou Preferred. Se o plano de custo mínimo não for Approved nem Preferred, o otimizador escolherá o plano Preferred.