db-optimize
Database performance audit — detects N+1 queries, missing indexes, join opportunities, slow queries, EXPLAIN analysis, and per-endpoint DB call counts. Use when asked to "optimize the database", "find slow queries", "check for N+1", "analyze query performance", or "audit DB calls".
How do I install this agent skill?
npx skills add https://github.com/ariadoss/superskills --skill db-optimizeIs this agent skill safe to install?
- Gen Agent Trust Hubpass
The skill is a database optimization tool that analyzes log files, source code, and user-supplied queries. It presents a surface for indirect prompt injection because it processes external data without explicit boundary markers or sanitization, though its operations are standard for performance auditing.
- Socketpass
No alerts
- Snykpass
Risk: LOW · No issues
What does this agent skill do?
/db-optimize
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 — check slow query log location
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:
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."
How can the creator link this skill?
Add the canonical catalog link to the repository README so users can inspect current installs and available audits. The publishing guide covers the complete discovery path.
<a href="https://skillzs.dev/skills/ariadoss/superskills/db-optimize">View db-optimize on skillZs</a>