Ajustar consultas SQL usando IA

Para otimizar o desempenho de consultas do banco de dados do AlloyDB para PostgreSQL, ajuste suas consultas SQL usando insights baseados em IA. Ao analisar os planos de execução de consultas, a função ai.tune_sql, que usa um modelo de linguagem grande (LLM), identifica gargalos complexos e fornece recomendações que você pode usar para otimização.

Quando você invoca a função ai.tune_sql, ela analisa sua consulta e fornece Query Insights e recomendações nas seguintes áreas:

  • Resumo da consulta: analisa gargalos de desempenho da consulta.
  • Reescrever consulta: sugere técnicas de otimização para reescrever a consulta original em uma consulta equivalente e mais eficiente.
  • Recomendações de índice: recomenda tipos de índice, como índices em expressões, índices de índice invertido generalizado (GIN, na sigla em inglês) e índices de vizinhos mais próximos escalonáveis (ScaNN, na sigla em inglês).
  • Recomendações de coleta de estatísticas: recomenda a coleta de estatísticas e ANALYZE de tabelas em várias colunas com base no plano de execução de consultas.
  • Ajuste de flags da configuração unificada (GUC, na sigla em inglês): recomenda novos valores de GUC analisando o plano de execução e as informações do sistema, como o uso e o tamanho da memória e do buffer.
  • Analisar e limpar: recomenda executar ANALYZE e VACUUM em tabelas com base no histórico de análise e limpeza e nas contagens de tuplas ativas e inativas.

Limitações

O ajuste de consulta SQL tem as seguintes limitações:

  • Instruções preparadas e parametrizadas: para instruções preparadas e parametrizadas, o ai.tune_sql não fornece recomendações de reescrita de consulta, embora ainda forneça outras recomendações de ajuste. Para receber recomendações de reescrita de consulta para uma consulta que contém marcadores de posição de parâmetro, como $1 ou $2 copiados do Query Insights ou pg_stat_statements, substitua os marcadores de posição por valores literais representativos antes de executar ai.tune_sql.
  • Instruções SELECT somente leitura apenas quando explain_analyze for TRUE: somente instruções SELECT únicas e somente leitura são compatíveis quando explain_analyze está definido como TRUE. A execução de ai.tune_sql com explain_analyze definido como TRUE em outras instruções, como INSERT, UPDATE, DELETE, SELECT INTO ou SELECT FOR UPDATE, retorna uma mensagem de erro.

Antes de começar

Verifique se você tem o seguinte:

Ativar o ajuste de SQL

Registrar um LLM e ativar as flags de banco de dados necessárias.

  1. Crie um endpoint de modelo do Gemini para a função ai.tune_sql registrando um endpoint de modelo global ou regional na região da instância:

    • Para registrar um endpoint de modelo global quando o modelo não estiver disponível na região da instância, por exemplo, gemini-3.5-flash em locations/global, execute a seguinte instrução:

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

      Substitua PROJECT_ID pelo ID do projeto em que o modelo está disponível.

    • Para registrar um endpoint de modelo regional na região da instância, por exemplo, gemini-2.5-pro em locations/REGION_ID, execute a seguinte instrução:

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

      Substitua:

      • REGION_ID: a região da instância do AlloyDB, por exemplo, us-central1.
      • PROJECT_ID: o ID do projeto em que o modelo está disponível.

  2. Defina o ID do modelo para recomendações de ajuste de consulta SQL na sessão atual do banco de dados. Especifique o model_id que você registrou na etapa anterior, como 'gemini-3.5-flash-global' ou 'gemini-2.5-pro':

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

  3. Ative o ajuste de consulta SQL para a sessão atual do banco de dados definindo a flag google_db_advisor.enable_ai_tune_sql:

    SET google_db_advisor.enable_ai_tune_sql = on;

    Como SET se aplica apenas à sessão atual do banco de dados, é possível persistir essas configurações em todas as sessões executando ALTER DATABASE DATABASE_NAME SET FLAG_NAME = VALUE; para as duas flags ou configurando as flags de banco de dados da instância.

Ajustar consultas SQL manualmente

Para ajustar manualmente as consultas SQL, execute a função ai.tune_sql usando uma instrução SELECT.

  1. Conecte um cliente psql a uma instância do AlloyDB ou abra o AlloyDB Studio.

    Se você usou SET para configurar google_db_advisor.llm_model_id e google_db_advisor.enable_ai_tune_sql, execute essas instruções SET na mesma sessão do banco de dados antes de chamar ai.tune_sql.

  2. Para ajustar as consultas SQL, execute a função ai.tune_sql. Essa função recupera o esquema relacionado à consulta e o envia, junto com a consulta, para o LLM para análise de desempenho:

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

    Substitua SQL_QUERY pela consulta SQL que você quer analisar.

    Por padrão, o parâmetro de execução (explain_analyze) é definido como TRUE. Quando definido como TRUE, a função executa EXPLAIN ANALYZE para executar a consulta e capturar o plano de execução. Essa abordagem oferece conselhos de ajuste de consulta mais precisos e úteis. Se você definir explain_analyze como FALSE, a função executará apenas EXPLAIN para gerar um plano sem executar a consulta.

Ver planos de consulta com recomendações de desempenho

A saída mostra primeiro o plano de consulta padrão, seguido pelas recomendações detalhadas geradas com IA. As recomendações incluem um resumo da consulta, sugestões de reescrita e recomendações para coleta de estatísticas, ajuste de flag GUC e vacuuming. A função ai.tune_sql pode levar de dois a quatro minutos para gerar recomendações.

O exemplo a seguir mostra a saída ai.tune_sql de uma consulta 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)

A seguir