Optimiser les requêtes SQL à l'aide de l'IA

Pour optimiser les performances des requêtes de base de données AlloyDB pour PostgreSQL, vous pouvez ajuster vos requêtes SQL à l'aide d'insights basés sur l'IA. En analysant vos plans d'exécution de requêtes, la fonction ai.tune_sql, optimisée par un grand modèle de langage (LLM), identifie les goulots d'étranglement complexes et fournit des recommandations que vous pouvez appliquer pour l'optimisation.

Lorsque vous appelez la fonction ai.tune_sql, elle analyse votre requête et fournit des insights et des recommandations dans les domaines suivants :

  • Résumé de la requête : analyse les goulots d'étranglement des performances des requêtes.
  • Réécriture de requête : suggère des techniques d'optimisation pour réécrire la requête d'origine en une requête équivalente plus efficace.
  • Recommandations d'index : recommande des types d'index tels que les index sur les expressions, les index GIN (Generalized Inverted Index) et les index ScaNN (Scalable Nearest Neighbors).
  • Recommandations de collecte de statistiques : recommande la collecte de statistiques et de ANALYZE de table sur plusieurs colonnes en fonction du plan d'exécution de la requête.
  • Réglage des options GUC (Grand Unified Configuration) : recommande de nouvelles valeurs GUC en analysant le plan d'exécution et les informations système telles que l'utilisation et la taille de la mémoire et de la mémoire tampon.
  • Analyse et nettoyage : recommande d'exécuter ANALYZE et VACUUM sur les tables en fonction de l'historique d'analyse et de nettoyage, ainsi que du nombre de tuples actifs et inactifs.

Limites

Le réglage des requêtes SQL présente les limites suivantes :

  • Instructions préparées et paramétrées : pour les instructions préparées et paramétrées, ai.tune_sql ne fournit pas de recommandations de réécriture de requête, bien qu'il fournisse d'autres recommandations de réglage. Pour recevoir des recommandations de réécriture de requête pour une requête contenant des espaces réservés de paramètres, tels que $1 ou $2 copiés à partir d'Insights sur les requêtes ou de pg_stat_statements, remplacez les espaces réservés par des valeurs littérales représentatives avant d'exécuter ai.tune_sql.
  • Instructions SELECT en lecture seule uniquement lorsque explain_analyze est défini sur TRUE : seules les instructions SELECT uniques en lecture seule sont acceptées lorsque explain_analyze est défini sur TRUE. L'exécution de ai.tune_sql avec explain_analyze défini sur TRUE sur d'autres instructions (telles que INSERT, UPDATE, DELETE, SELECT INTO ou SELECT FOR UPDATE) renvoie un message d'erreur.

Avant de commencer

Vérifiez que vous disposez des éléments suivants :

Activer le réglage SQL

Enregistrez un LLM et activez les indicateurs de base de données requis.

  1. Créez un point de terminaison de modèle Gemini pour la fonction ai.tune_sql en enregistrant un point de terminaison de modèle mondial ou régional dans la région de votre instance :

    • Pour enregistrer un point de terminaison de modèle global lorsque le modèle n'est pas disponible dans la région de votre instance (par exemple, gemini-3.5-flash sur locations/global), exécutez l'instruction suivante :

      CALL google_ml.create_model(
  model_id => 'gemini-3.5-flash-global',
  model_request_url => 'https://aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/global/publishers/google/models/gemini-3.5-flash:generateContent',
  model_qualified_name => 'gemini-3.5-flash',
  model_provider => 'google',
  model_type => 'llm',
  model_auth_type => 'alloydb_service_agent_iam');

      Remplacez PROJECT_ID par l'ID du projet dans lequel le modèle est disponible.

    • Pour enregistrer un point de terminaison de modèle régional dans la région de votre instance (par exemple, gemini-2.5-pro sur locations/REGION_ID), exécutez l'instruction suivante :

      CALL google_ml.create_model(
  model_id => 'gemini-2.5-pro',
  model_request_url => 'https://REGION_ID-aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/REGION_ID/publishers/google/models/gemini-2.5-pro:generateContent',
  model_qualified_name => 'gemini-2.5-pro',
  model_provider => 'google',
  model_type => 'llm',
  model_auth_type => 'alloydb_service_agent_iam');

      Remplacez les éléments suivants :

      • REGION_ID : région de votre instance AlloyDB (par exemple, us-central1).
      • PROJECT_ID : ID du projet dans lequel le modèle est disponible.

  2. Définissez l'ID du modèle pour les recommandations de réglage des requêtes SQL dans votre session de base de données actuelle. Spécifiez le model_id que vous avez enregistré à l'étape précédente, par exemple 'gemini-3.5-flash-global' ou 'gemini-2.5-pro' :

    SET google_db_advisor.llm_model_id = 'gemini-3.5-flash-global';

  3. Activez le réglage des requêtes SQL pour votre session de base de données actuelle en définissant l'option google_db_advisor.enable_ai_tune_sql :

    SET google_db_advisor.enable_ai_tune_sql = on;

    Étant donné que SET ne s'applique qu'à la session de base de données en cours, vous pouvez conserver ces paramètres d'une session à l'autre en exécutant ALTER DATABASE DATABASE_NAME SET FLAG_NAME = VALUE; pour les deux options ou en configurant les options de base de données de l'instance.

Régler manuellement les requêtes SQL

Pour régler manuellement les requêtes SQL, exécutez la fonction ai.tune_sql à l'aide d'une instruction SELECT.

  1. Connectez un client psql à une instance AlloyDB ou ouvrez AlloyDB Studio.

    Si vous avez utilisé SET pour configurer google_db_advisor.llm_model_id et google_db_advisor.enable_ai_tune_sql, exécutez ces instructions SET dans la même session de base de données avant d'appeler ai.tune_sql.

  2. Pour régler les requêtes SQL, exécutez la fonction ai.tune_sql. Cette fonction récupère le schéma lié à la requête et l'envoie, avec la requête, au LLM pour l'analyse des performances :

    SELECT ai.tune_sql(query => 'SQL_QUERY', explain_analyze => TRUE);

    Remplacez SQL_QUERY par la requête SQL que vous souhaitez analyser.

    Par défaut, le paramètre d'exécution (explain_analyze) est défini sur TRUE. Lorsqu'elle est définie sur TRUE, la fonction exécute EXPLAIN ANALYZE pour exécuter la requête et capturer le plan d'exécution. Cette approche fournit des conseils de réglage des requêtes plus précis et plus utiles. Si vous définissez explain_analyze sur FALSE, la fonction n'exécute que EXPLAIN pour générer un plan sans exécuter la requête.

Afficher les plans de requête avec des recommandations de performances

Le résultat affiche d'abord le plan de requête standard, suivi des recommandations détaillées générées par l'IA. Les recommandations incluent un résumé de la requête, des suggestions de réécriture et des recommandations pour la collecte de statistiques, l'ajustement des options GUC et le nettoyage. La fonction ai.tune_sql peut prendre deux à quatre minutes pour générer des recommandations.

L'exemple suivant montre le résultat de ai.tune_sql pour une requête d'anti-jointure :

SELECT ai.tune_sql(
  'SELECT *
   FROM store_sales
   WHERE ss_customer_sk NOT IN (
     SELECT sr_customer_sk
     FROM store_returns
     WHERE sr_item_sk = ss_item_sk)');

                     tune_sql
--------------------------------------------------
 NOTE
 --------------------------------------------------
 Advices generated with the assistance of an LLM. Independent verification of these advices is required due to the potential for inaccuracies.
 --------------------------------------------------
 ORIGINAL QUERY PLAN
 --------------------------------------------------
 Seq Scan on store_sales  (cost=0.00..111292742.52 rows=1440384 width=115) (actual time=0.092..103093.207 rows=1306312.00 loops=1)
   Filter: (NOT (ANY (ss_customer_sk = (SubPlan 1).col1)))
   Rows Removed by Filter: 1574198
   Buffers: shared hit=72987539
   SubPlan 1
     ->  Index Scan using idx_sr_item_sk on store_returns  (cost=0.42..76.74 rows=18 width=4) (actual time=0.005..0.034 rows=19.38 loops=2880510)
           Index Cond: (sr_item_sk = store_sales.ss_item_sk)
           Index Searches: 2880510
           Buffers: shared hit=72933670
 Planning Time: 0.257 ms
 --------------------------------------------------
 QUERY SUMMARY
 --------------------------------------------------
 The original query plan shows a Sequential Scan on store_sales with a correlated SubPlan for the NOT IN condition. For each of the 1.44 million rows in store_sales, it executes an Index Scan on store_returns, resulting in over 2.88 million index searches. This nested loop execution is the primary bottleneck, taking over 103 seconds. Converting the correlated NOT IN subquery to an anti-join (NOT EXISTS) will allow the optimizer to use a Hash Anti Join, processing both tables in bulk and significantly reducing execution time.
 --------------------------------------------------
 AI SUGGESTED QUERY 1
 --------------------------------------------------
 SELECT * FROM store_sales WHERE NOT EXISTS (SELECT 1 FROM store_returns WHERE store_returns.sr_item_sk = store_sales.ss_item_sk AND (store_returns.sr_customer_sk = store_sales.ss_customer_sk OR store_returns.sr_customer_sk IS NULL OR store_sales.ss_customer_sk IS NULL));
 Reason: The original query uses a NOT IN subquery which is executed as a correlated subquery (SubPlan), leading to millions of index scans. Rewriting to NOT EXISTS allows the optimizer to use a Hash Anti Join, which is much more efficient for large datasets. The OR IS NULL conditions ensure strict semantic equivalence with NOT IN when dealing with NULL values.
 --------------------------------------------------
 REWRITTEN QUERY PLAN
 --------------------------------------------------
 Gather  (cost=8541.96..139088.40 rows=154194 width=115)
   Workers Planned: 2
   ->  Parallel Hash Anti Join  (cost=7541.96..122669.00 rows=64248 width=115)
         Hash Cond: (store_sales.ss_item_sk = store_returns.sr_item_sk)
         Join Filter: ((store_returns.sr_customer_sk = store_sales.ss_customer_sk) OR (store_returns.sr_customer_sk IS NULL) OR (store_sales.ss_customer_sk IS NULL))
         ->  Parallel Seq Scan on store_sales  (cost=0.00..65872.20 rows=1200320 width=115)
         ->  Parallel Hash  (cost=6044.76..6044.76 rows=119776 width=8)
               ->  Parallel Seq Scan on store_returns  (cost=0.00..6044.76 rows=119776 width=8)
 --------------------------------------------------
 AI SUGGESTED INDEX 1
 --------------------------------------------------
 CREATE INDEX idx_sr_item_customer ON store_returns (sr_item_sk, sr_customer_sk);
 Reason: A composite index on (sr_item_sk, sr_customer_sk) allows index-only scans for the subquery or early aggregation, reducing table access and speeding up the anti-join condition evaluation.
 Note: Indexes are suggested solely based on the given query, but they don't consider the overall system workload.
           The suggested indexes improve the performance of the original query.
 --------------------------------------------------
 GUC FLAGS TUNING
 --------------------------------------------------
 SET work_mem = '64MB'; The current work_mem of 4MB is too small for a Hash Anti Join on tables with hundreds of thousands of rows. Increasing it to 64MB allows the hash table for store_returns to fit entirely in memory, preventing costly disk spills during the join.

 Execution Time: 103167.475 ms
(50 rows)

Étapes suivantes