Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.
# Présentation de la gestion des plans de requêtes d’Aurora PostgreSQL
La gestion des plans de requêtes Aurora PostgreSQL est conçue pour garantir la stabilité du plan, quelles que soient les modifications apportées à la base de données qui peuvent entraîner une régression du plan de requête. La *régression du plan de requête* se produit lorsque l’optimiseur choisit un plan sous-optimal pour une instruction SQL donnée après des modifications du système ou de la base de données. Les changements en termes de statistiques, de contraintes, de paramètres d’environnement, de liaisons de paramètres de requêtes et de mises à niveau du moteur de base de données PostgreSQL peuvent tous provoquer une régression de plan.
Avec la gestion des plans de requêtes Aurora PostgreSQL, vous pouvez contrôler quand et comment les plans d’exécution de requêtes changent. Les avantages de la gestion de plans de requêtes Aurora PostgreSQL incluent ce qui suit.
+ Optimisez la stabilité du plan en forçant l’optimiseur à opérer sa sélection parmi une poignée de plans de qualité connus.
+ Optimisez les plans de manière centralisée, puis distribuez les meilleurs à l’échelle mondiale.
+ Identifiez les index non utilisés et évaluez l’impact de la création ou de la suppression d’un index.
+ Détectez automatiquement un nouveau plan à coût minimal découvert par l’optimiseur.
+ Essayez les nouvelles fonctionnalités de l’optimiseur avec moins de risques, car vous pouvez choisir de n’approuver que les changements de plans qui optimisent les performances.
Vous pouvez utiliser les outils fournis par la gestion des plans de requêtes de manière proactive afin de définir le meilleur plan pour certaines requêtes. Vous pouvez également utiliser la gestion des plans de requêtes pour réagir à l’évolution des circonstances et éviter les régressions du plan. Pour plus d’informations, consultez [Bonnes pratiques pour la gestion de plans de requêtes Aurora PostgreSQL](AuroraPostgreSQL.Optimize.BestPractice.md).
**Topics**
+ [Instructions SQL prises en charge](#AuroraPostgreSQL.Optimize.overview.features)
+ [Limites de la gestion des plans de requêtes](#AuroraPostgreSQL.Optimize.overview.limitations)
+ [Terminologie de la gestion des plans de requête](#AuroraPostgreSQL.Optimize.Start-terminology)
+ [Versions de la gestion de plans de requêtes Aurora PostgreSQL](#AuroraPostgreSQL.Optimize.overview.versions)
+ [Activation de la gestion de plans de requêtes Aurora PostgreSQL](#AuroraPostgreSQL.Optimize.Enable)
+ [Mise à niveau de la gestion des plans de requête d’Aurora PostgreSQL](#AuroraPostgreSQL.Optimize.Upgrade)
+ [Désactivation de la gestion des plans de requêtes Aurora PostgreSQL](#AuroraPostgreSQL.Optimize.Enable.turnoff)
## Instructions SQL prises en charge
La gestion des plans de requêtes prend en charge les types d’instructions SQL suivants.
+ Instruction SELECT, INSERT, UPDATE ou DELETE, quelle que soit la complexité.
+ Instructions préparées. Pour en savoir plus, consultez [PREPARE](https://www.postgresql.org/docs/14/sql-prepare.html) dans la documentation PostgreSQL.
+ Instructions dynamiques, y compris celles qui s’exécutent en mode immédiat. Pour plus d’informations, consultez [Dynamic SQL](https://www.postgresql.org/docs/current/ecpg-dynamic.html) et [EXECUTE IMMEDIATE](https://www.postgresql.org/docs/current/ecpg-sql-execute-immediate.html) dans la documentation PostgreSQL.
+ Commandes et instructions SQL intégrées. Pour en savoir plus, consultez [Commandes SQL intégrées](https://www.postgresql.org/docs/current/ecpg-sql-commands.html) dans la documentation PostgreSQL.
+ Instructions à l’intérieur de fonctions nommées. Pour plus d’informations, consultez [CREATE FUNCTION](https://www.postgresql.org/docs/current/sql-createfunction.html) dans la documentation PostgreSQL.
+ Déclarations contenant des tables temporaires.
+ Les instructions à l’intérieur des procédures et des blocs DO.
Vous pouvez utiliser la gestion des plans de requêtes avec `EXPLAIN` en mode manuel pour capturer un plan sans l’exécuter réellement. Pour plus d’informations, consultez [Analyse du plan choisi par l'optimiseur](AuroraPostgreSQL.Optimize.UsePlans.md#AuroraPostgreSQL.Optimize.UsePlans.AnalyzePlans). Pour en savoir plus sur les modes de gestion des plans de requêtes (manuel, automatique), consultez [Capture des plans d'exécution d'Aurora PostgreSQL](AuroraPostgreSQL.Optimize.CapturePlans.md).
La gestion des plans de requêtes Aurora PostgreSQL prend en charge toutes les fonctionnalités du langage PostgreSQL, notamment les tables partitionnées, l'héritage, la sécurité au niveau des lignes et les expressions de table communes récursives (). CTEs Pour en savoir plus sur ces fonctionnalités du langage PostgreSQL, consultez [Partitionnement de tables](https://www.postgresql.org/docs/current/ddl-partitioning.html), [Politiques de sécurité des lignes](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) et [Requêtes WITH (expressions de table communes)](https://www.postgresql.org/docs/current/queries-with.html) et d’autres rubriques de la documentation de PostgreSQL.
Pour plus d’informations sur les différentes versions de la fonction de gestion du plan de requête d’Aurora PostgreSQL, consultez [Versions de l’extension apg\$1plan\$1mgmt d’Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.apg_plan_mgmt) dans *Notes de mise à jour d’Aurora PostgreSQL*.
## Limites de la gestion des plans de requêtes
La version actuelle de la gestion du plan de requête d’Aurora PostgreSQL présente les limites suivantes.
+ **Les plans ne sont pas capturés pour les déclarations qui font référence aux relations du système** : les déclarations qui font référence aux relations du système, telles que `pg_class`, ne sont pas capturées. Ceci est conçu pour empêcher la capture d’un grand nombre de plans générés par le système et utilisés en interne. Cela s’applique également aux tables système à l’intérieur des vues.
+ Une **classe d'instance de base de données plus importante peut être nécessaire pour votre cluster de base de données Aurora PostgreSQL** : en fonction de la charge de travail, la gestion du plan de requêtes peut nécessiter une classe d'instance de base de données de plus de 2 v. CPUs Le nombre de `max_worker_processes` est limité par la taille de classe de l'instance de base de données. Le nombre de `max_worker_processes` fournis par une classe d’instance de base de données à 2 vCPU (db.t3.medium, par exemple) peut ne pas être suffisant pour une charge de travail donnée. Nous vous recommandons de choisir une classe d'instance de base de données avec plus de 2 v CPUs pour votre cluster de base de données Aurora PostgreSQL si vous utilisez la gestion du plan de requêtes.
Lorsque la classe d’instance de base de données ne peut pas supporter la charge de travail, la gestion du plan de requête affiche un message d’erreur comme suit.
```
WARNING: could not register plan insert background process
HINT: You may need to increase max_worker_processes.
```
Dans ce cas, vous devez augmenter la taille de votre cluster de bases de données Aurora PostgreSQL à une taille de classe d’instance de base de données avec plus de mémoire. Pour plus d’informations, consultez [Moteurs de base de données pris en charge pour les classes d’instance de base de données](Concepts.DBInstanceClass.SupportAurora.md).
+ **Les plans déjà stockés dans les sessions ne sont pas affectés** : la gestion des plans de requêtes permet d’influencer les plans de requêtes sans modifier le code de l’application. Toutefois, lorsqu’un plan générique est déjà stocké dans une session existante et que vous souhaitez modifier son plan de requêtes, vous devez d’abord définir `plan_cache_mode` sur `force_custom_plan` dans le groupe de paramètres du cluster de bases de données.
+ `queryid` dans `apg_plan_mgmt.dba_plans` et `pg_stat_statements` peuvent diverger lorsque :
+ Les objets sont supprimés et recréés après leur stockage dans apg\$1plan\$1mgmt.dba\$1plans.
+ La table `apg_plan_mgmt.plans` est importée depuis un autre cluster.
Pour plus d’informations sur les différentes versions de la fonction de gestion du plan de requête d’Aurora PostgreSQL, consultez [Versions de l’extension apg\$1plan\$1mgmt d’Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.apg_plan_mgmt) dans *Notes de mise à jour d’Aurora PostgreSQL*.
## Terminologie de la gestion des plans de requête
Les termes suivants sont utilisés tout au long de cette rubrique.
**instruction gérée**
Une instruction SQL capturée par l’optimiseur dans le cadre de la gestion des plans de requêtes. Une instruction gérée possède un ou plusieurs plans d’exécution de requêtes stockés dans la vue `apg_plan_mgmt.dba_plans`.
**référence du plan**
Ensemble de plans approuvés pour une instruction gérée donnée. C’est-à-dire tous les plans de l’instruction gérée dont la colonne `status` de la vue `dba_plan` indique « Approuvé ».
**historique du plan**
Ensemble de plans capturés pour une instruction gérée donnée. L’historique du plan contient tous les plans capturés pour l’instruction, quel que soit leur statut.
**régression du plan de requêtes**
Le cas où l’optimiseur choisit un plan moins optimal qu’avant une modification donnée de l’environnement de base de données, telle qu’une nouvelle version de PostgreSQL ou des modifications des statistiques.
## Versions de la gestion de plans de requêtes Aurora PostgreSQL
La gestion des plans de requêtes est prise en charge par toutes les versions actuellement disponibles d’Aurora PostgreSQL. Pour plus d’informations, consultez la liste des [Mises à jour d’Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Updates.html) dans *Notes de mise à jour d’Aurora PostgreSQL*.
La fonctionnalité de gestion des plans de requêtes est ajoutée à votre cluster de bases de données Aurora PostgreSQL lorsque vous installez l’extension `apg_plan_mgmt`. Différentes versions d’Aurora PostgreSQL prennent en charge différentes versions de l’extension `apg_plan_mgmt`. Nous vous recommandons de mettre à niveau l’extension de gestion des plans de requêtes vers la dernière version de votre version d’Aurora PostgreSQL.
**Note**
Pour les notes de mise à jour relatives à chaque version d’extension `apg_plan_mgmt`, consultez [Versions d’extension apg\$1plan\$1mgmt d’Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.apg_plan_mgmt) dans *Notes de mise à jour d’Aurora PostgreSQL*.
Vous pouvez identifier la version exécutée sur votre cluster en vous connectant à une instance à l’aide de `psql` et de la métacommande \$1dx pour répertorier les extensions, comme indiqué ci-dessous.
```
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)
```
La sortie indique que ce cluster utilise la version 1.0 de l’extension. Seules certaines versions `apg_plan_mgmt` sont disponibles pour une version d’Aurora PostgreSQL donnée. Dans certains cas, vous devrez peut-être mettre à niveau le cluster de bases de données Aurora PostgreSQL vers une nouvelle version mineure ou appliquer un correctif afin de pouvoir effectuer la mise à niveau vers la version la plus récente de la gestion des plans de requêtes. La version 1.0 d’`apg_plan_mgmt` affichée dans la sortie provient d’un cluster de bases de données Aurora PostgreSQL version 10.17, pour lequel aucune version plus récente d’`apg_plan_mgmt` n’ est disponible. Dans ce cas, le cluster de bases de données Aurora PostgreSQL doit être mis à niveau vers une version plus récente de PostgreSQL.
Pour plus d’informations sur la mise à niveau de votre cluster de bases de données Aurora PostgreSQL vers une nouvelle version de PostgreSQL, consultez [Mises à jour du moteur de base de données pour Amazon Aurora PostgreSQL](AuroraPostgreSQL.Updates.md).
Pour savoir comment mettre à niveau l’extension `apg_plan_mgmt`, consultez [Mise à niveau de la gestion des plans de requête d’Aurora PostgreSQL](#AuroraPostgreSQL.Optimize.Upgrade).
## Activation de la gestion de plans de requêtes Aurora PostgreSQL
La configuration de la gestion des plans de requêtes pour votre cluster de bases de données Aurora PostgreSQL implique l’installation d’une extension et la modification de plusieurs paramètres de cluster de bases de données. Vous devez disposer d’autorisations `rds_superuser` pour installer l’extension `apg_plan_mgmt` et activer la fonction pour le cluster de bases de données Aurora PostgreSQL.
L’installation de l’extension crée un nouveau rôle, `apg_plan_mgmt`. Ce rôle permet aux utilisateurs de la base de données de consulter, de gérer et de gérer des plans de requêtes. En tant qu’administrateur doté de privilèges `rds_superuser`, veillez à attribuer le rôle `apg_plan_mgmt` aux utilisateurs de la base de données, selon leurs besoins.
Seuls les utilisateurs possédant le rôle `rds_superuser` peuvent effectuer la procédure suivante. Le rôle `rds_superuser` est nécessaire pour créer l’extension `apg_plan_mgmt` et son rôle `apg_plan_mgmt`. Les utilisateurs doivent recevoir le rôle `apg_plan_mgmt` pour administrer l’extension `apg_plan_mgmt`.
**Activer la gestion des plans de requêtes pour votre cluster de bases de données Aurora PostgreSQL**
Les étapes suivantes activent la gestion des plans de requêtes pour toutes les instructions SQL qui sont soumises au cluster de bases de données Aurora PostgreSQL. C’est ce que l’on appelle le mode *automatique*. Pour en savoir plus sur les différences entre les modes, consultez [Capture des plans d'exécution d'Aurora PostgreSQL](AuroraPostgreSQL.Optimize.CapturePlans.md).
1. Ouvrez la console Amazon RDS à l'adresse [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Créez un groupe de paramètres de cluster de bases de données personnalisé pour le cluster de bases de données Aurora PostgreSQL. Vous devez modifier certains paramètres pour activer la gestion des plans de requêtes et définir son comportement. Pour plus d’informations, consultez [Création d’un groupe de paramètres de base de données dans Amazon Aurora](USER_WorkingWithParamGroups.Creating.md).
1. Ouvrez le groupe de paramètres de cluster de bases de données personnalisé et définissez le paramètre `rds.enable_plan_management` sur `1`, comme illustré dans l’image suivante.
![\[Image du groupe de paramètres de cluster de bases de données.\]](http://docs.aws.amazon.com/fr_fr/AmazonRDS/latest/AuroraUserGuide/images/aurora-qpm-custom-db-cluster-param-change-1.png)
Pour plus d’informations, consultez [Modification des paramètres d'un groupe de paramètres de cluster de base de données dans Amazon Aurora](USER_WorkingWithParamGroups.ModifyingCluster.md).
1. Créez un groupe de paramètres de base de données personnalisé que vous pouvez utiliser pour définir les paramètres des plans de requêtes au niveau de l’instance. Pour plus d’informations, consultez [Création d'un groupe de paramètres de cluster de base de données dans Amazon Aurora](USER_WorkingWithParamGroups.CreatingCluster.md).
1. Modifiez l’instance d’enregistreur du cluster de bases de données Aurora PostgreSQL pour utiliser le groupe de paramètres de base de données personnalisé. Pour plus d’informations, consultez [Modification d’une instance de base de données dans un cluster de bases de données](Aurora.Modifying.md#Aurora.Modifying.Instance).
1. Modifiez le cluster de bases de données Aurora PostgreSQL pour utiliser le groupe de paramètres du cluster de bases de données personnalisé. Pour plus d’informations, consultez [Modification du cluster de bases de données à partir de la console, de l’CLI (CLI) et de l’API](Aurora.Modifying.md#Aurora.Modifying.Cluster).
1. Réinitialisez votre instance de base de données pour activer les paramètres des groupes de paramètres personnalisés.
1. Connectez-vous au point de terminaison de j’instance de base de données du cluster de bases de données Aurora PostgreSQL à l’aide de `psql` ou `pgAdmin`. L’exemple suivant utilise le compte `postgres` par défaut pour le rôle `rds_superuser`.
```
psql --host=cluster-instance-1.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=my-db
```
1. Créez l’extension `apg_plan_mgmt` pour votre instance de base de données, comme indiqué ci-dessous.
```
labdb=> CREATE EXTENSION apg_plan_mgmt;
CREATE EXTENSION
```
**Astuce**
Installez l’extension `apg_plan_mgmt` dans la base de données des modèles pour votre application. La base de données de modèles par défaut est nommée `template1`. Pour en savoir plus, consultez la section [Bases de données modèles](https://www.postgresql.org/docs/current/manage-ag-templatedbs.html) dans la documentation PostgreSQL.
1. Remplacez le paramètre `apg_plan_mgmt.capture_plan_baselines` par `automatic`. Ce paramètre permet à l’optimiseur de générer des plans pour chaque instruction SQL qui est soit planifiée, soit exécutée deux fois ou plus.
**Note**
La gestion des plans de requêtes dispose également d’un mode *manuel* que vous pouvez utiliser pour des instructions SQL spécifiques. Pour en savoir plus, consultez [Capture des plans d'exécution d'Aurora PostgreSQL](AuroraPostgreSQL.Optimize.CapturePlans.md).
1. Remplacez la valeur du paramètre `apg_plan_mgmt.use_plan_baselines` par « on ». Ce paramètre force l’optimiseur à choisir un plan pour l’instruction à partir de sa référence de plan. Pour en savoir plus, consultez [Utilisation des plans gérés Aurora PostgreSQL](AuroraPostgreSQL.Optimize.UsePlans.md).
**Note**
Vous pouvez modifier la valeur de l’un ou l’autre de ces paramètres dynamiques pour la session sans avoir à redémarrer l’instance.
Lorsque la configuration de la gestion de votre plan de requêtes est terminée, veillez à attribuer le rôle `apg_plan_mgmt` à tous les utilisateurs de bases de données qui ont besoin de consulter, de gérer ou de gérer des plans de requêtes.
## Mise à niveau de la gestion des plans de requête d’Aurora PostgreSQL
Nous vous recommandons de mettre à niveau l’extension de gestion des plans de requêtes vers la dernière version de votre version d’Aurora PostgreSQL.
1. Connectez-vous à l’instance d’enregistreur de votre cluster de bases de données Aurora PostgreSQL en tant qu’utilisateur avec des privilèges `rds_superuser`. Si vous avez conservé le nom par défaut lors de la configuration de votre instance, vous vous connectez en tant que `postgres`. Cet exemple montre comment utiliser `psql`, mais vous pouvez également utiliser pgAdmin si vous préférez.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
1. Exécutez la requête suivante pour mettre à niveau l’extension.
```
ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.1';
```
1. Utilisez la fonction [apg\$1plan\$1mgmt.validate\$1plans](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.validate_plans) pour mettre à jour les hachages de tous les plans. L’optimiseur valide tous les plans approuvés, non approuvés et rejetés afin de s’assurer qu’ils sont toujours viables pour la nouvelle version de l’extension.
```
SELECT apg_plan_mgmt.validate_plans('update_plan_hash');
```
Pour en savoir plus sur cette fonction, consultez [Validation des plans](AuroraPostgreSQL.Optimize.Deleting.md#AuroraPostgreSQL.Optimize.Maintenance.ValidatingPlans).
1. Utilisez cette fonction [apg\$1plan\$1mgmt.reload](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.reload) pour actualiser tous les plans de la mémoire partagée avec les plans validés depuis la vue dba\$1plans.
```
SELECT apg_plan_mgmt.reload();
```
Pour en savoir plus sur toutes les fonctions disponibles pour la gestion des plans de requêtes, consultez [Référence de la fonction pour la gestion du plan de requête Aurora PostgreSQL](AuroraPostgreSQL.Optimize.Functions.md).
## Désactivation de la gestion des plans de requêtes Aurora PostgreSQL
Vous pouvez désactiver la gestion des plans de requêtes à tout moment en désactivant `apg_plan_mgmt.use_plan_baselines` et `apg_plan_mgmt.capture_plan_baselines`.
```
labdb=> SET apg_plan_mgmt.use_plan_baselines = off;
labdb=> SET apg_plan_mgmt.capture_plan_baselines = off;
```