Comprehensive database performance audit: N+1 detection, EXPLAIN analysis, slow query log review, index gaps, join opportunities, and per-endpoint call counts.
Step 1: Check for DBMAP.md
[ -f DBMAP.md ] && echo "DBMAP_EXISTS=true" || echo "DBMAP_EXISTS=false"
If DBMAP_EXISTS=false, tell the user: "No DBMAP.md found. Run /dbmap first to generate a schema map — this audit uses it to cross-reference indexes and table structure."
Step 2: Slow query log
Check if a slow query log exists or can be accessed:
# PostgreSQL — check pg_stat_statements if available
psql "$DATABASE_URL" -c "SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;" 2>/dev/null || echo "PG_STAT_UNAVAILABLE"
mysql -e "SHOW VARIABLES LIKE 'slow_query_log%';" 2>/dev/null || echo "MYSQL_UNAVAILABLE"
If neither is available, scan the codebase for query logging output files:
```bash
find . -name "*.log" | xargs grep -l "slow\|duration\|query_time" 2>/dev/null | head -5
Report the 10 slowest queries found, sorted by mean execution time. For each: query text (truncated to 120 chars), call count, mean time (ms), total time (ms).
Step 3: EXPLAIN analysis
For each slow query identified in Step 2 (or any query the user provides), run EXPLAIN ANALYZE:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <query>;
Interpret the output and flag:
- Seq Scan on large tables (>1000 rows) — missing index
- Nested Loop with high row estimates — consider hash join or index
- Hash Join with high memory usage — consider work_mem tuning
- Sort without index — consider index on ORDER BY column
- high actual rows >> estimated rows — stale statistics, suggest
ANALYZE <table>
Format findings as:
EXPLAIN: <query summary>
⚠ Seq Scan on orders (5,432 rows) — add index on orders.user_id
⚠ Sort on created_at — add index on events(created_at)
✓ Index Scan on users — OK
Step 4: N+1 detection
Scan the codebase for N+1 patterns:
ORM patterns to flag:
- A loop containing a DB call (
.find,.where,.query,.fetch,.get,.filter,.select) - Accessing a relation inside a loop without eager loading (
.user,.author,.comments,.itemsetc. on a model instance inside a loop) - Multiple sequential awaits on DB calls inside a loop
# Find loops with DB calls inside
grep -rn "for\|each\|map\|forEach\|loop" --include="*.rb" --include="*.py" --include="*.js" --include="*.ts" --include="*.go" --include="*.java" . | head -5
For each file with loop+query patterns, read the surrounding context and determine if it's a true N+1.
Flag as:
⚠ N+1 DETECTED: app/controllers/orders_controller.rb:45
Loop over orders calls order.user on each iteration
Fix: Use includes(:user) / eager_load(:user) / preload(:user)
SQL impact: 1 query → 1+N queries (N = number of orders)
Step 5: Per-endpoint DB call audit
Scan route handlers / controller actions for query counts:
For each endpoint/action found, count the number of distinct DB calls made:
- Direct SQL:
db.query,connection.execute,pool.query - ORM:
.find,.findAll,.where,.first,.save,.create,.update - Each call inside a loop counts as N calls
Flag any endpoint making more than 3 DB calls as a candidate for optimization:
⚠ HIGH DB CALL COUNT: GET /api/orders (OrdersController#index)
8 DB calls detected:
- Order.all (1)
- order.user (N — N+1 pattern)
- order.items (N — N+1 pattern)
Fix: Use a single query with LEFT JOINs or eager loading
Step 6: Join opportunities
Find places where multiple sequential queries fetch related data that could be a single LEFT JOIN:
Pattern: two queries where the second uses IDs from the first result.
💡 JOIN OPPORTUNITY: app/services/dashboard_service.rb:23-31
Query 1: SELECT * FROM orders WHERE user_id = ?
Query 2: SELECT * FROM items WHERE order_id IN (...)
Fix: SELECT o.*, i.* FROM orders o LEFT JOIN items i ON i.order_id = o.id WHERE o.user_id = ?
Step 7: Summary report
Output a prioritized findings report:
## DB Optimization Report
### Critical (fix immediately)
- N+1 queries: X found
- Endpoints with >10 DB calls: Y found
### High Priority
- Slow queries (>100ms mean): Z found
- Missing indexes on FK columns: W found (see DBMAP.md Index Analysis)
### Optimization Opportunities
- JOIN consolidations: V found
- EXPLAIN issues: U found
### Recommended next steps
1. [highest impact fix]
2. [second fix]
3. ...
Ask the user: "Want me to implement any of these fixes now? I can start with the highest-impact items."