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

Pour contrôler la façon dont le planificateur de requêtes AlloyDB pour PostgreSQL génère des plans d'exécution sans modifier les requêtes de votre application, vous pouvez créer, vérifier, afficher, activer, désactiver et supprimer des indications nommées. Les indications nommées associent une requête à un ensemble d'indications qui spécifient les détails du plan d'exécution préféré. Lorsque le planificateur de requêtes génère un plan d'exécution pour la requête associée, il applique les indications et les réapplique automatiquement lors des exécutions ultérieures de la requête. Par exemple, vous pouvez utiliser des indications nommées pour forcer une analyse d'index au lieu d'une analyse séquentielle sur une table, ou pour appliquer un ordre de jointure spécifique sur plusieurs tables.

Les indications nommées dans AlloyDB sont compatibles avec toutes les indications de l'extension Open Source pg_hint_plan et les indications suivantes 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 indications nommées pour les requêtes paramétrées et non paramétrées, que ce document appelle requêtes sensibles aux paramètres.

Workflow

L'utilisation d'indications nommées implique les étapes suivantes :

  1. Identifiez la requête pour laquelle vous souhaitez créer des indications nommées.

  2. Créez des indications nommées avec des indications à appliquer lors de la prochaine exécution de la requête.

  3. Vérifiez l'application des indications nommées.

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 indications nommées que vous avez créées à l'aide d'une version antérieure, recréez-les en suivant les instructions de cette page.

Avant de commencer

  • Activez la fonctionnalité d'indications nommées sur votre instance. Définissez l'option alloydb.enable_named_hints sur on. Vous pouvez activer cet indicateur au niveau du serveur ou de la session. Pour minimiser les frais généraux pouvant résulter de l'utilisation de cette fonctionnalité, n'activez cette option qu'au niveau de la session.

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

    Pour vérifier que l'option est activée, exécutez la commande show alloydb.enable_named_hints;. Si l'option est activée, 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 indications nommées, demandez à votre administrateur de vous accorder les rôles IAM (Identity and Access Management) suivants :

Bien que l'autorisation par défaut ne permette qu'à l'utilisateur disposant du rôle alloydbsuperuser de 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 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 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 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 indications nommées

Pour créer des indications nommées, utilisez la fonction google_create_named_hints(), qui crée une association entre la requête et les indications 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 indications nommées. 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 de la requête ou le texte de la requête (paramètre SQL_TEXT) pour créer des indications nommées. 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 requête normalisé 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, celui-ci 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 indications nommées. 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 la session pour laquelle vous souhaitez utiliser les indications nommées. 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 d'indications pour la requête, séparées par des espaces.

  • DISABLED (facultatif) : BOOL. Si la valeur est TRUE, les indications nommées sont initialement créées comme désactivées.

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 une indication nommée my_hint1. Son hint 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 indications nommées, vous pouvez utiliser google_named_hints_view pour confirmer 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 indications nommées sensibles aux paramètres

Par défaut, lorsque des indications nommées sont créées pour une requête, le texte de la requête associée est normalisé en remplaçant toute valeur littérale et constante dans le texte de la requête par un marqueur de paramètre, tel que ?. Les indications nommées sont ensuite utilisées 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);

Ensuite, une requête telle que SELECT * FROM t WHERE a = 99; peut 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 la requête est significative 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 les plans pour les deux requêtes, vous devez créer des indications nommées distinctes pour chaque requête. Toutefois, vous pouvez utiliser des indications différentes pour les deux indications nommées.

Pour créer des indications nommées 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 indications nommées my_hint3, car la valeur littérale "99" ne correspond pas à "88".

Lorsque vous utilisez des indications 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 indications nommées sensibles aux paramètres et des indications nommées par défaut pour la même requête, les indications nommées sensibles aux paramètres sont privilégiées par rapport aux indications nommées par défaut.
  • Si vous souhaitez utiliser l'ID de requête pour créer des indications 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 l'exécution la plus récente (dans la session en cours) sont utilisées pour créer les indications nommées.

Vérifier l'application des indications nommées

Après avoir créé les indications 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 indications que le planificateur tente d'appliquer, vous pouvez définir les options suivantes 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 indications nommées

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

Afficher les indications nommées

Pour afficher les indications 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 indications nommées

Pour activer des indications nommées existantes, utilisez la fonction google_enable_named_hints(HINTS_NAME). Par défaut, les indications 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 indications nommées

Pour désactiver les indications nommées existantes, utilisez la fonction google_disable_named_hints(HINTS_NAME).

Par exemple, pour supprimer l'exemple d'indications nommées my_hint1 de la base de données, exécutez la fonction suivante :

SELECT google_disable_named_hints('my_hint1');

Supprimer des indications nommées

Pour supprimer des indications nommées, utilisez la fonction google_delete_named_hints(HINTS_NAME).

Par exemple, pour supprimer l'exemple d'indications nommées my_hint1 de la base de données, exécutez la fonction suivante :

SELECT google_delete_named_hints('my_hint1');

Désactiver la fonctionnalité d'indications nommées

Pour désactiver la fonctionnalité d'indications nommées sur votre instance, définissez l'option alloydb.enable_named_hints sur off. Pour en savoir plus, consultez Configurer les indicateurs de base de données d'une instance.

Limites

L'utilisation d'indications nommées présente les limites suivantes :

  • Lorsque vous utilisez un ID de requête pour créer des indications nommées, le texte de la requête d'origine est limité à 2 048 caractères.
  • Compte tenu de la sémantique d'une requête complexe, tous les indices et leurs combinaisons ne peuvent pas être entièrement appliqués. Nous vous recommandons de tester les indications prévues sur vos requêtes avant de déployer des indications nommées en production.
  • Le forçage des ordres de jointure pour les requêtes complexes est limité.

  • L'utilisation d'indications nommées pour influencer la sélection du plan peut interférer avec les futures améliorations de l'optimiseur AlloyDB. Assurez-vous de revoir le choix d'utiliser des indications nommées et d'ajuster les indications nommées en conséquence lorsque les événements suivants se produisent :

    • La charge de travail a considérablement changé.
    • Un nouveau déploiement ou une nouvelle mise à niveau d'AlloyDB impliquant des modifications et des améliorations de l'optimiseur est disponible.
    • D'autres méthodes de réglage des requêtes sont appliquées aux mêmes requêtes.
    • L'utilisation d'indications nommées ajoute une surcharge importante aux performances du système.

Pour en savoir plus sur les limites, consultez la documentation pg_hint_plan.

Étape suivante