Cette page a été traduite par l'API Cloud Translation.

Créer et gérer des correctifs de plan de requête

Cette page explique comment créer et gérer des correctifs de plan de requête dans AlloyDB pour PostgreSQL.

Un correctif de plan de requête est une association entre une requête et un ensemble d'indices qui vous permettent de spécifier les détails du plan de requête. Une optimisation spécifie des informations supplémentaires sur le plan d'exécution final préféré pour la requête. Par exemple, lorsque vous analysez une table dans la requête, utilisez une analyse d'index au lieu d'autres types d'analyses, comme une analyse séquentielle.

Pour limiter le choix du plan final dans les spécifications des indices, le planificateur de requêtes applique d'abord les indices à la requête lors de la génération de son plan d'exécution. Les indices sont ensuite appliqués automatiquement chaque fois que la requête est émise. Cette approche vous permet de forcer différents plans de requête à partir du planificateur. Par exemple, vous pouvez utiliser des indices pour forcer une analyse d'index sur certaines tables ou pour forcer un ordre de jointure spécifique entre plusieurs tables.

Le correctif du plan de requête AlloyDB est compatible avec tous les indices de l'extension pg_hint_plan Open Source.

De plus, AlloyDB est compatible avec les indications suivantes pour le moteur en colonnes :

ColumnarScan(table) : force une analyse par colonne sur la table.
NoColumnarScan(table) : désactive l'analyse par colonne sur la table.

AlloyDB vous permet de créer des correctifs de plan pour les requêtes paramétrées et non paramétrées. Sur cette page, les requêtes non paramétrées sont appelées requêtes sensibles aux paramètres.

Important : Avant d'utiliser la fonctionnalité de correctif du plan de requête, tenez compte de la surcharge potentielle qu'elle peut ajouter aux performances du système. Bien qu'un correctif de plan de requête puisse résoudre les problèmes liés aux plans de requête, il ajoute du temps supplémentaire à l'optimisation des requêtes pour rechercher les plans correspondants dans le catalogue et demande à l'optimiseur d'appliquer plusieurs indications dans le correctif. Bien que les frais généraux soient souvent négligeables dans les requêtes complexes, ils peuvent être importants dans les requêtes simples. Par exemple, lors de nos tests internes, le temps de compilation supplémentaire des requêtes a dépassé 10 % pour les requêtes de planification courte (moins d'une milliseconde).

Workflow

L'utilisation d'un correctif de plan de requête implique les étapes suivantes :

Identifiez la requête pour laquelle vous souhaitez créer un correctif de plan.
Créez un correctif de plan avec des indications à appliquer lors de la prochaine exécution de la requête.
Vérifiez l'application du correctif du plan.

Cette page utilise la table et l'index suivants pour les exemples :

CREATE TABLE t(a INT, b INT);
CREATE INDEX t_idx1 ON t(a);

  DROP EXTENSION IF EXISTS google_auto_hints;

Pour continuer à utiliser les correctifs de plan de requête que vous avez créés avec une version antérieure, recréez-les en suivant les instructions de cette page.

Avant de commencer

Activez la fonctionnalité de correctif du plan de requête sur votre instance. Définissez le flag alloydb.enable_query_plan_patch sur on. Vous pouvez activer ce signal au niveau du serveur ou de la session. Pour minimiser la surcharge qui pourrait résulter de l'utilisation de cette fonctionnalité, n'activez cet indicateur qu'au niveau de la session.

Pour en savoir plus, consultez Configurer les options de base de données d'une instance.

Remarque : Pour forcer le même plan de requête sur votre instance de pool de lecture, activez la fonctionnalité de correctif du plan de requête sur l'instance de pool de lecture.

Pour vérifier que l'indicateur est activé, exécutez la commande show alloydb.enable_query_plan_patch;. Si l'indicateur est activé, le résultat renvoie "on".
Pour chaque base de données dans laquelle vous souhaitez utiliser des correctifs de plan de requête, créez une extension dans la base de données à partir de l'instance principale AlloyDB en tant qu'utilisateur alloydbsuperuser ou postgres :
```
CREATE EXTENSION google_auto_hints CASCADE;
```

Rôles requis

Pour obtenir les autorisations nécessaires pour créer et gérer des correctifs de plan de requête, demandez à votre administrateur de vous accorder les rôles IAM (Identity and Access Management) suivants :

Rôle alloydbsuperuser

L'autorisation par défaut n'autorise que l'utilisateur disposant du rôle alloydbsuperuser à créer des correctifs de plan. Toutefois, vous pouvez éventuellement accorder l'autorisation d'écriture aux autres utilisateurs ou rôles de la base de données afin qu'ils puissent créer des correctifs de plan.

GRANT INSERT,DELETE,UPDATE ON hint_plan.plan_patches, hint_plan.hints TO role_name;
GRANT USAGE ON SEQUENCE hint_plan.hints_id_seq, hint_plan.plan_patches_id_seq TO role_name;

Identifier la requête

Vous pouvez utiliser l'ID de requête pour identifier la requête dont le plan par défaut doit être ajusté. L'ID de requête devient disponible après au moins une exécution de la requête.

Utilisez les méthodes suivantes pour identifier l'ID de requête :

Exécutez la commande EXPLAIN (VERBOSE) comme illustré dans l'exemple suivant :

EXPLAIN (VERBOSE) SELECT * FROM t WHERE a = 99;
                        QUERY PLAN
----------------------------------------------------------
Seq Scan on public.t  (cost=0.00..38.25 rows=11 width=8)
  Output: a, b
  Filter: (t.a = 99)
Query Identifier: -6875839275481643436

Dans le résultat, l'ID de requête est -6875839275481643436.

Interrogez la vue pg_stat_statements.

Si vous avez activé l'extension pg_stat_statements, vous pouvez trouver l'ID de la requête en interrogeant la vue pg_stat_statements, comme illustré dans l'exemple suivant :
```
select query, queryid from pg_stat_statements;
```

Créer un correctif de plan de requête

Pour créer un correctif de plan de requête, utilisez la fonction google_create_plan_patch(), qui crée une association entre la requête et les indications dans la base de données.

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'PLAN_PATCH_NAME',
SQL_ID=>QUERY_ID,
SQL_TEXT=>QUERY_TEXT,
APPLICATION_NAME=>'APPLICATION_NAME',
HINTS=>'HINTS',
DISABLED=>DISABLED);

Remplacez les éléments suivants :

PLAN_PATCH_NAME : nom du correctif du forfait. Il doit être unique dans la base de données.
SQL_ID (facultatif) : ID de la requête pour laquelle vous créez le correctif de plan.

Vous pouvez utiliser l'ID ou le texte de la requête (paramètre SQL_TEXT) pour créer un correctif de plan. Toutefois, nous vous recommandons d'utiliser l'ID de requête pour créer un correctif de plan, car AlloyDB localise automatiquement le texte de la requête normalisée en fonction de l'ID de requête.
SQL_TEXT (facultatif) : texte de la requête pour laquelle vous créez le correctif de plan.

Lorsque vous utilisez le texte de la requête, il doit être identique à la requête prévue, à l'exception des valeurs littérales et constantes de la requête. Toute incohérence, y compris au niveau de la casse, peut empêcher l'application du correctif au forfait. Pour savoir comment créer des correctifs de plan pour les requêtes avec des littéraux et des constantes, consultez Créer un correctif de plan de requête sensible aux paramètres.
APPLICATION_NAME (facultatif) : nom de l'application cliente de session pour laquelle vous souhaitez utiliser le correctif de forfait. Une chaîne vide vous permet d'appliquer le correctif de plan à la requête, quelle que soit l'application cliente qui l'émet.
HINTS : liste des suggestions pour la requête, séparées par des espaces.
DISABLED (facultatif) : BOOL. Si TRUE, crée initialement le correctif du forfait comme désactivé.

Exemple :

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'my_hint1',
SQL_ID=>-6875839275481643436,
SQL_TEXT=>NULL,
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL);

Cette requête crée un correctif de plan nommé my_hint1. Son indication IndexScan(t) est appliquée par le planificateur pour forcer une analyse d'index sur la table t lors de la prochaine exécution de cet exemple de requête.

Après avoir créé un correctif de plan, vous pouvez utiliser google_query_plan_patch_view pour vérifier si le correctif de plan a été créé, comme illustré dans l'exemple suivant :

postgres=>\x
postgres=>select * from google_query_plan_patch_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
plan_patch_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

Une fois le correctif de plan créé sur l'instance principale, il est automatiquement appliqué aux requêtes associées sur l'instance du pool de lecture, à condition que vous ayez également activé la fonctionnalité de correctif de plan de requête sur l'instance du pool de lecture.

Créer un correctif de plan de requête sensible aux paramètres

Par défaut, lorsqu'un correctif de plan est créé pour une requête, le texte de requête associé est normalisé en remplaçant toute valeur littérale et constante dans le texte de requête par un marqueur de paramètre, tel que ?. Le correctif du plan est ensuite utilisé pour cette requête normalisée, même avec une valeur différente pour le marqueur de paramètre.

Par exemple, l'exécution de la requête suivante permet à une autre requête, telle que SELECT * FROM t WHERE a = 99;, d'utiliser le correctif de plan my_hint2 par défaut.

SELECT google_create_plan_patch(
  PLAN_PATCH_NAME=>'my_hint2',
  SQL_ID=>NULL,
  SQL_TEXT=>'SELECT * FROM t WHERE a = ?;',
  APPLICATION_NAME=>'',
  HINTS=>'SeqScan(t)',
  DISABLED=>NULL);

Une requête, telle que SELECT * FROM t WHERE a = 99;, peut alors utiliser le correctif de planification my_hint2 par défaut.

AlloyDB vous permet également de créer un correctif de plan pour les textes de requête non paramétrés, dans lesquels chaque valeur littérale et constante du texte de requête est importante lors de la mise en correspondance des requêtes.

Lorsque vous appliquez un correctif sensible aux paramètres, deux requêtes qui ne diffèrent que par les valeurs littérales ou constantes correspondantes sont également considérées comme différentes. Si vous souhaitez forcer les plans pour les deux requêtes, vous devez créer des correctifs de plan distincts pour chaque requête. Toutefois, vous pouvez utiliser des indices différents pour les deux correctifs de planification.

Pour créer un correctif de plan sensible aux paramètres, définissez le paramètre SENSITIVE_TO_PARAM de la fonction google_create_plan_patch() sur TRUE, comme indiqué dans l'exemple suivant :

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'my_hint3',
SQL_ID=>NULL,
SQL_TEXT=>'SELECT * FROM t WHERE a = 88;',
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL,
SENSITIVE_TO_PARAM=>TRUE);

La requête SELECT * FROM t WHERE a = 99; ne peut pas utiliser le correctif de plan my_hint3, car la valeur littérale "99" ne correspond pas à "88".

Lorsque vous utilisez des correctifs de plan sensibles aux paramètres, tenez compte des points suivants :

Les correctifs de plan sensibles aux paramètres n'acceptent pas un mélange de valeurs littérales et constantes, ni de marqueurs de paramètres dans le texte de la requête.
Lorsque vous créez un correctif de plan sensible aux paramètres et un correctif de plan par défaut pour la même requête, le correctif de plan sensible aux paramètres est préféré au correctif par défaut.
Si vous souhaitez utiliser l'ID de requête pour créer un correctif de plan sensible aux paramètres, assurez-vous que la requête a été exécutée dans la session en cours. Les valeurs de paramètre de la dernière exécution (dans la session actuelle) sont utilisées pour créer le correctif du plan.

Vérifier l'application du correctif du plan de requête

Après avoir créé le correctif du plan, utilisez les méthodes suivantes pour vérifier que le plan de requête est forcé en conséquence.

Utilisez la commande EXPLAIN ou la commande EXPLAIN (ANALYZE).

Pour afficher les indices que le planificateur tente d'appliquer, vous pouvez définir les indicateurs suivants au niveau de la session avant d'exécuter la commande EXPLAIN :
```
SET pg_hint_plan.debug_print = ON;
SET client_min_messages = LOG;
```
Utilisez l'extension auto_explain.

Gérer les correctifs de plan de requête

AlloyDB vous permet d'afficher, d'activer, de désactiver et de supprimer un correctif de plan de requête.

Afficher un patch de plan de requête

Pour afficher les correctifs de forfait existants, utilisez la fonction google_query_plan_patch_view, comme illustré dans l'exemple suivant :

postgres=>\x
postgres=>select * from google_query_plan_patch_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
plan_patch_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

Activer un correctif de plan de requête

Pour activer un correctif de forfait existant, utilisez la fonction google_enable_plan_patch(PLAN_PATCH_NAME). Par défaut, un correctif de plan est activé lorsque vous le créez.

Par exemple, pour réactiver le correctif de plan my_hint1 précédemment désactivé à partir de la base de données, exécutez la fonction suivante :

SELECT google_enable_plan_patch('my_hint1');

Désactiver un correctif de plan de requête

Pour désactiver un correctif de forfait existant, utilisez la fonction google_disable_plan_patch(PLAN_PATCH_NAME).

Par exemple, pour supprimer l'exemple de correctif de plan my_hint1 de la base de données, exécutez la fonction suivante :

SELECT google_disable_plan_patch('my_hint1');

Supprimer un correctif de plan de requête

Pour supprimer un correctif de plan, utilisez la fonction google_delete_plan_patch(PLAN_PATCH_NAME).