Google utilise la technologie IA pour traduire le contenu dans votre langue préférée. Les traductions générées par IA peuvent contenir des erreurs.

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

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

Les indices nommés sont 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. Un indice 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 la spécification 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.

Les indices nommés AlloyDB sont compatibles avec tous les indices de l' extension Open Source pg_hint_plan.

De plus, AlloyDB est compatible avec les indices suivants pour le moteur de données en colonnes :

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

AlloyDB vous permet de créer des indices nommés 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.

Workflow

L'utilisation d'indices nommés comprend les étapes suivantes :

Identifiez la requête pour laquelle vous souhaitez créer des indices nommés.
Créez des indices nommés avec les indices à 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 à titre d'exemple :

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 à l'aide d'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 l'indicateur alloydb.enable_named_hints sur on. Vous pouvez activer cet indicateur au niveau du serveur ou au niveau de la session. Pour minimiser les frais généraux pouvant résulter de l'utilisation de cette fonctionnalité, activez cet indicateur uniquement au niveau de la session.

Pour en savoir plus, consultez Configurer les indicateurs 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’ indices nommés 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 indices nommés, 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 indices nommés, demandez à votre administrateur de vous accorder les rôles IAM (Identity and Access Management) suivants :

alloydbsuperuser rôle

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

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

Pour identifier l'ID de requête, procédez comme suit :

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 la sortie, 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 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 indices nommés, utilisez la fonction google_create_named_hints(), qui crée une association entre la requête et les indices 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. Ce nom doit être unique dans la base de données.
SQL_ID (facultatif) : ID de requête pour laquelle vous créez les indices nommés.

Vous pouvez utiliser l'ID de requête ou le texte de 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 indices nommés, car AlloyDB localise automatiquement le texte de requête normalisé en fonction de l'ID de requête.
SQL_TEXT (facultatif) : texte de requête pour laquelle vous créez les indices nommés.

Lorsque vous utilisez le texte de requête, celui-ci doit être identique à la requête prévue, à l'exception des valeurs littérales et constantes de la requête. Toute non-concordance, y compris la différence de casse, peut entraîner la non-application des indices nommés. Pour savoir comment créer des indices nommés pour les requêtes avec des littéraux et des constantes, consultez Créer des indices nommés 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 indices nommés à la requête, quelle que soit l'application cliente qui émet la requête.
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 créés initialement 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 indices nommés appelés my_hint1. Son indice IndexScan(t) est appliqué 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 indices nommés, vous pouvez utiliser google_named_hints_view pour vérifier si les indices nommés sont créés, 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 indices nommés créés sur l'instance principale, ils sont automatiquement appliqués aux requêtes associées sur l'instance de pool de lecture, à condition que vous ayez également activé la fonctionnalité d'indices nommés sur l'instance de pool de lecture.

Créer des indices nommés sensibles aux paramètres

Par défaut, lorsque des indices nommés sont créés pour une requête, le texte de requête associé est normalisé en remplaçant toute valeur littérale et constante du 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 indices nommés 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 indices nommés my_hint2 par défaut.

AlloyDB vous permet également de créer des indices nommés 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 indices nommés 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 indices nommés distincts pour chaque requête. Toutefois, vous pouvez utiliser différents indices pour les deux indices nommés.

Pour créer des indices nommés sensibles aux paramètres, définissez le paramètre SENSITIVE_TO_PARAM de la fonction google_create_named_hints() sur TRUE, comme illustré 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 indices nommés my_hint3, car la valeur littérale "99" ne correspond pas à "88".

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

Les indices nommés 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 requête.
Lorsque vous créez des indices nommés sensibles aux paramètres et des indices nommés par défaut pour la même requête, les indices nommés sensibles aux paramètres sont préférés aux indices nommés par défaut.
Si vous souhaitez utiliser l'ID de requête pour créer des indices nommés sensibles aux paramètres, assurez-vous que la requête a été exécutée dans la session en cours. Les valeurs de paramètres de l'exécution la plus récente (dans la session en cours) sont utilisées pour créer les indices nommés.

Vérifier l'application des indices nommés

Après avoir créé les indices nommés, 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'auto_explain extension.

Gérer les indices nommés

AlloyDB vous permet d'afficher, d'activer, de désactiver et de supprimer des indices nommés.

Afficher les indices nommés

Pour afficher les indices nommés existants, 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 indices nommés sont activés lorsque vous les créez.

Par exemple, pour réactiver les indices nommés my_hint1 précédemment désactivés 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 indices 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).