What this prompt does
This prompt casts the AI as a senior database engineer and makes it optimise a specific slow query rather than recite generic tuning advice. You provide [db_engine], [row_scale], and the query plus its EXPLAIN plan via [query], and it returns a stage-by-stage reading of the plan, the cost hotspots, exact CREATE INDEX statements, a rewritten query, the new expected plan, and an estimated improvement factor — all without changing the logical result set.
The structure works because it forces diagnosis before prescription. By asking the model to read the existing EXPLAIN plan in plain English first, then name the hotspots (seq scans, nested loops, spills, bad cardinality estimates), the prompt grounds every fix in what the plan actually shows. [db_engine] matters because index syntax and planner behaviour differ across engines, and [row_scale] shapes whether an index is worth its write cost. The final step's note on locking and write-amplification keeps the rewrite honest about trade-offs.
When to use it
- A dashboard or list page feels broken because one query scans a huge table.
- You have an EXPLAIN ANALYZE plan but are not sure which stage is the real bottleneck.
- You want concrete index DDL, not a lecture about adding indexes in general.
- You are tempted to reach for caching and want to try a real index fix first.
- You need to confirm a rewrite returns the identical result set before shipping it.
- You want the locking and write cost of a new index spelled out before you add it.
Example output
You get a numbered analysis: a plain-English walkthrough of the current plan, a list of cost hotspots, the proposed CREATE INDEX statements, the rewritten query (using CTEs, window functions, or a better join order), the new expected plan with the cost moved, and an estimated improvement factor with its locking caveats. It reads as a worked optimisation, not a tip sheet.
Pro tips
- Paste the full EXPLAIN ANALYZE output into
[query], not just the SQL — the model can only find hotspots it can see in the plan. - Set
[db_engine]to the exact version; index features and planner behaviour shift between releases, and the DDL has to match. - Use
[row_scale]honestly — an index that pays off at 50M rows may be overkill on a small table, and the model weighs that. - Always verify the rewrite returns the same rows before and after; a faster query that silently drops rows is a bug, not a win.
- Treat the estimated improvement factor as a hypothesis, then confirm it with a real EXPLAIN ANALYZE on your own data.
- If the rewrite adds an index, read the write-amplification note before applying it on a write-heavy table.
