The slow query that brings down production almost never arrives with a clear error message. It arrives as a p95 latency graph creeping upward at 3 a.m., and by morning the on-call engineer is staring at a 40-line query with four JOINs and no idea which one is eating the buffer cache. The LLM in your editor can be a genuinely useful DBA here — but only if you stop asking it "optimize this query" and start feeding it the right artifacts: the plan, the schema, the row counts, the indexes.
Below are 14 prompts I actually reuse. Each one assumes PostgreSQL unless stated otherwise, because the plan output format and catalog tables differ across engines and mixing them produces confident nonsense.
The foundation: what to feed the model before any prompt
A prompt without context gets you a generic answer. Before pasting any of these, attach three things:
- The DDL of every table in the query, including indexes and constraints.
- The output of
EXPLAIN (ANALYZE, BUFFERS), not plainEXPLAIN— the planner's estimate without actual timing tells you nothing about where reality diverges. - Rough row counts per table (
SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';gives the planner's own estimate).
EXPLAIN ANALYZE on a write query in production without wrapping it in BEGIN; ... ROLLBACK;. The statement actually executes.
The 14 prompts
1. Plan interpreter for beginners
Prompt: "Here is my EXPLAIN (ANALYZE, BUFFERS) output and the query. Walk me through the plan node by node: what each node does, where estimated rows diverge from actual rows by more than 10x, and which node accounts for the largest share of total time. Do not suggest rewrites yet — diagnose first."
Why it works: separating diagnosis from prescription stops the model from jumping to a rewrite before it understands the bottleneck. The 10x divergence threshold is the real signal — that is where the planner's statistics are lying.
2. The estimate-vs-reality audit
Prompt: "Compare rows= estimates with actual rows in each node. List every node where the ratio exceeds 5x in either direction. For each, tell me the likely cause: stale statistics, correlated columns, a function call the planner cannot estimate, or a missing index. Suggest the exact ANALYZE or CREATE STATISTICS command to fix it."
Correlated columns (city + postal code, for example) are the classic case where the planner multiplies selectivities and badly underestimates. Extended statistics exist precisely for this.
3. Index advisor with a cost caveat
Prompt: "Given these queries and this schema, propose indexes that would help. For each index, state which query it serves, the estimated selectivity, and what write path it slows down. Rank by benefit-to-cost ratio. Do not propose an index on a column with fewer than 100 distinct values unless it is a partial index with a stated predicate."
Trade-off: every index speeds reads and taxes every INSERT, UPDATE, and DELETE. A model that never mentions write cost is not doing DBA work.
4. JOIN refactoring
Prompt: "This query has four JOINs. For each, tell me whether it is filtering (reduces rows) or enriching (adds columns). Then rewrite so filters happen before enrichment, and check whether any JOIN can become a semi-join (EXISTS) or be eliminated by denormalization. Show the before/after plan."
5. EXISTS vs IN vs JOIN
Prompt: "Compare these three formulations for the same intent on PostgreSQL: IN (subquery), EXISTS (subquery), and INNER JOIN. Explain the NULL-handling difference, which one the planner treats as a semi-join, and which is safest when the subquery column is nullable."
NOT IN with a NULL in the subquery returns zero rows — a silent correctness bug, not a performance one.
6. Window function tutor
Prompt: "Rewrite this correlated subquery using a window function. Show the PARTITION BY and ORDER BY you chose and why. Then tell me the case where the window version is slower than the correlated version."
7. N+1 detector for ORM output
Prompt: "Here is my ORM's generated SQL log. Group statements by shape, count repetitions per request, and identify N+1 patterns. For each, propose the eager-load fix at the ORM level and the equivalent single SQL query."
8. The pagination trap
Prompt: "My query uses LIMIT 20 OFFSET 40000 and is slow. Explain why offset pagination degrades, then rewrite using keyset pagination on (created_at, id). Show the index that makes it work and the edge case when rows are inserted between pages."
9. Lock and deadlock reader
Prompt: "Here is a deadlock report from pg_locks / the server log. Identify the two transactions, the lock types, and the access order. Propose the minimal change to transaction ordering that removes the cycle."
10. Migration safety review
Prompt: "Review this migration for lock risk. Flag any ALTER TABLE that takes an ACCESS EXCLUSIVE lock, any ADD COLUMN ... DEFAULT that rewrites the table, and any index built without CONCURRENTLY. Give the safe rewrite for each."
11. Query-to-test harness
Prompt: "Write a pgTAP-style test that asserts this query returns the expected shape and row count against a fixture. Include a fixture with NULLs and one with duplicate keys — those are where the query breaks."
12. The EXPLAIN diff prompt
Prompt: "Here are two plans: before and after my change. Diff them node by node. Did the change reduce total time, or just move cost to a different node? Is the improvement real or noise given the actual row counts?"
13. Parameterized-query cache trap
Prompt: "This query is fast with a literal value and slow with a bind parameter. Explain generic vs custom plans in PostgreSQL, and show how plan_cache_mode and PREPARE interact with my case."
14. Schema smell test
Prompt: "Review this schema for normalization issues, missing foreign keys, and columns that should be enums or lookup tables. Flag anything that will make future queries harder, but do not suggest premature denormalization."
Where the model fails you
The failure modes are consistent. The model will:
- Invent index names that do not exist in your schema — always verify against
\d table. - Assume MySQL syntax for
EXPLAINwhen you meant PostgreSQL — state the engine in every prompt. - Recommend indexes without write-cost analysis unless you explicitly ask for it.
- Confidently misread a plan when the divergence is subtle, like a bitmap heap scan on a low-selectivity filter.
A note on where this fits
These prompts pair naturally with a code-review workflow — the same discipline of feeding context before asking for judgment applies to catching bugs before the merge button. If you are building AI-assisted workflows more broadly, the family-budget guide shows the same pattern of scoping inputs.
Bottom line
The LLM is not a DBA. It is a very fast, very literal reader of plans and schemas — and it is only as good as the artifacts you hand it. Give it EXPLAIN (ANALYZE, BUFFERS), the DDL, and the row counts, and it will spot the divergence you missed. Skip those, and it will confidently rewrite a query that was never the problem. Start with prompt 1 and 2 today; the rest follow once you trust the diagnosis.