Utilisation des plans gérés Aurora PostgreSQL

Pour que l'optimiseur utilise les plans capturés pour vos instructions gérées, définissez le paramètre apg_plan_mgmt.use_plan_baselines sur true. L'exemple suivant est un exemple d'instance locale.

SET apg_plan_mgmt.use_plan_baselines = true;

Pendant que l'application s'exécute, ce paramètre contraint l'optimiseur à utiliser le plan à coût minimal, préféré ou approuvé qui est valide et activé pour chaque instruction gérée.

Analyse du plan choisi par l'optimiseur

Lorsque le paramètre apg_plan_mgmt.use_plan_baselines est défini sur true, vous pouvez utiliser les instructions SQL EXPLAIN ANALYZE pour contraindre l'optimiseur à afficher le plan qu'il utiliserait s'il exécutait l'instruction. Voici un exemple.

EXPLAIN ANALYZE EXECUTE rangeQuery (1,10000);

                                                    QUERY PLAN           
--------------------------------------------------------------------------
 Aggregate  (cost=393.29..393.30 rows=1 width=8) (actual time=7.251..7.251 rows=1 loops=1)
   ->  Index Only Scan using t1_pkey on t1 t  (cost=0.29..368.29 rows=10000 width=0) (actual time=0.061..4.859 rows=10000 loops=1)
Index Cond: ((id >= 1) AND (id <= 10000))         
         Heap Fetches: 10000
 Planning time: 1.408 ms
 Execution time: 7.291 ms
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: 1984047223, Plan Hash: 512153379

La sortie montre le plan approuvé par rapport à la ligne de base qui serait exécutée. Cependant, la sortie indique également qu'elle a trouvé un plan à un coût inférieur. Dans ce cas, vous pouvez capturer ce nouveau plan à coût minimal en activant la capture automatique des plans comme décrit dans la section Capture automatique de plans.

Les nouveaux plans sont toujours capturés par l'optimiseur comme Unapproved. Utilisez la fonction apg_plan_mgmt.evolve_plan_baselines pour comparer les plans et remplacer leur statut par approuvé, rejeté ou désactivé. Pour plus d'informations, consultez Évaluation des performances des plans.

Sélection du plan à exécuter par l'optimiseur.

Le coût d'un plan d'exécution est une estimation effectuée par l'optimiseur pour comparer différents plans. Lorsqu'il calcule le coût d'un plan, l'optimiseur inclut des facteurs tels que les opérations de CPU et d'E/S requises par ce plan. Pour en savoir plus sur l'estimation des coûts du planificateur de requêtes PostgreSQL, consultez la section Query Planning (Planification des requêtes) dans la documentation PostgreSQL.

L'image suivante montre comment un plan est choisi pour une instruction SQL donnée lorsque la gestion du plan de requête est active, et lorsqu'elle ne l'est pas.

Le déroulement est le suivant :

1. L'optimiseur génère un plan à coût minimal pour l'instruction SQL.

2. Si la gestion du plan de requête n'est pas active, le plan de l'optimiseur est exécuté immédiatement (A. Exécuter le plan de l'optimiseur). La gestion du plan de requête est inactive lorsque les paramètres apg_plan_mgmt.capture_plan_baselines et apg_plan_mgmt.use_plan_baselines sont tous deux à leur valeur par défaut (« off » et « false », respectivement).

   Dans le cas contraire, la gestion du plan de requête est active. Dans ce cas, l'instruction SQL et le plan de l'optimiseur sont évalués plus en détail avant qu'un plan ne soit choisi.

   Astuce

   Les utilisateurs de la base de données possédant le rôle apg_plan_mgmt peuvent comparer les plans de manière proactive, modifier le statut des plans et forcer l'utilisation de plans spécifiques si nécessaire. Pour plus d'informations, consultez Maintenance des plans d'exécution d'Aurora PostgreSQL.

3. L'instruction SQL peut déjà contenir des plans qui ont été stockés par la gestion des plans de requêtes dans le passé. Les plans sont stockés dans apg_plan_mgmt.dba_plans, avec des informations sur les instructions SQL qui ont été utilisées pour les créer. Les informations sur un plan comprennent son statut. Le statut d'un plan peut déterminer s'il est utilisé ou non, comme suit.

   1. Si le plan ne figure pas parmi les plans stockés pour l'instruction SQL, cela signifie que ce plan vient juste d'être généré par l'optimiseur pour l'instruction SQL donnée. Le plan est envoyé au traitement de la capture du plan (4).

   2. Si le plan figure parmi les plans stockés et que son statut est Approuvé ou Préféré, le plan est exécuté (A. Exécuter le plan de l'optimiseur).

      Si le plan figure parmi les plans stockés mais qu'il n'est ni approuvé ni préféré, il est envoyé au traitement de la capture des plans (4).

4. Lorsqu'un plan est capturé pour la première fois pour une instruction SQL donnée, le statut du plan est toujours défini comme Approuvé (P1). Si l'optimiseur génère par la suite le même plan pour la même instruction SQL, l'état de ce plan est modifié en Non-approuvé (P1+n).

   Une fois le plan capturé et son statut mis à jour, l'évaluation se poursuit à l'étape suivante (5).

5. La référence d'un plan contient l'historique de l'instruction SQL et de ses plans à différents états. La gestion du plan de requête peut prendre en compte la référence lors du choix d'un plan, selon que l'option Use plan baselines (Utiliser les références du plan) est activée ou non, comme suit.

   • L'option Use plan baselines (Utiliser les références du plan) est « désactivée » lorsque le paramètre apg_plan_mgmt.use_plan_baselines est défini sur sa valeur par défaut (false). Le plan n'est pas comparé à la référence avant son exécution (A. Exécuter le plan de l'optimiseur).

   • L'option Use plan baselines (Utiliser les références du plan) est « activée » lorsque le paramètre apg_plan_mgmt.use_plan_baselines est défini sur true. Le plan est ensuite évalué à l'aide de la référence (6).

6. Le plan est comparé à d'autres plans pour l'instruction dans la référence.

   1. Si le plan de l'optimiseur figure parmi les plans de la référence, son statut est vérifié (7a).

   2. Si le plan de l'optimiseur ne figure pas parmi les plans de la référence, il est ajouté aux plans de l'instruction en tant que nouveau plan Unapproved.

7. Le statut du plan est vérifié pour déterminer uniquement si son statut est Non approuvé.

   1. Si le statut du plan est Non approuvé, le coût estimé du plan est comparé au coût estimé spécifié pour le seuil du plan d'exécution non approuvé.

      • Si le coût estimé du plan est inférieur au seuil, l'optimiseur l'utilise même s'il s'agit d'un plan Non approuvé (A. Exécuter le plan de l'optimiseur). En général, l'optimiseur n'exécute pas de plan non approuvé. Toutefois, lorsque le paramètre apg_plan_mgmt.unapproved_plan_execution_threshold spécifie une valeur seuil de coût, l'optimiseur compare le coût du plan Non approuvé à ce seuil. Si le coût estimé est inférieur au seuil, l'optimiseur exécute le plan. Pour plus d'informations, consultez apg_plan_mgmt.unapproved_plan_execution_threshold.

      • Si le coût estimé du plan n'est pas inférieur au seuil, les autres attributs du plan sont vérifiés (8a).

   2. Si le statut du plan est autre que Non approuvé, ses autres attributs sont vérifiés (8a).

8. L'optimiseur n'utilisera pas un plan qui est désactivé. C'est-à-dire le plan dont l'attribut enable est défini comme « f » (faux). L'optimiseur n'utilisera pas non plus un plan dont l'état est Rejeté.

   L'optimiseur ne peut pas utiliser de plans qui ne sont pas valides. Les plans peuvent devenir invalides au fil du temps lorsque les objets dont ils dépendent, tels que les index et les partitions de table, sont retirés ou supprimés.

   1. Si l'instruction possède des plans Préférés activés et valides, l'optimiseur choisit le plan à coût minimal parmi les plans Préférés stockés pour cette instruction SQL. L'optimiseur exécute ensuite le plan Préféré à coût minimal.

   2. Si l'instruction n'a pas de plans Préféré activés et valides, elle est évaluée à l'étape suivante (9).

9. Si l'instruction possède des plans Approuvés activés et valides, l'optimiseur choisit le plan à coût minimal parmi les plans Approuvés stockés pour cette instruction SQL. L'optimiseur exécute ensuite le plan Approuvé à coût minimal.

   Si l'instruction n'a pas de plan Approuvé valide et activé, l'optimiseur utilise le plan de coût minimum (A. Exécuter le plan de l'optimiseur).