arbazkhan971 Activate When
/godmode:query, "this query is slow", "optimize"- EXPLAIN plan interpretation, indexing strategy
- N+1 problem, complex aggregation, slow query log
Workflow
1. Query Context
Database: PostgreSQL|MySQL|SQLite|MongoDB|Redis
Access via: Raw SQL|Prisma|Django ORM|ActiveRecord|GORM
Table(s): <involved tables>
Estimated rows: <approximate counts>
Current time: <ms> Target: <ms>
# Extract ORM-generated SQL
# Prisma: new PrismaClient({ log: ['query'] })
# Django: django.db.connection.queries
# Rails: ActiveRecord::Base.logger = Logger.new(STDOUT)
2. EXPLAIN Analysis
-- PostgreSQL (most informative)
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <query>;
-- Safe mode (no execution)
EXPLAIN (COSTS, FORMAT JSON) <query>;
Red flags:
[ ] Seq Scan on > 10K rows — needs index
[ ] Nested Loop on large tables — use hash join
[ ] Estimated vs actual off > 10x — stale stats
[ ] Sort on disk — needs work_mem or index
[ ] Filter removes > 90% scanned — index needed
[ ] Seq Scan inside loop — N+1 pattern
IF MongoDB: db.collection.find().explain("executionStats")
Check totalDocsExamined vs nReturned ratio.
IF Redis: SLOWLOG GET 10 for slow commands.
3. Diagnose Issues
Missing Index:
Evidence: Seq Scan on <table> filtering by <col>
Fix: CREATE INDEX idx_<table>_<col> ON <table>(<col>)
N+1 Query:
Evidence: <N> identical queries in loop
Fix: JOIN or eager loading (includes/select_related)
ORM: Django select_related, Prisma include,
Rails includes, SQLAlchemy joinedload
Inefficient Join:
Evidence: Nested Loop on <N>x<M> rows
Fix: Ensure join columns indexed both sides
Over-fetching:
Evidence: SELECT * returning <N> unused columns
Fix: SELECT only needed columns
Stale Statistics:
Evidence: Estimated <N> vs actual <M> (off by >10x)
Fix: ANALYZE <table>
IF improvement < 10% after optimization: diminishing returns. IF query still > 1s after indexes: consider materialized view.
4. Index Recommendations
B-tree (default): equality, range, sort, LIKE 'prefix%'
GIN: full-text search, JSONB, arrays
BRIN: very large tables with natural ordering
Partial: WHERE active=true (smaller, faster)
Covering (INCLUDE): enables index-only scan
-- ALWAYS use CONCURRENTLY in production PostgreSQL
CREATE INDEX CONCURRENTLY idx_name ON table(col);
Trade-off: every index speeds reads, slows writes. IF write-heavy table (> 1000 writes/sec): limit to 3-5 indexes.
5. Verify
# Run EXPLAIN ANALYZE on optimized query
# Run 3 times, take median
# Run full test suite — verify correctness
# Check write performance — new indexes slow writes
# Analyze query performance
psql -c "EXPLAIN (ANALYZE, BUFFERS) SELECT 1"
npx prisma studio
Hard Rules
- NEVER claim optimized without EXPLAIN before/after.
- ALWAYS CREATE INDEX CONCURRENTLY in production PG.
- NEVER add index without stating write trade-off.
- NEVER SELECT * in production queries.
- NEVER OFFSET for deep pagination — use keyset.
- NEVER apply functions to indexed columns in WHERE.
- ALWAYS fix N+1 at ORM level, not just SQL.
TSV Logging
Append .godmode/query-results.tsv:
timestamp database queries_optimized indexes_added latency_pct status
Keep/Discard
KEEP if: time improved AND tests pass AND
write overhead acceptable.
DISCARD if: correctness changed OR no improvement.
Stop Conditions
STOP when FIRST of:
- Query meets target (default < 50ms)
- No seq scans on > 10K row tables
- All N+1 eliminated
- Improvement < 10% last iteration
Autonomous Operation
On failure: git reset --hard HEAD~1. Never pause.
Error Recovery
| Failure | Action |
|---|---|
| Wrong results | Check JOINs, WHERE, NULL handling |
| Still slow > 1s | Check missing indexes, full scans |
| ORM inefficiency | Use raw SQL for complex queries |