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

# 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

# ❌ 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

// ❌ 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 },
});

# ❌ 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

# ❌ 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

-- 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

-- 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

-- 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 *

# ❌ 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)

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

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

Problem #4: Queries in Loops

# ❌ 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

# ❌ 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

# ❌ 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)

// ❌ 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

-- ❌ 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

# ❌ 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

# 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