When the marketing team at a mid-sized marketplace complained that their daily revenue report took 40 seconds to load, nobody expected a solo analyst to fix it with a handful of well-crafted AI prompts. But that's exactly what happened. In this case study, I'll walk you through the problem, the solution, and the results — and share the exact prompt collection that turned a sluggish PostgreSQL database into a responsive analytics engine. You'll learn how to use AI to generate optimization suggestions, interpret EXPLAIN ANALYZE output, design indexes, and rewrite queries — all without deep PostgreSQL expertise.
The Problem: 40 Seconds of Pain
The marketplace had grown to 2 million orders and 500,000 users. The daily revenue report — a simple aggregation of orders by day, category, and region — was taking 40 seconds. The dashboard would time out, analysts would refresh repeatedly, and trust in the data eroded. The solo analyst (let's call her Maya) inherited this mess. She knew basic SQL but wasn't a database tuning expert. Her toolkit: PostgreSQL 15, a read replica, and a growing sense of dread.
She started by running the query manually and capturing the execution plan. The culprit was clear: sequential scans on the orders table, a nested loop join with the order_items table, and a sort that spilled to disk. The query was doing far more work than necessary.
The Solution: AI-Assisted Query Optimization
Maya didn't have weeks to master PostgreSQL internals. Instead, she used a series of AI prompts to guide her through the optimization process. Each prompt was designed to extract specific, actionable advice — from rewriting the query to creating the right indexes. Below is the prompt collection she used, organized by task. Each prompt includes the exact wording, an example of its use, and the outcome.
1. Understanding the Execution Plan
Task: Decode EXPLAIN ANALYZE output.
Prompt:
"""
Here is the EXPLAIN ANALYZE output for a slow query. Explain in plain English what the bottleneck is, which operations are most expensive, and suggest three concrete optimizations. Output format: a table with columns: Operation, Cost, Rows, Issue, Suggestion.
[Paste EXPLAIN ANALYZE output]
"""
Example Result:
The AI identified a sequential scan on orders (cost 0.00..45000.00, rows 2,000,000) as the main issue, followed by a hash join with order_items that spilled to disk. It suggested adding an index on orders(created_at) and rewriting the join to use a subquery.
2. Rewriting the Query
Task: Transform a slow query into a faster one.
Prompt:
"""
Rewrite the following SQL query to improve performance. Assume PostgreSQL 15. Focus on reducing sequential scans and avoiding unnecessary sorts. Provide the optimized query and explain the changes.
[Paste original query]
"""
Example Result:
Original query used a correlated subquery to calculate total revenue per order. The AI replaced it with a JOIN and aggregation, reducing execution time from 40s to 12s before indexing.
3. Index Design
Task: Create indexes that actually help.
Prompt:
"""
Given this query and table schema, suggest the best indexes. For each index, provide the CREATE INDEX statement and explain why it helps. Consider composite indexes and partial indexes.
[Paste query and schema]
"""
Example Result:
The AI recommended a composite index on orders(created_at, status) and a partial index on order_items(order_id) WHERE quantity > 0. After creating these, the query dropped to 2.5 seconds.
4. Detecting Missing Indexes
Task: Find indexes that should exist but don't.
Prompt:
"""
Analyze the following PostgreSQL statistics (pg_stat_user_tables, pg_stat_user_indexes) and identify tables with high sequential scan counts and low index usage. Suggest missing indexes.
[Paste statistics]
"""
Example Result:
The AI flagged the orders table with 1.2M sequential scans and only 5% index usage. It suggested an index on (status, created_at) which later reduced scans by 80%.
5. Optimizing JOINs
Task: Choose the right join strategy.
Prompt:
"""
This query uses a nested loop join between orders and order_items. Would a hash join or merge join be faster? Explain the trade-offs and provide the rewritten query with hints if necessary.
[Paste query and EXPLAIN output]
"""
Example Result:
The AI explained that for large tables, a hash join is often better when there's no index on the join key. It suggested creating an index on order_items(order_id) and letting the planner choose. Execution time improved by 60%.
6. Partitioning Large Tables
Task: Decide if partitioning helps.
Prompt:
"""
The orders table has 2 million rows and grows by 10,000 daily. Would partitioning by range on created_at improve query performance for reports that filter by date? Provide a step-by-step plan and sample DDL.
"""
Example Result:
The AI recommended monthly partitions and provided CREATE TABLE ... PARTITION BY RANGE (created_at) syntax. After partitioning, date-range queries scanned only relevant partitions, cutting time by 70%.
7. Using CTEs vs Subqueries
Task: Avoid performance pitfalls with CTEs.
Prompt:
"""
Compare the performance of a CTE versus a subquery for this aggregation. In PostgreSQL 15, CTEs are inlined by default unless MATERIALIZED is specified. Show both versions and explain which is faster.
[Paste query]
"""
Example Result:
The AI demonstrated that the CTE version was inlined and performed similarly to the subquery, but adding MATERIALIZED caused a slowdown. Maya kept the subquery.
8. Analyzing Query Plans with Visual Tools
Task: Use explain.depesz.com or PEV.
Prompt:
"""
I've uploaded my EXPLAIN ANALYZE output to explain.depesz.com. Here is the link. What are the top three issues and how can I fix them?
[Link]
"""
Example Result:
The AI parsed the visual plan and pointed out a costly sort operation that could be eliminated by adding an index on the ORDER BY column.
9. Monitoring and Alerting
Task: Set up proactive monitoring.
Prompt:
"""
Write a SQL query that identifies the top 5 slowest queries from pg_stat_statements. Include total time, calls, and mean time. Also suggest a cron job to run this daily.
"""
Example Result:
The AI provided a query using pg_stat_statements and a sample crontab entry. Maya set up a daily email alert for queries exceeding 5 seconds.
10. Caching with Materialized Views
Task: Reduce load for repetitive reports.
Prompt:
"""
This report runs every hour and takes 20 seconds. Would a materialized view help? Show how to create one and refresh it concurrently. Discuss trade-offs.
[Paste query]
"""
Example Result:
The AI suggested a materialized view refreshed every 15 minutes, with a unique index to allow CONCURRENTLY refresh. Report time dropped to 0.5 seconds.
The Results
After applying the optimizations, the daily revenue report ran in under 1 second. The dashboard loaded instantly, and analysts could slice data by any dimension without delay. Maya documented the process and shared the prompt collection with her team. The database CPU usage dropped by 40%, and the read replica could handle more concurrent users.
Key Takeaways
- AI as a copilot: Even without deep PostgreSQL expertise, you can leverage AI to interpret execution plans, suggest indexes, and rewrite queries.
- Iterative optimization: Start with the biggest bottleneck (usually sequential scans), then refine.
- Measure everything: Always use EXPLAIN ANALYZE before and after changes.
- Share the knowledge: Document your prompts and results so others can replicate.
If you're facing similar performance issues, try these prompts. They're not magic — but they can turn a 40-second query into a sub-second one, just like they did for Maya. For more AI-powered database tips, explore our blog at asibiont.com/blog.
Comments