Priya Nair·
Cut a dashboard query from 4.2s to 40ms - it spotted the missing composite index instantly
Analyzes slow database queries and produces optimized versions with proper indexing, query rewriting, and caching strategies.
Database Query Optimization Engine
You are a database performance engineer optimizing slow queries. Analyze and rewrite for maximum performance.
## Slow Query
```sql
{{slow_query}}
```
## Database Context
- Database: {{database_type}} (PostgreSQL, MySQL, MongoDB, etc.)
- Version: {{db_version}}
- Schema:
```sql
{{schema_definition}}
```
- Table sizes: {{table_sizes}}
- Current EXPLAIN output:
```
{{explain_output}}
```
## Optimization Requirements
1. **Query Analysis** - Identify why it's slow:
- Missing indexes
- Full table scans
- N+1 patterns
- Inefficient joins
- Subquery issues
- Lock contention
2. **Optimized Query** - Rewritten query with:
- Proper index hints where beneficial
- Join order optimization
- Subquery → JOIN conversion where faster
- Pagination optimization (keyset/cursor vs OFFSET)
- SELECT only needed columns
3. **Index Recommendations** - CREATE INDEX statements with:
- Index type justification (B-tree, GIN, GiST, BRIN)
- Partial index opportunities
- Covering index design
- Index maintenance considerations
4. **Query Plan Analysis** - Expected EXPLAIN output after optimization
5. **ORM Equivalent** - If using an ORM, show optimized ORM query
6. **Caching Strategy** - When to cache vs always-query
7. **Connection Pool Tuning** - If applicable
8. **Benchmark** - Expected latency before/after
Prioritize correctness. Do not change query semantics. Provide the optimization as a migration script.
Ergebnisse
The query does a seq scan on a 12M-row `orders` table and sorts on disk because there's no index supporting the `customer_id` filter + `created_at` sort.
**Optimized query** (keyset pagination instead of `OFFSET`):
```sql
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = $1
AND created_at < $2 -- cursor from the last row of the prior page
ORDER BY created_at DESC
LIMIT 20;
```
**Index** — a covering composite kills both the scan and the sort:
```sql
CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC)
INCLUDE (total_cents);
```
**Expected plan after:** `Index Only Scan using idx_orders_customer_created` — no Sort node, no heap fetch.
| Metric | Before | After |
|--------|--------|-------|
| Plan | Seq Scan + external Sort | Index Only Scan |
| Rows examined | 12,000,000 | 20 |
| Latency (p95) | 4.2 s | ~40 ms |
Semantics are unchanged. Ship the `CREATE INDEX CONCURRENTLY` as its own migration so it doesn't lock writes.
Modell: Claude Sonnet 4
18 Likes7 SavesScore: 16
2 Kommentare
Luca Brunner·
The rollback path is what makes this safe to actually run. Good call.
Lena Fischer·
Did not expect a code generation prompt to be this thorough.