Query Optimization

Analyze database queries and provide actionable optimization recommendations.

Workflow

Copy and track progress:

Query Optimization:
- [ ] Identify database type
- [ ] Analyze query structure
- [ ] Check for red flags
- [ ] Provide recommendations
- [ ] Suggest verification

1. Identify database type from syntax or user context
2. Analyze query structure - parse SELECT, FROM, WHERE, JOIN, ORDER BY, GROUP BY clauses
3. Check for red flags (see Quick Reference)
4. Provide concrete recommendations with copy-pasteable code
5. Suggest verification - EXPLAIN command or metric to confirm

For complex queries (3+ JOINs or nested subqueries):

• Decompose into logical units
• Analyze each unit's access pattern
• Synthesize recommendations in priority order

Analysis Checklist

For each query, evaluate:

• Index usage: Missing or inefficient indexes
• Scan type: Full scans on large tables
• Join efficiency: Join order, type, missing conditions
• Filtering: WHERE placement, function usage on columns
• Projection: SELECT * vs specific columns
• Pagination: OFFSET vs keyset/cursor
• N+1 detection: Loop patterns in code context

Quick Reference

SQL Red Flags

• SELECT * on large tables
• WHERE function(column) prevents index use
• LIKE '%pattern' (leading wildcard)
• OR conditions spanning different columns
• Large OFFSET values
• NOT IN with nullable subquery
• Missing LIMIT on unbounded queries

NoSQL Red Flags

• MongoDB: COLLSCAN, $where, unbounded arrays
• Redis: KEYS *, missing TTL, wrong data structure
• DynamoDB: Scan operations, hot partitions
• Elasticsearch: Deep pagination, wildcard prefix

Examples

Example 1: Missing Index

Input:

SELECT * FROM orders WHERE customer_email = 'user@example.com';

Output:

## Issue [HIGH]
Full table scan on `orders` - no index on `customer_email`

## Impact
Query time grows linearly with table size; causes lock contention

## Solution
CREATE INDEX idx_orders_customer_email ON orders(customer_email);

## Verification
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_email = 'user@example.com';
-- Should show "Index Scan" instead of "Seq Scan"

Example 2: N+1 Query Pattern

Input:

users = db.query("SELECT * FROM users")
for user in users:
    orders = db.query(f"SELECT * FROM orders WHERE user_id = {user.id}")

Output:

## Issue [HIGH]
N+1 query pattern - executes 1 + N queries instead of 1

## Impact
100 users = 101 queries; network latency multiplied

## Solution
SELECT u.*, o.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

## Verification
Check query count before/after; should reduce from N+1 to 1

Example 3: When NOT to Optimize

Input:

SELECT * FROM config WHERE key = 'app_version';

Output:

## No Issues Detected

This query on a small lookup table (typically <100 rows) is acceptable.
Adding an index would add write overhead without meaningful read benefit.

## When to Reconsider
- If table grows beyond 10,000 rows
- If query is called >100 times/second

Example 4: Incomplete Information

Input:

SELECT * FROM large_table WHERE status = ?;

Output:

## Issue [MEDIUM]
Preliminary analysis without schema information

## Potential Issues
1. `SELECT *` - may fetch unnecessary columns
2. `status` column - if low cardinality (e.g., 5 values), index may not help

## Information Needed
- Table row count
- Cardinality of `status` column
- Existing indexes: `SHOW INDEX FROM large_table;`

Example 5: MongoDB COLLSCAN

Input:

db.orders.find({ customer_email: "user@example.com" })

Output:

## Issue [HIGH]
COLLSCAN on `orders` - no index on `customer_email`

## Impact
Query scans entire collection; O(n) performance

## Solution
db.orders.createIndex({ customer_email: 1 })

## Verification
db.orders.find({ customer_email: "..." }).explain("executionStats")
// Should show "stage": "IXSCAN" instead of "COLLSCAN"

Response Format

Structure recommendations as:

## Issue [HIGH/MEDIUM/LOW]
[Specific problem identified]

## Impact
[Performance/resource cost]

## Solution
[Concrete fix with code]

## Verification
[EXPLAIN command or metric to confirm]

Confidence Levels

When uncertainty exists, indicate confidence:

• HIGH: Pattern clearly matches known anti-pattern
• MEDIUM: Likely issue, depends on data distribution/schema
• LOW: Potential issue, needs EXPLAIN to confirm

Response Constraints

1. Max 5 recommendations per query - prioritize by impact
2. Order by severity: HIGH → MEDIUM → LOW
3. One primary fix per issue - mention alternatives briefly
4. Code must be copy-pasteable - no placeholders like <table_name>
5. Verification commands must be complete - include all flags

Prioritization Matrix

Issue Type	Impact	Priority
Missing JOIN index	Query blocks	P0
Full table scan (>100k rows)	Slow response	P0
N+1 pattern	Latency × N	P1
SELECT *	Memory/bandwidth	P1
Missing LIMIT	Resource exhaustion	P1
Large OFFSET	Slow pagination	P2
Suboptimal ORDER BY	Sort overhead	P2

Reference Files

Consult these for detailed patterns (use grep to find specific sections):

• references/sql-patterns.md - SQL optimization
  • Grep: ## Index, ## Query Rewrite, ## Join, ## Aggregation
• references/nosql-patterns.md - MongoDB, Redis, DynamoDB, Elasticsearch
  • Grep: ## MongoDB, ## Redis, ## DynamoDB, ## Elasticsearch
• references/explain-analysis.md - Execution plans
  • Grep: ## PostgreSQL, ## MySQL, ## MongoDB
• references/common-scenarios.md - Common patterns
  • Grep: ## Slow pagination, ## N+1, ## Dashboard, ## Bulk
• references/orm-patterns.md - ORM fixes
  • Grep: ### Django, ### SQLAlchemy, ### ActiveRecord, ### Prisma

Utility Scripts

All scripts use Python standard library only (no pip install required).

Run these for automated analysis:

Query Analysis

analyze_query.py - Detect anti-patterns and score complexity:

python scripts/analyze_query.py "SELECT * FROM orders WHERE ..."
python scripts/analyze_query.py --file query.sql --json

suggest_index.py - Generate index recommendations:

python scripts/suggest_index.py "SELECT ... FROM orders WHERE status = 'pending'"

EXPLAIN & Logs

parse_explain.py - Analyze EXPLAIN output:

python scripts/parse_explain.py --pg explain_output.txt
python scripts/parse_explain.py --mysql explain_output.txt

diff_explain.py - Compare before/after EXPLAIN outputs:

python scripts/diff_explain.py --pg before.txt after.txt
python scripts/diff_explain.py --mysql before.txt after.txt --json

parse_slow_log.py - Analyze slow query logs:

python scripts/parse_slow_log.py --pg /var/log/postgresql.log --top 20
python scripts/parse_slow_log.py --mysql slow-query.log --json

Schema & Code

analyze_schema.py - Find schema optimization opportunities:

python scripts/analyze_schema.py schema.sql

detect_orm_issues.py - Find ORM anti-patterns in code:

python scripts/detect_orm_issues.py app.py
python scripts/detect_orm_issues.py --dir ./models

check_migration.py - Check migration safety:

python scripts/check_migration.py --pg migration.sql
python scripts/check_migration.py --mysql migration.sql

Workflow with Scripts

For query optimization:

1. analyze_query.py → detect issues
2. suggest_index.py → generate indexes
3. User runs EXPLAIN (before)
4. User applies optimization
5. User runs EXPLAIN (after)
6. diff_explain.py → verify improvement

For verifying optimization impact:

# Save before state
psql -c "EXPLAIN (ANALYZE, BUFFERS) SELECT ..." > before.txt

# Apply optimization (add index, rewrite query, etc.)

# Save after state  
psql -c "EXPLAIN (ANALYZE, BUFFERS) SELECT ..." > after.txt

# Compare
python scripts/diff_explain.py --pg before.txt after.txt

For codebase audit:

1. detect_orm_issues.py --dir ./ → find N+1 patterns
2. analyze_schema.py → check schema
3. Prioritize fixes by severity

For migration review:

1. check_migration.py → safety check
2. Fix critical issues before deployment
3. Use safe alternatives (CONCURRENTLY, etc.)

EXPLAIN Commands

Provide appropriate commands:

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- MySQL
EXPLAIN ANALYZE SELECT ...;

-- SQLite
EXPLAIN QUERY PLAN SELECT ...;

// MongoDB
db.collection.find({...}).explain("executionStats")

Index Recommendation Format

When suggesting indexes:

-- PostgreSQL/MySQL
CREATE INDEX [CONCURRENTLY] idx_table_columns 
ON table(col1, col2, col3);
-- Rationale: [why this column order, covering considerations]

// MongoDB (ESR: Equality, Sort, Range)
db.collection.createIndex({ equality_field: 1, sort_field: -1, range_field: 1 })

Handling Missing Information

When user provides incomplete context:

No schema provided:

"To give precise recommendations, I need the table schema. Could you share:

1. Column data types
2. Existing indexes
3. Approximate row counts

Meanwhile, here's what I can infer from the query..."

No EXPLAIN output:

"Run this command and share the output:

EXPLAIN (ANALYZE, BUFFERS) <their query>;

This will show actual vs estimated rows and scan types."

Unknown database type:

"Which database are you using? (PostgreSQL, MySQL, SQLite, MongoDB, etc.) The optimization strategies differ significantly between them."

Conditional Workflows

Analyzing a query:

1. Run analyze_query.py first
2. Check for anti-patterns
3. Suggest fixes with examples

Interpreting EXPLAIN output:

1. Run parse_explain.py with appropriate flag
2. Identify warnings
3. Correlate with query structure

Full optimization request:

1. Analyze query → identify issues
2. Suggest indexes → generate CREATE statements
3. Request EXPLAIN → interpret results
4. Provide before/after comparison

Complex optimization (3+ issues or architectural decisions): Use extended reasoning to:

1. Map full query execution path
2. Identify all optimization opportunities
3. Evaluate trade-offs between approaches
4. Prioritize by impact and implementation cost

Script Error Handling

If scripts fail:

1. Check Python version (requires 3.7+)
2. Verify input format (SQL string, valid file path)
3. Fall back to manual analysis using reference files

Common errors:

• FileNotFoundError → Check file path, use absolute paths
• json.JSONDecodeError → Input not valid JSON, check --pg/--mysql flags
• Empty output → Query may be valid, no issues detected

When scripts are unavailable: Perform manual analysis using Quick Reference red flags and reference files.

Unsupported Databases

For databases not explicitly covered (Oracle, SQL Server, CockroachDB, etc.):

1. Apply general SQL patterns from references/sql-patterns.md
2. Note: "Optimization patterns based on PostgreSQL/MySQL; verify syntax for [database]"
3. Recommend user consult database-specific documentation for EXPLAIN syntax and index creation