From Chaos to Clarity: 15 SQL Prompts to Optimize Queries and Design Bulletproof Databases
If you’ve ever watched a query crawl through millions of rows, you know the feeling: the database is fast, but your SQL is slow. The problem isn’t the hardware — it’s the approach. Whether you're a data analyst wrestling with a 5-second query or a developer designing a schema for a new feature, the right prompt can turn an AI assistant into a senior database engineer. This isn't about generating code — it's about generating correct code, with execution plans, indexing strategies, and schema designs that scale.
In this guide, I’ve collected 15 battle-tested prompts that cover the full lifecycle: from profiling and rewriting slow queries to designing normalized schemas and automating migration checks. Each prompt is copy-paste ready, includes a real-world example, and explains why it works. No fluff — just actionable prompts that save you hours.
1. The Query Profiler: Understand Before You Optimize
Task: Analyze a slow query and identify bottlenecks.
Prompt:
I have a SQL query that runs in 12 seconds on a table with 5 million rows.
[INSERT QUERY]
Using PostgreSQL, explain the execution plan step-by-step.
Point out which operations cause the most I/O (Seq Scan, Hash Join, Sort, etc.).
Suggest 3 specific optimizations (index, query rewrite, or schema change) with SQL examples.
Why it works: It forces the AI to ground its advice in the actual execution plan, not generic tips. You get actionable steps, not “add an index.”
Example: For a query joining orders and customers on customer_id, the AI might spot a Sequential Scan on orders and recommend a composite index on (customer_id, order_date).
2. The Index Architect: Design Indexes Like a Pro
Task: Generate a comprehensive indexing strategy for a given workload.
Prompt:
Given the following PostgreSQL table schema and the 5 most frequent queries below, design an indexing strategy:
- Table: [CREATE TABLE statement]
- Queries: [list of queries]
For each index, specify: column(s), type (B-tree, GIN, etc.), and the exact CREATE INDEX statement.
Explain how each index speeds up the query and any trade-offs (write overhead, storage).
Why it works: It forces the AI to think about the whole workload, not just one query. You get a balanced strategy.
Example: For a table with a tags JSONB column and a query filtering by tag, the AI might suggest a GIN index on tags.
3. The Query Rewriter: From Procedural to Declarative
Task: Rewrite a slow query using modern SQL features (window functions, CTEs, LATERAL joins).
Prompt:
Rewrite this query to be more efficient and readable:
[INSERT QUERY]
Use PostgreSQL features like CTEs, window functions, or LATERAL joins if applicable.
Explain how the rewritten version reduces scans or joins.
Show a before/after comparison with EXPLAIN ANALYZE output (estimated).
Why it works: Many slow queries are written in a procedural style. This prompt taps into the AI’s knowledge of declarative SQL patterns.
Example: A correlated subquery for “top 3 orders per customer” can be rewritten with ROW_NUMBER() OVER (PARTITION BY customer_id ...).
4. The EXPLAIN ANALYZE Translator: Decode the Jargon
Task: Translate a raw EXPLAIN ANALYZE output into plain English.
Prompt:
Here is the EXPLAIN ANALYZE output for a query:
[INSERT OUTPUT]
Explain in plain English what each node does (Seq Scan, Hash Join, etc.).
Identify the most expensive operation and suggest a fix.
Use a table to summarize: operation
| cost | rows | actual time | bottleneck?
Why it works: It demystifies the plan, making it accessible to developers who don't read execution plans daily.
Example: The AI breaks down a Hash Join, explaining that the hash table is built on the smaller table, and suggests increasing work_mem if the hash spills to disk.
5. The Schema Designer: Normalize Without Losing Performance
Task: Design a normalized schema for a given set of entities.
Prompt:
Design a PostgreSQL schema for a simple e-commerce system with entities: Product, Category, Order, Customer.
Requirements:
- Use 3NF (Third Normal Form) where possible.
- Include primary keys, foreign keys, and appropriate constraints.
- For each table, provide a CREATE TABLE statement.
- Explain the relationships (1-to-many, many-to-many) and why denormalization might be needed for reporting.
Why it works: It balances theory (normalization) with practice (denormalization for performance), which is exactly what real-world schemas need.
Example: The AI produces tables for customers, orders, order_items, products, and categories, with a junction table for product-category relationships.
6. The Data Types Guru: Choose the Right Type for Every Column
Task: Recommend optimal PostgreSQL data types for a set of fields.
Prompt:
I'm designing a table to store user activity logs. Suggest the most efficient data types for these columns:
- user_id (integer, up to 1 billion)
- event_time (timestamp with time zone)
- event_type (string, 10 possible values)
- metadata (JSON, variable structure)
- ip_address (IPv4/IPv6)
For each, explain why you chose that type (size, performance, constraints) and show the CREATE TABLE statement.
Why it works: Data type choices have a huge impact on storage and speed. This prompt gets expert advice.
Example: The AI recommends integer for user_id, timestamptz for event_time (to handle time zones), varchar(20) for event_type (with a CHECK constraint), jsonb for metadata (for indexing), and inet for ip_address.
7. The Partitioning Planner: Scale Your Tables
Task: Design a partitioning strategy for a large table.
Prompt:
I have a table `events` that grows by 10 million rows per month. I need to keep data for 2 years.
Design a partitioning strategy using PostgreSQL declarative partitioning.
- Choose a partition key (e.g., by month on event_date).
- Provide the CREATE TABLE statements for the partitioned table and a few partitions.
- Explain how to query specific partitions and how to detach old partitions.
- Discuss indexing strategies within partitions.
Why it works: Partitioning is complex, but this prompt gives you a complete, copy-paste solution.
Example: The AI creates a events table partitioned by RANGE on event_date, with monthly partitions like events_2026_08.
8. The Query Reviewer: Catch Anti-Patterns
Task: Review a batch of queries for common performance anti-patterns.
Prompt:
Review the following SQL queries for anti-patterns:
[INSERT QUERIES]
Look for: SELECT *, N+1 queries, missing LIMIT, implicit type conversion, non-sargable predicates.
For each anti-pattern, explain why it's bad and provide a corrected version.
Why it works: It’s like a code review for SQL. The AI catches issues you might miss.
Example: It flags WHERE YEAR(order_date) = 2026 as non-sargable and suggests WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'.
9. The Migration Safe-Keeper: Generate Rollback Scripts
Task: Generate a migration script and its rollback for a schema change.
Prompt:
Write a PostgreSQL migration script to add a `discount` column to the `products` table and populate it based on a category.
Also write a rollback script.
Follow best practices: use transactions, add comments, and make the script idempotent where possible.
Why it works: It ensures you have a safe, reversible migration — critical in production.
Example: The AI produces a DO $$ ... $$ block that checks if the column exists, adds it, updates it, and then a rollback that drops it.
10. The Performance Benchmarker: Compare Alternatives
Task: Compare two query variants and recommend the faster one.
Prompt:
I have two queries that do the same thing. Compare their performance:
Query A: [INSERT]
Query B: [INSERT]
Use PostgreSQL's EXPLAIN ANALYZE to estimate costs. Which is faster and why?
Consider indexes, joins, and subquery elimination.
Why it works: It helps you make data-driven decisions, not guesses.
Example: The AI shows that Query B uses an index-only scan, making it 10x faster.
11. The Deadlock Detective: Find Lock Contention
Task: Identify potential deadlocks and lock contention in a set of transactions.
Prompt:
Here are two transactions that occasionally deadlock:
[INSERT TRANSACTIONS]
Analyze the lock ordering. Suggest a fix (e.g., consistent ordering, explicit locking).
Show the corrected transaction code.
Why it works: Deadlocks are subtle. This prompt gets a systematic analysis.
Example: The AI notices that Transaction A locks orders then customers, while Transaction B does the opposite, and suggests standardizing the order.
12. The CTE Master: Write Readable, Efficient Queries
Task: Rewrite a complex nested query using CTEs for clarity and performance.
Prompt:
Rewrite this nested query using CTEs (WITH clauses):
[INSERT QUERY]
Make it more readable without sacrificing performance.
If possible, show how to materialize a CTE (with MATERIALIZED) to improve performance.
Why it works: CTEs can be a performance trap (if inlined), but the AI knows when to use MATERIALIZED.
Example: The AI breaks a triple-nested subquery into three CTEs and adds MATERIALIZED to the heaviest one.
13. The JSONB Navigator: Optimize JSON Queries
Task: Optimize a query on a JSONB column.
Prompt:
I have a table with a JSONB column `data` and I run this query:
[INSERT QUERY]
How can I optimize it? Consider:
- GIN indexes
- jsonb_path_ops
- Extracting fields into generated columns
Show the index creation and the rewritten query.
Why it works: JSONB queries are notoriously slow if not indexed properly. This prompt gets expert advice.
Example: The AI suggests a GIN index with jsonb_path_ops and shows how to use @> operator effectively.
14. The Data Cleaner: Write Idempotent Cleanup Scripts
Task: Write a script to clean up orphaned rows.
Prompt:
Write a PostgreSQL script to delete orphaned rows from `order_items` that have no corresponding `orders` row.
Make it idempotent (safe to run multiple times). Include a SELECT to show the count before deletion.
Why it works: It gives you a safe, repeatable cleanup routine.
Example: The AI uses NOT EXISTS and wraps it in a transaction with a SELECT count(*) first.
15. The PostgreSQL Tuning Advisor: Adjust Config for Workload
Task: Get recommendations for PostgreSQL configuration parameters.
Prompt:
Based on the following workload (OLTP, 100 concurrent connections, 16GB RAM), recommend PostgreSQL settings:
- shared_buffers
- work_mem
- effective_cache_size
- max_connections
- maintenance_work_mem
Provide the exact values and explain why. Also mention any relevant `EXPLAIN` implications.
Why it works: Tuning is tricky, but the AI gives you a solid starting point based on known best practices.
Example: The AI recommends shared_buffers = 4GB (25% of RAM) and work_mem = 64MB (for complex sorts).
The Bottom Line
These 15 prompts aren't magic — they're a lens for seeing your database through an expert's eyes. The key is to iterate: run the prompt, apply the suggestion, measure with EXPLAIN ANALYZE, and refine. Over time, you'll internalize these patterns, and your SQL will get faster, your schemas cleaner, and your nights shorter.
Ready to turn chaos into clarity? Copy the prompt that matches your current pain point, paste it into your favorite AI assistant, and watch the performance drop. And if you want to learn more about using AI for database work, check out Asibiont's blog for more guides.
Note: All prompts assume PostgreSQL. For other databases, adjust the syntax.
Comments