Checking an AI SQL Recommendation Before Applying It to Production

AI can help developers identify slow SQL queries, suggest better query structures, and recommend indexes. However, receiving an optimization recommendation does not mean it is ready for production.

A query that runs faster in a test environment might return different results, consume more memory, or affect other queries when deployed to a busy database.

This is particularly important when using AI-powered SQL tuning in AlloyDB for PostgreSQL. Its ai.tune_sql function analyzes query execution plans and generates recommendations for query rewrites, indexes, statistics, database configuration, and maintenance operations.

The real responsibility starts after the recommendation is generated.

In this article, we will build a practical validation workflow for AI-generated SQL recommendations. We will compare query results, inspect execution plans, evaluate index changes, and establish a safe process before deploying any optimization to production.

Why AI SQL Recommendations Need Validation

Consider an e-commerce application that retrieves customer orders from a database containing millions of records.

A query that normally takes 800 milliseconds suddenly takes several seconds. The development team submits it to AlloyDB's AI tuning function and receives a recommendation to rewrite the query and create a composite index.

The suggested query looks cleaner and its estimated execution plan appears better.

Should the team apply it immediately?

Not necessarily.

An AI-generated recommendation can have several problems:

It might change the original query's behavior for NULL values.
It might improve one query but slow down other queries.
It might increase memory consumption.
It might introduce an expensive index that affects write operations.
It might perform well with test data but poorly with production data.
It might recommend configuration changes that are unsuitable for concurrent workloads.

The objective is not simply to make one SQL statement faster. It is to improve application performance without introducing correctness or operational problems.

Understand What AlloyDB's AI Tuning Function Provides

AlloyDB provides the ai.tune_sql function for AI-assisted query optimization.

It uses query information and execution plans to generate recommendations.

The output can include:

Recommendation	What you should validate
Query rewrite	Whether the rewritten query returns identical results
Index recommendation	Whether it improves the overall workload
Statistics collection	Whether the statistics are outdated or insufficient
GUC configuration	Whether the setting is appropriate under concurrent load
ANALYZE or VACUUM	Whether database maintenance is actually required

One important detail is that ai.tune_sql can execute a query when explain_analyze is enabled. This is the default behavior.

When enabled, AlloyDB uses EXPLAIN ANALYZE to collect actual execution information. With explain_analyze => FALSE, it uses EXPLAIN without executing the query.

The runtime analysis is more informative, but it should be used carefully, especially against production databases.

Establish a Baseline Before Changing Anything

Before applying an AI recommendation, capture the original query's behavior and performance.

Let's use the following example:

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

Run the original query with execution-plan analysis:

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

Capture the following information:

Execution time.
Planning time.
Rows returned.
Rows removed by filters.
Shared buffer hits and reads.
Sequential scans and index scans.
Sorting operations.
Temporary disk usage, if reported.

For example, an execution plan might show a sequential scan across a large portion of the orders table, followed by a sorting operation.

That gives you a measurable baseline.

Do not rely only on execution time. A query can become faster in one test while consuming significantly more memory or reading more data.

For a reliable comparison, execute the same query several times under comparable conditions and account for cache effects and concurrent database activity.

Validate Query Rewrites for Correctness

One of the most important checks is ensuring that the rewritten query returns exactly the same results as the original.

Consider a query that uses NOT IN to find customers who have not placed certain orders.

SELECT customer_id
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
);

An AI recommendation might suggest replacing NOT IN with NOT EXISTS.

However, these expressions can behave differently when NULL values are present.

For example, if the subquery returns a NULL value, NOT IN can evaluate to UNKNOWN for comparisons that would otherwise appear to match. NOT EXISTS follows different NULL semantics.

A rewrite that ignores this difference can return incorrect results.

Compare the original and rewritten results

PostgreSQL provides EXCEPT ALL, which is useful for comparing query results while preserving duplicate counts.

You can test both directions:

WITH original_result AS (
    SELECT customer_id
    FROM customers
    WHERE customer_id NOT IN (
        SELECT customer_id
        FROM orders
    )
),
rewritten_result AS (
    SELECT c.customer_id
    FROM customers c
    WHERE NOT EXISTS (
        SELECT 1
        FROM orders o
        WHERE o.customer_id = c.customer_id
    )
)
SELECT *
FROM original_result

EXCEPT ALL

SELECT *
FROM rewritten_result;

Then reverse the comparison:

WITH original_result AS (
    SELECT customer_id
    FROM customers
    WHERE customer_id NOT IN (
        SELECT customer_id
        FROM orders
    )
),
rewritten_result AS (
    SELECT c.customer_id
    FROM customers c
    WHERE NOT EXISTS (
        SELECT 1
        FROM orders o
        WHERE o.customer_id = c.customer_id
    )
)
SELECT *
FROM rewritten_result

EXCEPT ALL

SELECT *
FROM original_result;

Both queries should return zero rows if the results are equivalent for the tested database state.

For a complete test, include records with NULL values, duplicate values, empty result sets, and boundary conditions.

Also remember that these comparisons do not prove equivalence for every possible database state. Use representative test data and application-level tests before approving a rewrite.

Evaluate Index Recommendations Carefully

AI tuning can recommend indexes based on the submitted query.

For example, an optimization might suggest:

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

This index could help a query that filters by customer and status and sorts by order date.

However, creating an index has costs.

Every additional index requires storage and can increase the work performed during INSERT, UPDATE, and DELETE operations.

Before creating the index, check the existing indexes:

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'orders';

Then investigate:

Whether an equivalent or overlapping index already exists.
Whether the index columns match the actual filtering and sorting patterns.
Whether the query is frequent enough to justify the index.
Whether the index increases write latency.
Whether the index consumes an acceptable amount of storage.

Where possible, test the candidate index in a staging environment with production-like data.

AlloyDB's AI tuning functionality uses the hypopg extension to evaluate index recommendations. This helps assess potential indexes without immediately creating physical indexes. However, hypothetical index analysis is not a substitute for testing the actual index under a representative workload.

Test Configuration Recommendations Under Load

Sometimes an AI recommendation involves PostgreSQL configuration parameters rather than SQL changes.

For example, the tuning output might recommend increasing work_mem to improve a hash join or sorting operation.

You might receive a recommendation similar to:

SET work_mem = '64MB';

It can be tempting to apply this setting globally.

However, work_mem is not a single memory allocation shared by the entire database. It applies to individual query operations, and a query can use multiple operations that require memory.

Under concurrent workloads, increasing it too much can result in substantial memory consumption.

A safer approach is to test the setting in a controlled session:

BEGIN;

SET LOCAL work_mem = '64MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    customer_id,
    SUM(total_amount)
FROM orders
GROUP BY customer_id;

ROLLBACK;

SET LOCAL limits the setting to the current transaction.

Compare the execution plan and memory-related behavior with the original configuration. Also test concurrent queries before considering a broader configuration change.

Never assume that a configuration recommendation based on one query is appropriate for the entire database.

Create a Controlled Validation Workflow

A practical production workflow should separate recommendation generation, testing, approval, and deployment.

The following process works well for database teams.

Development environment

Start by submitting the slow query to ai.tune_sql.

Review the generated recommendations and identify which changes are relevant.

For example, separate the suggestions into:

Query rewrite.
Index creation.
Statistics collection.
Database configuration.
Maintenance operations.

Do not combine every recommendation into one change.

Staging environment

Use a staging environment with representative data and database settings.

Test the original and optimized queries under comparable conditions.

For query rewrites, verify result equivalence.

For index changes, measure both read and write performance.

For configuration changes, test under realistic concurrency.

For maintenance recommendations, inspect table statistics and database health before executing the suggested operation.

Production deployment

Once the change passes validation, deploy it through your normal database change-management process.

For index creation, consider the operational impact of the chosen creation method. PostgreSQL supports CREATE INDEX CONCURRENTLY for building an index without blocking ordinary writes in the same way as a standard index build, although it has its own operational considerations and cannot run inside a transaction block.

After deployment, monitor query latency, database load, and application behavior.

Keep the original query or configuration available as a rollback reference.

Build a SQL Recommendation Review Checklist

Before approving an AI-generated SQL recommendation, use a consistent review process.

Validation area	Review question
Query correctness	Does the optimized query return equivalent results?
NULL handling	Have NULL values and edge cases been tested?
Execution plan	Does the new plan reduce the actual bottleneck?
Execution time	Is the improvement repeatable?
Buffer usage	Does the query read fewer database pages?
Index impact	Does the index help the wider workload?
Memory usage	Can the configuration handle concurrent queries?
Application tests	Do existing application tests still pass?
Rollback	Can the change be safely reversed?

This checklist also helps reviewers understand why a particular recommendation was accepted or rejected.

Common Mistakes to Avoid

Trusting estimated execution costs alone

Estimated costs help compare plans, but they are not actual execution times. Validate with representative runtime measurements where safe.

Running EXPLAIN ANALYZE on production without considering impact

EXPLAIN ANALYZE executes the query. Even a read-only query can consume substantial CPU, memory, and I/O resources.

Applying every recommendation together

If you change the query, indexes, and configuration simultaneously, it becomes difficult to identify which change improved or degraded performance.

Ignoring the complete workload

An index that helps one SELECT statement might slow down write-heavy operations. Evaluate the broader workload before approving it.

Skipping rollback planning

Every production database change should have a clear rollback strategy. Do not treat AI-generated recommendations differently from manually proposed changes.

Summary

AI-powered SQL tuning in AlloyDB can help database developers investigate performance problems and identify useful optimization opportunities. However, an AI recommendation should be treated as a candidate change, not a production-ready solution. Establish a baseline, verify query correctness, compare execution plans, test indexes against the wider workload, and evaluate configuration changes under realistic concurrency. A controlled development, staging, and production workflow helps teams benefit from AI-assisted tuning while protecting database reliability and application behavior.