How to Rewrite Queries with AI

It’s the most common story in the world of software development. A developer writes SQL queries. On their development machine, with a thousand-row database, it returns the result in 50 milliseconds. It works perfectly. The code passes tests, is approved in the code review, and goes to production. Three months later, with the table now containing 50 million rows, that same query becomes a monster. It consumes 90% of the database server’s CPU, causes cascading locks, and degrades the performance of the entire application.

The witch hunt begins. The on-call SRE identifies the offending query and throws it to the DBA, who, in turn, returns it to the development team with a single, dreaded word: “optimize.” The problem is that the difference between SQL that works and SQL that performs is an abyss of deep technical knowledge about indexes, statistics, and the internal workings of the query optimizer.

Teaching every developer to be an SQL optimization master is an unrealistic dream. Hiring an army of DBAs to review every line of code is financially unfeasible. So, how do you scale performance expertise? The answer lies in Artificial Intelligence. AI-powered query rewriting isn’t about using a chatbot to fix a syntax error. It’s about having a virtual senior DBA, powered by Machine Learning, that analyzes the execution plan of each query, understands the intent behind the code, and automatically suggests or rewrites a version that is orders of magnitude more efficient.

This article explores how observability platforms like dbsnOOp are transforming query optimization from a manual and reactive art into an automated and predictive science that integrates directly into your DevOps workflow.

The Anatomy of Slow Queries: Why Functional SQL Isn’t Always Performant

To understand how AI can rewrite a query, we first need to understand why a “functional” query can be slow. The answer almost always lies in the work of the Cost-Based Optimizer (CBO), the brain of any modern database (whether it’s PostgreSQL, SQL Server, Oracle, etc.). The CBO analyzes your query and tries to predict the cheapest way (in terms of CPU and I/O resources) to get the data. To do this, it critically depends on statistics about your data. If these statistics are outdated or if the query is written in a way that confuses the optimizer, it will make bad decisions.

Common Anti-Patterns that AI Detects and Fixes

There are several SQL writing “anti-patterns” that are functional but prevent the optimizer from using indexes effectively. AI is trained to identify and correct these patterns.

• Functions in WHERE Clauses (Non-SARGable Predicates): One of the most common mistakes. When you apply a function to a column in the WHERE clause, you generally prevent the database from using an index on that column.
• Unnecessary SELECT *: While convenient, SELECT * forces the database to read all columns of the table, even if the application only needs two of them. This increases I/O and prevents the use of covering indexes.
• Implicit JOINs and Correlated Subqueries: Using the old JOIN syntax (FROM tableA, tableB WHERE tableA.id = tableB.id) can be less clear and, in some optimizers, less efficient. Subqueries that are executed once for each row of the outer result are performance killers.
• Incorrect Use of OR or IN: A long list of ORs can sometimes be less efficient than a UNION ALL or a query restructuring.

AI as Your Personal SQL Master: The Role of dbsnOOp

A platform like dbsnOOp doesn’t just look at the text of your query. It dives into the execution plan to understand how the database is interpreting your code. It’s this deep analysis that allows for intelligent rewriting.

Beyond Syntax: Predictive Execution Plan Analysis

The dbsnOOp AI Copilot operates in a continuous optimization cycle:

• Capture: It ingests queries from the slow query log, Performance Schema (MySQL/MariaDB), Query Store (SQL Server), pg_stat_statements (PostgreSQL), or similar sources.
• Analysis: The AI analyzes the execution plan of each query. It doesn’t just see text; it sees operations like Table Scan, Index Scan, Hash Join, Nested Loop, etc. It compares the estimated cost from the optimizer with the actual execution cost.
• Anti-Pattern Identification: The AI identifies the anti-patterns mentioned above and other inefficiencies, such as implicit type conversions that prevent the use of indexes.
• Recommendation and Rewriting: Based on the analysis, the Copilot does two things:
  • Recommends structural changes: “This query would be 95% faster if you created this covering index.”
  • Rewrites the query: “The way you used the DATE() function in the WHERE clause is causing a Full Table Scan. Here is a rewritten version of the query that uses a date range and will allow the use of the index, resulting in an estimated 100x better performance.”

Practical performance Examples: From Slow Query to AI-Optimized Version

Let’s see in practice how AI can transform problematic queries.

Case 1: Eliminating Functions in the WHERE Clause

A developer needs to find all orders made on a specific day. The order_date column is of type DATETIME.

Original Query (Slow):

-- This query looks harmless, but the DATE() function on the order_date column
-- prevents the use of a standard B-Tree index on that field.
SELECT * FROM orders WHERE DATE(order_date) = '2025-09-10';

The execution plan for this query will result in a Full Table Scan.

Rewriting Suggested by dbsnOOp’s AI:

The AI understands that the intent is to find all orders within a 24-hour range. It rewrites the query to use a SARGable (Search ARGument Able) predicate.

-- This version allows the optimizer to use an index on the order_date column,
-- transforming a Table Scan into an Index Range Scan, which is much faster.
SELECT * FROM orders WHERE order_date >= '2025-09-10 00:00:00' AND order_date < '2025-09-11 00:00:00';

dbsnOOp not only suggests the rewrite but also explains the why behind the change, educating the developer in the process.

Case 2: The Magic of the Covering Index

A query needs to get only the title and author of published blog posts, ordered by date.

Original Query:

-- The query only needs 2 columns, but SELECT * forces the reading of all of them.
SELECT * FROM blog_posts WHERE status = 'published' ORDER BY publish_date DESC;

Even with an index on (status, publish_date), the database still needs to perform a lookup on the main table to get all the other columns (like post_content, author_bio, etc.), which generates unnecessary I/O.

Rewriting and Recommendation from dbsnOOp’s AI:

The AI detects that only a subset of columns is truly needed and that a covering index would be ideal.

Step 1 (Query Rewrite):

-- The AI first suggests limiting the columns to what is actually needed.
SELECT title, author_name FROM blog_posts WHERE status = 'published' ORDER BY publish_date DESC;

Step 2 (Index Recommendation):

“To further optimize this query, create the following covering index. This will allow the query to be answered entirely from the index, without touching the main table (Index-Only Scan).”

-- The recommended covering index.
CREATE INDEX idx_pub_posts_cover ON blog_posts (status, publish_date) INCLUDE (title, author_name);
-- The INCLUDE syntax varies between databases (in MySQL/MariaDB, the index would be on (status, publish_date, title, author_name)).

Integrating Query Rewriting into the DevOps Workflow (Shift-Left)

The biggest advantage of AI is its ability to scale. dbsnOOp can be integrated directly into your CI/CD pipeline. When a developer submits a pull request containing new SQL queries, the pipeline can trigger an automatic analysis by dbsnOOp.

The AI acts as an expert database performance code reviewer. It can flag a problematic query and suggest the rewrite before the code is merged, moving performance optimization to the left in the development lifecycle (“Shift-Left”). This prevents slow queries from reaching production, instead of fixing them after the fact.

The art of rewriting queries for maximum performance no longer needs to be the exclusive domain of a small group of specialists. Artificial Intelligence is democratizing this expertise, transforming it into an automated, predictive, and scalable service. By incorporating a platform like dbsnOOp into your workflows, you are not just fixing slow queries; you are building an engineering culture where performance is a shared and automated responsibility.

Ready to solve this challenge intelligently? Schedule a meeting with our specialist or watch a practical demonstration!

Schedule a demo here.

Learn more about dbsnOOp!

Learn about database monitoring with advanced tools here.

Visit our YouTube channel to learn about the platform and watch tutorials.

Recommended Reading

• AI Autonomous DBA: Automated RCA for Database Performance, Observability, and Security: Understand how the same AI that analyzes execution plans to rewrite queries also performs root cause analysis for the most complex performance problems in your database.
• The Future of the DBA: Why the Role Will Change (But Not Disappear): Discover how the automation of query optimization with AI is evolving the role of the DBA, allowing them to focus on data architecture instead of reactive manual tuning.
• Text-to-SQL in Practice: How dbsnOOp Democratizes the Operation of Complex Databases: See how the ability to generate and optimize queries complements each other, allowing technical teams to not only create but also validate the performance of their queries quickly and intuitively.