Diagnose and rewrite a slow SQL query using its plan

When a query is slow, guessing wastes time—the query plan already holds the answer if you can read it. This prompt acts as a performance partner: you paste the slow query, its EXPLAIN (or EXPLAIN ANALYZE) output, and the relevant table sizes and indexes, and it diagnoses the actual cause—sequential scans, a missing index, a bad join order, non-sargable predicates that defeat indexes, or row-estimate errors that mislead the planner. Then it rewrites the query to return identical results faster and recommends indexes, honestly including the write-time and storage cost of adding them. The key inputs are the real plan and accurate schema info; without the plan, any advice is speculation. Name the database engine, because plan formats and tuning options differ across Postgres, MySQL, and others. Use the 'what to re-measure' section to confirm the fix actually helped on production-like data rather than assuming it did—plans change with data volume and statistics.

What you can expect back

Diagnosis: The planner does a full Seq Scan over ~50M rows because `DATE(created_at)` wraps the column in a function—this is a non-sargable predicate, so no index on `created_at` can be used even if one existed.

Rewrite (same results, sargable):
```sql
SELECT * FROM orders
WHERE created_at >= '2026-06-01'
  AND created_at <  '2026-06-02';
```
This is a range over the raw column, which an index can serve.

Suggested index:
```sql
CREATE INDEX idx_orders_created_at ON orders(created_at);
```
Trade-off: speeds this and similar date-range queries, but adds write overhead on a high-insert table and ~1–2 GB of storage at 50M rows. If you usually filter by user too, consider (user_id, created_at) instead.

Re-measure: run EXPLAIN ANALYZE after indexing; confirm an Index/Bitmap scan replaces the Seq Scan and check actual vs. estimated rows.

Illustrative example — your results will vary by tool and inputs.

Get sharper results

• 01Provide EXPLAIN ANALYZE, not just EXPLAIN—actual vs. estimated rows reveals planner misestimates.
• 02Include real row counts; advice differs wildly between a 10k-row and a 50M-row table.
• 03Watch for SELECT * — fetching unused columns can defeat covering-index opportunities.
• 04Re-measure on production-like data; a fix that wins on a small table can lose at scale.

Adapt it for your case