Add

Coding & Software

Optimize a Slow SQL Query

Reads a slow query and its execution plan, explains where the time goes and proposes changes you can test one at a time.

Designed for general-purpose AI assistantsPublished November 2026

Customize

Fill in the fields and the prompt updates. What you type stays in this page and is never saved or sent.

Preview

Help me speed up a slow SQL query. Work from the evidence below, and tell me what you cannot tell from it.

Database and version: {{database and version}}
How slow it is and how fast it needs to be: slow, needs to be under one second
Approximate table sizes (rows): {{table sizes}}
How often it runs and whether it reads or writes: runs often, read only

Query:
{{query}}

Schema and existing indexes:
{{schema and indexes}}

Execution plan (EXPLAIN or EXPLAIN ANALYZE output):
not available

Respond in this order:
1. Where the time goes: point to the specific plan nodes or query clauses that are expensive, quoting row estimates versus actual rows if the plan shows both. If I gave no plan, say that any diagnosis is a hypothesis and tell me exactly which EXPLAIN command to run.
2. Ranked suggestions, each with: the change (an index, a rewrite, a filter pushed earlier, a join order or a smaller select list), the reason it should help, the cost (write overhead, storage, risk of a different plan), and how to test it.
3. The exact SQL for each suggestion, one at a time, so I can measure the effect of each separately.
4. What could make a suggestion backfire, such as stale statistics, low-selectivity columns or a table that is written to heavily.
5. How to confirm the improvement: which numbers to compare before and after, on realistic data.

Rules: do not promise a speedup you cannot justify from the plan. Do not suggest dropping indexes or changing data without telling me to check what else uses them. Test index changes on a copy of the data first.

4 required fields are empty and will stay as a {{placeholder}} in the copied text.

How to use it

  1. Run EXPLAIN ANALYZE on the real query with realistic data and paste the whole output. Without a plan, the best any assistant can do is guess.
  2. Include existing indexes. Half the time the right fix is making an existing index usable, not adding another.
  3. Apply and measure one suggestion at a time, on a copy of production-sized data, and report each result back.
  4. Mention how often the query runs and whether the table is written to. An index that speeds a nightly report may hurt a busy write path.

Example output

Illustrative only. Real answers depend on your input and the assistant you use.

Where the time goes (assuming PostgreSQL, from your input): The plan shows a sequential scan on orders (est. 1,000 rows, actual 2,100,000) feeding a nested loop, which accounts for about 3.8 s of the 4.1 s total. The row estimate is far off, so statistics are probably stale.

Suggestions, ranked:
1. Run ANALYZE orders; to refresh the planner's statistics. Cost: it samples the table rather than reading all of it and does not block normal reads or writes (its SHARE UPDATE EXCLUSIVE lock does not conflict with them), but it adds some I/O while it runs. Test: rerun EXPLAIN ANALYZE and compare the estimate.
2. Add an index on orders (customer_id, created_at). Reason: the query filters on both columns and sorts by created_at. Cost: extra storage and slower inserts; CONCURRENTLY avoids blocking writes but takes longer and cannot run inside a transaction block. Test: rerun the query and compare time.

CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at);

What could backfire: if customer_id has few distinct values the planner may still prefer a scan.

Confirm: compare total time and rows scanned before and after, on a copy of production data, over several runs.

Tips

A fast result on an empty development database says nothing about production. Always test on data of realistic size and shape.

If the assistant suggests rewriting the query completely, verify that it returns exactly the same rows before you compare speed. A faster wrong answer is not an improvement.

Keep a record of each change and its measured effect. It makes the next slow query much quicker to diagnose.

Back to the Prompt Library

Related prompts

  • Coding & Software

    Write a SQL Query From Your Schema

    Writes a SQL query from a plain-English question and your table definitions, with assumptions listed and checks to verify the result.

    Customizable fields
    5 fields

  • Coding & Software

    Debug an Error Message

    Paste an error and the code around it to get ranked causes, the smallest fix and a quick check to confirm the diagnosis.

    Customizable fields
    5 fields

  • Coding & Software

    Debug a Flaky Test

    Finds why a test passes sometimes and fails other times, ranks the likely causes and gives an experiment to prove each one.

    Customizable fields
    8 fields