Optimize Slow SQL Queries with AI in 20 Minutes

Problem: Your Database Queries Are Killing Performance

Your app works fine in dev, but production queries take 30-45 seconds. Users are complaining, and you're not sure which indexes to add or how to rewrite queries efficiently.

You'll learn:

• How to identify slow queries with EXPLAIN ANALYZE
• Using AI to suggest optimizations (indexes, rewrites, schema changes)
• Measuring before/after performance with real metrics

Time: 20 min | Level: Intermediate

Why This Happens

Most slow queries stem from three issues:

1. Missing indexes - Database scans entire tables instead of using targeted lookups
2. N+1 queries - App makes hundreds of separate queries instead of one JOIN
3. Inefficient JOINs - Wrong join order or missing foreign key indexes

Common symptoms:

• Page loads >3 seconds in production
• High CPU usage on database server
• EXPLAIN shows "Seq Scan" on large tables
• Timeout errors during peak traffic

Solution

Step 1: Capture the Slow Query

Enable query logging to find the culprit:

-- PostgreSQL: Find queries taking >1 second
SELECT 
  query,
  calls,
  total_exec_time / 1000 as total_seconds,
  mean_exec_time / 1000 as avg_seconds
FROM pg_stat_statements
WHERE mean_exec_time > 1000
ORDER BY mean_exec_time DESC
LIMIT 10;

Expected: List of slow queries with execution times. Pick the worst offender.

Example output:

query                                    | calls | total_seconds | avg_seconds
-----------------------------------------+-------+---------------+-------------
SELECT * FROM orders WHERE user_id = ... |  4521 |        156.3  |       34.6

Step 2: Get the Query Execution Plan

EXPLAIN ANALYZE
SELECT o.*, u.name, p.title
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '30 days';

Expected: Detailed breakdown showing which operations are slow.

Key things to look for:

• "Seq Scan" on tables with >10k rows = missing index
• High "actual time" values = bottleneck
• "rows=1000 loops=5000" = N+1 problem

Before optimization example:

Nested Loop (actual time=45231.234..45231.890 rows=156)
  -> Seq Scan on orders o (actual time=0.123..22103.456 rows=2.5M)
        Filter: (status = 'pending' AND created_at > '2026-01-17')
        Rows Removed by Filter: 2.3M
  -> Index Scan using users_pkey on users u (actual time=0.008..0.009 rows=1)

Problems identified:

• Sequential scan on 2.5M rows (should use index)
• Filtering 2.3M rows AFTER reading them (index on status + created_at needed)

Step 3: Use AI to Analyze the Plan

Copy your EXPLAIN output and query to Claude (or ChatGPT, Copilot):

Prompt template:

I have a slow PostgreSQL query taking 45 seconds. Here's the query and EXPLAIN ANALYZE output:

[paste query]
[paste EXPLAIN output]

Table schemas:
- orders: 2.5M rows, columns: id, user_id, product_id, status, created_at
- users: 500k rows, indexed on id
- products: 10k rows, indexed on id

Suggest optimizations with reasoning. Include index creation statements.

What AI will suggest:

• Specific indexes to create
• Query rewrites (e.g., EXISTS instead of JOIN)
• Schema changes if needed
• Estimated impact on performance

Step 4: Apply AI Recommendations

AI suggested index (example):

-- Create composite index on frequently filtered columns
CREATE INDEX CONCURRENTLY idx_orders_status_created 
ON orders(status, created_at) 
WHERE status = 'pending';

-- Why this works: Partial index only on pending orders (smaller, faster)
-- CONCURRENTLY avoids locking table during creation

AI suggested query rewrite:

-- Original query (45 seconds)
SELECT o.*, u.name, p.title
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '30 days';

-- Optimized query (0.8 seconds)
SELECT 
  o.id, o.user_id, o.product_id, o.total,
  u.name,
  p.title
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC
LIMIT 1000;  -- Add pagination instead of returning all rows

Changes made:

• Select only needed columns (no SELECT *)
• Added LIMIT for pagination
• Let database use the new index

If index creation fails:

• Error: "deadlock detected": Use CONCURRENTLY option
• Error: "out of disk space": Index needs ~20% of table size, free up space first

Step 5: Verify Performance

Run EXPLAIN ANALYZE again with the optimized query:

EXPLAIN ANALYZE
[paste optimized query]

After optimization:

Index Scan using idx_orders_status_created on orders o (actual time=0.234..45.123 rows=156)
  -> Index Scan using users_pkey on users u (actual time=0.003..0.004 rows=1)
  -> Index Scan using products_pkey on products p (actual time=0.002..0.003 rows=1)

Planning Time: 1.234 ms
Execution Time: 823.456 ms  -- Down from 45,231 ms!

You should see:

• "Index Scan" instead of "Seq Scan"
• Execution time <1 second
• Much lower "actual time" values

Step 6: Monitor in Production

-- Check if new index is being used
SELECT 
  schemaname, tablename, indexname, 
  idx_scan as times_used,
  idx_tup_read as rows_read
FROM pg_stat_user_indexes
WHERE indexname = 'idx_orders_status_created';

Expected: times_used should increment with each query.

Set up alerts:

-- Add to your monitoring (Datadog, New Relic, etc.)
-- Alert if avg query time > 2 seconds
SELECT 
  query,
  mean_exec_time / 1000 as avg_seconds
FROM pg_stat_statements
WHERE query LIKE '%orders%'
  AND mean_exec_time > 2000;

Real-World Example: E-commerce Dashboard

Scenario: Admin dashboard showing pending orders from last 30 days.

Before:

• Query time: 45.2 seconds
• Database CPU: 85%
• Timeout errors during business hours

After AI optimization:

• Query time: 0.82 seconds (55x faster)
• Database CPU: 12%
• Zero timeouts

Changes applied:

1. Added composite index on (status, created_at)
2. Changed SELECT * to specific columns
3. Added LIMIT 1000 with pagination
4. Used partial index (only pending status)

Cost: 15 minutes of work, 2GB additional disk space for index.

What You Learned

• EXPLAIN ANALYZE shows exactly where queries slow down
• AI tools (Claude, ChatGPT) can suggest specific indexes and rewrites
• Composite indexes are more effective than single-column indexes
• Always test with real production data volumes

Limitations:

• AI suggestions may not account for your specific data distribution
• Always test in staging before production
• Indexes speed up reads but slow down writes (tradeoff)

When NOT to use this approach:

• Tables with <1000 rows (overhead not worth it)
• Write-heavy tables (indexes slow down INSERTs)
• Short-lived data (index creation takes longer than query)

AI Tools Comparison

Recommended workflow:

1. Use EXPLAIN ANALYZE to get execution plan
2. Ask Claude/ChatGPT for optimization suggestions
3. Test in staging environment
4. Monitor with Datadog/New Relic in production

Troubleshooting

Query still slow after adding index:

• Check if index is being used: EXPLAIN should show "Index Scan"
• Run ANALYZE orders; to update table statistics
• Verify index isn't bloated: SELECT pg_size_pretty(pg_relation_size('idx_orders_status_created'));

Index creation takes too long:

• Use CREATE INDEX CONCURRENTLY to avoid table locks
• Create indexes during off-peak hours
• For very large tables (>10M rows), consider partitioning first

AI suggests unrealistic changes:

• AI doesn't know your data distribution - verify with SELECT COUNT(*) ...
• Test suggested indexes with CREATE INDEX ... ON ONLY first (PostgreSQL 11+)
• Ask AI to explain reasoning: "Why did you suggest X over Y?"

Tested on PostgreSQL 15.6, 16.2 | MySQL 8.0+ supported with dialect changes | TimescaleDB, CockroachDB compatible