Slow queries are the #1 cause of application performance issues. AI can analyze query plans, suggest indexes, and rewrite queries — but your database schema contains business logic, table names, and data patterns you might not want to share with cloud AI providers.

Quick query optimization

# Pipe a slow query + its EXPLAIN output to Ollama
psql -c "EXPLAIN ANALYZE SELECT * FROM orders JOIN users ON orders.user_id = users.id WHERE orders.created_at > '2026-01-01' AND users.country = 'US';" | \
  ollama run qwen3:8b "Analyze this PostgreSQL query plan. Identify why it's slow and suggest specific optimizations (indexes, query rewrites, etc.):"

Python script for automated analysis

import ollama
import psycopg2

def analyze_slow_queries(conn_string, min_duration_ms=1000):
    conn = psycopg2.connect(conn_string)
    cur = conn.cursor()
    
    # Get slow queries from pg_stat_statements
    cur.execute("""
        SELECT query, calls, mean_exec_time, total_exec_time
        FROM pg_stat_statements
        WHERE mean_exec_time > %s
        ORDER BY total_exec_time DESC
        LIMIT 10
    """, (min_duration_ms,))
    
    slow_queries = cur.fetchall()
    
    for query, calls, mean_time, total_time in slow_queries:
        # Get the query plan
        cur.execute(f"EXPLAIN (FORMAT TEXT) {query}")
        plan = '\n'.join(row[0] for row in cur.fetchall())
        
        response = ollama.chat(model="qwen3:8b", messages=[{
            "role": "user",
            "content": f"""Optimize this PostgreSQL query:

Query (called {calls} times, avg {mean_time:.0f}ms):
{query}

Query plan:
{plan}

Suggest:
1. Missing indexes (with CREATE INDEX statement)
2. Query rewrite if applicable
3. Whether this query pattern should be cached
4. Estimated improvement"""
        }])
        
        print(f"\n{'='*60}")
        print(f"Query: {query[:100]}...")
        print(f"Calls: {calls}, Avg: {mean_time:.0f}ms, Total: {total_time:.0f}ms")
        print(f"\nAI Analysis:\n{response['message']['content']}")
    
    conn.close()

analyze_slow_queries("postgresql://user:pass@localhost/mydb")

Index suggestion

# Get all tables and their current indexes
psql -c "\di+" | ollama run qwen3:8b "Based on these existing indexes, what common indexes might be missing? Consider: foreign keys without indexes, columns used in WHERE clauses, and composite indexes for common query patterns."

Query rewriting

# Ask AI to rewrite a slow query
ollama run qwen3.5:27b "Rewrite this SQL query for better performance. The orders table has 10M rows, users has 500K rows.

Original:
SELECT u.name, COUNT(o.id) as order_count, SUM(o.total) as revenue
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.created_at BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY u.name
HAVING COUNT(o.id) > 5
ORDER BY revenue DESC
LIMIT 100;

Provide the optimized query and explain what you changed and why."

Schema review

# Export schema (no data) and review
pg_dump --schema-only mydb | ollama run qwen3.5:27b "Review this database schema for:
1. Missing indexes on foreign keys
2. Normalization issues
3. Missing constraints
4. Data type choices that could cause performance issues
5. Tables that might benefit from partitioning"

Automate with cron

# Weekly slow query report
0 9 * * 1 python3 /opt/scripts/analyze_slow_queries.py | mail -s "Weekly Slow Query Report" team@company.com

Or integrate with n8n for Slack notifications when new slow queries appear.

Which model for SQL optimization

Task	Model	Why
Quick index suggestions	qwen3:8b	Fast, handles common patterns
Complex query rewrites	qwen3.5:27b	Better reasoning for joins and subqueries
Schema review	qwen3.5:27b	Needs to understand relationships
EXPLAIN plan analysis	qwen3:8b	Pattern matching, well-documented

Security reminder

Your database schema reveals your business model. Table names like subscriptions, invoices, user_permissions tell a lot about your application. Use local models for schema analysis, not cloud APIs.

Related: Ollama Complete Guide · PostgreSQL Cheat Sheet · AI Log Analysis with Local Models · AI for CI/CD Pipelines · Self-Hosted AI for Enterprise · Vector Databases Compared