Using AI to Tune Slow SQL Queries in AlloyDB

The table contains millions of records, and users have started reporting slow order-history pages.

Here is a sample query:

SELECT
    order_id,
    customer_id,
    order_date,
    order_status,
    total_amount
FROM orders
WHERE customer_id = 1050
  AND order_status = 'Completed'
ORDER BY order_date DESC
LIMIT 20;

The query looks straightforward, but its performance depends on several factors:

• The number of rows in the table.
• The distribution of customer IDs and order statuses.
• Available indexes.
• Table statistics.
• The execution plan selected by PostgreSQL.

Before changing anything, inspect the execution plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    order_id,
    customer_id,
    order_date,
    order_status,
    total_amount
FROM orders
WHERE customer_id = 1050
  AND order_status = 'Completed'
ORDER BY order_date DESC
LIMIT 20;

Review the plan for expensive operations, such as large sequential scans, unnecessary sorting, and significant buffer reads.

You can now submit the query to the AI tuning function:

SELECT ai.tune_sql(
    query => $$
        SELECT
            order_id,
            customer_id,
            order_date,
            order_status,
            total_amount
        FROM orders
        WHERE customer_id = 1050
          AND order_status = 'Completed'
        ORDER BY order_date DESC
        LIMIT 20
    $$,
    explain_analyze => TRUE
);

The function analyzes the query and provides performance insights and possible optimizations.

When explain_analyze is TRUE, the function uses EXPLAIN ANALYZE to execute the query and collect execution-plan information. This can provide more useful tuning insights, but it also means the query actually runs.

Only use this option for supported, read-only SELECT statements. Do not use it with statements that modify data.

If you want to inspect the estimated execution plan without executing the query, use:

SELECT ai.tune_sql(
    query => $$
        SELECT
            order_id,
            customer_id,
            order_date,
            order_status,
            total_amount
        FROM orders
        WHERE customer_id = 1050
          AND order_status = 'Completed'
        ORDER BY order_date DESC
        LIMIT 20
    $$,
    explain_analyze => FALSE
);

The second approach is useful when you want an initial plan-based analysis without running the query.

Understand the AI Recommendations

After analyzing the query, AlloyDB can provide recommendations in different categories.

For example, suppose the execution plan indicates that PostgreSQL is reading many rows before returning the requested orders.

The AI analysis might suggest reviewing the existing indexes or considering a composite index that supports the filtering and sorting conditions.

An example candidate index could be:

CREATE INDEX idx_orders_customer_status_date
ON orders (
    customer_id,
    order_status,
    order_date DESC
);

This is an illustrative index, not a guaranteed recommendation from AlloyDB.

Whether it helps depends on the actual query workload, data distribution, existing indexes, and write activity.

Similarly, the AI might recommend:

• Rewriting a query to reduce unnecessary operations.
• Collecting fresh statistics using ANALYZE.
• Reviewing indexes for frequently executed queries.
• Investigating memory or database configuration settings.
• Running maintenance operations when table statistics or dead tuples indicate a problem.

Do not apply every recommendation automatically. An index can improve read performance but increase storage requirements and the cost of INSERT, UPDATE, and DELETE operations.

Use Query Insights Alongside AI Tuning

The ai.tune_sql function is useful when you want to analyze an individual SQL statement. However, production database performance requires continuous monitoring.

AlloyDB Query Insights helps identify which queries contribute most to database load.

You can use it to:

• Identify queries with high execution latency.
• Compare query performance across different time ranges.
• Review database load and query activity.
• Investigate queries that have become slower than their normal baseline.
• Find queries that might benefit from additional indexes.

AlloyDB also provides AI-assisted troubleshooting through Gemini Cloud Assist. This feature can analyze performance anomalies and provide observations and hypotheses about possible causes.

It is useful to understand the distinction:

These capabilities complement each other. Query Insights can help you identify which query needs attention, while ai.tune_sql can help investigate how that query might be improved.

Best Practices for AI-Assisted SQL Tuning

AI can reduce the time required to investigate database performance, but it should remain part of a controlled tuning process.

Consider the following practices:

• Start with actual performance data rather than assuming a query is slow.
• Capture the existing execution plan before making changes.
• Use representative parameter values when testing query rewrites.
• Review existing indexes before creating new ones.
• Validate index recommendations against read and write workloads.
• Compare execution time, buffer usage, and database load before and after changes.
• Test recommendations in a development or staging environment.
• Monitor the query after deployment to confirm that the improvement continues.
• Avoid sending sensitive data or confidential information to an AI model without reviewing your organization's data-handling requirements.

One important limitation concerns prepared and parameterized statements. When a query contains placeholders such as $1 or $2, ai.tune_sql does not provide query rewrite recommendations. Other tuning recommendations can still be available.

If you need query rewrite suggestions, use representative literal values when submitting the query for analysis.

Also remember that AI-generated recommendations can be incorrect or unsuitable for a particular workload. AlloyDB's architecture has characteristics that differ from standard PostgreSQL deployments, so validate recommendations using actual AlloyDB metrics.

Summary

AI-powered SQL tuning in AlloyDB gives developers another way to investigate slow queries. The ai.tune_sql function analyzes SQL execution plans and provides recommendations for query rewrites, indexes, statistics, database configuration, and maintenance operations. When combined with Query Insights and the index advisor, it can help database teams identify performance problems and prioritize tuning work. However, AI should support database engineering decisions, not replace them. Always validate recommendations against real execution plans, application workloads, and production performance measurements before applying changes.