query

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.