L'optimisation des performances des requêtes SQL est une tâche cruciale dans la gestion des bases de données. Parfois, l'optiimseur de requêtes ne choisit pas le plan d'exécution le plus efficace, entraînant des latences indésirables. Plutôt que de modifier le code de l'application, les bases de données comme GaussDB offrent des mécanismes pour "patcher" les plans d'exécution. Ce guide explore l'utilisation des patchs SQL pour forcer un plan d'exécution optimal.

1. Connexion à l'instance GaussDB en tant qu'utilisateur de test.

Commencez par vous connecter à votre base de données en utilisant un compte utilisateur standard. Pour cet exemple, nous utiliserons utilisateur_test.

gsql -d ma_base_donnees -h 192.168.3.60 -U utilisateur_test -p 8000 -W MonMotDePasse@123 -r

2. Création de la table et de l'index de démonstration.

Nous allons créer une table simple nommée articles avec quelques colonnes et un index sur la colonne id_article, qui sera notre cible d'optimisation.

CREATE TABLE articles (
    id_article INT,
    description VARCHAR(255),
    prix DECIMAL(10, 2)
);
CREATE INDEX idx_id_article ON articles (id_article);

3. Insertion de données de test.

Pour simuler un scénario réel, nous allons insérer un grand volume de données dans la table articles.

INSERT INTO articles
SELECT
    generate_series(1, 100000),
    'Article ' || generate_series(1, 100000)::text,
    (random() * 100)::DECIMAL(10, 2);

4. Collecte des statistiques de la table.

Il est essentiel de s'assurer que l'optimiseur dispose d'informations à jour sur la distribution des données. Exécutez ANALYZE pour mettre à jour les statistiques.

ANALYZE articles;

5. Exécution et analyse du plan initial de la requête.

Exécutons une requête simple pour observer le plan d'exécution par défaut. Nous cherchons les articles dont l'ID est supérieur à 999.

EXPLAIN ANALYZE SELECT id_article FROM articles WHERE id_article > 999;

Le résultat de EXPLAIN ANALYZE pourrait ressembler à ceci :

 id |       operation        | A-time | A-rows | E-rows | Peak Memory | A-width | E-width |     E-costs
----+------------------------+--------+--------+--------+-------------+---------+---------+------------------
  1 | ->  Seq Scan on articles | 65.123 |  99001 |   9026 | 63KB        |         |       4 | 16.339..1631.000
(1 row)

 Predicate Information (identified by plan id)
-----------------------------------------------
   1 --Seq Scan on articles
         Filter: (id_article > 999)
         Rows Removed by Filter: 999
(3 rows)

       ====== Query Summary =====
----------------------------------------
 Datanode executor start time: 0.050 ms
 Datanode executor run time: 78.500 ms
 Datanode executor end time: 0.030 ms
 Planner runtime: 0.600 ms
 Query Id: 1234567890123456789
 Total runtime: 78.700 ms
(6 rows)

Comme on peut le constater, la requête utilise un Seq Scan (analyse séquentielle complète de la table), ce qui n'est pas optimal malgré l'existence d'un index sur id_article. Nous souhaitons forcer un IndexOnlyScan pour améliorer les performances.

6. Identification de l'ID SQL unique de la requête.

Pour appliquer un patch, nous avons besoin de l'identifient unique de la requête. Connectez-vous en tant qu'utilisateur disposant de privilèges d'administrateur (par exemple, root).

gsql -d ma_base_donnees -p 8000 -r

Recherchez la requête dans la vue système dbe_perf.summary_statement :

SELECT unique_sql_id, query FROM dbe_perf.summary_statement WHERE query LIKE 'SELECT%id_article%FROM%articles%WHERE%id_article%?%';

Le résultat devrait inclure l'ID de notre requête :

 unique_sql_id |                        query
---------------+-----------------------------------------------------
    9876543210 | SELECT id_article FROM articles WHERE id_article > ?;
(1 row)

7. Création du patch SQL pour forcer un IndexOnlyScan.

Nous allons maintenant créer un patch SQL qui indique à l'optimiseur d'utiliser un IndexOnlyScan pour la requête identifiée.

SELECT * FROM dbe_sql_util.create_hint_sql_patch('optim_articles', 9876543210, 'IndexOnlyScan(articles)');

Remplacez 9876543210 par l'unique_sql_id que vous avez obtenu à l'étape précédente.

8. Vérification du plan d'exécution après l'application du patch.

Reconnectez-vous en tant qu'utilisateur_test et exécutez à nouveau la requête pour observer l'effet du patch.

gsql -d ma_base_donnees -h 192.168.3.60 -U utilisateur_test -p 8000 -W MonMotDePasse@123 -r

EXPLAIN ANALYZE SELECT id_article FROM articles WHERE id_article > 999;

Vous devriez maitnenant voir un plan d'exécution optimisé :

NOTICE:  Plan influenced by SQL hint patch
 id |                     operation                      | A-time | A-rows | E-rows | Peak Memory | A-width | E-width |     E-costs
----+----------------------------------------------------+--------+--------+--------+-------------+---------+---------+-----------------
  1 | ->  Index Only Scan using idx_id_article on articles | 28.500 |  99001 |  99026 | 93KB        |         |       4 | 0.000..2155.605
(1 row)

    Predicate Information (identified by plan id)
------------------------------------------------------
   1 --Index Only Scan using idx_id_article on articles
         Index Cond: (id_article > 999)
(2 rows)

       ====== Query Summary =====
----------------------------------------
 Datanode executor start time: 0.040 ms
 Datanode executor run time: 35.800 ms
 Datanode executor end time: 0.020 ms
 Planner runtime: 0.550 ms
 Query Id: 1234567890123456790
 Total runtime: 35.900 ms
(6 rows)

Le plan indique désormais un Index Only Scan, et le temps d'exécution total a été significativement réduit (par exemple, de 78 ms à 35 ms dans cet exemple simulé), démontrant l'efficacité du patch.

9. Nettoyage des ressources.

Pour conclure, supprimez la table de test et le patch SQL que nous avons créé.

DROP TABLE articles;

Connectez-vous à nouveau en tant qu'administrateur pour supprimer le patch :

gsql -d ma_base_donnees -p 8000 -r

SELECT * FROM dbe_sql_util.drop_sql_patch('optim_articles');