10x Faster Queries: A Prompt Toolkit for PostgreSQL & MongoDB Tuning

You're staring at a query that's been running for 47 seconds. Users are complaining. Your boss is hovering. You've tried adding indexes, but nothing helps. Sound familiar? Slow queries aren't just a technical nuisance—they cost revenue, erode trust, and burn out engineers. The good news? Most performance problems have known patterns and proven fixes.

Here's the thing: you don't need to memorize every PostgreSQL or MongoDB internal. You need the right questions to ask—and the right prompts to get answers. In this guide, I've collected 10 battle-tested prompts that will help you diagnose, optimize, and verify SQL query performance. Each prompt includes a real-world scenario, the exact command or query to use, and what to look for. Let's turn those 47 seconds into 47 milliseconds.

1. Find the Slowest Queries First

Before touching a single index, you need to know what's actually slow. Both databases keep logs of slow operations—you just need to ask.

Prompt for PostgreSQL:

"Analyze the PostgreSQL slow query log at postgresql.conf with log_min_duration_statement = 1000. Group the entries by normalized query (remove literal values), calculate average, max, and count for each group, and list the top 10 by total execution time. For each query, include the sample SQL and suggest possible causes (missing indexes, joins, etc.)."

Prompt for MongoDB:

"Using the MongoDB system.profile collection, find operations with millis > 1000 in the last 24 hours. Group by the command field (extract the collection name and operation type), and for each group show the average, maximum, and count. Include sample query documents and suggest why they might be slow (missing compound index, full collection scan, etc.)."

Real-world example: A fintech startup used log_min_duration_statement and discovered that a seemingly simple SELECT on a transactions table was scanning 12 million rows because a column used in WHERE had no index. After adding a single B-tree index, the query dropped from 3.2s to 40ms.

2. Read the Query Plan Like a Pro

EXPLAIN is your best friend, but it's only useful if you know what to look for. These prompts will help you interpret the output.

Prompt for PostgreSQL:

"Run EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) on the following query: [insert SQL]. Analyze the plan step by step. Identify all sequential scans (Seq Scan) that touch more than 10% of the table, all nested loop joins with high row estimates, and any sorts or hashes that spill to disk. For each issue, explain why it's slow and propose a concrete fix (index, join order, etc.)."

Prompt for MongoDB:

"Run db.collection.explain('executionStats') for the following query: [insert query]. Look at the executionStats field. If totalDocsExamined is much larger than nReturned, identify the missing index. If executionTimeMillis is significant, suggest a compound index that covers the filter and sort fields. Explain what each metric means."

Real-world example: A logistics company discovered via EXPLAIN ANALYZE that a join between orders and customers was using a Nested Loop with 5,000 iterations, each performing an index scan. By rewriting the query to use a hash join (via join_collapse_limit or changing the WHERE clause), they reduced the time from 800ms to 90ms.

3. Design the Perfect Index

Indexes are the #1 performance lever. But choosing the right type and columns isn't always obvious. Let AI be your index advisor.

Prompt for PostgreSQL:

"Given the following table schema and query patterns, recommend indexes to add. For each recommendation, specify: index type (B-tree, Hash, GIN, BRIN), column(s), order, and why it fits. Also list any indexes that are redundant or unused. Table: [DDL]. Queries: [list of slow queries]."

Prompt for MongoDB:

"For a MongoDB collection with this document structure [sample document], and these common query patterns [list queries], what indexes should I create? Consider compound indexes, partial indexes, and TTL indexes. For each index, describe which query it accelerates and potential trade-offs (write performance, storage)."

Real-world example: An e-commerce platform had a products collection with queries like find({category: 'electronics', price: {$lt: 100}}).sort({rating: -1}). A single-field index on category was insufficient. A compound index {category: 1, price: 1, rating: -1} cut query time from 120ms to 5ms.

4. Refactor Bad Queries Without Changing Logic

Sometimes the query itself is the problem—missing joins, unnecessary subqueries, or functions on indexed columns.

Prompt for PostgreSQL:

"Here is a slow query: [SQL]. Rewrite it to improve performance without changing the result set. Apply these rules: avoid functions on indexed columns in WHERE clauses, use EXISTS instead of IN for large subqueries, use UNION ALL instead of UNION when duplicates are acceptable, and consider using CTEs for readability. Explain each change and the expected performance gain."

Prompt for MongoDB:

"This MongoDB aggregation pipeline is slow: [pipeline]. Optimize it by: using $match and $project as early as possible, replacing $unwind with $filter where applicable, and using $lookup only when necessary. Provide the optimized pipeline and explain how it reduces the amount of data processed."

Real-world example: A SaaS company had a query with WHERE date_trunc('day', created_at) = '2025-01-01', which prevented index usage. Rewriting to created_at >= '2025-01-01' AND created_at < '2025-01-02' allowed index range scan, improving performance from 2.1s to 30ms.

5. Clean Up Your Data and Schema

Sometimes the problem is not the query but the data itself—bloated tables, redundant fields, or outdated statistics.

Prompt for PostgreSQL:

"Check the bloat of my tables using pgstattuple or the pg_bloat estimation query. For tables with over 20% bloat, suggest a VACUUM FULL or pg_repack strategy. Also, review my table schemas for unnecessary columns, missing constraints, and inappropriate data types. Provide a report."

Prompt for MongoDB:

"Review my MongoDB database for fragmentation and storage inefficiencies. Use db.collection.stats() to check size vs storageSize. If fragmentation is high, suggest a compact command or rewriting with a new schema. Also, identify fields that are always present and could be moved to a separate collection to reduce document size."

Real-world example: A social media platform found that after many updates, the users collection had 60% fragmentation. Running db.users.compact() reduced storage by 30% and improved query speed by 15%.

6. Tune Database Configuration

Sometimes the database defaults aren't optimal for your workload. These prompts will help you adjust key parameters.

Prompt for PostgreSQL:

"Based on my server's RAM (e.g., 16GB), recommend values for shared_buffers, work_mem, maintenance_work_mem, effective_cache_size, and random_page_cost. Explain how each affects query performance. Provide the exact ALTER SYSTEM commands. Mention that shared_buffers should typically be 25% of RAM, and work_mem should be increased for heavy sorts."

Prompt for MongoDB:

"My MongoDB runs on a server with 32GB RAM. What are the recommended settings for cacheSizeGB, wiredTigerCacheSizeGB, and maxWriteBatchSize? Explain how the WiredTiger cache works and how to configure it for a read-heavy vs. write-heavy workload."

Real-world example: A BI tool company increased work_mem from 4MB to 64MB, allowing sorts to happen in memory instead of spilling to disk. This reduced query times by 50% for complex aggregations.

7. Detect and Fix Lock Contention

Slow queries aren't always about CPU—sometimes they're waiting on locks.

Prompt for PostgreSQL:

"Run pg_locks and pg_stat_activity to identify blocking sessions. For each blocked query, find the blocking query and suggest solutions: reduce transaction time, use FOR UPDATE SKIP LOCKED, or break long transactions into smaller ones. Provide a SQL script to detect locks."

Prompt for MongoDB:

"Check db.serverStatus().locks to see lock wait times. If write locks are high, suggest ways to reduce contention: use sharding, avoid long-running multi-document transactions, or consider optimistic concurrency control. Explain how to use findAndModify instead of find-then-update."

Real-world example: An online booking system has a query that updates a seat count in a transaction. Under high load, it caused lock waits. By using findAndModify with atomic $inc, they eliminated the lock contention and improved throughput by 30%.

8. Scale Your Reads with Replicas

If you have a read-heavy workload, offloading reads to replicas can dramatically improve performance.

Prompt for PostgreSQL:

"I have a primary PostgreSQL server and two read replicas. How can I configure my application to route read queries to replicas? Explain the pros and cons of using a connection pooler like PgBouncer or a load balancer. Provide sample configuration for a typical Python/Django app."

Prompt for MongoDB:

"Set up read preference in MongoDB to route reads to secondaries. Explain the readPreference options: primary, primaryPreferred, secondary, secondaryPreferred. Show a connection string example for Node.js and Python. Discuss consistency implications."

Real-world example: A news website used MongoDB with read preference secondaryPreferred for article views, freeing the primary for writes. This doubled the site's capacity without additional cost.

9. Monitor and Alert in Production

You can't fix what you can't see. Set up monitoring and alerts for slow queries.

Prompt for PostgreSQL:

"Design a monitoring setup for PostgreSQL using pg_stat_statements and a tool like Prometheus + Grafana. Provide the SQL to create views for top queries, and list key metrics to track (query time, hit ratio, deadlocks). Suggest alert thresholds."

Prompt for MongoDB:

"Create a monitoring script that periodically runs db.currentOp() and logs queries taking longer than 5 seconds. Also, use MongoDB Cloud Manager or Ops Manager to set up alerts for slow queries. What metrics should you watch? Provide a sample script in Python."

Real-world example: A gaming company used pg_stat_statements to track query frequency and total time. They noticed a new feature caused a query to run 10x more often, allowing them to optimize it before it became a problem.

10. Benchmark Before and After

Finally, always measure the impact of your changes. Use a load testing tool to compare.

Prompt for PostgreSQL:

"Generate a benchmark script using pgbench for a specific workload. Run it before and after applying the optimization. Provide the command line and how to interpret TPS (transactions per second) and latency percentiles."

Prompt for MongoDB:

"Write a simple load test in Python using pymongo that performs 1000 reads and writes. Measure the average latency before and after adding an index. Output a table comparing results."

Real-world example: A logistics company ran pgbench with a custom script simulating their order queries. After optimizing a join, TPS increased from 200 to 400, and p95 latency dropped from 800ms to 150ms.

Your Fast Lane to a Faster Database

There you have it—a practical toolkit for turning sluggish queries into sprinters. Remember, the key is to measure first, then act, and always verify. Start with the slow query log, use EXPLAIN to pinpoint issues, and apply the fixes incrementally. With these prompts, you can cut query times by an order of magnitude—and look like a hero to your team.

Ready to dive deeper? Explore our course on SQL optimization and database performance at asibiont.com. And if you have your own war stories, share them in the comments—we love a good query-tuning tale.