0
Diagnose and rewrite a slow SQL query with execution-plan reasoning
⁂auto-checked, 4 days oldAauraNovice
The prompt
This query is too slow. I'll give you the query, the schema (with indexes), and the EXPLAIN/EXPLAIN ANALYZE output. Do this:
1. Read the execution plan and identify the specific operation that dominates cost (seq scan, nested loop, sort spill, etc.), quoting the relevant line from the plan.
2. Explain in plain terms why the planner is choosing that path given the current indexes and statistics.
3. Propose the smallest change that would fix it, ranked: (a) query rewrite with no schema change, (b) new index, (c) schema change, in that order of preference, and say why you're not jumping straight to an index if a rewrite would do.
4. Show the rewritten query or CREATE INDEX statement.
5. Predict how the plan should change after your fix, and tell me what to check in the new EXPLAIN output to confirm it worked.
6. Flag if the fix could hurt a different query that shares the same table (e.g. a new index that slows down writes on a hot table).
Database: {{database, e.g. Postgres 15}}
Query: query
Schema/indexes: schema
EXPLAIN output: explain_output
Where it has been run
| Model | Version | People | Broke | Auto | Last |
|---|---|---|---|---|---|
| Claude | Haiku 4.5 | 0 | 0 | 1/1 | 4 days ago |
Example output
The plan shows `Seq Scan on orders (cost=0.00..48213.00 rows=1200000) Filter: (status = 'pending')` as the dominant cost, 94% of total runtime. This happens because there's no index on `status`, and with 1.2M rows the planner correctly avoids a bad index if one existed with poor selectivity, but here there simply isn't one.
Rewrite first: the query also does `ORDER BY created_at LIMIT 20` after the filter, so a composite index beats a single-column one.
Fix: `CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC) WHERE status = 'pending';` (partial index, since 'pending' is a small fraction of rows).
Expected new plan: `Index Scan using idx_orders_status_created`, cost should drop by roughly 95%. Check that the new EXPLAIN shows an Index Scan, not Bitmap Heap Scan falling back to seq scan, and confirm `status='pending'` selectivity stayed low so the partial index stays effective. Caution: this table has heavy insert traffic, a partial index keeps write overhead lower than a full index would.
0 comments
Sign in to comment or report what this prompt did for you.
Sign inNo comments yet. Run the prompt and report what you got.