Build a Custom AI-Powered SQL Query Optimizer with Python and GPT-4o: A Step-by-Step Developer Tutorial

Every developer hits that wall. You ship a feature, it works locally, then production starts crawling. You pop open `pg_stat_activity` and there it is—a query that’s been running for 47 seconds. You stare at the `EXPLAIN ANALYZE` output, and honestly, it might as well be in ancient Greek.

I’ve been there. Recently, while helping a client in Ho Chi Minh City optimize their PostgreSQL-backed analytics dashboard, we found a single query consuming 80% of their database CPU. The fix wasn’t obvious. But after building a custom AI-powered SQL query optimizer, we cut that query’s runtime from 12 seconds to 300 milliseconds.

I Built a Custom AI PR Reviewer with Claude API and GitHub Webhooks — Here’s the Exact Code

I Built a Custom AI PR Reviewer with Claude API and GitHub Webhooks — Here’s the Exact Code… ...

Read more

Here’s the exact tool we built, and how you can build one too.

Why Build an AI SQL Query Optimizer?

Most query optimization tools are either:

We Cut a Legacy Fintech’s Batch Processing from 4 Hours to 12 Minutes — Here’s the Exact Architecture We Used

We Cut a Legacy Fintech’s Batch Processing from 4 Hours to 12 Minutes — Here’s the Exact Architecture… ...

Read more

• Too generic (they just suggest adding an index on every column)
• Too manual (you have to parse EXPLAIN plans yourself)
• Too expensive (enterprise tools that cost thousands per month)

A custom Python tool with GPT-4o solves all three. It understands your specific schema, reads EXPLAIN plans like a senior DBA, and costs pennies per query.

The Architecture

Here’s what we’re building:

Slow Query → EXPLAIN ANALYZE → GPT-4o Analysis → Optimized Query + Schema Suggestions

The tool has three core components:

1. Query parser – extracts table names, joins, and WHERE clauses
2. EXPLAIN analyzer – feeds the raw plan to GPT-4o with a structured prompt
3. Query rewriter – generates the optimized version

Step 1: Setting Up the Environment

First, let’s get our dependencies in order.

bash
pip install openai psycopg2-binary sqlparse python-dotenv

Create a `.env` file:

OPENAI_API_KEY=sk-your-key-here
DATABASE_URL=postgresql://user:pass@localhost:5432/mydb

Now, the core configuration:

python
import os
import json
import sqlparse
import psycopg2
from openai import OpenAI
from dotenv import load_dotenv

load_dotenv()

client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))
DB_URL = os.getenv("DATABASE_URL")

Step 2: The EXPLAIN Plan Fetcher

We need to actually get the execution plan. Here’s a function that runs `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)` on any query:

python
def fetch_explain_plan(query: str) -> dict:
    """Run EXPLAIN ANALYZE and return the JSON plan."""
    conn = psycopg2.connect(DB_URL)
    try:
        with conn.cursor() as cur:
            explain_query = f"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) {query}"
            cur.execute(explain_query)
            result = cur.fetchone()
            return result[0][0]  # First row, first column (the JSON)
    except Exception as e:
        print(f"EXPLAIN failed: {e}")
        return None
    finally:
        conn.close()

Pro tip: Never run `EXPLAIN ANALYZE` on a production write query without a read replica. It actually executes the query. Use `EXPLAIN (ANALYZE, FORMAT JSON)` on a staging environment first.

Step 3: The Schema Extractor

GPT-4o needs context about your tables to give good suggestions. Let’s extract schema info:

python
def get_table_schema(table_name: str) -> str:
    """Get column names, types, and indexes for a table."""
    conn = psycopg2.connect(DB_URL)
    try:
        with conn.cursor() as cur:
            # Get columns
            cur.execute("""
                SELECT column_name, data_type, is_nullable
                FROM information_schema.columns
                WHERE table_name = %s
            """, (table_name,))
            columns = cur.fetchall()
            
            # Get indexes
            cur.execute("""
                SELECT indexname, indexdef
                FROM pg_indexes
                WHERE tablename = %s
            """, (table_name,))
            indexes = cur.fetchall()
            
            schema = f"Table: {table_name}\n"
            schema += "Columns:\n"
            for col in columns:
                schema += f"  - {col[0]} ({col[1]}, nullable={col[2]})\n"
            schema += "Indexes:\n"
            for idx in indexes:
                schema += f"  - {idx[0]}: {idx[1]}\n"
            return schema
    finally:
        conn.close()

def extract_tables_from_query(query: str) -> list:
    """Simple regex-free table extraction using sqlparse."""
    parsed = sqlparse.parse(query)[0]
    tables = []
    for token in parsed.tokens:
        if token.ttype is None and hasattr(token, 'get_real_name'):
            tables.append(token.get_real_name())
    # Fallback: extract FROM and JOIN clauses
    from_idx = query.upper().find("FROM")
    if from_idx == -1:
        return []
    # Crude but effective for most queries
    after_from = query[from_idx + 4:].strip()
    # Split on JOIN, WHERE, GROUP BY, ORDER BY, LIMIT
    import re
    parts = re.split(r'\b(JOIN|WHERE|GROUP BY|ORDER BY|LIMIT)\b', after_from, flags=re.IGNORECASE)
    table_part = parts[0].strip()
    # Handle aliases
    tables = [t.split()[0].strip('"') for t in table_part.split(',')]
    return [t for t in tables if t]

Step 4: The GPT-4o Prompt Engineering

This is where the magic happens. The prompt needs to be structured and opinionated:

python
def build_optimization_prompt(query: str, explain_plan: dict, schema_info: str) -> str:
    """Build a structured prompt for GPT-4o."""
    plan_json = json.dumps(explain_plan, indent=2)
    
    prompt = f"""You are a senior PostgreSQL DBA with 15 years of experience. Analyze this slow query and its EXPLAIN plan.

## Current Query

{query}

## EXPLAIN ANALYZE Plan

{plan_json}

## Schema Information
{schema_info}

## Your Task
Provide a JSON response with exactly these fields:
1. "issues": List of specific problems found (e.g., "Sequential scan on users table", "Missing index on orders.user_id")
2. "suggested_indexes": List of CREATE INDEX statements that would help
3. "optimized_query": The rewritten SQL query that fixes the issues
4. "estimated_improvement": Percentage estimate of performance gain
5. "explanation": Plain English explanation of what's wrong and how the fix works

Rules:
- Only suggest indexes that make sense for the query pattern
- Never suggest removing existing indexes without evidence they're unused
- If the query can be rewritten with EXISTS instead of IN, do it
- Consider adding LIMIT if the application doesn't need all rows
- Check for implicit type conversions in WHERE clauses
- Look for missing JOIN conditions that could cause cartesian products

Respond ONLY with valid JSON. No markdown, no extra text."""
    
    return prompt

Step 5: The Optimization Engine

Now let’s wire it all together:

python
def optimize_query(query: str) -> dict:
    """Main optimization function."""
    # Step 1: Get the EXPLAIN plan
    print("Fetching EXPLAIN plan...")
    plan = fetch_explain_plan(query)
    if not plan:
        return {"error": "Failed to get EXPLAIN plan"}
    
    # Step 2: Extract tables and get schema
    print("Extracting schema...")
    tables = extract_tables_from_query(query)
    schema_info = ""
    for table in tables:
        schema_info += get_table_schema(table) + "\n"
    
    # Step 3: Build prompt and call GPT-4o
    print("Analyzing with GPT-4o...")
    prompt = build_optimization_prompt(query, plan, schema_info)
    
    response = client.chat.completions.create(
        model="gpt-4o",
        messages=[
            {"role": "system", "content": "You are a PostgreSQL optimization expert. Respond only with JSON."},
            {"role": "user", "content": prompt}
        ],
        temperature=0.1,  # Low temperature for deterministic output
        max_tokens=2000
    )
    
    # Step 4: Parse the response
    try:
        result = json.loads(response.choices[0].message.content)
        return result
    except json.JSONDecodeError:
        return {"error": "Failed to parse AI response", "raw": response.choices[0].message.content}

Step 6: Putting It All Together

Here’s the CLI interface:

python
def main():
    print("=== AI SQL Query Optimizer ===\n")
    
    # Example slow query
    example_query = """
    SELECT u.name, COUNT(o.id) as order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE o.created_at > '2024-01-01'
    AND u.status = 'active'
    GROUP BY u.id, u.name
    HAVING COUNT(o.id) > 5
    ORDER BY order_count DESC
    LIMIT 100;
    """
    
    print("Enter your slow query (or press Enter to use the example):")
    user_query = input().strip()
    query = user_query if user_query else example_query
    
    print(f"\nAnalyzing query:\n{query}\n")
    
    result = optimize_query(query)
    
    if "error" in result:
        print(f"Error: {result['error']}")
        return
    
    print("\n" + "="*60)
    print("OPTIMIZATION RESULTS")
    print("="*60)
    
    print(f"\n📊 Estimated Improvement: {result.get('estimated_improvement', 'N/A')}")
    
    print("\n🔍 Issues Found:")
    for issue in result.get('issues', []):
        print(f"  - {issue}")
    
    print("\n📝 Suggested Indexes:")
    for idx in result.get('suggested_indexes', []):
        print(f"  {idx}")
    
    print("\n⚡ Optimized Query:")
    print(result.get('optimized_query', 'N/A'))
    
    print("\n💡 Explanation:")
    print(result.get('explanation', 'N/A'))

if __name__ == "__main__":
    main()

Real-World Results

We ran this tool against a production query from a fintech client in Can Tho. The original query:

sql
SELECT t.*, u.name 
FROM transactions t
JOIN users u ON t.user_id = u.id
WHERE t.amount > 1000 
  AND t.status = 'pending'
  AND u.verified = true
ORDER BY t.created_at DESC;

Before: 8.2 seconds, sequential scan on transactions (1.2M rows)

After: 120ms, index-only scan

GPT-4o suggested:

1. A composite index on `transactions(status, amount, created_at)`
2. An index on `users(verified, id)`
3. Rewriting the JOIN order to filter transactions first

Limitations to Know

This tool isn’t magic. Here’s what it struggles with:

• Correlated subqueries – GPT-4o sometimes suggests rewrites that change semantics
• Window functions – Optimization suggestions are hit-or-miss
• Very long queries (>100 lines) – Context window limits analysis depth

Always test the optimized query against your original with `EXPLAIN ANALYZE` before deploying.

Taking It Further

Want to make this production-ready? Here are three improvements:

1. Add a query cache – Store results in Redis to avoid re-analyzing the same query
2. Integrate with pg_stat_statements – Automatically pull the top 10 slowest queries
3. Add a web UI – Flask or FastAPI frontend for the non-CLI folks on your team

At ECOA AI, we’ve integrated this tool into our AI agent orchestration platform. Our Vietnamese engineering teams use it daily to optimize client databases. The combination of skilled developers and AI tools is powerful—we’re seeing 5x efficiency gains on database optimization tasks alone.

Frequently Asked Questions

Q: Will this work with MySQL or SQL Server?

A: The EXPLAIN format differs significantly between databases. You’d need to modify the `fetch_explain_plan` function and update the prompt to specify your database. The core logic remains the same.

Q: How much does it cost per query analysis?

A: With GPT-4o, each analysis costs roughly $0.01-$0.03 depending on query length and schema size. For a team running 50 analyses per day, that’s about $1.50/day.

Q: Can this suggest query rewrites that change the results?

A: Yes, it can. Always verify the optimized query returns identical results. We recommend running both queries and comparing output on a sample of data before deploying.

Q: What if GPT-4o suggests dropping an index that’s critical for another query?

A: The tool only sees one query at a time. For production use, you’d want to integrate with pg_stat_user_indexes to check index usage across all queries before dropping anything.

Related reading: Why Smart CTOs Hire Vietnamese Developers: A Data-Driven Guide to Offshore Engineering in 2025

Related reading: Vietnam Outsourcing: Why It’s the Smartest Offshore Development Move in 2025