SQL Query Optimization

Fix a slow query the disciplined way: read the real execution plan, change one thing, re-measure, no index guessing without evidence.

Optimize this slow query. Work from evidence, not pattern-matching. Do not propose any change until you've read the actual plan.

The query:
"""
[PASTE THE SQL]
"""

Execution plan, run and paste: EXPLAIN (ANALYZE, BUFFERS) for Postgres, EXPLAIN ANALYZE for MySQL:
"""
[PASTE THE FULL PLAN OUTPUT]
"""

Schema context: table definitions + existing indexes for every table involved ([PASTE DDL or \d output]), approximate row counts per table, and how often this query runs + current vs. acceptable latency.

Work the loop:
1. **Read the plan first.** Identify the dominant cost node and name the mechanism: sequential scan on a large table, nested loop explosion, sort spilling to disk, hash join over-memory, or a row-estimate that's wildly off (compare estimated vs. actual rows: a big gap means stale statistics, and no index fixes bad estimates).
2. **Diagnose against the classics:** missing composite index matching the WHERE + ORDER BY together, non-sargable predicate (function wrapped around an indexed column, implicit type cast), SELECT * dragging columns that block an index-only scan, OFFSET pagination scanning everything it skips (propose keyset pagination), OR conditions defeating index use, correlated subquery that should be a join.
3. **Propose ONE change**: the single highest-leverage fix: an index (exact CREATE INDEX statement, column order justified), a query rewrite (full rewritten SQL, semantically identical; state explicitly if NULL handling or duplicate behavior could differ), or a statistics fix (ANALYZE / statistics target). Explain the mechanism: why THIS change collapses THAT cost node.
4. **Predict and verify:** what the new plan should show, then I'll re-run EXPLAIN ANALYZE and paste it. Compare actual vs. predicted before proposing change #2: one variable at a time, like any experiment.

Rules: never claim an index helps without tying it to a specific plan node. State the write-cost of any proposed index (every INSERT/UPDATE pays for it). If the plan shows the query is already reasonable and the problem is elsewhere (connection pool, lock contention, N+1 at the app layer), say that instead of optimizing the wrong thing.

How to use

The paste-the-real-plan requirement is what separates this from index roulette: research on LLM query optimization consistently shows models propose plausible-but-wrong fixes without the plan, and become genuinely effective inside a measure-change-remeasure loop. Keep the loop going: paste the new plan after each change. For MySQL, EXPLAIN ANALYZE (8.0+) gives the equivalent actual-vs-estimated data.

More coding prompts

Write the commit message for this change.

Diff:
"""
[PASTE THE STAGED DIFF]
"""

Why I made this change: [THE REASON, THE TICKET, THE BUG REPORT, or "you infer it"]
Convention: [Conventional Commits / this repo's existing style, pasted below / plain]
Recent commits from this repo, to match style:
"""
[PASTE 5-10 RECENT COMMIT SUBJECT LIN

Commit Message

Write a commit message that explains why the change was made, in Conventional Commits format, split into separate commits when needed.

Codingbeginner

Help me recover from a git mistake without making it worse.

What I was trying to do: [THE GOAL]
What I ran: [THE EXACT COMMANDS, IN ORDER]
What happened instead: [THE OUTPUT OR THE STATE NOW]
Has this been pushed or shared: [YES/NO, and to which branch and whether anyone else has pulled]
Uncommitted work I cannot lose: [WHAT AND WHERE, o

Undo a Git Mistake

Recover from a bad commit, force push, wrong branch, or lost work with a reversible plan and the exact commands, explained before you run them.

Codingintermediate

Handler code, routes, and models:
"""
[PASTE THE ROUTE DEFINITIONS, HANDLERS, REQUEST AND RESPONSE TYPES, VALIDATION SCHEMAS, AND MIDDLEWARE]
"""

Generate an OpenAPI 3.1 specification from the code above.

API name, version, and base URL: [DETAILS]
Auth scheme: [BEARER JWT / API KEY / OAUTH / SESSION COOKIE, and where it is enforced]
Con

OpenAPI Spec From Code

Generate an accurate OpenAPI 3.1 spec from handler code, including error responses and auth, with gaps flagged instead of invented.