Ahmed Hassan·
Partial covering index turned an 8M-row seq scan into a 25ms index-only scan
Analyze slow database queries, execution plans, and schema design to generate optimized queries with index recommendations and query restructuring.
Database Query Optimizer
You are a database performance engineer. Analyze and optimize the following database queries and schema.
**Slow Query / Query Log:**
```
{{slow_query}}
```
**Execution Plan:**
```
{{execution_plan}}
```
**Database Schema:**
```
{{database_schema}}
```
**Database Engine:**
{{database_engine}}
**Performance Requirements:**
{{performance_requirements}}
**Table Sizes:**
{{table_sizes}}
Analyze and optimize:
1. **Query Analysis**: Parse the execution plan to identify bottlenecks (full table scans, nested loops, sorts)
2. **Missing Indexes**: Recommend composite/partial/covering indexes with exact CREATE INDEX statements
3. **Query Rewrite**: Provide optimized query versions with explanations for each change
4. **Schema Optimization**: Suggest denormalization, partitioning, or data type improvements
5. **N+1 Detection**: Find and eliminate N+1 query problems
6. **Join Optimization**: Recommend join order changes, join type changes (nested loop -> hash -> merge)
7. **Pagination Optimization**: Efficient cursor-based pagination instead of OFFSET
8. **Caching Strategy**: Which queries/results to cache and how
9. **Connection Pool Tuning**: Pool size and timeout recommendations
10. **Benchmark Comparison**: Expected before/after metrics for each optimization
11. **Migration Script**: Safe index creation without locking tables in production
Provide EXPLAIN-ready queries and exact DDL statements. Quantify expected improvements.
Ergebnisse
The query scans all 8M `orders` rows and filesort-sorts them because the `status` filter plus `created_at` sort has no supporting index. Postgres, 8M rows.
**Original plan symptoms:** `Seq Scan on orders` (cost ~180k) + `Sort (external merge, disk)`.
**Optimized query** — keyset pagination replaces a deep `OFFSET`:
```sql
-- before: OFFSET 100000 forces Postgres to read and discard 100k rows
-- after: seek by the last row's cursor — constant cost regardless of page depth
SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE status = 'shipped'
AND created_at < $1 -- cursor from the previous page
ORDER BY created_at DESC
LIMIT 25;
```
**Index** — a partial covering index, narrow because most rows aren't `shipped`:
```sql
CREATE INDEX CONCURRENTLY idx_orders_shipped_created
ON orders (created_at DESC)
INCLUDE (customer_id, total_cents)
WHERE status = 'shipped';
```
**Plan after:** `Index Only Scan using idx_orders_shipped_created` — no Seq Scan, no Sort node, no heap fetch (all selected columns are in the index).
| Metric | Before | After |
|--------|--------|-------|
| Rows read | 8,000,000 | 25 |
| Sort | external (disk) | none |
| p95 latency | 3.1 s | ~25 ms |
Ship the index as its own migration with `CONCURRENTLY` so it builds without locking writes. The partial `WHERE status = 'shipped'` keeps the index small and write-cheap.
Modell: Claude Sonnet 4
42 Likes10 SavesScore: 21
3 Kommentare
Jonas Weber·
Running this debugging, testing prompt on a real ticket right now.
Tobias Keller·
Okay this debugging, testing output just saved me an afternoon.
Daniel Cohen·
This made my PR review 20 minutes shorter. Appreciate it.