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

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

At a glanceWhere the slow orders query spends its timePostgreSQL 15 dashboard query, from the EXPLAIN ANALYZE plan
Current query time
2.8 s
Runs about 40 times a minute
Rows read by Seq Scan
4,812,330
Whole orders table scanned
Rows discarded by filter
4,790,118
No index on status and created_at
Rows returned
50
Fix: composite index on (status, created_at DESC)

Example for: PostgreSQL 15, an orders dashboard query that runs about 40 times a minute and takes 2.8 s.

1. Diagnosis

The query filters orders by status and created_at, but no index covers those columns, so PostgreSQL scans the whole table. The scan reads 4,812,330 rows and discards 4,790,118 of them. A top-N sort then runs on the survivors, which is cheap only because the scan already paid the full cost.

2. Costly nodes
NodePlan lineEstimated vs actual rowsWhy it hurts
Seq Scan on ordersSeq Scan on orders o (cost=0.00..198442.10 rows=21540 ...) (actual time=0.031..2611.4 rows=22212 loops=1)21,540 vs 22,212Reads every page; 4.79M rows removed by filter
SortSort Key: o.created_at DESC ... Sort Method: top-N heapsort Memory: 41kBnot shown vs 22,212Minor; disappears if an index supplies the order
Hash Join to customersHash Join (actual time=2640.2..2702.9 rows=50)50 vs 50Fine; time is inherited from the scan
3. Fixes ranked by impact

A. Composite index (high impact)

CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC);

Equality column status first, then created_at DESC so the index returns rows already sorted and the LIMIT 50 stops early. Trade-off: every insert and status update writes one more index entry. CONCURRENTLY avoids blocking writes but takes longer and cannot run inside a transaction.

B. Rewrite the date filter (medium impact)
Original: WHERE date(o.created_at) >= '2024-05-01'
Rewrite: WHERE o.created_at >= '2024-05-01'
The function call blocks index use. Same rows returned if created_at is timestamp; if it is timestamptz, the boundary shifts by your session time zone, so confirm the type first.

4. How to verify
  1. Run EXPLAIN (ANALYZE, BUFFERS) on the original and save it.
  2. Apply Fix B, then Fix A, rerunning the same command after each.
  3. Look for: Index Scan using idx_orders_status_created, no Sort node, and shared read + shared hit buffers falling sharply.
  4. Run each version 5 times and compare the median execution time, not the first run, which may hit a cold cache.
  5. A real improvement is a lower median and lower buffer counts. Faster time with the same buffers usually just means a warm cache.
5. Open questions
  • Is created_at timestamp or timestamptz?
  • How many writes per second hit orders?

Assumptions: table statistics are current; the plan did not show index sizes or autovacuum state.

Written by AI from this prompt. Your results will vary.

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 in Coding