Créer et gérer des indices nommés

Cette page explique comment créer et gérer des indications nommées dans AlloyDB pour PostgreSQL.

Les indications nommées sont une association entre une requête et un ensemble d'indications 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 indications pour forcer une analyse d'index sur certaines tables ou pour forcer un ordre de jointure spécifique entre plusieurs tables.

Les indications nommées AlloyDB sont compatibles avec toutes les indications de l'extension pg_hint_plan Open Source.

De plus, AlloyDB accepte les indications suivantes pour le moteur colonnaire :

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

AlloyDB vous permet de créer des indications nommées 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é d'indices nommés, tenez compte de la surcharge potentielle qu'elle peut ajouter aux performances du système. Bien que les indications nommées puissent résoudre les plans de requête problématiques, elles ajoutent du temps supplémentaire à l'optimisation des requêtes pour rechercher les plans correspondants dans le catalogue et demander à l'optimiseur d'appliquer plusieurs indications dans les indications nommées. 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'indices nommés implique les étapes suivantes :

Identifiez la requête pour laquelle vous souhaitez créer des indications nommées.
Créez des indications nommées avec des indications à appliquer lors de la prochaine exécution de la requête.
Vérifiez l'application des indices nommés.

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 indices nommés 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é d'indices nommés sur votre instance. Définissez le flag alloydb.enable_named_hints 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é d'indications nommées sur l'instance de pool de lecture.

Pour vérifier que l'indicateur est activé, exécutez la commande show alloydb.enable_named_hints;. Si l'indicateur est activé, la sortie renvoie "on".
Pour chaque base de données dans laquelle vous souhaitez utiliser des indications nommées, 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 suggestions nommées, demandez à votre administrateur de vous accorder les rôles IAM (Identity and Access Management) suivants :

Rôle alloydbsuperuser

Alors que l'autorisation par défaut n'autorise que l'utilisateur disposant du rôle alloydbsuperuser à créer des indications nommées, 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 indications nommées.

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 la requête devient disponible après au moins une exécution de la requête.

Pour identifier l'ID de requête, utilisez les méthodes suivantes :

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 des indices nommés

Pour créer des suggestions nommées, utilisez la fonction google_create_named_hints(), qui crée une association entre la requête et les suggestions dans la base de données.

SELECT google_create_named_hints(
HINTS_NAME=>'HINTS_NAME',
SQL_ID=>QUERY_ID,
SQL_TEXT=>QUERY_TEXT,
APPLICATION_NAME=>'APPLICATION_NAME',
HINTS=>'HINTS',
DISABLED=>DISABLED);

Remplacez les éléments suivants :

HINTS_NAME : nom des indices nommés. Il doit être unique dans la base de données.
SQL_ID (facultatif) : ID de la requête pour laquelle vous créez les indications nommées.

Vous pouvez utiliser l'ID ou le texte de la requête (paramètre SQL_TEXT) pour créer des indices nommés. Toutefois, nous vous recommandons d'utiliser l'ID de requête pour créer des indications nommées, 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 les indications nommées.

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 une différence de casse, peut empêcher l'application des indices nommés. Pour savoir comment créer des indications nommées pour les requêtes avec des littéraux et des constantes, consultez Créer des indications nommées sensibles aux paramètres.
APPLICATION_NAME (facultatif) : nom de l'application cliente de session pour laquelle vous souhaitez utiliser les indices nommés. Une chaîne vide vous permet d'appliquer les indications nommées à la requête, quelle que soit l'application cliente qui l'émet.
HINTS : liste des indices pour la requête, séparés par des espaces.
DISABLED (facultatif) : BOOL. Si la valeur est TRUE, les indices nommés sont initialement créés comme désactivés.

Exemple :

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

Cette requête crée des indications nommées 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éé des indications nommées, vous pouvez utiliser google_named_hints_view pour vérifier si elles ont été créées, comme illustré dans l'exemple suivant :

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

Une fois les indications nommées créées sur l'instance principale, elles sont automatiquement appliquées aux requêtes associées sur l'instance du pool de lecture, à condition que vous ayez également activé la fonctionnalité d'indications nommées sur l'instance du pool de lecture.

Créer des suggestions nommées sensibles aux paramètres

Par défaut, lorsque des suggestions nommées sont créées 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 ?. Les indices nommés sont ensuite utilisés 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 les indications nommées my_hint2 par défaut.

SELECT google_create_named_hints(
  HINTS_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 ensuite utiliser les indications nommées my_hint2 par défaut.

AlloyDB vous permet également de créer des indications nommées 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 des indications nommées sensibles 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 des plans pour les deux requêtes, vous devez créer des indications nommées distinctes pour chacune d'elles. Toutefois, vous pouvez utiliser des indications différentes pour les deux indications nommées.

Pour créer des suggestions nommées sensibles aux paramètres, définissez le paramètre SENSITIVE_TO_PARAM de la fonction google_create_named_hints() sur TRUE, comme indiqué dans l'exemple suivant :

SELECT google_create_named_hints(
HINTS_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 les indications nommées my_hint3, car la valeur littérale "99" ne correspond pas à "88".

Lorsque vous utilisez des suggestions nommées sensibles aux paramètres, tenez compte des points suivants :

Les indications nommées sensibles aux paramètres ne sont pas compatibles avec un mélange de valeurs littérales et constantes, et de marqueurs de paramètres dans le texte de la requête.
Lorsque vous créez des suggestions nommées sensibles aux paramètres et des suggestions nommées par défaut pour la même requête, les suggestions nommées sensibles aux paramètres sont privilégiées par rapport aux suggestions nommées par défaut.
Si vous souhaitez utiliser l'ID de requête pour créer des suggestions nommées sensibles 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 les suggestions nommées.

Vérifier l'application des indices nommés

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

Utilisez la commande EXPLAIN ou 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 indices nommés

AlloyDB vous permet d'afficher, d'activer, de désactiver et de supprimer les indications nommées.

Afficher les indices nommés

Pour afficher les suggestions nommées existantes, utilisez la fonction google_named_hints_view, comme illustré dans l'exemple suivant :

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

Activer les indices nommés

Pour activer les indices nommés existants, utilisez la fonction google_enable_named_hints(HINTS_NAME). Par défaut, les suggestions nommées sont activées lorsque vous les créez.

Par exemple, pour réactiver les indications nommées my_hint1 précédemment désactivées dans la base de données, exécutez la fonction suivante :

SELECT google_enable_named_hints('my_hint1');

Désactiver les indices nommés

Pour désactiver les indices nommés existants, utilisez la fonction google_disable_named_hints(HINTS_NAME).

Par exemple, pour supprimer les conseils nommés my_hint1 de la base de données, exécutez la fonction suivante :

SELECT google_disable_named_hints('my_hint1');

Supprimer les indices nommés

Pour supprimer des indices nommés, utilisez la fonction google_delete_named_hints(HINTS_NAME).