/query-optimize
Make slow queries fast.
Usage
/query-optimize "SELECT * FROM orders WHERE ..." # paste a query
/query-optimize @slow-query.sql # from file
/query-optimize --from-logs # picks slowest from pg_stat_statements
What it does
1. Run EXPLAIN ANALYZE
- Captures the actual query plan (not just EXPLAIN)
- Shows row counts, costs, scan types
2. Identify the bottleneck
- Sequential scans on big tables
- Missing indexes on join keys / WHERE clauses
- Sort spilling to disk (work_mem too small)
- Subqueries that should be JOINs
- N+1 patterns from the calling app
3. Propose solutions
- New index (with full
CREATE INDEX CONCURRENTLYSQL) - Query rewrite (with side-by-side diff)
- Schema changes if structural (last resort, flagged clearly)
4. Predict impact
- Index size estimate
- Write-throughput cost
- Expected speedup (with caveats)
Output example
Slow query: 4.2s on average
Plan: Seq Scan on orders (8M rows) + Hash Join
Recommendation:
CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders (user_id, status)
WHERE status IN ('pending', 'paid');
Expected: 4.2s → 50ms (84x faster)
Index size: ~120MB
Write overhead: +5% on INSERT
Rules
- Adapt to DB engine (Postgres, MySQL, SQLite have different optimizers)
- Don't suggest indexes that overlap with existing ones
- Always use
CONCURRENTLYon Postgres prod recommendations