Optimize a Slow SQL Query Using Its EXPLAIN Plan Step by Step
Paste a slow query and its EXPLAIN output to get the costliest plan node named, exact CREATE INDEX or rewrite fixes, and a clear way to verify the speedup.
You getDiagnosis, costly nodes table and fixes ranked by expected impact
Prompt
Act as a senior database engineer who tunes production SQL for a living.
I will paste a slow SQL query and its EXPLAIN or EXPLAIN ANALYZE output right after this message. If I have not said which database engine and version I use, or how often the query runs, ask me those 2 questions in one message and wait for my answer.
Your job: find why the query is slow and give me changes I can test today.
Rules:
1. Name the single most expensive node in the plan, quote its line, and explain in one sentence why it costs so much.
2. Use only row counts, costs and timings that appear in the plan. If a number is missing, say so instead of estimating it.
3. Write every proposed index as a complete CREATE INDEX statement and justify the column order.
4. Every rewrite must return the same rows as the original. Point out any case where results could differ, such as NULLs or duplicates.
5. For each new index, note the write overhead and any lock risk during creation.
Output format:
1. Diagnosis (2-4 sentences)
2. Costly nodes table: Node | Plan line | Estimated vs actual rows | Why it hurts
3. Fixes ranked by expected impact: change, SQL, trade-off
4. How to verify: exact commands to rerun, which numbers should drop, and what counts as a real improvement
5. Open questions
End with one line on your assumptions and what the plan did not show.Example output
Tips and common edits
Why this works
- Forcing it to quote the costly plan line keeps the diagnosis tied to your real plan instead of generic tuning advice.
- Banning invented row counts stops the model from guessing numbers and makes it flag gaps in your EXPLAIN output.
- The verify step asks for buffers and median timings, so you can tell a real gain from a warm cache.
Use when a query is slow in dev or production. Have the full query and EXPLAIN ANALYZE output ready (with BUFFERS if on PostgreSQL), then paste both into ChatGPT, Claude or Gemini.
Common edits
- Name your engine up front, such as MySQL 8 or SQL Server, to skip the opening questions.
- Add a rule that indexes are off-limits so it proposes query rewrites only.
Prompt details
- By
- Prompts-GPT editors
- Published
Did this prompt work?
Keep exploring
More like this
Find and Fix Data Quality Issues with Ready-to-Run SQL Checks
Paste your schema and get SQL queries that catch duplicates, NULLs, orphaned foreign keys and out-of-range values, each with what a bad result means and a fix.
Translate SQL Between MySQL, PostgreSQL and SQL Server with a Change Table
Returns your query rewritten for the target database, plus a table flagging every function, date, quoting and LIMIT change and where behavior could differ.
SQL Query from Schema and Question
Writes a SQL query that answers your question from your schema, explains each clause and suggests indexes worth adding.
Design a Normalized Database Schema from a Plain Feature Description
Turns a plain-language feature description into a normalized relational schema with typed columns, keys, constraints, ready-to-run CREATE TABLE SQL, example queries and trade-offs.
Explain a Complex SQL Query in Plain English, Step by Step
Get a plain-English walkthrough of any SQL query in execution order, with tiny sample tables, row counts after each clause, and the hidden gotchas.
Explain Any Regex Token by Token with Matching and Failing Examples
Get a token-by-token table explaining your regex, plus strings that match, nearly match and fail, and a clear catastrophic backtracking risk check with a safer rewrite.