The Query Whisperer: How AI Prompts Turn Raw SQL into Production-Ready Code
Picture this: it's 5:45 PM on a Friday, and a stakeholder needs a report on customer churn with a 12-month rolling average, cohort retention, and a comparison against last year's seasonal peaks—all in a single query. Your hands hover over the keyboard, and you know the SQL will be a monster. This is where most developers start typing, but the smart ones open a chat with an AI assistant and start prompting.
AI has quietly become the pair programmer every database developer wished for. It doesn't just write SELECT statements; it explains execution plans, suggests index strategies, and even refactors bloated queries. But here's the catch: the AI is only as good as your prompt. A vague request yields a generic query; a precise, context-rich prompt yields production-grade SQL that even your DBA would approve.
This article is your practical playbook. I've spent years writing queries for PostgreSQL (and watching others struggle), and I've distilled that experience into 10 battle-tested prompts. Each one is ready to copy-paste, with a real-world example and the reasoning behind it. By the end, you'll have a toolkit that turns your AI chat into a SQL optimization engine—and you'll never fear a complex reporting request again.
1. The "Explain Like I'm a DBA" Query Breakdown
What it does: Takes an existing SQL query and explains it line-by-line, including what each part does, potential pitfalls, and performance implications.
Why it works: Most developers know what their query does, but they don't see the hidden costs—like implicit type conversions or non-sargable WHERE clauses. This prompt forces the AI to think like a database administrator.
Prompt:
You are a senior PostgreSQL DBA. Analyze this SQL query and provide:
1. A line-by-line explanation of what each clause does.
2. Potential performance issues (e.g., full table scans, missing indexes, implicit casts).
3. Rewritten version with best practices applied (use CTEs if helpful, avoid SELECT *).
4. Estimated cost breakdown (use EXPLAIN ANALYZE format).
Query:
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5;
Example in action: I once used this on a 200-line monster query that joined 6 tables. The AI pointed out a missing composite index on (user_id, status) and a redundant GROUP BY because u.id was already unique. The rewritten version ran 3x faster.
2. The Index Advisor: Let AI Suggest Your Next Index
What it does: Analyzes a slow query and recommends specific indexes, including composite indexes with correct column order.
Why it works: Index tuning is as much art as science. The AI knows that for a query with WHERE a = 1 AND b > 2, a composite index on (a, b) beats two separate indexes.
Prompt:
You are a PostgreSQL performance expert. For the following query, recommend indexes that would improve performance. For each index, provide:
- The exact CREATE INDEX statement.
- Why this index helps (which WHERE/GROUP BY/ORDER BY clauses it accelerates).
- Any trade-offs (e.g., write overhead).
Query:
SELECT product_id, SUM(quantity) AS total_sold
FROM sales
WHERE sale_date BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY product_id
ORDER BY total_sold DESC;
Example in action: For a sales analytics dashboard, the AI suggested a partial index ON sales (product_id) WHERE sale_date >= '2026-01-01' because the query only looks at recent data. This reduced the index size by 40% and sped up the query from 800ms to 120ms.
3. The Query Rewriter: From 5-Second to 50ms
What it does: Takes a slow query and rewrites it using advanced techniques like window functions, LATERAL joins, or recursive CTEs, with a clear explanation of the changes.
Why it works: Sometimes the fastest way is to rethink the logic. AI can pivot from a correlated subquery to a window function, which is often more efficient.
Prompt:
You are a SQL optimizer. Rewrite this query to be as efficient as possible in PostgreSQL. Explain each change you make and why it's faster. If you use a window function or a LATERAL join, explain how it works.
Query:
SELECT u.id, u.name,
(SELECT MAX(o.amount) FROM orders o WHERE o.user_id = u.id) AS max_order
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100);
Example in action: I had a query that took 4 seconds due to a correlated subquery. The AI rewrote it using a LATERAL join and a single pass over orders. The new version ran in 150ms—a 26x improvement.
4. The EXPLAIN ANALYZE Decoder
What it does: Interprets the output of EXPLAIN ANALYZE and translates the planner's jargon into actionable insights.
Why it works: EXPLAIN output is cryptic for many devs. This prompt turns it into plain English, highlighting bottlenecks like seq scans, hash joins, and buffer usage.
Prompt:
Here is the EXPLAIN ANALYZE output from a PostgreSQL query. Explain it in simple terms:
1. What is the overall cost and runtime?
2. Identify the most expensive node(s).
3. Are there any sequential scans on large tables? If so, suggest an index.
4. Is the plan using the most efficient join strategy?
5. Give a step-by-step plan to optimize.
EXPLAIN ANALYZE output:
Seq Scan on users (cost=0.00..1500.00 rows=10000 width=8) (actual time=0.015..20.123 rows=10000 loops=1)
Filter: (created_at > '2025-01-01'::date)
Example in action: After running this prompt, I learned that a sequential scan on a 10M-row table was the culprit. The AI suggested a BRIN index on created_at, which cut query time from 5s to 30ms.
5. The CTE vs. Subquery Showdown
What it does: Evaluates whether to use a CTE or a subquery for a given logic, and generates the better version with reasoning.
Why it works: CTEs are cleaner but can be optimization fences in PostgreSQL (before v12). The AI knows when a CTE is materialized and when it's inlined, and can choose accordingly.
Prompt:
You are a PostgreSQL expert. I need to write a query that finds the top 3 products by revenue for each category. Compare using a CTE vs. a subquery with ROW_NUMBER(). Provide both versions and explain which one is more efficient in PostgreSQL 16, considering the planner's ability to inline CTEs.
Schema: products(id, category_id, price), order_items(product_id, quantity, unit_price)
Example in action: For an e-commerce report, the AI produced both versions. It noted that in PostgreSQL 16, CTEs are inlined, so the difference is negligible, but the subquery version with ROW_NUMBER() was more readable and just as fast. I used that.
6. The Data Type Detective: Fixing Casts and Collations
What it does: Identifies implicit type conversion issues in queries and proposes explicit casts or schema changes.
Why it works: Implicit casts often render indexes useless. The AI spots them and suggests fixes.
Prompt:
You are a PostgreSQL data modeling expert. Given this query, identify any implicit type casts or collation mismatches that could slow it down. Provide a corrected version with explicit casts, and if needed, recommend schema changes (e.g., changing a column type).
Query:
SELECT * FROM events
WHERE event_time::date = '2026-08-26'
AND user_id = 12345;
Example in action: The AI pointed out that event_time::date prevents index usage on event_time. It recommended using event_time >= '2026-08-26' AND event_time < '2026-08-27', which allowed a btree index to be used. Query time dropped from 1.2s to 50ms.
7. The Window Function Wizard
What it does: Transforms complex self-joins or correlated subqueries into efficient window functions, with a clear explanation of the logic.
Why it works: Window functions are the Swiss Army knife of SQL. This prompt teaches you to use them for moving averages, running totals, and ranking.
Prompt:
You are a SQL teacher. I have a table of daily sales (sale_date, amount). I need a query that calculates a 7-day moving average of sales. Write the SQL using a window function, and explain how the frame clause works. Also, show how to handle edge cases (e.g., first 6 days with fewer than 7 data points).
Example in action: I used this to build a revenue dashboard. The AI generated a query with AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), which was both efficient and easy to understand.
8. The Recursive CTE Explorer
What it does: Generates recursive queries for hierarchical data (e.g., org charts, category trees) and explains the recursion logic.
Why it works: Recursive CTEs are powerful but tricky. The AI creates a working query and demystifies the anchor and recursive parts.
Prompt:
You are a PostgreSQL expert. I have a table categories(id, parent_id, name). Write a recursive CTE to get all descendant categories of a given category (id = 10). Include the depth level and a path column. Explain how the recursive part works and how to avoid infinite loops.
Example in action: For a product catalog, I needed a full category tree. The AI generated a query with depth and path, and it worked flawlessly. It also added a cycle detection clause to prevent infinite loops—a detail I would have missed.
9. The Performance Tuning Checklist
What it does: Generates a comprehensive checklist of things to review when a query is slow, including configuration parameters, hardware, and query design.
Why it works: Sometimes the problem isn't the query but the server settings. This prompt helps you cover all bases.
Prompt:
You are a PostgreSQL performance consultant. Create a checklist for diagnosing a slow query. Include:
- Query-level checks (EXPLAIN, indexes, joins).
- Configuration parameters (shared_buffers, work_mem, effective_cache_size).
- Hardware considerations (disk type, RAM).
- Monitoring queries to identify bottlenecks.
For each item, provide a brief explanation and a command to check it.
Example in action: After running a similar checklist, I discovered that work_mem was too low, causing disk sorts. Increasing it to 64MB solved the issue without any query changes.
10. The Schema Designer: From Requirements to Tables
What it does: Takes a natural-language description of a data model and generates a PostgreSQL schema with tables, constraints, and indexes.
Why it works: This is a huge time-saver. Instead of writing CREATE TABLE statements from scratch, you describe the entities and relationships, and the AI produces a solid foundation.
Prompt:
You are a database architect. Design a PostgreSQL schema for a simple e-commerce platform. Include tables for: users, products, orders, order_items. Add appropriate data types, primary keys, foreign keys, check constraints (e.g., positive quantities), and indexes for common queries (e.g., orders by user, products by category). Provide the full SQL script and a brief explanation of your choices.
Example in action: I used this to prototype a new feature. The AI generated a schema with UUID primary keys, CHECK (quantity > 0), and indexes on orders.user_id and products.category_id. It even added a trigger to update product stock—a nice touch.
Beyond the Prompts: Making AI Your SQL Wingman
These prompts aren't magic spells; they're starting points. The real power comes from iterating: run the AI's output, test it, and refine. Ask follow-up questions like "What if I add a filter on this column?" or "How would this perform on 100M rows?" The AI is your tireless pair programmer, always ready to explain, optimize, and generate.
Start with one prompt today. Take a slow query you've been dreading, run it through the Index Advisor, and watch the execution time drop. You'll not only save hours but also learn something new about PostgreSQL's planner. The future of database work isn't writing SQL—it's directing AI to write it well. And with this playbook, you're already ahead of the curve.
Comments