# Speed up a slow SQL query from its EXPLAIN ANALYZE plan

> Paste a slow query, its plan and indexes. Get the costliest step named, rewrites and index changes ranked by risk, and a same-results check.

- **Author:** [Thabo Mokoena (@thabo_mokoena)](https://promptabide.com/thabo_mokoena)
- **Tested on:** Claude · Opus 5.5
- **You fill in:** `database`, `query`, `schema`, `plan`
- **Published:** 2026-08-13
- **Updated:** 2026-09-24
- **Tags:** `coding`, `data-analysis`, `web-development`
- **Keywords:** optimize slow sql query with ai, explain analyze postgres prompt, why is my postgres query doing a seq scan, sql index recommendation prompt, make sql query faster without changing results
- **Views:** 799
- **Likes:** 12

**Best for:** Backend developers and analysts with one slow Postgres or MySQL query and the plan to go with it.

## Prompt

```
This query is slow. Help me make it fast without changing its results.

Database: {{database}}
Query:
{{query}}

Tables, sizes, indexes and constraints:
{{schema}}

EXPLAIN ANALYZE output:
{{plan}}

1. Name the single most expensive step and quote the line from the plan that shows it (actual time and rows, not just cost).
2. Explain why the planner chose it: missing index, non-sargable predicate, bad row estimate, sort spill, or something else.
3. Propose changes, least invasive first: query rewrite, then index changes, then schema changes. For each, give the exact SQL, the plan shape you expect afterwards, and the write or storage cost.
4. Prove the rewrite returns the same rows. Call out every NULL, duplicate, case-sensitivity or timezone edge case where it might not, and what I'd need to check.
5. Tell me exactly what to run to verify the improvement, and what number I should expect to see.

If the plan doesn't contain enough information to be sure, say what extra output you need.
```

## Variables

- `{{database}}` — Database engine and version (e.g. PostgreSQL 16)
- `{{query}}` — The slow query, exactly as it runs (e.g. SELECT ... FROM orders JOIN customers ... ORDER BY created_at DESC LIMIT 50)
- `{{schema}}` — Table sizes, relevant columns, existing indexes and any constraints (e.g. orders 18M rows, indexes on customer_id and created_at)
- `{{plan}}` — Full EXPLAIN (ANALYZE) output (e.g. The plan text from psql)

## Example input

````
database: PostgreSQL 16
query:
```sql
SELECT o.id, o.created_at, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE lower(o.status) = 'paid'
  AND date(o.created_at) >= '2026-09-01'
ORDER BY o.created_at DESC
LIMIT 50;
```
schema: orders: 18M rows; created_at timestamptz; status text (values seen: paid, pending, refunded, failed). Indexes: PK id, btree (customer_id), btree (created_at). customers: 900k rows, PK id.
plan:
```
Limit  (actual time=6421.3..6421.4 rows=50 loops=1)
  ->  Sort  (actual time=6421.3..6421.3 rows=50 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  Hash Join  (actual time=402.1..6188.9 rows=412877 loops=1)
              Hash Cond: (o.customer_id = c.id)
              ->  Seq Scan on orders o  (actual time=0.03..5410.7 rows=412877 loops=1)
                    Filter: ((lower(status) = 'paid'::text) AND (date(created_at) >= '2026-09-01'::date))
                    Rows Removed by Filter: 17587123
              ->  Hash  (actual time=398.2..398.2 rows=900000 loops=1)
Planning Time: 0.4 ms
Execution Time: 6423.0 ms
```
````

## Output

*Produced by Claude · Opus 5.5.*

````
**1. Most expensive step**
```
Seq Scan on orders o  (actual time=0.03..5410.7 rows=412877 loops=1)
  Rows Removed by Filter: 17587123
```
That's 5.4 s of the 6.4 s total, reading all 18M rows to keep 413k. Building a hash of all 900k customers adds another 0.4 s, just to return 50 rows.

**2. Why**
Both predicates wrap the column in a function. `lower(status)` and `date(created_at)` can't use the plain btree on `created_at`, and `date()` on a `timestamptz` isn't even indexable, because its result depends on the session's `TimeZone`. With no usable index, the planner can't walk `created_at` backwards and stop after 50 rows. It has to filter everything, join everything, then sort.

**3. Changes, least invasive first**

*a) Rewrite the predicates (free):*
```sql
WHERE o.status = 'paid'
  AND o.created_at >= '2026-09-01'::timestamptz
```
On its own, this lets Postgres walk the `created_at` index backwards, filtering on status as it goes. That's fast while paid orders are common among recent rows.

*b) Partial index (small write cost, only for paid rows):*
```sql
CREATE INDEX CONCURRENTLY orders_paid_created_at_idx
  ON orders (created_at DESC) WHERE status = 'paid';
```
Expected plan: `Limit → Nested Loop → Index Scan using orders_paid_created_at_idx` plus 50 PK lookups on customers. No hash, no sort. Storage is roughly proportional to the number of paid rows. `CONCURRENTLY` avoids blocking writes while it builds.

No schema change needed.

**4. Same rows?**
- **Timezone:** `date(created_at) >= '2026-09-01'` means "on or after midnight 1 Sept in the session TimeZone". `'2026-09-01'::timestamptz` is also interpreted in the session TimeZone, so the two are equivalent as long as both run in the same session setting. Don't hard-code `+00` unless the old query always ran in UTC.
- **Case:** dropping `lower()` is only safe if no row has `'Paid'` or `'PAID'`. Check with `SELECT DISTINCT status FROM orders;`. Better, add `CHECK (status = lower(status))` so it stays true.
- **NULLs:** rows with NULL `status` or `created_at` were excluded before and are still excluded.
- **Ties:** equal `created_at` values can come back in a different order at the LIMIT boundary. Add `, o.id DESC` if order must be stable.

**5. Verify**
```sql
EXPLAIN (ANALYZE, BUFFERS) <new query>;
```
Expect an Index Scan on the new index, `rows=50` at the top, and execution in single-digit milliseconds. Compare `shared read` buffers against the old plan.
````

## Why it works

Asking for **the quoted plan line with actual time and rows** anchors the answer in evidence instead of generic "add an index" advice. The **least invasive first** ordering keeps a free rewrite ahead of a new index. The step that sets this prompt apart is **"prove the rewrite returns the same rows"**. Index-friendly rewrites often change results quietly: case folding, timezone boundaries, tie order at a LIMIT. Here it caught all three. Ending with **the number to expect** tells you when you're done.

## When not to use it

It won't diagnose problems that don't show in a single plan: lock contention, connection pool exhaustion, bloat, or a query that's fast alone but slow under load. Use `pg_stat_statements` and wait-event data for those. Without `EXPLAIN ANALYZE` output (only the query), expect educated guesses. Always test index builds on a staging copy of production-sized data.

---

Canonical HTML: https://promptabide.com/bides/optimize-slow-sql-query-from-explain-plan
Agent guide: https://promptabide.com/llms.txt · https://promptabide.com/agent-instructions.md
Sitemap: https://promptabide.com/sitemap.xml