12 SQL Prompts That Turn EXPLAIN ANALYZE Into a Superpower (PostgreSQL Edition)
Every developer has faced a dashboard that loads for 20 seconds, a nightly ETL job that suddenly takes hours, or a migration that locked a production table during peak traffic. PostgreSQL is powerful, but its planner can be opaque, and manual optimization often feels like guesswork. This article collects 12 structured prompts that act as a senior DBA sitting next to you. Each prompt is designed to be pasted into any capable LLM, with your schema and query details, to get concrete, executable advice: rewritten SQL, index definitions, migration scripts, and diagnostic queries.
The prompts are grouped by difficulty: basic (understanding plans and simple rewrites), advanced (indexing strategy, schema audits, test data generation), and expert (zero-downtime migrations, lock debugging, and stress testing). For every prompt you get the task, the exact wording to use, and an example of what a good result looks like. All examples reference real PostgreSQL syntax and system views, so you can verify them in psql.
Before you start, make sure you can run EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) on your queries. The official PostgreSQL documentation (postgresql.org/docs/current/using-explain.html) explains every node type. The prompts below assume you have that output handy.
Basic: Understand What the Planner Is Doing
1. Explain an EXPLAIN ANALYZE plan in plain English
Task: You have a JSON plan but no idea which node is the bottleneck.
Prompt:
You are a PostgreSQL performance engineer. I will give you the output of EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON). Identify the most expensive node by total time and by rows removed. Explain in plain English what the planner did, which statistics are missing, and suggest two concrete actions. Schema: [paste CREATE TABLE statements]. Plan: [paste JSON].
Example result: The model points to a Seq Scan on orders with 1.2M rows filtered down to 300, and notes that actual rows is 4000× the estimated rows, indicating stale statistics. It suggests running ANALYZE orders; and creating an index on (customer_id, created_at).
2. Rewrite a correlated subquery as a JOIN
Task: A subquery in the SELECT list runs once per row.
Prompt:
Rewrite the following query to eliminate the correlated subquery. Use a LEFT JOIN LATERAL or a window function where appropriate. Show the original and the rewritten version, and explain which plan node disappears. Query: [paste SQL].
Example result: The model converts (SELECT max(paid_at) FROM payments p WHERE p.order_id = o.id) into a LEFT JOIN LATERAL (SELECT max(paid_at) ...) p ON true, reducing the plan from a SubPlan to a single Hash Join.
3. Detect implicit casts that kill index usage
Task: An index exists but is not used because of type mismatch.
Prompt:
Given the table definition and the WHERE clause below, list every implicit cast that prevents index usage. For each, show the exact expression that would use the index. Table: [paste]. Query: [paste].
Example result: The model finds that WHERE user_id = '123' compares uuid to text, forcing a cast on the column. It recommends WHERE user_id = '123'::uuid.
Advanced: Indexes, Schema, and Data
4. Generate the right index for a specific WHERE + ORDER BY
Task: You need an index that covers both filtering and sorting.
Prompt:
Propose the minimal set of indexes for the following query. Consider composite order, partial indexes, and INCLUDE columns. Show CREATE INDEX statements and the expected plan change. Query: [paste]. Table: [paste].
Example result: For WHERE status = 'active' AND created_at > now() - interval '7 days' ORDER BY created_at DESC LIMIT 50, the model suggests CREATE INDEX CONCURRENTLY idx_orders_active_recent ON orders (created_at DESC) WHERE status = 'active'; and notes it avoids a sort node.
5. Audit schema normalization and missing constraints
Task: You inherited a database with no foreign keys.
Prompt:
Review the schema below. Identify missing foreign keys, redundant columns, and normalization violations (1NF–3NF). For each issue, provide the ALTER TABLE statement and a note on the risk of adding it online. Schema: [paste].
Example result: The model flags orders.customer_email duplicating customers.email, suggests a foreign key on orders.customer_id, and warns to add it as NOT VALID first to avoid a full table scan lock.
6. Generate realistic test data with generate_series
Task: You need 1M rows for a load test.
Prompt:
Write a PostgreSQL script using generate_series and random() to populate the tables below with 1,000,000 rows, respecting foreign keys. Include realistic distributions for status and timestamps. Tables: [paste].
Example result: The model outputs an INSERT INTO orders SELECT ... FROM generate_series(1, 1000000) with random() for amounts and a skewed status distribution using CASE.
7. Find unused and duplicate indexes
Task: Your database has 200 indexes and slow writes.
Prompt:
Using pg_stat_user_indexes and pg_index, write a query that lists indexes with zero scans and indexes that duplicate a prefix of another index. Explain how to drop them safely. Schema: [paste].
Example result: The model provides a query joining pg_stat_user_indexes with pg_index and suggests DROP INDEX CONCURRENTLY during low traffic, after checking pg_stat_all_indexes.idx_scan = 0.
Expert: Zero-Downtime Migrations and Locks
8. Design a zero-downtime migration
Task: Add a NOT NULL column to a 50GB table without blocking writes.
Prompt:
Write a step-by-step zero-downtime migration for the change below. Use ALTER TABLE ... ADD COLUMN, backfill in batches, and SET NOT NULL only after validation. Include lock modes and estimated duration. Change: [paste]. Table size: [X GB].
Example result: The model returns four steps: add column nullable, backfill with UPDATE ... WHERE id BETWEEN ... in batches, add a CHECK (col IS NOT NULL) NOT VALID, validate, then SET NOT NULL. It notes that ADD COLUMN with a constant default is metadata-only since PostgreSQL 11.
9. Debug a deadlock from pg_locks and pg_stat_activity
Task: You see deadlock detected in logs.
Prompt:
Given the output of pg_locks and pg_stat_activity below, identify the blocking chain and the exact statements causing the deadlock. Suggest a consistent lock ordering or a retry strategy. Output: [paste].
Example result: The model shows two transactions acquiring locks on accounts and transfers in opposite order, and recommends always updating accounts before transfers or using SELECT ... FOR UPDATE in a fixed order.
10. Write a stress test with pgbench
Task: You want to simulate 200 concurrent users.
Prompt:
Create a custom pgbench script that mimics the following workload: 70% SELECT, 20% UPDATE, 10% INSERT. Include a command to run it with 200 clients for 5 minutes and how to interpret the latency report. Query mix: [paste].
Example result: The model outputs a .sql file with \set variables and the command pgbench -c 200 -j 4 -T 300 -f workload.sql mydb, plus notes on reading latency average and tps.
11. Tune autovacuum for a high-churn table
Task: A table bloats because autovacuum never catches up.
Prompt:
Given the table below and its update rate, propose per-table autovacuum settings. Show ALTER TABLE statements and explain the trade-off between vacuum cost and write amplification. Table: [paste]. Update rate: [X rows/min].
Example result: The model suggests ALTER TABLE hot_table SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_limit = 1000); and warns about I/O impact.
12. Convert a slow OFFSET pagination to keyset pagination
Task: Page 10,000 is unusably slow.
Prompt:
Rewrite the following OFFSET-based pagination query to keyset pagination using a stable sort key. Show the new query and the index it needs. Query: [paste].
Example result: The model replaces OFFSET 100000 LIMIT 20 with WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20 and adds an index on (created_at DESC, id DESC).
These 12 prompts cover the full lifecycle of SQL performance work: from reading a plan to shipping a migration without downtime. They are not magic; they require you to paste accurate schema and query text. But used consistently, they compress hours of trial and error into minutes. Start with prompt #1 on your slowest query today, then work through the list. Your future self, woken up by a 3 AM deadlock alert, will thank you.
For deeper reading, see the official PostgreSQL documentation on EXPLAIN, pg_stat_statements, and lock monitoring. All examples above use syntax valid for PostgreSQL 14 and later.
Comments