Context & Why: The 2 AM PagerDuty Nightmare
It was 2:14 AM last Tuesday when the alert hit my phone. The production database CPU was pinned at 99%, and API response times had spiked to 12 seconds. After a quick look at the process list, I found the culprit: a massive, nested query on a 45-million-row orders table. A developer—likely me, three months ago—had pushed it without a proper index.
In a typical scenario, I would run EXPLAIN ANALYZE and stare at a 500-line JSON execution plan. I’d spend 20 minutes trying to visualize exactly where a Sequential Scan was causing the bottleneck. But when you’re sleep-deprived, human error is inevitable. I needed a way to offload this mental heavy lifting. This is where Large Language Models (LLMs) turn a tedious manual process into a streamlined workflow.
Standard optimizers in PostgreSQL or MySQL excel at choosing the best path among existing indexes. However, they rarely suggest the indexes you should have created in the first place. By feeding structured execution plans into an LLM, we can get immediate, high-context recommendations for missing indexes. I have implemented this approach in production environments. It consistently reduces query costs by 85% in minutes rather than hours.
Installation: Setting Up Your AI Database Assistant
Getting started doesn’t require a bloated enterprise toolset. We will use a lightweight Python script to bridge your database and an LLM like GPT-4o or Claude 3.5 Sonnet. These models are surprisingly adept at parsing the structured hierarchy of a JSON execution plan.
1. Environment Setup
Start by creating a virtual environment. This keeps your local setup clean and ensures you have the right versions of the OpenAI and Postgres drivers.
# Create and activate virtualenv
python3 -m venv sql-ai-env
source sql-ai-env/bin/activate
# Install dependencies
pip install openai psycopg2-binary python-dotenv2. Database Access
Your database user must have permissions to run EXPLAIN. While this guide uses PostgreSQL, the logic translates perfectly to MySQL or SQL Server. You’ll also need a standard API key from your LLM provider of choice.
Configuration: Feeding the Plan to the AI
The real breakthrough happens when you stop sending raw queries to the AI. If you simply ask, “How do I optimize this?”, you’ll get generic advice. But if you provide the Execution Plan, the AI sees the engine’s internal struggle. It identifies exactly when a Hash Join consumes too much memory or when a Parallel Seq Scan hits a massive table.
1. The Optimization Script
Build a script named optimize.py. This handles the heavy lifting of extracting the plan and formatting it for the model.
import os
import psycopg2
import json
from openai import OpenAI
from dotenv import load_dotenv
load_dotenv()
client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))
def get_execution_plan(query):
conn = psycopg2.connect(os.getenv("DATABASE_URL"))
cur = conn.cursor()
# We use FORMAT JSON to provide the AI with structured data
cur.execute(f"EXPLAIN (FORMAT JSON, ANALYZE) {query}")
plan = cur.fetchone()[0]
cur.close()
conn.close()
return plan
def get_ai_suggestion(query, plan):
prompt = f"""
You are a Senior Database Administrator.
Analyze this SQL query and its PostgreSQL Execution Plan.
Identify bottlenecks like Seq Scans or high-cost nodes.
Provide:
1. The exact CREATE INDEX command needed.
2. Specific query rewrites to improve performance.
Query:
{query}
Execution Plan:
{json.dumps(plan, indent=2)}
"""
response = client.chat.completions.create(
model="gpt-4o",
messages=[{"role": "user", "content": prompt}]
)
return response.choices[0].message.content
# Example usage
slow_query = "SELECT * FROM orders WHERE status = 'pending' AND created_at > '2023-01-01'"
plan = get_execution_plan(slow_query)
suggestion = get_ai_suggestion(slow_query, plan)
print(suggestion)2. Refining the System Prompt
High-quality results depend on specific constraints. I’ve found that instructing the AI to “prioritize high-cardinality columns” or “favor covering indexes” prevents hallucinated suggestions. Tell the model to only suggest indexes based on columns present in WHERE, JOIN, or GROUP BY clauses.
Verification & Monitoring: Validating the Results
Blindly running AI-generated SQL in production is a recipe for disaster. My workflow always includes a mandatory verification step on a staging environment that contains a representative slice of production data.
1. The “Before and After” Check
Measure the performance before you apply any changes. Record the execution time and the “Total Cost” metric from the plan. Once the index is live, run EXPLAIN ANALYZE again. You want to see the plan shift from a Seq Scan to an Index Scan. In one recent test, this reduced the node cost from 145,000 to just 120.
-- AI Suggested Index
CREATE INDEX idx_orders_status_created_at ON orders(status, created_at);
-- Verify the improvement
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending' AND created_at > '2023-01-01';2. Managing Index Bloat
Remember that indexes aren’t free. Every new index adds overhead to INSERT and UPDATE operations. After the AI helps you extinguish the immediate fire, monitor the index usage for a week. Use the following query to check if your new index is actually carrying its weight:
SELECT
relname AS table_name,
indexrelname AS index_name,
idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_orders_status_created_at';If idx_scan remains at zero after several days of traffic, drop it. The AI might have been too aggressive, or the application’s query patterns may have shifted.
3. Automating the Pipeline
Our current setup integrates this directly into the CI/CD pipeline. When a developer submits a PR containing a new complex query, a GitHub Action runs EXPLAIN on a sanitized dev database. The LLM then comments on the PR with optimization suggestions. This prevents 2 AM incidents from ever reaching production.
Shifting from manual plan analysis to AI-assisted diagnostics reduces the time-to-fix from hours to seconds. It allows engineering teams to focus on high-level architecture rather than squinting at tree diagrams of nested loops.
