# DB Query Optimizer

> This skill should be used when "optimize this query", "slow database queries", "N+1 query problem", "add database index", "SQL performance", "query is slow", "optimize ORM queries", "database bottleneck", "reduce query count".

- Skill: `kwokyc/db-query-optimizer` (Agent Skill)
- Install (CLI): `npx skillmds@latest add kwokyc/db-query-optimizer`
- Raw SKILL.md: https://api.skillmd.com/api/skills/kwokyc/db-query-optimizer/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: KwokYC (https://skillmd.com/u/kwokyc)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/kwokyc/db-query-optimizer

---


# DB Query Optimize

Find and fix slow database queries. Eliminate N+1 problems. Add proper indexes. Batch operations.

## Problem #1: N+1 Queries

The most common ORM performance killer. One query for the list, then N queries for related data.

### Detection

```python
# Django: enable query logging
LOGGING = {
    'loggers': {
        'django.db.backends': {'level': 'DEBUG'}
    }
}

# Rails: check logs for repeated queries
# SELECT * FROM users WHERE id = 1
# SELECT * FROM users WHERE id = 2  ← suspicious pattern
# SELECT * FROM users WHERE id = 3

# SQLAlchemy: enable echo
engine = create_engine(url, echo=True)
```

### Fix: Eager Loading

```python
# ❌ N+1 Problem (Django)
users = User.objects.all()                    # 1 query
for user in users:
    print(user.profile.bio)                    # N queries (one per user)

# ✅ Eager load
users = User.objects.select_related("profile").all()  # 1 query with JOIN

# ❌ N+1 for many-to-many
for user in users:
    for post in user.posts.all():              # N queries
        print(post.title)

# ✅ Prefetch
users = User.objects.prefetch_related("posts").all()  # 2 queries total
```

```javascript
// ❌ N+1 Problem (Prisma)
const users = await prisma.user.findMany();
for (const user of users) {
  user.posts = await prisma.post.findMany({ where: { userId: user.id } });
}

// ✅ Eager load
const users = await prisma.user.findMany({
  include: { posts: true },
});
```

```ruby
# ❌ N+1 (Rails)
users = User.all
users.each { |u| puts u.posts.count }  # N queries

# ✅ Eager load
users = User.includes(:posts).each { |u| puts u.posts.count }  # 2 queries
```

### Fix: Batch Loading

```python
# ❌ Loading one by one
for user_id in user_ids:
    user = User.objects.get(id=user_id)

# ✅ Batch load
users = User.objects.filter(id__in=user_ids)
user_map = {u.id: u for u in users}
```

## Problem #2: Missing Indexes

### When to Add Indexes

```sql
-- Columns that appear in WHERE clauses
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_posts_created_at ON posts(created_at);

-- Foreign keys (often not auto-indexed)
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Composite indexes for common query patterns
-- Query: WHERE status = 'active' AND created_at > '2025-01-01'
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
```

### Index Order Matters

```sql
-- Query: WHERE status = 'active' ORDER BY created_at DESC
-- ✅ Correct order (equality column first, range column second)
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- ❌ Wrong order (created_at index won't help with status filter)
CREATE INDEX idx_orders_created_status ON orders(created_at DESC, status);
```

### When NOT to Add Indexes

```
❌ Don't index:
- Columns with very low cardinality (boolean, gender)
  unless used in combination with other indexed columns
- Columns that are rarely used in WHERE/JOIN
- Tables with heavy writes and few reads (index slows writes)
- Very small tables (<1000 rows)
```

### Verify Index Usage

```sql
-- PostgreSQL: Check if query uses index
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- Look for:
-- ✅ Index Scan or Index Only Scan
-- ❌ Seq Scan (full table scan) on large tables
```

## Problem #3: SELECT *

```python
# ❌ Fetching all columns when you need 2
users = User.objects.all()
for user in users:
    send_email(user.email, user.name)  # Only needs email and name

# ✅ Select only needed columns
users = User.objects.values_list("email", "name")
for email, name in users:
    send_email(email, name)
```

```sql
-- ❌
SELECT * FROM orders WHERE user_id = 123;

-- ✅
SELECT id, total, status, created_at FROM orders WHERE user_id = 123;
```

## Problem #4: Queries in Loops

```python
# ❌ Query inside loop
for order in orders:
    user = User.objects.get(id=order.user_id)  # N queries
    process(order, user)

# ✅ Batch load, then loop
user_ids = [o.user_id for o in orders]
users = User.objects.in_bulk(user_ids)  # 1 query
for order in orders:
    process(order, users[order.user_id])
```

## Problem #5: COUNT on Large Tables

```python
# ❌ Slow: counts ALL rows
total = User.objects.count()  # Full scan on large tables

# ✅ Use estimation for display purposes (PostgreSQL)
from django.db import connection
with connection.cursor() as cursor:
    cursor.execute("SELECT reltuples FROM pg_class WHERE relname = 'users'")
    estimate = int(cursor.fetchone()[0])

# ✅ Or use cached counter
total = User.objects.filter(is_active=True).count()  # At least filter
```

## Problem #6: Bulk Operations

```python
# ❌ One INSERT per record
for item in items:
    Product.objects.create(name=item.name, price=item.price)

# ✅ Bulk insert
Product.objects.bulk_create([
    Product(name=item.name, price=item.price)
    for item in items
])

# ❌ One UPDATE per record
for user in users:
    user.is_active = True
    user.save()

# ✅ Bulk update
User.objects.filter(id__in=[u.id for u in users]).update(is_active=True)
```

```javascript
// ❌ Individual inserts (Prisma)
for (const item of items) {
  await prisma.product.create({ data: item });
}

// ✅ Batch insert
await prisma.product.createMany({ data: items });
```

## Problem #7: Pagination Performance

```sql
-- ❌ Slow: OFFSET on large tables
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;
-- Database must scan and skip 100,000 rows

-- ✅ Cursor-based: Use last seen ID
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;
-- Uses index, constant time regardless of page
```

```python
# ❌ Slow offset pagination
page = 1000
orders = Order.objects.all()[page*20:(page+1)*20]

# ✅ Cursor pagination
last_id = request.GET.get("after")
orders = Order.objects.filter(id__gt=last_id).order_by("id")[:20]
```

## Query Optimization Checklist

```
Before deploying queries, check:

- [ ] No N+1 queries (check query count in dev)
- [ ] Indexes on WHERE, JOIN, ORDER BY columns
- [ ] No SELECT * (select only needed columns)
- [ ] No queries inside loops
- [ ] Bulk operations for batch inserts/updates
- [ ] Cursor pagination for large datasets
- [ ] Query returns reasonable row count (< 1000)
- [ ] Connection pooling configured
- [ ] Slow query logging enabled in production
```

## Performance Testing

```python
# Time a query
import time
start = time.time()
results = list(YourModel.objects.filter(...).select_related(...))
elapsed = time.time() - start
print(f"Query took {elapsed:.3f}s, returned {len(results)} rows")

# Django: count queries
from django.test.utils import override_settings
from django.db import connection

with override_settings(DEBUG=True):
    results = list(User.objects.all())
    print(f"Queries: {len(connection.queries)}")
    for q in connection.queries:
        print(f"  {q['time']}s: {q['sql'][:100]}")
```

## Quick Fixes Reference

| Problem | Fix |
|---------|-----|
| N+1 on FK | `select_related()` / `include` |
| N+1 on M2M | `prefetch_related()` / `include` |
| Missing index | `CREATE INDEX` on WHERE/JOIN columns |
| SELECT * | Specify columns explicitly |
| Query in loop | Batch load, then process |
| Individual inserts | `bulk_create()` / `createMany()` |
| Slow OFFSET | Cursor-based pagination |
| COUNT on big table | Estimate or cache |