Optimize a slow SQL query

Find why a query is slow and get a faster version, index suggestions and a way to prove the improvement.

Change note: Initial version

Prompt

Help me speed up a slow {{dialect}} query.

Query:
{{query}}

Execution plan (EXPLAIN / EXPLAIN ANALYZE output), if I have it:
{{plan}}

Table sizes and existing indexes:
{{tables}}

How slow it is and what is acceptable: {{target}}

Please:
1. Diagnose: explain in plain words what the database is doing and where the time goes. If a plan is provided, cite specific nodes (seq scans, sorts, nested loops, bad row estimates). If not, say what you suspect and ask me to run EXPLAIN ANALYZE, showing the exact command.
2. Rank the fixes by expected impact: query rewrite, new or changed indexes, schema or statistics changes, and application-side changes such as pagination or caching.
3. Show the rewritten query and the exact CREATE INDEX statements, with the column order justified.
4. Warn about tradeoffs: write cost of new indexes, locking during creation, and whether the rewrite returns exactly the same rows.
5. Give me a way to verify: what to compare before and after.

Do not suggest rewrites that change the results unless you flag it clearly.

Variables

• {{dialect}}SQL dialectselect

  Options: PostgreSQL, MySQL, SQL Server, SQLite, BigQuery, Snowflake, Oracle

  Default: PostgreSQL

• {{query}}Slow querylongrequired

  Paste the SQL.

• {{plan}}Execution planlong

  Output of EXPLAIN ANALYZE if you have it.

• {{tables}}Table sizes and indexeslong

  Default: orders: ~40M rows, index on id only customers: ~2M rows, index on id

• {{target}}Targettext

  Default: Currently 12 seconds, needs under 500 ms

Example output

**Diagnosis:** No plan was provided, so this is a suspicion, not a measurement. With only `id` indexed on `orders` (about 40M rows), a query that filters on `status` and a date range almost certainly does a sequential scan of the whole table, then sorts the matches for `ORDER BY created_at DESC LIMIT 50`. Twelve seconds is consistent with reading tens of millions of rows. Please run this and send me the output:
```sql
EXPLAIN (ANALYZE, BUFFERS) SELECT o.id, o.total_cents, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'paid' AND DATE(o.created_at) = '2026-09-01' ORDER BY o.created_at DESC LIMIT 50;
```

**Fixes by impact**
1. Index (high):
```sql
CREATE INDEX CONCURRENTLY idx_orders_status_created ON orders (status, created_at DESC) INCLUDE (customer_id, total_cents);
```
Equality column first, then the range and sort column, so rows come out already ordered and the LIMIT stops early. INCLUDE allows an index-only scan.
2. Rewrite (medium): `DATE(created_at)` hides the column from the index. Use a range instead:
```sql
WHERE o.status = 'paid' AND o.created_at >= '2026-09-01' AND o.created_at < '2026-09-02'
```
Same rows returned.
3. Application (low): paginate by keyset (`created_at < :last_seen`) instead of OFFSET.

**Tradeoffs:** the index adds write cost to every insert and roughly 2-3 GB of disk. CONCURRENTLY avoids blocking writes but takes longer and must not run inside a transaction.

**Verify:** compare plan nodes (Seq Scan should become Index Scan), execution time and buffers read before and after, on production-sized data, and confirm both queries return identical row counts.

Notes

Asking for the plan, and for the exact EXPLAIN command when it is missing, anchors advice in evidence rather than generic tips. Requiring same-result rewrites keeps optimizations safe.