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

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:

sql

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