Every developer hits the wall: a query that worked fine in development crawls in production. You've added indexes, rewritten joins, and still the database groans. The bottleneck is often not the database itself, but the way we think about it. AI can help you think better. This isn't about magic; it's about using precise prompts to get precise, actionable advice from AI models. Below are 15 prompts I've refined over months of real-world performance tuning. They're not theoretical. Each one has saved me hours of manual investigation and, in several cases, prevented a late-night incident.
The Anatomy of a Slow Query
Before we dive in, let's set the stage. A slow query is rarely just a slow SELECT. It's a symptom of a deeper issue: missing indexes, poor schema design, or even a suboptimal execution plan chosen by the optimizer. The prompts below are designed to help you systematically address each layer. They work with both PostgreSQL and MongoDB, though I'll point out specific syntax and tools for each. The key is to provide the model with enough context—your actual schema and query—so it can give you specific, actionable advice.
1. The Index Advisor (PostgreSQL)
This prompt asks the AI to act as a database index advisor. It should analyze your table structure and query patterns to suggest indexes, but, crucially, it should also warn against over-indexing.
You are a PostgreSQL performance expert. Here is a table schema and a list of slow queries. For each query, propose a specific index (with the exact CREATE INDEX statement) that would speed it up. Also, for each proposed index, explain the trade-off: how much it will speed up reads vs. slow down writes. Finally, tell me if you see any potential for over-indexing (too many indexes on one table).
Table schema:
[INSERT SCHEMA]
Slow queries:
[INSERT QUERIES]
Example: I used this with a schema for an e-commerce product table. The AI suggested a composite index on (category_id, price) for a query filtering by category and sorting by price. It noted that while this index would speed up that query, it would add overhead to insert operations, and that a single index on category_id might be sufficient if the price sort was done in-memory for most categories. This level of nuance saved me from creating a redundant index.
2. Execution Plan Decoder (PostgreSQL)
EXPLAIN ANALYZE output can be cryptic. This prompt turns the AI into a translator.
Act as a PostgreSQL query optimizer expert. I will give you the output of `EXPLAIN ANALYZE` for a slow query. Explain in plain English what each step in the plan means, identify the biggest bottlenecks (e.g., Seq Scan, Nested Loop, Sort), and propose specific rewrite suggestions or configuration changes (like `work_mem` or `random_page_cost`) to fix them.
EXPLAIN ANALYZE output:
[INSERT OUTPUT]
Example: I had a query with a Hash Join that was spilling to disk. The AI pointed out that increasing work_mem from the default 4MB to 64MB would likely prevent the spill and significantly speed up the join. It also explained that the Hash Cond was on a column with a low cardinality, making a Bitmap Index Scan a better choice than a Hash Join. That insight led me to rewrite the query to use an EXISTS clause instead of a JOIN.
3. The MongoDB Index Designer
MongoDB indexes are just as critical, but the rules differ. This prompt focuses on explain("executionStats") and index design.
You are a MongoDB performance expert. Here is a collection schema and a slow query. Analyze the `explain("executionStats")` output and tell me:
1. What is the current winning plan? (COLLSCAN, IXSCAN, SORT, etc.)
2. Propose a specific index to improve performance, including the exact `createIndex` command.
3. Explain how your proposed index supports the query's sort and filter operations.
4. If the query does a collection scan, explain why an index is still not a good idea (e.g., low selectivity).
Schema and query:
[INSERT]
explain("executionStats") output:
[INSERT]
Example: For a user activity log, a query filtering by user_id and sorting by timestamp was doing a COLLSCAN. The AI suggested a compound index { user_id: 1, timestamp: -1 }. It explained that this index would not only filter but also return documents in the correct sort order, avoiding an in-memory sort. The execution time dropped from 800ms to 5ms.
4. The Query Rewriter (PostgreSQL)
Sometimes the optimizer can't fix a bad query; you have to. This prompt encourages the AI to rewrite the query in multiple ways.
You are a SQL expert. Here is a slow query and its execution plan. Rewrite the query in at least three different ways to improve performance. For each rewrite, explain the logic and the expected performance benefit. Consider using WITH clauses (CTEs), subqueries, EXISTS instead of IN, window functions, or changing the join order.
Original query:
[INSERT]
Execution plan:
[INSERT]
Example: I had a query with multiple OR conditions on different columns. The AI rewrote it using a UNION ALL of separate queries, each using an index on one of the conditions. This allowed the planner to use an index for each branch, turning a sequential scan into three index scans. The overall runtime improved by 5x.
5. The Data Type Auditor (PostgreSQL)
Incorrect data types are a silent killer. This prompt helps you find them.
Act as a PostgreSQL data modeling expert. Analyze the following table schema. For each column, evaluate if the data type is the most efficient choice. Point out if a `VARCHAR` is used where a `TEXT` or an enum would be better, if a `NUMERIC` is used where an `INTEGER` would suffice, or if a `TIMESTAMP` is used where `DATE` is enough. Provide specific `ALTER TABLE` statements to change the types, and explain the performance impact.
Schema:
[INSERT]
Example: A table storing order statuses used VARCHAR(20). The AI suggested converting it to an enum type order_status. This reduced the row size and allowed the planner to use a more efficient comparison. It also reduced the index size, making scans faster. A simple change, but it shaved 15% off the query time.
6. The MongoDB Aggregation Pipeline Optimizer
The aggregation pipeline is powerful but easy to misuse. This prompt focuses on $match, $lookup, and $unwind ordering.
You are a MongoDB aggregation pipeline expert. I will give you a pipeline. Analyze it and suggest optimizations. Specifically, ensure that `$match` and `$project` stages are placed as early as possible to reduce the document count. Evaluate if `$lookup` can be replaced with a denormalized field or a separate query. Show the optimized pipeline and explain the expected performance gain.
Pipeline:
[INSERT]
Example: A pipeline to aggregate sales data was doing a $lookup on a large products collection before a $match on the date field. The AI reordered it to $match on date first, reducing the documents entering the $lookup from 1 million to 10 thousand. This single change cut the execution time from 12 seconds to 1 second.
7. The Deadlock and Blocking Detector (PostgreSQL)
Concurrency issues can make queries slow. This prompt helps you diagnose them.
You are a PostgreSQL concurrency expert. Here is a query that is slow only when run concurrently. Explain how to detect if it's being blocked by other transactions using `pg_locks` and `pg_stat_activity`. Provide a diagnostic query that lists blocking and blocked sessions. Then, suggest fixes: `FOR UPDATE` vs `FOR SHARE`, index on foreign key columns, or changing the transaction isolation level.
Query:
[INSERT]
Example: A simple UPDATE statement was hanging. The AI suggested running a diagnostic query that showed a long-running transaction holding a lock on the same row. The fix was to add an index on the foreign key column referenced in the WHERE clause, which allowed the database to lock the row more efficiently and reduced the blocking time.
8. The Schema Denormalization Advisor (MongoDB)
MongoDB is document-oriented, but sometimes you need to embed rather than reference. This prompt helps you decide.
You are a MongoDB schema design expert. I have two collections: [Collection A] and [Collection B]. I have a query that joins them using `$lookup` and it's slow. Analyze the access patterns and suggest whether I should embed the documents from Collection B into Collection A, or keep them separate. If embedding is recommended, provide a sample embedded document structure. If not, explain why and suggest an alternative (like a separate index or a materialized view).
Query:
[INSERT]
Collections' schemas:
[INSERT]
Example: In a blog, I had posts and comments collections. A query to fetch a post with its comments was doing a $lookup, which was fast but started to slow down as the comment count grew. The AI suggested embedding the first 10 comments in the post document and having a separate comments collection for the rest. This reduced the $lookup size and made the initial page load faster.
9. The Vacuum and Maintenance Scheduler (PostgreSQL)
PostgreSQL is not self-tuning. This prompt helps you plan maintenance.
You are a PostgreSQL DBA. Based on the following table statistics (from `pg_stat_user_tables`), suggest a vacuum and analyze schedule. Determine if `autovacuum` is keeping up with the write load, and if not, propose changes to the `autovacuum_vacuum_scale_factor` and `autovacuum_analyze_scale_factor` for this specific table. Also, suggest when to run a manual `VACUUM FULL` or `CLUSTER`.
Statistics:
[INSERT]
Example: A heavily-updated table had high n_dead_tup and the query planner was choosing bad plans. The AI suggested setting a lower autovacuum_vacuum_scale_factor (e.g., 0.01 instead of 0.2) to trigger vacuums more frequently, and running a CLUSTER on the primary index to physically reorder the table. This restored query performance without any code changes.
10. The MongoDB Sharding Key Selector
If you're using MongoDB Atlas or a cluster, sharding key choice is critical. This prompt helps you pick one.
You are a MongoDB sharding expert. I have a collection that is growing large and I need to shard it. Based on the following access patterns (queries and indexes), recommend a sharding key. Explain the trade-offs between hashed and ranged sharding keys. Also, explain how the choice affects write and read performance.
Access patterns:
[INSERT]
Example: For a time-series collection, the AI recommended a hashed shard key on the device_id field instead of a range key on the timestamp. This distributed writes evenly across all shards, avoiding a hotspot on the most recent data. The read performance for a specific device remained fast because the query included the shard key.
11. The Query Cache Analyzer (PostgreSQL)
While PostgreSQL doesn't have a query cache like MySQL, it does have a shared buffer cache. This prompt helps you analyze its effectiveness.
You are a PostgreSQL performance expert. Analyze the following output from `pg_stat_database` and `pg_buffercache`. Is the shared buffer cache being used efficiently? Are there a lot of cache misses? Should I increase `shared_buffers`? Also, check if there are any tables that are frequently accessed but not cached, and suggest indexing or query rewriting to improve cache utilization.
Statistics:
[INSERT]
Example: The AI noticed a high blks_read count compared to blks_hit. It suggested increasing shared_buffers from 128MB to 1GB (which required a server restart) and also identified a small lookup table that was evicted frequently, recommending to CLUSTER it to keep it in one page. This reduced disk I/O significantly.
12. The Index Bloat Fixer (PostgreSQL)
Indexes can become bloated. This prompt guides you through detection and repair.
You are a PostgreSQL maintenance expert. I suspect my indexes are bloated. Give me a query to check index bloat using `pgstatindex` or a standard bloat query. If the bloat is high, explain how to rebuild the index using `REINDEX INDEX CONCURRENTLY` and what the trade-offs are (locking vs. speed).
My table is: [table name]
Example: A large index on a log table had grown to 3x its actual size. The AI provided a bloat-check query that showed a bloat ratio of 60%. I ran REINDEX INDEX CONCURRENTLY during a low-traffic period, and the index size dropped back to normal, speeding up all queries that used it.
13. The MongoDB Connection Pool Tuner
Connection pools are often misconfigured. This prompt helps you tune them.
You are a MongoDB performance expert. I have an application that talks to MongoDB. My connection pool is set to [current value]. Based on the following workload characteristics (concurrent users, average query latency, and server CPU usage), suggest an optimal connection pool size. Explain the trade-offs of having too few (queueing) vs. too many (context switching, memory pressure).
Workload:
[INSERT]
Example: The AI noticed that the server had 4 CPU cores and the average query latency was 10ms. It suggested a pool size of (4 cores * 2) = 8 connections, which is a common heuristic. This prevented the server from being overwhelmed by too many concurrent operations, reducing response times.
14. The Slow Query Log Miner (PostgreSQL)
This prompt helps you make sense of pg_stat_statements data.
You are a PostgreSQL performance analyst. Here is the output from `pg_stat_statements` showing the top slow queries. For each query, suggest a specific optimization. Focus on queries with high `total_time` or high `mean_time`. Also, check if there are any queries that are called frequently but have low individual time, as they might also be worth optimizing.
Output:
[INSERT]
Example: The AI saw a query that was called 10,000 times per hour with an average time of 50ms. It was a simple SELECT on a table with a WHERE clause on a non-indexed column. The AI suggested adding an index, which reduced the time to 1ms, saving 490 seconds of CPU time per hour.
15. The End-to-End Query Review
This is a meta-prompt that combines everything. It's my go-to for a final review before deploying a new feature.
Act as a senior database architect. I have a new feature that involves the following query. Before I deploy it, review it for performance issues. Check the execution plan, suggest index changes, schema changes, or query rewrites. Also, consider the application-level impact (N+1 queries, etc.). Provide a comprehensive report.
Query:
[INSERT]
Schema:
[INSERT]
Example: Before launching a new dashboard, I ran this prompt. The AI caught an N+1 query pattern in my application code (not just the SQL) and suggested batching the queries. It also pointed out that the query was using a COUNT(*) on a large table without a covering index. I fixed both issues, and the dashboard loaded instantly.
The Takeaway
These 15 prompts are a starting point. The key is to give the AI context—actual schema, query, and execution plan—and demand specific, actionable advice. You'll find that with each use, you'll refine your prompts to get even better results. The goal is not to blindly follow the AI's advice, but to use it as a tool to understand your database better. In the long run, this understanding is more valuable than any single optimization. Now go ahead, try one of these prompts on your slowest query, and see what you learn.
Comments