Thabo Mokoena

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.

At a glance

Best for: Backend developers and analysts with one slow Postgres or MySQL query and the plan to go with it.
Tested on: Claude · Opus 5.5
You fill in: databasequeryschemaplan
You get: Most expensive step 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,… (full result below)

coding

data-analysis

web-development

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).

1. Explain why the planner chose it: missing index, non-sargable predicate, bad row estimate, sort spill, or something else.

1. 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.

1. 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.

1. 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.

Fill in

{{database}}: Database engine and versione.g. PostgreSQL 16
{{query}}: The slow query, exactly as it runse.g. SELECT ... FROM orders JOIN customers ... ORDER BY created_at DESC LIMIT 50
{{schema}}: Table sizes, relevant columns, existing indexes and any constraintse.g. orders 18M rows, indexes on customer_id and created_at
{{plan}}: Full EXPLAIN (ANALYZE) outpute.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
```

7990

Generated Outputs (1)

3 hours ago

Claude

Opus 5.5

3 hours ago

Generated Output

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) ;
`
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.

Comments (0)

No comments yet. Be the first to share your thoughts!