Avaliar performance do plano Validar planos Corrigir planos usando pg_hint_plan Excluir planos Exportar e importar planos

Manutenção dos planos de execução do Aurora PostgreSQL

O gerenciamento de planos de consultas oferece técnicas e funções para adicionar, manter e melhorar planos de execução.

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.

Validar planos

Use a função apg_plan_mgmt.validate_plans para excluir ou desabilitar planos que sejam inválidos.

Os planos podem se tornar inválidos ou obsoletos quando objetos dependentes são removidos, como um índice ou uma tabela. No entanto, um plano só poderá ser inválido temporariamente se o objeto removido for recriado. Caso um plano inválido se torne válido depois, convém optar por desabilitar um plano inválido ou não fazer nada, em vez de excluí-lo.

Para encontrar e excluir todos os planos que sejam inválidos e que não tenham sido usados na semana passada, use a função apg_plan_mgmt.validate_plans da maneira a seguir.


SELECT apg_plan_mgmt.validate_plans(sql_hash, plan_hash, 'delete') 
FROM apg_plan_mgmt.dba_plans
WHERE last_used < (current_date - interval '7 days');

Para habilitar ou desabilitar diretamente um plano, use a função apg_plan_mgmt.set_plan_enabled.

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

Ative o modo de captura manual.


SET apg_plan_mgmt.capture_plan_baselines = manual;

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.

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;

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' ); 
```
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.

Excluir planos

Os planos serão excluídos automaticamente se não forem utilizados em mais de um mês, especificamente, 32 dias. Essa é a configuração padrão do parâmetro apg_plan_mgmt.plan_retention_period. Você pode alterar o período de retenção do plano para um período mais longo ou mais curto, a partir do valor 1. A determinação do número de dias desde o último uso de um plano é calculada subtraindo a data last_used da data atual. A data last_used é a mais recente em que o otimizador selecionou um plano como plano de custo mínimo ou em que o plano foi executado. A data é armazenada para o plano na visualização apg_plan_mgmt.dba_plans.

Recomendamos que você exclua planos que não tiverem sido usados por muito tempo ou que não tiverem sido úteis. Cada plano tem uma data last_used atualizada pelo otimizador sempre que ele executa um plano ou o seleciona como o plano de custo mínimo para uma declaração. Confira as últimas datas last_used para identificar os planos que você pode excluir com segurança.

A consulta a seguir retorna uma tabela de três colunas com a contagem do número total de planos, dos planos que não foram excluídos e dos planos que foram excluídos. Há uma consulta aninhada que é um exemplo de como usar a função apg_plan_mgmt.delete_plan para excluir todos os planos que não tiverem sido selecionados como o plano de custo mínimo nos últimos 31 dias e o status não seja Rejected.


SELECT (SELECT COUNT(*) from apg_plan_mgmt.dba_plans) total_plans,
       COUNT(*) FILTER (WHERE result = -1) failed_to_delete,
       COUNT(*) FILTER (WHERE result = 0) successfully_deleted
       FROM (
            SELECT apg_plan_mgmt.delete_plan(sql_hash, plan_hash) as result
            FROM apg_plan_mgmt.dba_plans
            WHERE last_used < (current_date - interval '31 days')
            AND status <> 'Rejected'
            ) as dba_plans ;



 total_plans | failed_to_delete | successfully_deleted
-------------+------------------+----------------------
           3 |                0 |                    2

Para obter mais informações, consulte apg_plan_mgmt.delete_plan.

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.

Importante

Caso você não exclua planos divergentes, poderá acabar ficando sem memória compartilhada separada para o gerenciamento de planos de consultas. Para controlar a memória disponível para planos gerenciados, use o parâmetro apg_plan_mgmt.max_plans. Defina esse parâmetro no grupo de parâmetros de banco de dados personalizado e reinicie a instância de banco de dados para que as alterações tenham efeito. Para obter mais informações, consulte o parâmetro apg_plan_mgmt.max_plans.

Exportar e importar planos

Exporte os planos gerenciados e os importe para outra instância de banco de dados.

Para exportar planos gerenciados

Um usuário autorizado pode copiar qualquer subconjunto da tabela apg_plan_mgmt.plans para outra tabela e salvá-lo usando o comando pg_dump. Veja um exemplo a seguir.


CREATE TABLE plans_copy AS SELECT * 
FROM apg_plan_mgmt.plans [ WHERE predicates ] ;


% pg_dump --table apg_plan_mgmt.plans_copy -Ft mysourcedatabase > plans_copy.tar


DROP TABLE apg_plan_mgmt.plans_copy;

Para importar planos gerenciados

Copie o arquivo .tar dos planos gerenciados exportados para o sistema onde os planos precisam ser restaurados.
Use o comando pg_restore a fim de copiar o arquivo tar para uma nova tabela.
```
% pg_restore --dbname mytargetdatabase -Ft plans_copy.tar
```

Mescle a tabela plans_copy com a tabela apg_plan_mgmt.plans, conforme mostrado no exemplo a seguir.

nota

Em alguns casos, você pode descarregar de uma versão da extensão apg_plan_mgmt e restaurar para outra versão. Nesses casos, as colunas na tabela de planos podem ser diferentes. Se for diferente, nomeie as colunas explicitamente, em vez de usar SELECT *.


INSERT INTO apg_plan_mgmt.plans SELECT * FROM plans_copy
 ON CONFLICT ON CONSTRAINT plans_pkey
 DO UPDATE SET
 status = EXCLUDED.status,
 enabled = EXCLUDED.enabled,
 -- Save the most recent last_used date 
 --
 last_used = CASE WHEN EXCLUDED.last_used > plans.last_used 
 THEN EXCLUDED.last_used ELSE plans.last_used END, 
 -- Save statistics gathered by evolve_plan_baselines, if it ran:
 --
 estimated_startup_cost = EXCLUDED.estimated_startup_cost,
 estimated_total_cost = EXCLUDED.estimated_total_cost,
 planning_time_ms = EXCLUDED.planning_time_ms,
 execution_time_ms = EXCLUDED.execution_time_ms,
 total_time_benefit_ms = EXCLUDED.total_time_benefit_ms, 
 execution_time_benefit_ms = EXCLUDED.execution_time_benefit_ms;

Recarregue os planos gerenciados na memória compartilhada e remova a tabela de planos temporária.
```
SELECT apg_plan_mgmt.reload(); -- refresh shared memory
DROP TABLE plans_copy;
```

Atenção O Javascript está desativado ou não está disponível no seu navegador.

Para usar a documentação da AWS, o Javascript deve estar ativado. Consulte as páginas de Ajuda do navegador para obter instruções.

Convenções do documento

Examinar planos de consulta do Aurora PostgreSQL na exibição dba_plans

Referência