11 SQL Prompts to Tame PostgreSQL Performance: From EXPLAIN to Index Design

When Your Database Feels Like a Snail

You've written a query that works perfectly on your laptop, but in production it crawls. The usual suspects? Missing indexes, suboptimal joins, or a planner that's making poor choices. PostgreSQL is powerful, but it doesn't optimize your schema for you. The good news: with the right prompts, you can turn any LLM into a PostgreSQL performance expert. This article gives you 11 battle-tested prompts that help you diagnose slow queries, design indexes, and refactor SQL for speed. Each prompt is a ready-to-use template with a real-world example, so you can copy-paste and adapt.

Why Prompts for PostgreSQL Performance?

Before diving in, let's clarify why prompts matter. An LLM doesn't know your database schema, your data distribution, or your query patterns. But if you give it the right context—like an EXPLAIN ANALYZE output or a table DDL—it can provide expert-level advice. The prompts below are designed to extract maximum value from your AI assistant, whether you're using ChatGPT, Claude, or an embedded AI in your IDE.

The Prompts

1. Diagnose a Slow Query with EXPLAIN ANALYZE

Prompt:

I have a slow query in PostgreSQL. Here's the query and its EXPLAIN ANALYZE output:

[Paste your query]

[Paste EXPLAIN ANALYZE output]

Identify the bottlenecks, such as sequential scans, high loop counts, or memory issues. Suggest specific indexes or query rewrites. Be concrete.

Why it works: The prompt gives the AI the exact execution plan, so it can point to the actual problem—not guess.

Example:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 123 AND created_at > now() - interval '30 days';

The AI might spot a sequential scan on orders and suggest a composite index on (customer_id, created_at).

Pro tip: Always include BUFFERS to see cache hits vs. disk reads.

2. Design an Index for a Specific Query Pattern

Prompt:

Here's a table DDL and a set of slow queries:

[DDL]
[Queries]

What indexes should I create? Consider composite indexes, partial indexes, and index ordering. Explain the trade-offs (write overhead, disk space).

Why it works: The AI can analyze the WHERE, ORDER BY, and JOIN clauses to propose optimal indexes.

Example:

Pro tip: For a partial index, the condition must match the query's WHERE.

3. Rewrite a Correlated Subquery as a JOIN

Prompt:

I have a query with a correlated subquery that's slow. Rewrite it using a JOIN or a window function, and explain why the rewrite is faster.

Query:
[Paste query]

Why it works: Correlated subqueries often cause nested loop scans. A JOIN can be more efficient if indexes are present.

Example:

-- Slow
SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 1000);

-- Faster
SELECT DISTINCT c.* FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.total > 1000;

The AI can also warn about duplicates and suggest DISTINCT or EXISTS alternatives.

4. Optimize a Slow JOIN with Proper Indexes

Prompt:

Here's a slow JOIN query and the EXPLAIN ANALYZE output:

[Query]
[EXPLAIN]

What indexes should I create on the join columns? Should I change the join type (e.g., to a hash join)? Provide the index DDL.

Why it works: The AI can see if the planner is doing a nested loop when a hash join would be better, and suggest indexes to enable a merge join.

Example:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

If the planner still chooses a nested loop, you can hint with SET enable_nestloop = off; but that's a last resort.

5. Find Missing Indexes from pg_stat

Prompt:

Using the pg_stat_user_tables and pg_stat_user_indexes views, here's the data:

[Paste relevant stats]

Which tables have high seq_scan but low idx_scan? Suggest indexes and justify based on the stats.

Why it works: The stats show where scans are happening, so the AI can recommend indexes with evidence.

Example:

Clearly, an index on orders(customer_id) would help.

Pro tip: Also look at pg_stat_user_indexes for unused indexes to drop.

6. Optimize a Query with Multiple OR Conditions

Prompt:

This query is slow because of OR conditions. How can I rewrite it to use UNION ALL or a more efficient index strategy?

Query:
[Paste query]

Why it works: OR conditions often prevent index usage. Splitting into UNION ALL can allow each part to use an index.

Example:

-- Slow
SELECT * FROM products WHERE brand='Apple' OR category='Phone';

-- Faster
SELECT * FROM products WHERE brand='Apple'
UNION ALL
SELECT * FROM products WHERE category='Phone' AND brand<>'Apple';

7. Analyze and Improve a Slow Window Function Query

Prompt:

Here's a query with a window function that's slow. Is there a way to speed it up, e.g., by adding an index on the PARTITION BY column?

Query:
[Paste query]

Why it works: Window functions often require sorting. An index on the partition and order columns can eliminate the sort.

Example:

SELECT customer_id, order_date, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) FROM orders;

An index on (customer_id, order_date) helps.

8. Refactor a Query to Use CTEs for Readability and Performance

Prompt:

Rewrite this complex query using CTEs (WITH clauses) to make it more readable. Ensure the CTEs don't hurt performance—if they do, suggest alternatives.

Query:
[Paste query]

Why it works: CTEs can be inlined (in PostgreSQL 12+), but sometimes materialization helps. The AI can advise.

Example:

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT * FROM recent_orders WHERE total > 100;

9. Tune PostgreSQL Configuration for a Specific Workload

Prompt:

I have a PostgreSQL instance with 16GB RAM and a workload that is [OLTP/OLAP/mixed]. Based on the following current settings, what should I change?

Current settings:
[Paste from SHOW ALL]

Suggest values for shared_buffers, work_mem, effective_cache_size, and other relevant parameters. Provide reasoning.

Why it works: The AI can recommend best practices based on your hardware and workload.

Example: For 16GB RAM, shared_buffers could be 4GB, work_mem 64MB, effective_cache_size 12GB.

Pro tip: Use pg_settings to get current values.

10. Identify and Remove Redundant Indexes

Prompt:

Here's the list of indexes on my database and their usage stats (from pg_stat_user_indexes). Which indexes are redundant or unused? Provide DROP statements.

Why it works: Unused indexes waste write performance and disk space. The stats show which are rarely used.

Example:

Drop it if no queries use it.

11. Generate a Test Plan for a Query Optimization

Prompt:

I've rewritten a query for performance. Generate a test plan to compare the old and new versions, including measuring execution time, cache behavior, and correctness. Provide a script to run the tests.

Why it works: This helps you validate that your optimization actually works and doesn't break results.

Example: Use EXPLAIN (ANALYZE, TIMING) and compare the actual times.

Putting It All Together

These prompts are not magic—they're tools. The key is to provide the AI with accurate, detailed context. Always include the query, the EXPLAIN output, and the table DDL when relevant. Then critically evaluate the AI's suggestions. Remember that the planner's decisions depend on data statistics, so after creating an index, run ANALYZE to update stats.

Final Thoughts

Performance tuning is iterative. Use these prompts to get initial advice, then test and refine. Over time, you'll learn to spot issues yourself. But having an AI assistant that understands PostgreSQL deeply can save hours of manual analysis. Start with the first prompt and work through them as you encounter problems. Your database—and your users—will thank you.

If you want to dive deeper into PostgreSQL internals, the official documentation is your best friend. And for real-world case studies, check out the PostgreSQL Performance Blog. Happy tuning!