Have you ever waited for a query to execute longer than it takes to drink your morning coffee? I have, and it hurts. When the database starts to slow down and users complain about slow pages, it's time for optimization. But how do you quickly find the bottleneck? The traditional approach is to manually analyze EXPLAIN, look at indexes, rewrite queries. This takes hours. What if I told you that with well-crafted AI prompts, you can reduce this process to minutes? In this article, I've compiled a collection of prompts that will help you speed up PostgreSQL and MongoDB significantly. You'll learn how to use AI to analyze query plans, find missing indexes, refactor, and even tune configuration. Let's go!
Basic Prompts: Starting with Diagnostics
Before optimizing anything, you need to understand what's slowing down. These prompts will help you quickly assess the state of your database and find obvious problems.
1. Analyzing a Slow Query with EXPLAIN
Task: Get a detailed breakdown of the query execution plan and identify bottlenecks.
Prompt:
Analyze the execution plan of the following SQL query in PostgreSQL. Explain which operations take the most time and suggest specific optimization measures. Note that the orders table contains 10 million rows, and the query takes 15 seconds.
EXPLAIN (ANALYZE, BUFFERS) SELECT customer_id, COUNT(*) FROM orders WHERE status = 'shipped' GROUP BY customer_id ORDER BY COUNT(*) DESC LIMIT 10;
Example Result:
The AI will analyze the plan and point out, for example, that a Seq Scan is used on the orders table (which is bad for 10M rows), suggest creating an index on status, and possibly on (status, customer_id) for a covering index. It may also suggest using a materialized view for aggregates if data changes rarely.
2. Finding Missing Indexes
Task: Determine which indexes should be added to speed up queries.
Prompt:
Here is a list of the most frequent queries to my PostgreSQL database. Determine which indexes need to be created to speed up their execution. For each index, provide the CREATE INDEX command.
1. SELECT * FROM users WHERE email = 'test@example.com';
2. SELECT * FROM orders WHERE user_id = 123 AND created_at > '2025-01-01';
3. SELECT product_id, SUM(quantity) FROM order_items GROUP BY product_id;
Example Result:
The AI will suggest:
- A unique index on users(email) (if not already present).
- A composite index on orders(user_id, created_at).
- An index on order_items(product_id) for GROUP BY.
3. Profiling MongoDB: find().explain()
Task: Understand how MongoDB executes a query and find reasons for slow performance.
Prompt:
I have a MongoDB collection products with 5 million documents. The query db.products.find({category: 'electronics', price: {$lt: 1000}}).sort({rating: -1}).limit(10) takes 3 seconds. Run explain and analyze the result. Suggest optimization.
Example Result:
The AI will explain that it's likely using COLLSCAN and suggest creating a composite index {category: 1, price: 1, rating: -1}. It may also recommend using a projection to reduce the amount of data transferred.
Advanced Prompts: Diving Deeper
When basic prompts don't yield results, it's time to dig deeper. These prompts will help you tune indexes, rewrite queries, and optimize schema.
4. Refactoring a Complex SQL Query
Task: Rewrite a slow query with subqueries and correlations into a more efficient version.
Prompt:
Rewrite the following SQL query for PostgreSQL to make it run faster. Use JOIN instead of subqueries if possible, and explain why the new version is better.
SELECT * FROM orders o WHERE o.total > (SELECT AVG(total) FROM orders WHERE customer_id = o.customer_id) AND o.status = 'paid';
Example Result:
The AI might suggest using a window function AVG() OVER (PARTITION BY customer_id) or a JOIN with an aggregated subquery. It will explain that a correlated subquery runs for each row, while a JOIN runs once.
5. Tuning PostgreSQL Configuration
Task: Choose optimal configuration parameters for a specific workload.
Prompt:
I have PostgreSQL 16 on a server with 16 GB RAM and SSD. The database is used for OLTP workload with a high number of read and write queries. Which parameters in postgresql.conf should I change to improve performance? Give specific values and explain why.
Example Result:
The AI will suggest increasing shared_buffers to 4 GB, effective_cache_size to 12 GB, work_mem to 64 MB, and tuning wal_buffers. It will explain how this affects caching and sorting.
6. Optimizing Queries with JOIN in MongoDB
Task: Improve performance of queries with $lookup (similar to JOIN).
Prompt:
I have collections users and orders in MongoDB. I'm running an aggregation with $lookup to get a user's orders. The query is very slow. Here's the code:
db.users.aggregate([{ $match: { _id: ObjectId('...') } }, { $lookup: { from: 'orders', localField: '_id', foreignField: 'user_id', as: 'orders' } }])
How can I speed up this query? Suggest options.
Example Result:
The AI will suggest creating an index on orders.user_id, using $lookup with a pipeline for filtering, or even denormalizing data (storing orders inside the user document) for frequent queries.
Expert Prompts: Mastery of Optimization
For those who want to squeeze out maximum performance, here are prompts that help find hidden issues and use advanced techniques.
7. Analyzing and Optimizing EXPLAIN ANALYZE with Statistics
Task: Deep analysis of a query plan considering table statistics and data distribution.
Prompt:
Here is the execution plan of a query from PostgreSQL. Based on table statistics (I'll provide pg_stats), explain why the optimizer chose this plan and suggest how to improve it. Also check if the statistics are outdated and whether ANALYZE needs to be run.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table WHERE date_column BETWEEN '2025-01-01' AND '2025-12-31';
Example Result:
The AI will analyze the plan, see that a Seq Scan is used, and advise creating an index on date_column, as well as checking if statistics were updated (last_analyze). If not, run ANALYZE.
8. Using pg_hint_plan to Force a Plan Choice
Task: Force PostgreSQL to use a specific execution plan when the optimizer is wrong.
Prompt:
I have a query that PostgreSQL executes suboptimally. I want to use the pg_hint_plan extension to force it to use the index idx_orders_status. Write how to enable the extension and add a hint to the query.
Example Result:
The AI will give instructions: install the extension, add a comment /*+ IndexScan(orders idx_orders_status) */ before SELECT. Explain when this is useful and what risks exist.
9. Optimizing Full-Text Search Queries
Task: Speed up full-text search in PostgreSQL using GIN indexes.
Prompt:
I have a table articles with a column body. I'm running a full-text search: SELECT * FROM articles WHERE to_tsvector('russian', body) @@ to_tsquery('russian', 'оптимизация & запросов');. The query is slow. What should I do?
Example Result:
The AI will suggest creating a GIN index on to_tsvector('russian', body), and also using a separate column for tsvector to avoid computing it every time. It will show an example of index creation.
10. Diagnosing Locks and Deadlocks
Task: Find and resolve lock issues that slow down query execution.
Prompt:
I have PostgreSQL where deadlocks occasionally occur. How can I find queries that block each other, and what can I do to avoid them? Provide example SQL queries for diagnostics.
Example Result:
The AI will suggest queries to pg_locks, pg_stat_activity, and advise enabling lock logging (log_lock_waits = on). It will explain how to analyze the output.
11. Optimizing MongoDB with Partial Indexes
Task: Create partial indexes to speed up queries with conditions on rare values.
Prompt:
I have a MongoDB collection events with a field type (e.g., 'click', 'purchase', 'error'). Queries often search only for 'error'. Create a partial index that speeds up such queries and explain how it works.
Example Result:
The AI will suggest creating an index: db.events.createIndex({type: 1, created_at: -1}, {partialFilterExpression: {type: 'error'}}). It will explain that this reduces the index size and speeds up queries.
12. Using Materialized Views for Aggregates
Task: Speed up complex aggregate queries that run frequently using materialized views.
Prompt:
I have a query that calculates daily revenue by product. It runs slowly, but data changes once a day. How can I use a materialized view in PostgreSQL to speed up this query? Write the SQL.
Example Result:
The AI will write CREATE MATERIALIZED VIEW daily_revenue AS SELECT date, product_id, SUM(amount) FROM sales GROUP BY date, product_id; and advise refreshing it with REFRESH MATERIALIZED VIEW CONCURRENTLY. It will explain pros and cons.
Conclusion
Query optimization is an art, but with AI prompts you can turn it into a science. We've covered 12 prompts that cover basic and advanced techniques for PostgreSQL and MongoDB. Start with simple ones—analyzing EXPLAIN, finding missing indexes. Then move on to refactoring and configuration tuning. And when you feel confident, use expert prompts to solve complex issues like locks or partial indexes. Try applying these prompts in your projects—and you'll see query execution times drop significantly. If you have your own proven prompts, share them in the comments; let's learn from each other!
Comments