Ajusta las consultas en SQL con IA

Vista previa

Esta función está sujeta a las "Condiciones de la oferta previa a la DG" en la sección Condiciones Generales del Servicio de las Condiciones Específicas del Servicio. Las funciones de la fase previa a la DG están disponibles “sin modificaciones” y pueden tener asistencia limitada. Para obtener más información, consulta las descripciones de la etapa de lanzamiento.

Para optimizar el rendimiento de las consultas de la base de datos de AlloyDB para PostgreSQL, puedes ajustar tus consultas en SQL con estadísticas basadas en IA. Cuando analiza tus planes de ejecución de consultas, la función ai.tune_sql, con la tecnología de un modelo de lenguaje grande (LLM), identifica cuellos de botella complejos y proporciona recomendaciones que puedes seguir para la optimización.

Nota: La función ai.tune_sql está disponible a partir de la versión 1.6.2 de la extensión google_ml_integration (versión preliminar).

Cuando invocas la función ai.tune_sql, esta analiza tu consulta y proporciona estadísticas de consultas y recomendaciones en las siguientes áreas:

• Resumen de consultas: Analiza los cuellos de botella en el rendimiento de las consultas.
• Reescritura de consultas: Sugiere técnicas de optimización para reescribir la consulta original en una consulta equivalente y más eficiente.
• Recomendaciones de índices: Recomienda tipos de índices, como índices en expresiones, índices de índice invertido generalizado (GIN) y de vecinos más cercanos escalables (ScaNN).
• Recomendaciones de recopilación de estadísticas: Recomienda la recopilación de estadísticas y ANALYZE de tablas en varias columnas según el plan de ejecución de la consulta.
• Ajuste de marcas de configuración unificada general (GUC): Recomienda nuevos valores de GUC analizando el plan de ejecución y la información del sistema, como el uso y el tamaño de la memoria y el búfer.
• Analizar y limpiar: Recomienda ejecutar ANALYZE y VACUUM en las tablas según el historial de análisis y limpieza, y los recuentos de tuplas activas y muertas.

Limitaciones

El ajuste de consulta en SQL tiene las siguientes limitaciones:

• Sentencias preparadas y parametrizadas: Para las sentencias preparadas y parametrizadas, ai.tune_sql no proporciona recomendaciones de reescritura de consultas, aunque sí proporciona otras recomendaciones de ajuste. Para recibir recomendaciones de reescritura de consultas para una consulta que contiene marcadores de posición de parámetros, como $1 o $2 copiados de Estadísticas de consultas o pg_stat_statements, reemplaza los marcadores de posición por valores literales representativos antes de ejecutar ai.tune_sql.
• Sentencias SELECT de solo lectura solo cuando explain_analyze es TRUE: solo se admiten sentencias SELECT únicas de solo lectura cuando explain_analyze se establece en TRUE. Si ejecutas ai.tune_sql con explain_analyze establecido en TRUE en otras declaraciones, como INSERT, UPDATE, DELETE, SELECT INTO o SELECT FOR UPDATE, se muestra un mensaje de error.

Antes de comenzar

Verifica que tengas lo siguiente:

• Una instancia de AlloyDB que ejecute PostgreSQL 18 o una versión posterior. Para obtener más información, consulta Crea un clúster y su instancia principal.
• Un cliente de psql conectado a tu instancia o acceso a AlloyDB Studio.
• Se habilitó la integración de Vertex AI.
• La versión 1.6.2 o posterior de la extensión google_ml_integration instalada, y las extensiones google_db_advisor y hypopg habilitadas. Para habilitar la extensión hypopg, que ai.tune_sql usa para evaluar y generar recomendaciones de índices, ejecuta CREATE EXTENSION IF NOT EXISTS hypopg;.

Habilita el ajuste de SQL

Registrar un LLM y habilitar las marcas de base de datos requeridas

1. Crea un extremo de modelo de Gemini para la función ai.tune_sql registrando un extremo de modelo global o un extremo de modelo regional en la región de tu instancia:

   • Para registrar un extremo de modelo global cuando el modelo no está disponible en la región de tu instancia, por ejemplo, gemini-3.5-flash en locations/global, ejecuta la siguiente instrucción:

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

     Reemplaza PROJECT_ID por el ID del proyecto en el que está disponible el modelo.

   • Para registrar un extremo de modelo regional en la región de tu instancia, por ejemplo, gemini-2.5-pro en locations/REGION_ID, ejecuta la siguiente instrucción:

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

     Reemplaza lo siguiente:

     • REGION_ID: Es la región de tu instancia de AlloyDB, por ejemplo, us-central1.
     • PROJECT_ID: Es el ID del proyecto en el que está disponible el modelo.

2. Establece el ID del modelo para las recomendaciones de ajuste de consulta en SQL en tu sesión de base de datos actual. Especifica el model_id que registraste en el paso anterior, como 'gemini-3.5-flash-global' o 'gemini-2.5-pro':

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

3. Activa el ajuste de consulta en SQL para tu sesión de base de datos actual configurando la marca google_db_advisor.enable_ai_tune_sql:

   SET google_db_advisor.enable_ai_tune_sql = on;
   

   Debido a que SET solo se aplica a la sesión de base de datos actual, puedes conservar esta configuración en todas las sesiones ejecutando ALTER DATABASE DATABASE_NAME SET FLAG_NAME = VALUE; para ambas marcas o configurando las marcas de base de datos de la instancia.

Ajusta manualmente las consultas en SQL

Para ajustar de forma manual las consultas en SQL, ejecuta la función ai.tune_sql con una sentencia SELECT.

1. Conecta un cliente de psql a una instancia de AlloyDB o abre AlloyDB Studio.

   Si usaste SET para configurar google_db_advisor.llm_model_id y google_db_advisor.enable_ai_tune_sql, ejecuta esas sentencias SET en la misma sesión de base de datos antes de llamar a ai.tune_sql.

2. Para ajustar las consultas en SQL, ejecuta la función ai.tune_sql. Esta función recupera el esquema relacionado con la consulta y lo envía, junto con la consulta, al LLM para el análisis de rendimiento:

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

   Reemplaza SQL_QUERY por la consulta en SQL que deseas analizar.

   De forma predeterminada, el parámetro de ejecución (explain_analyze) se establece en TRUE. Cuando se establece en TRUE, la función ejecuta EXPLAIN ANALYZE para ejecutar la consulta y capturar el plan de ejecución. Este enfoque proporciona consejos de ajuste de consultas más precisos y útiles. Si configuras explain_analyze como FALSE, la función solo ejecuta EXPLAIN para generar un plan sin ejecutar la consulta.

Visualiza planes de consulta con recomendaciones de rendimiento

En el resultado, primero se muestra el plan de consulta estándar y, luego, las recomendaciones detalladas generadas por IA. Las recomendaciones incluyen un resumen de la consulta, reescrituras sugeridas y recomendaciones para la recopilación de estadísticas, el ajuste de marcas de GUC y la limpieza. La función ai.tune_sql puede tardar entre dos y cuatro minutos en generar recomendaciones.

En el siguiente ejemplo, se muestra el resultado de ai.tune_sql para una consulta de 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)

¿Qué sigue?

• Obtén más información sobre las estadísticas de consultas.
• Aplica las recomendaciones de índices.