Visão geral do gerenciamento de planos de consulta do Aurora PostgreSQL - Amazon Aurora
Declarações SQL compatíveisLimitaçõesTerminologiaVersões disponíveisHabilitar o gerenciamento de planos de consultasHabilitar o gerenciamento de planos de consultasDesativar o gerenciamento de planos de consulta do Aurora PostgreSQL

Visão geral do gerenciamento de planos de consulta do Aurora PostgreSQL

O gerenciamento de planos de consulta do Aurora PostgreSQL foi projetado para garantir a estabilidade do plano, independentemente das alterações no banco de dados que possam causar a regressão do plano de consulta. A regressão do plano de consulta ocorre quando o otimizador seleciona um plano abaixo do ideal para determinada declaração SQL após alterações no sistema ou no banco de dados. Alterações em estatísticas, restrições, configurações do ambiente, associações de parâmetros de consulta e atualizações do mecanismo de banco de dados PostgreSQL podem causar regressão do plano.

Com o gerenciamento de planos de consulta do Aurora PostgreSQL, é possível controlar como e quando planos de execução de consultas mudam. Os benefícios do gerenciamento de planos de consultas do Aurora PostgreSQL incluem o seguinte.

  • Melhorar a estabilidade do plano forçando o otimizador a escolher entre um pequeno número de planos bons e conhecidos.

  • Otimizar os planos de forma centralizada e distribuir globalmente os melhores planos.

  • Identificar índices não utilizados e avaliar o impacto da criação ou remoção de um índice.

  • Detectar automaticamente um novo plano de custo mínimo descoberto pelo otimizador.

  • Experimentar novos recursos do otimizador com menos risco, pois você pode aprovar apenas as alterações de planos que melhoram a performance.

Você pode usar as ferramentas fornecidas pelo gerenciamento de planos de consulta de forma proativa para especificar o melhor plano para determinadas consultas. Ou você pode usar o gerenciamento de planos de consulta para reagir às mudanças nas circunstâncias e evitar regressões do plano. Para obter mais informações, consulte Práticas recomendadas para gerenciamento de planos de consultas do Aurora PostgreSQL.

Declarações SQL compatíveis

O gerenciamento de planos de consulta é compatível com os tipos de declaração SQL a seguir.

  • Qualquer declaração SELECT, INSERT, UPDATE ou DELETE, independentemente da complexidade.

  • Instruções preparadas. Para obter mais informações, consulte PREPARE na documentação do PostgreSQL.

  • Declarações dinâmicas, como as executadas no modo imediato. Para obter mais informações, consulte Dynamic SQL (SQL dinâmico) e EXECUTE IMMEDIATE na documentação do PostgreSQL.

  • Comandos e declarações SQL incorporados. Para obter mais informações, consulte Embedded SQL Commands (Comandos SQL incorporados) na documentação do PostgreSQL.

  • Declarações dentro de funções nomeadas. Para obter mais informações, consulte CREATE FUNCTION na documentação do PostgreSQL.

  • Declarações com tabelas temporárias.

  • Declarações em procedimentos e bloqueios de DO.

Você pode usar o gerenciamento de planos de consulta com EXPLAIN no modo manual para capturar um plano sem realmente executá-lo. Para obter mais informações, consulte Analisar o plano selecionado pelo otimizador. Para saber mais sobre os modos de gerenciamento de planos de consulta (manual, automático), consulte Capturar planos de execução do Aurora PostgreSQL.

O gerenciamento de planos de consulta do Aurora PostgreSQL é compatível com todos os recursos da linguagem PostgreSQL, inclusive tabelas particionadas, herança, segurança em nível de linha e Common Table Expressions (CTEs, Expressões de tabelas comuns) recursivas. Para saber mais sobre esses recursos da linguagem PostgreSQL, consulte Table Partitioning (Particionamento de tabelas), Row Security Policies (Políticas de segurança de linha) e WITH Queries (Common Table Expressions) [Consultas WITH (expressões de tabela comuns)], bem como outros tópicos na documentação do PostgreSQL.

Para obter informações sobre diferentes versões do recurso de gerenciamento de planos de consulta do Aurora PostgreSQL, consulte Aurora PostgreSQL apg_plan_mgmt extension versions (Versões da extensão apg_plan_mgmt do Aurora PostgreSQL) nas Release Notes for Aurora PostgreSQL (Notas de versão do Aurora PostgreSQL).

Limitações do gerenciamento de planos de consulta

A versão atual do gerenciamento de planos de consulta do Aurora PostgreSQL tem as limitações a seguir.

  • Os planos não são capturados para declarações que fazem referência às relações do sistema: declarações que fazem referência às relações do sistema, como pg_class, não são capturadas. Isso ocorre naturalmente, para evitar que um grande número de planos gerados pelo sistema que são usados internamente seja capturado. Isso também se aplica a tabelas do sistema dentro de visualizações.

  • Pode ser necessária uma classe de instância de banco de dados maior para seu cluster de banco de dados do Aurora PostgreSQL: dependendo da workload, o gerenciamento de planos de consulta pode precisar de uma classe de instância de banco de dados que tenha mais de duas vCPUs. O número de max_worker_processes é limitado pelo tamanho da classe da instância de banco de dados. O número de max_worker_processes fornecidos por uma classe de instância de banco de dados de duas vCPU (db.t3.medium, por exemplo) pode não ser suficiente para uma determinada workload. Recomendamos que você selecione uma classe de instância de banco de dados com mais de duas vCPUs para seu cluster de banco de dados do Aurora PostgreSQL se você usar gerenciamento de planos de consulta.

    Quando a classe da instância de banco de dados não for compatível com a workload, o gerenciamento de planos de consulta gerará uma mensagem de erro como a seguinte. 

    WARNING: could not register plan insert background process
HINT: You may need to increase max_worker_processes.

    Nesse caso, você deve aumentar a escala verticalmente de seu cluster de banco de dados do Aurora PostgreSQL para um tamanho de classe de instância de banco de dados com mais memória. Para obter mais informações, consulte Mecanismos de banco de dados compatíveis para classes de instância de banco de dados.

  • Os planos já armazenados nas sessões não são afetados: o gerenciamento do plano de consulta é uma forma de influenciar os planos de consulta sem alterar o código da aplicação. No entanto, quando um plano genérico já estiver armazenado em uma sessão existente e você quiser alterar o respectivo plano de consulta, primeiro defina plan_cache_mode como force_custom_plan no grupo de parâmetros do cluster de banco de dados.

  • queryid em apg_plan_mgmt.dba_plans e pg_stat_statements pode divergir quando:

    • Os objetos são descartados e recriados após serem armazenados em apg_plan_mgmt.dba_plans.

    • A tabela apg_plan_mgmt.plans é importada de outro cluster.

Para obter informações sobre diferentes versões do recurso de gerenciamento de planos de consulta do Aurora PostgreSQL, consulte Aurora PostgreSQL apg_plan_mgmt extension versions (Versões da extensão apg_plan_mgmt do Aurora PostgreSQL) nas Release Notes for Aurora PostgreSQL (Notas de versão do Aurora PostgreSQL).

Terminologia do gerenciamento de planos de consulta

Os seguintes termos são utilizados neste tópico:

declaração gerenciada

Uma declaração SQL capturada pelo otimizador no gerenciamento de planos de consulta. Uma declaração gerenciada tem um ou mais planos de execução de consultas armazenados na visualização apg_plan_mgmt.dba_plans.

linha de base do plano

O conjunto de planos aprovados para determinada declaração gerenciada. Ou seja, todos os planos para a declaração gerenciada que têm “Aprovado” em sua coluna status na visualização dba_plan.

histórico de planos

O conjunto de todos os planos capturados para determinada declaração gerenciada. O histórico de planos contém todos os planos capturados para a declaração, independentemente do status.

regressão de planos de consultas

O caso em que o otimizador seleciona um plano abaixo do ideal do que o anterior a determinada alteração no ambiente do banco de dados, como uma nova versão do PostgreSQL ou alterações nas estatísticas.

Versões do gerenciamento de planos de consulta do Aurora PostgreSQL

O gerenciamento de planos de consulta é compatível com todas as versões do Aurora PostgreSQL atualmente disponíveis. Para obter informações, consulte a lista de Amazon Aurora PostgreSQL updates (Atualizações do Amazon Aurora PostgreSQL) nas Release Notes for Aurora PostgreSQL (Notas de versão do Aurora PostgreSQL).

A funcionalidade de gerenciamento de planos de consulta é adicionada ao seu cluster de banco de dados do Aurora PostgreSQL quando você instala a extensão apg_plan_mgmt. Versões diferentes do Aurora PostgreSQL são compatíveis com diferentes versões da extensão apg_plan_mgmt. Recomendamos que você atualize a extensão de gerenciamento de planos de consulta para a versão mais recente de sua versão do Aurora PostgreSQL.

nota

Para ver as notas de cada versão da extensão apg_plan_mgmt, consulte Aurora PostgreSQL apg_plan_mgmt extension versions (Versões da extensão apg_plan_mgmt do Aurora PostgreSQL) nas Release Notes for Aurora PostgreSQL (Notas de versão do Aurora PostgreSQL).

Você pode identificar a versão em execução em seu cluster conectando-se a uma instância com psql e usando o metacomando \dx para listar extensões, conforme mostrado a seguir.

labdb=> \dx
                       List of installed extensions
     Name      | Version |    Schema     |                            Description
---------------+---------+---------------+-------------------------------------------------------------------
 apg_plan_mgmt | 1.0     | apg_plan_mgmt | Amazon Aurora with PostgreSQL compatibility Query Plan Management
 plpgsql       | 1.0     | pg_catalog    | PL/pgSQL procedural language
(2 rows)

A saída mostra que esse cluster está usando a versão 1.0 da extensão. Somente determinadas versões de apg_plan_mgmt estão disponíveis para determinada versão do Aurora PostgreSQL. Em alguns casos, talvez seja necessário atualizar o cluster de banco de dados do Aurora PostgreSQL para uma nova versão secundária ou aplicar um patch para poder realizar a atualização para a versão mais recente do gerenciamento de planos de consulta. O apg_plan_mgmt versão 1.0 mostrado na saída é de um cluster de banco de dados do Aurora PostgreSQL versão 10.17, que não tem uma versão mais recente do apg_plan_mgmt disponível. Nesse caso, o cluster de banco de dados do Aurora PostgreSQL deve ser atualizado para uma versão mais recente do PostgreSQL.

Para obter mais informações sobre a atualização de um cluster de banco de dados do Aurora PostgreSQL para uma nova versão do PostgreSQL, consulte Atualizações do mecanismo de banco de dados do Amazon Aurora PostgreSQL.

Para saber como atualizar a extensão apg_plan_mgmt, consulte Atualizar o gerenciamento de planos de consultas do Aurora PostgreSQL.

Ativar o gerenciamento de planos de consulta do Aurora PostgreSQL

Configurar o gerenciamento de planos de consulta para seu cluster de banco de dados do Aurora PostgreSQL envolve a instalação de uma extensão e a alteração de várias configurações de parâmetros do cluster de banco de dados. Você precisa de permissões rds_superuser para instalar a extensão apg_plan_mgmt e ativar o recurso para o cluster de banco de dados do Aurora PostgreSQL.

A instalação da extensão cria uma função, apg_plan_mgmt. Essa função permite que os usuários do banco de dados visualizem, gerenciem e mantenham planos de consulta. Como administrador com privilégios rds_superuser, não deixe de conceder a função apg_plan_mgmt aos usuários do banco de dados conforme necessário.

Somente os usuários com a função rds_superuser podem concluir o seguinte procedimento. rds_superuser é necessário para criar a extensão apg_plan_mgmt e sua função apg_plan_mgmt. Os usuários devem receber a função apg_plan_mgmt para administrar a extensão apg_plan_mgmt.

Como ativar o gerenciamento de planos de consulta para o cluster de banco de dados do Aurora PostgreSQL

As etapas a seguir ativam o gerenciamento de planos de consulta para todas as declarações SQL enviadas ao cluster de banco de dados do Aurora PostgreSQL. Isso é conhecido como modo automático. Para saber mais sobre a diferença entre os modos, consulte Capturar planos de execução do Aurora PostgreSQL.

  1. Abra o console do Amazon RDS em https://console.aws.amazon.com/rds/.

  2. Crie um grupo de parâmetros de cluster de banco de dados personalizado para o cluster de bancos de dados do Aurora PostgreSQL. Você precisa alterar determinados parâmetros para ativar o gerenciamento de planos de consulta e definir seu comportamento. Para obter mais informações, consulte Criar um grupo de parâmetros de banco de dados no Amazon Aurora.

  3. Abra o grupo de parâmetros de cluster de banco de dados personalizado e defina o parâmetro rds.enable_plan_management como 1, como mostrado na imagem a seguir.

    Imagem do grupo de parâmetros de um cluster de banco de dados.

    Para obter mais informações, consulte Modificar parâmetros em um grupo de parâmetros do cluster de banco de dados no Amazon Aurora.

  4. Crie um grupo de parâmetros de banco de dados personalizado que você possa usar para definir os parâmetros do plano de consulta em nível de instância. Para obter mais informações, consulte Criar um grupo de parâmetros do cluster de banco de dados no Amazon Aurora.

  5. Modifique a instância do gravador do cluster de banco de dados do Aurora PostgreSQL para usar o grupo de parâmetros de banco de dados personalizado. Para obter mais informações, consulte Modificar uma instância de banco de dados em um cluster de banco de dados.

  6. Modifique o cluster de banco de dados do Aurora PostgreSQL para usar o grupo de parâmetros de cluster de banco de dados personalizado. Para obter mais informações, consulte Modificar o cluster de banco de dados usando o console, a CLI e a API.

  7. Reinicialize a instância de banco de dados para habilitar as configurações do grupo de parâmetros personalizado.

  8. Conecte-se ao endpoint da instância de banco de dados de seu cluster de banco de dados do Aurora PostgreSQL usando psql ou pgAdmin. O exemplo a seguir usa a conta postgres padrão para a função rds_superuser.

    psql --host=cluster-instance-1.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=my-db

  9. Crie a extensão apg_plan_mgmt para a instância de banco de dados, conforme mostrado a seguir.

    labdb=> CREATE EXTENSION apg_plan_mgmt;
CREATE EXTENSION
    dica

    Instale a extensão apg_plan_mgmt no banco de dados de modelo de sua aplicação. O banco de dados de modelo padrão é chamado de template1. Para saber mais, consulte Template Databases (Bancos de dados de modelos) na documentação do PostgreSQL.

  10. Altere o parâmetro apg_plan_mgmt.capture_plan_baselines para automatic. Essa configuração faz com que o otimizador gere planos para cada declaração SQL planejada ou executada duas ou mais vezes.

    nota

    O gerenciamento de planos de consulta também tem um modo manual que você pode usar para declarações SQL específicas. Para saber mais, consulte Capturar planos de execução do Aurora PostgreSQL.

  11. Altere o valor do parâmetro apg_plan_mgmt.use_plan_baselines para “on” (ativado). Esse parâmetro faz com que o otimizador selecione um plano para a declaração na linha de base do plano. Para saber mais, consulte Usar planos gerenciados do Aurora PostgreSQL.

    nota

    Você pode modificar o valor de qualquer um desses parâmetros dinâmicos para a sessão sem precisar reinicializar a instância.

Quando a configuração do gerenciamento de planos de consulta estiver concluída, conceda a função apg_plan_mgmt a todos os usuários do banco de dados que precisem visualizar, gerenciar ou manter planos de consulta.

Atualizar o gerenciamento de planos de consultas do Aurora PostgreSQL

Recomendamos que você atualize a extensão de gerenciamento de planos de consulta para a versão mais recente de sua versão do Aurora PostgreSQL.

  1. Conecte-se à instância do gravador de seu cluster de banco de dados do Aurora PostgreSQL como um usuário com privilégios rds_superuser. Se você manteve o nome padrão ao configurar a instância, se conectará como postgres Este exemplo mostra como usar psql, mas você também pode usar pgAdmin, se preferir.

    psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

  2. Execute a consulta a seguir para atualizar a extensão.

    ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.1';

  3. Use a função apg_plan_mgmt.validate_plans para atualizar os hashes de todos os planos. O otimizador valida todos os planos Aprovados, Não aprovados e Rejeitados para garantir que eles ainda sejam planos viáveis para a nova versão da extensão. 

    SELECT apg_plan_mgmt.validate_plans('update_plan_hash');

    Para saber mais sobre o uso dessa função, consulte Validar planos.

  4. Use a função apg_plan_mgmt.reload para atualizar todos os planos na memória compartilhada com os planos validados na visualização dba_plans. 

    SELECT apg_plan_mgmt.reload();

Para saber mais sobre todas as funções disponíveis para o gerenciamento de planos de consulta, consulte Referência de funções do gerenciamento de planos de consultas do Aurora PostgreSQL.

Desativar o gerenciamento de planos de consulta do Aurora PostgreSQL

Você pode desativar o gerenciamento de planos de consulta a qualquer momento, desativando as apg_plan_mgmt.use_plan_baselines e as apg_plan_mgmt.capture_plan_baselines. 

labdb=> SET apg_plan_mgmt.use_plan_baselines = off;

labdb=> SET apg_plan_mgmt.capture_plan_baselines = off;