Ottimizza le query SQL utilizzando l'AI

Per ottimizzare le prestazioni delle query del database AlloyDB per PostgreSQL, puoi ottimizzare le query SQL utilizzando insight basati sull'AI. Analizzando i piani di esecuzione delle query, la funzione ai.tune_sql, basata su un modello linguistico di grandi dimensioni (LLM), identifica i colli di bottiglia complessi e fornisce suggerimenti che puoi applicare per l'ottimizzazione.

Quando richiami la funzione ai.tune_sql, questa analizza la query e fornisce Query Insights e suggerimenti sulle query nelle seguenti aree:

  • Riepilogo delle query: analizza i colli di bottiglia delle prestazioni delle query.
  • Query rewrite: suggerisce tecniche di ottimizzazione per riscrivere la query originale in una query equivalente più efficiente.
  • Suggerimenti per gli indici: suggerisce tipi di indici come indici su espressioni, indici GIN (Generalized Inverted Index) e indici ScaNN (Scalable Nearest Neighbors).
  • Suggerimenti per la raccolta delle statistiche: suggerisce la raccolta di statistiche e tabelle ANALYZE su più colonne in base al piano di esecuzione delle query.
  • Ottimizzazione dei flag GUC (Grand Unified Configuration): consiglia nuovi valori GUC analizzando il piano di esecuzione e le informazioni di sistema, come l'utilizzo e le dimensioni della memoria e del buffer.
  • Analisi e vacuum: consiglia di eseguire ANALYZE e VACUUM sulle tabelle in base alla cronologia di analisi e vacuum e ai conteggi delle tuple attive e inattive.

Limitazioni

L'ottimizzazione delle query SQL presenta le seguenti limitazioni:

  • Istruzioni preparate e parametrizzate: per le istruzioni preparate e parametrizzate, ai.tune_sql non fornisce suggerimenti per la riscrittura delle query, anche se fornisce comunque altri suggerimenti per l'ottimizzazione. Per ricevere suggerimenti per la riscrittura delle query per una query che contiene segnaposto dei parametri, ad esempio $1 o $2 copiati da Query Insights o pg_stat_statements, sostituisci i segnaposto con valori letterali rappresentativi prima di eseguire ai.tune_sql.
  • Solo istruzioni SELECT di sola lettura quando explain_analyze è TRUE: sono supportate solo singole istruzioni SELECT di sola lettura quando explain_analyze è impostato su TRUE. L'esecuzione di ai.tune_sql con explain_analyze impostato su TRUE in altre istruzioni, come INSERT, UPDATE, DELETE, SELECT INTO o SELECT FOR UPDATE, restituisce un messaggio di errore.

Prima di iniziare

Verifica di disporre di quanto segue:

Abilita l'ottimizzazione SQL

Registrare un LLM e abilitare i flag di database richiesti.

  1. Crea un endpoint del modello Gemini per la funzione ai.tune_sql registrando un endpoint del modello globale o un endpoint del modello regionale nella regione dell'istanza:

    • Per registrare un endpoint del modello globale quando il modello non è disponibile nella regione dell'istanza, ad esempio gemini-3.5-flash su locations/global, esegui la seguente istruzione:

      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');

      Sostituisci PROJECT_ID con l'ID del progetto in cui è disponibile il modello.

    • Per registrare un endpoint del modello regionale nella regione dell'istanza, ad esempio gemini-2.5-pro su locations/REGION_ID, esegui la seguente istruzione:

      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');

      Sostituisci quanto segue:

      • REGION_ID: la regione dell'istanza AlloyDB, ad esempio us-central1.
      • PROJECT_ID: l'ID del progetto in cui è disponibile il modello.

  2. Imposta l'ID modello per i suggerimenti di ottimizzazione delle query SQL nella sessione del database corrente. Specifica il model_id che hai registrato nel passaggio precedente, ad esempio 'gemini-3.5-flash-global' o 'gemini-2.5-pro':

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

  3. Attiva l'ottimizzazione delle query SQL per la sessione del database corrente impostando il flag google_db_advisor.enable_ai_tune_sql:

    SET google_db_advisor.enable_ai_tune_sql = on;

    Poiché SET si applica solo alla sessione di database corrente, puoi rendere persistenti queste impostazioni tra le sessioni eseguendo ALTER DATABASE DATABASE_NAME SET FLAG_NAME = VALUE; per entrambi i flag o configurando i flag di database dell'istanza.

Ottimizza manualmente le query SQL

Per ottimizzare manualmente le query SQL, esegui la funzione ai.tune_sql utilizzando un'istruzione SELECT.

  1. Connetti un client psql a un'istanza AlloyDB o apri AlloyDB Studio.

    Se hai utilizzato SET per configurare google_db_advisor.llm_model_id e google_db_advisor.enable_ai_tune_sql, esegui queste istruzioni SET nella stessa sessione del database prima di chiamare ai.tune_sql.

  2. Per ottimizzare le query SQL, esegui la funzione ai.tune_sql. Questa funzione recupera lo schema relativo alla query e lo invia, insieme alla query, all'LLM per l'analisi delle prestazioni:

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

    Sostituisci SQL_QUERY con la query SQL che vuoi analizzare.

    Per impostazione predefinita, il parametro di esecuzione (explain_analyze) è impostato su TRUE. Se impostata su TRUE, la funzione esegue EXPLAIN ANALYZE per eseguire la query e acquisire il piano di esecuzione. Questo approccio fornisce suggerimenti per l'ottimizzazione delle query più accurati e utili. Se imposti explain_analyze su FALSE, la funzione esegue solo EXPLAIN per generare un piano senza eseguire la query.

Visualizza i piani di query con i suggerimenti per il rendimento

L'output mostra prima il piano di query standard, seguito dai consigli dettagliati generati dall'AI. I suggerimenti includono un riepilogo delle query, riscritture suggerite e suggerimenti per la raccolta di statistiche, l'ottimizzazione dei flag GUC e il vacuuming. La funzione ai.tune_sql può richiedere da due a quattro minuti per generare i suggerimenti.

L'esempio seguente mostra l'output ai.tune_sql per una query anti-join:

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)

Passaggi successivi