You know that feeling when a query that used to return in milliseconds now crawls like a dial-up connection? You've added indexes, rewritten JOINs, and still the database groans. Welcome to the world of PostgreSQL performance tuning, where the difference between a sluggish app and a lightning-fast one often comes down to understanding the query planner—and asking the right questions.
Large language models (LLMs) have become surprisingly adept at reading execution plans and suggesting optimizations. But like any tool, they're only as good as the prompts you feed them. A vague "optimize my query" yields generic advice; a well-structured prompt that includes the execution plan, table statistics, and schema can produce targeted, actionable recommendations.
In this guide, I've curated 15 prompts that I've refined through countless hours of database tuning. They're organized by complexity: basic prompts for everyday analysis, advanced prompts for index and schema optimization, and expert prompts for deep-dive performance archaeology. Each prompt is designed to be used with a modern LLM (like Claude, GPT-4, or a specialized database assistant) and is accompanied by real-world examples and outputs.
Let's turn that sluggish query into a speed demon.
Basic Prompts: Foundation for Everyday Tuning
1. The Execution Plan Interpreter
Task: Explain what a PostgreSQL execution plan is doing, step by step, in plain English.
Prompt:
I have a PostgreSQL execution plan for a query that's running slowly. Here's the output from EXPLAIN (ANALYZE, BUFFERS):
[Paste the plan]
Please explain:
1. What each node does (Seq Scan, Hash Join, etc.) in simple terms.
2. Where the time is actually being spent (look at 'actual time' and 'rows removed by filter').
3. Any red flags like sequential scans on large tables, excessive buffer usage, or row count mismatches.
4. Suggest 2-3 quick wins (indexes, query rewrite) if any.
Example Result:
For a plan showing a sequential scan on a 10M-row orders table with a filter on customer_id, the LLM might respond: "The database is scanning all 10 million rows to find orders for customer 1234. This is slow (actual time=4500ms). A b-tree index on orders(customer_id) would reduce this to a bitmap index scan, potentially cutting time to under 10ms. Consider adding: CREATE INDEX idx_orders_customer_id ON orders (customer_id);"
2. The Query Simplifier
Task: Refactor a complex SQL query for readability and performance without changing semantics.
Prompt:
Here's a SQL query that's part of a reporting pipeline. It works but takes 30+ seconds and is hard to debug.
[Paste query]
Please:
1. Rewrite it using CTEs or subqueries to improve readability.
2. Remove any redundant joins or filters (e.g., conditions that are always true).
3. Replace correlated subqueries with JOINs or window functions if possible.
4. Keep the exact same result set.
5. Explain each change and why it might improve performance.
Example Result:
Given a query with multiple nested subqueries, the LLM might produce a version using CTEs, eliminating a self-join that was duplicating rows, and explain: "The original used a correlated subquery to get the latest order per customer, which executed once per row. I replaced it with a window function (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC)) which scans the table once, reducing time from 30s to 2s in my test."
3. The Missing Index Detector
Task: Identify which indexes are missing based on a query's WHERE and JOIN clauses.
Prompt:
I have a PostgreSQL query that's slow. Here's the schema:
[Paste schema for relevant tables]
And here's the query:
[Paste query]
Please suggest new indexes that would help this query, based on:
- Columns used in WHERE clauses with equality or range conditions.
- Columns used in JOIN conditions.
- Columns used in ORDER BY or GROUP BY.
For each index, provide the exact CREATE INDEX statement, and explain why it helps. Also note any existing indexes that might be redundant.
Example Result:
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);
"This composite index covers both the WHERE condition (customer_id = 5) and the ORDER BY (created_at DESC), allowing an index-only scan."
Advanced Prompts: Diving Deeper into Performance
4. The Index Strategist: B-tree vs. Hash vs. GIN vs. BRIN
Task: Choose the optimal index type for a given data distribution and query pattern.
Prompt:
I need to index a table called 'logs' with the following characteristics:
- 100 million rows, with a column 'event_time' that is monotonically increasing.
- Queries often filter by 'user_id' (equality) and by 'event_time' (range).
- There's also a JSONB column 'metadata' that we filter with the '?' operator.
Given these workloads, which index types (B-tree, Hash, GIN, BRIN) would you recommend for each column? Provide CREATE INDEX statements, and explain the trade-offs (size, write overhead, query speed). Also, mention when BRIN would be a better choice than B-tree for the 'event_time' column.
Example Result:
"For user_id with equality queries, a standard B-tree is ideal. For event_time, since it's always increasing and you're doing range queries, a BRIN index would be much smaller and faster to scan (the data is physically ordered). However, if you have many updates, BRIN might not be efficient. I'd suggest: CREATE INDEX idx_logs_event_time_brin ON logs USING brin (event_time); For the JSONB column, use a GIN index: CREATE INDEX idx_logs_metadata_gin ON logs USING gin (metadata);"
5. The Partitioning Architect
Task: Design a partitioning strategy for a large table.
Prompt:
I have a table 'transactions' with 2 billion rows. The main query pattern filters by 'created_at' (date range) and sometimes by 'account_id'. Write a detailed partitioning plan:
- Should I use RANGE or LIST partitioning?
- What partition key and interval (daily, monthly, yearly) is best?
- Show the DDL to create the partitioned table and partitions.
- How would indexes on the partitioned table work?
- Are there any gotchas with primary keys or foreign keys?
Example Result:
"I recommend RANGE partitioning on created_at with monthly intervals. This allows partition pruning for queries that filter on a single month. Here's the DDL:
CREATE TABLE transactions (...) PARTITION BY RANGE (created_at);
CREATE TABLE transactions_2026_09 PARTITION OF transactions
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
Make sure the primary key includes the partition key. Indexes on the parent table automatically create on partitions..."
6. The Statistics Whisperer
Task: Diagnose and fix row count estimation errors caused by stale or insufficient statistics.
Prompt:
My execution plan shows a huge discrepancy: the planner estimates 10 rows but actually processes 1,000,000 rows. This is likely a statistics issue. Here are the table details:
[Paste schema, row count, and any columns with skewed data]
Please walk me through:
1. How to check if autovacuum is running and when the last analyze was.
2. Commands to manually update statistics (ANALYZE, etc.).
3. How to adjust the statistics target for specific columns (e.g., ALTER TABLE ... SET STATISTICS).
4. When to use extended statistics for correlated columns.
5. Provide a step-by-step plan to fix the estimation issue.
Example Result:
"First, run SELECT relname, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname='your_table';. If last_analyze is old, run ANALYZE your_table;. For skewed columns like status, which has 99% 'completed', increase the statistics target: ALTER TABLE your_table ALTER COLUMN status SET STATISTICS 1000; Then ANALYZE your_table;. For correlated columns, create extended statistics: CREATE STATISTICS s1 ON (status, type) FROM your_table;"
7. The Query Rewrite Doctor
Task: Rewrite inefficient query patterns (e.g., OR conditions, NOT IN, LIKE with wildcards) into faster equivalents.
Prompt:
Here's a query that performs poorly:
[Paste query with ORs, NOT IN, or LIKE '%...%']
Rewrite it to use more efficient patterns:
- Replace OR with UNION or proper indexing.
- Replace NOT IN (subquery) with NOT EXISTS.
- Replace leading-wildcard LIKE with trigram indexes or full-text search if appropriate.
- Provide the rewritten query and any necessary indexes.
Example Result:
"The OR condition WHERE status = 'new' OR priority = 'high' can be rewritten as two separate queries UNIONed only if the conditions are on different columns. But with a single index on (status, priority) it can become a bitmap OR scan. Actually, the best fix is to create a partial index for each condition and use UNION ALL..."
Expert Prompts: Uncovering Hidden Bottlenecks
8. The Index Bloat Analyzer
Task: Detect and fix index bloat that wastes space and slows down scans.
Prompt:
I suspect index bloat on my PostgreSQL database. I have a table 'events' with frequent updates and deletes. Provide a query to measure index bloat (using pgstatindex or a custom query), then explain how to rebuild indexes using REINDEX or pg_repack to minimize locks. Include:
- A SQL query to list the top 10 bloated indexes.
- The exact command to rebuild an index online.
- How to schedule regular maintenance.
Example Result:
"Run this query to estimate bloat:
SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
For actual bloat percentage, use pgstattuple extension. To rebuild: REINDEX INDEX CONCURRENTLY idx_events_created; This avoids locking the table. For heavy bloat, consider pg_repack."
9. The Lock Contention Detective
Task: Identify and resolve locking issues that cause queries to block each other.
Prompt:
I'm seeing frequent lock timeouts in my application logs. I need to diagnose lock contention. Provide:
- A query to show current locks and blocked processes (from pg_locks and pg_stat_activity).
- A query to find queries that have been waiting the longest.
- Common causes (e.g., long transactions, missing indexes causing full-table locks) and solutions.
- How to set lock_timeout and statement_timeout to prevent indefinite waits.
Example Result:
"Run SELECT * FROM pg_locks WHERE NOT granted; to see blocked locks. Use pg_stat_activity to find the blocking PID. Common fix is to ensure all transactions are short and commit quickly. Consider setting lock_timeout = '2s' to fail fast. Also, check for queries that take locks on many rows due to missing indexes."
10. The Memory Tuning Guru
Task: Recommend PostgreSQL memory settings based on workload and hardware.
Prompt:
I have a PostgreSQL 16 instance on a server with 32GB RAM and 8 CPUs. The workload is mixed OLTP and reporting. My current config:
- shared_buffers = 128MB
- work_mem = 4MB
- effective_cache_size = 2GB
Please recommend optimal values for these and other parameters (maintenance_work_mem, max_connections, etc.), explaining the trade-offs. Also, suggest how to monitor if they are sufficient (e.g., using pg_stat_bgwriter).
Example Result:
"For 32GB RAM, set shared_buffers to about 25% (8GB), effective_cache_size to 75% (24GB), work_mem to 32MB (but be careful with parallel queries). maintenance_work_mem to 1GB for VACUUM. Use pg_stat_bgwriter to check if checkpoints are frequent..."
11. The Parallel Query Coordinator
Task: Tune parallel query settings for a specific query that is not using parallelism.
Prompt:
I have a query that does a heavy aggregation on a large table, but EXPLAIN shows no parallel workers. My server has 8 CPUs. Here's the query and the plan:
[Paste]
What settings affect parallelism (max_parallel_workers_per_gather, min_parallel_table_scan_size, etc.)? How can I force parallelism for this query? Are there any reasons why the planner might avoid parallelism (e.g., functions not marked PARALLEL SAFE)?
Example Result:
"Check that the table is large enough: min_parallel_table_scan_size defaults to 8MB, so it probably is. Ensure max_parallel_workers_per_gather is set to at least 2. Set force_parallel_mode = on to test. Also, if the query uses a function that is not PARALLEL SAFE, it will disable parallelism. You can mark your custom functions as PARALLEL SAFE if they are."
12. The Vacuum & Autovacuum Tuning Specialist
Task: Optimize VACUUM and autovacuum settings to prevent bloat and transaction ID wraparound.
Prompt:
I'm concerned about table bloat and transaction ID wraparound. My autovacuum seems to run too often, causing I/O spikes. Please provide:
- How to check current autovacuum settings and per-table overrides.
- Recommended values for autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor, etc., for a busy database.
- How to monitor dead tuples and the last vacuum time.
- When to use VACUUM FULL (and why it locks the table).
Example Result:
"Check SELECT relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;. If n_dead_tup is high relative to n_live_tup, increase autovacuum frequency by lowering scale_factor. For a busy DB, set autovacuum_vacuum_scale_factor = 0.05. Avoid VACUUM FULL on production due to locking; use pg_repack instead."
13. The Index-Only Scan Inspector
Task: Ensure that a query can use an index-only scan and avoid heap fetches.
Prompt:
I have a query like `SELECT user_id, email FROM users WHERE status = 'active';` and I created an index on (status, email). But EXPLAIN shows a Heap Fetches step. Why isn't it an index-only scan? The table is not updated frequently. Here's the plan:
[Paste]
Explain the concept of the visibility map and how to make it work (e.g., VACUUM the table). Also, if I add user_id to the index, would it help?
Example Result:
"Heap Fetches occur because the visibility map is not up to date. Run VACUUM users; to mark all rows as visible. If the table has updates, the visibility map will be incomplete, so index-only scans might not be used. Adding user_id to the index would make it a covering index, but it's already covered if you select user_id and email? Actually, user_id is in the index if it's the table's primary key? Anyway..."
14. The PostGIS & JSONB Performance Specialist
Task: Optimize queries involving PostGIS geometry or JSONB columns.
Prompt:
I have a table with a PostGIS geometry column (point) and a JSONB column. Queries filter by a bounding box and also by a JSONB property. Here's the query:
[Paste]
Please suggest:
- Indexing strategy (GIST for geometry, GIN for JSONB).
- How to combine both conditions to use both indexes (bitmap AND).
- Whether to use a generated column for a common JSONB field to improve performance.
- Any specific functions to avoid?
Example Result:
"Create a GIST index on the geometry column and a GIN index on the JSONB column. The planner can combine them using a BitmapAnd scan. For frequently accessed JSONB fields, create a generated column: ALTER TABLE t ADD COLUMN status text GENERATED ALWAYS AS (metadata->>'status') STORED; and index that."
15. The End-to-End Query Optimizer
Task: Perform a comprehensive performance review of a complex query involving multiple tables, subqueries, and window functions.
Prompt:
I have a complex query that runs in 15 seconds. It joins 6 tables, uses window functions, and has a subquery. Here is the full query and the EXPLAIN ANALYZE output:
[Paste]
Please provide a complete optimization roadmap:
1. Identify the most expensive nodes in the plan.
2. Suggest schema changes (indexes, partitioning, generated columns).
3. Rewrite parts of the query to be more efficient.
4. Consider if the logic can be simplified (e.g., pre-aggregation).
5. Provide a step-by-step implementation plan, with expected impact for each step.
Example Result:
"The Sort node is taking 5 seconds. Adding an index on the sort key could eliminate it. The subquery on orders is scanning 10M rows; consider a covering index. I'd rewrite the window function to use a LATERAL join..."
Putting It All Together: A Case Study
Let me show you how combining a few of these prompts can rescue a real query. Imagine a typical e-commerce dashboard that pulls top customers by revenue:
SELECT c.customer_id, c.name, SUM(o.total) AS revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.created_at BETWEEN '2026-01-01' AND '2026-09-01'
GROUP BY c.customer_id, c.name
ORDER BY revenue DESC
LIMIT 20;
It's slow. Using Prompt #1 (Execution Plan Interpreter), you paste the plan and learn it's doing sequential scans on orders. Prompt #3 (Missing Index Detector) suggests CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at); and a covering index for revenue. Prompt #7 (Query Rewrite Doctor) might suggest using a LATERAL join if you only need top N per customer, but here it's simple. After adding indexes, you re-run the plan and find a Sort node. Prompt #15 (End-to-End Optimizer) suggests an index on the GROUP BY columns. The result: the query drops from 8 seconds to 150 ms.
Your Turn: From Slow to Supercharged
These 15 prompts are your toolkit for PostgreSQL performance tuning. Start with the basic ones to understand your queries, then move to advanced for schema changes, and expert for environment-level tweaks. The key is to provide as much context as possible—execution plans, schema, and workload details—and to always verify the LLM's suggestions with your own testing.
Have you tried using AI for database optimization? What's your favorite prompt? Share it in the comments below—let's build a library of battle-tested optimizations together. And if you need a structured learning path, check out Asibiont's SQL and PostgreSQL courses to deepen your expertise.
Comments