performance-optimizer

name	performance-optimizer
description	This skill should be used when the user asks to "optimize queries", "fix slow queries", "improve performance", "detect N+1", "add indexes", or when writing Django ORM queries that access related objects. Detects and fixes performance issues.

Performance Optimizer

Auto-detects and fixes Django ORM performance issues before they reach production.

Activation Triggers

This skill activates when:

• Writing ORM queries
• Accessing related objects (foreign keys, M2M)
• Creating views that fetch data
• Mentioning "slow", "performance", "optimization"
• Looping over querysets
• Creating list/detail views

Performance Targets

• ✅ NO N+1 queries
• ✅ Appropriate indexes on frequently queried fields
• ✅ Pagination for list endpoints
• ✅ select_related() for foreign keys
• ✅ prefetch_related() for reverse foreign keys and M2M
• ✅ Caching for expensive operations

Auto-Detection Patterns

Pattern 1: N+1 Query Detection

❌ INEFFICIENT (N+1 query):

# Bad: 1 query for users + N queries for organizations
users = User.objects.all()  # 1 query
for user in users:  # N queries
    print(user.organization.name)  # Hits DB each time!

✅ OPTIMIZED:

# Good: 2 queries total (1 for users + 1 JOIN for organizations)
users = User.objects.select_related('organization').all()
for user in users:
    print(user.organization.name)  # No DB hit!

Pattern 2: Reverse Foreign Key N+1

❌ INEFFICIENT:

# Bad: 1 + N queries
organizations = Organization.objects.all()  # 1 query
for org in organizations:  # N queries
    users = org.users.all()  # Hits DB each time!
    print(f"{org.name}: {users.count()} users")

✅ OPTIMIZED:

# Good: 2 queries total
organizations = Organization.objects.prefetch_related('users').all()
for org in organizations:
    users = org.users.all()  # Pre-fetched!
    print(f"{org.name}: {users.count()} users")

Pattern 3: Many-to-Many N+1

❌ INEFFICIENT:

# Bad: 1 + N queries
users = User.objects.all()  # 1 query
for user in users:  # N queries
    roles = user.roles.all()  # Hits DB each time!

✅ OPTIMIZED:

# Good: 2 queries total
users = User.objects.prefetch_related('roles').all()
for user in users:
    roles = user.roles.all()  # Pre-fetched!

Pattern 4: Missing Indexes

❌ SLOW:

# Frequent query without index
User.objects.filter(email='[e-mail]')  # Table scan if no index!

✅ FAST:

# Model with index
class User(models.Model):
    email = models.EmailField(unique=True)  # Unique creates index
    # OR
    class Meta:
        indexes = [
            models.Index(fields=['email']),  # Explicit index
        ]

Pattern 5: Missing Pagination

❌ DANGEROUS:

# Returns ALL records - memory issues with large tables
class UserViewSet(viewsets.ModelViewSet):
    queryset = User.objects.all()  # No pagination!

✅ SAFE:

# Paginated automatically
class UserViewSet(viewsets.ModelViewSet):
    queryset = User.objects.all()
    pagination_class = PageNumberPagination  # DRF auto-paginates

Auto-Fix Process

Step 1: Detect Anti-Pattern

When detecting:

def get_users_with_organizations(self):
    users = User.objects.all()
    return [
        {'user': u.email, 'org': u.organization.name}
        for u in users
    ]

Step 2: Analyze Query Pattern

Identify:

• Queryset: User.objects.all() (1 query)
• Foreign key access: u.organization.name (N queries)
• Total: 1 + N queries = N+1 problem!

Step 3: Suggest Optimization

Provide:

N+1 Query Detected!

Problem:

• Query: User.objects.all() → 1 query
• Loop accesses user.organization.name → N queries
• Total: 1 + N queries (inefficient!)

Impact:

• 100 users = 101 queries 😱
• 1000 users = 1001 queries 🔥

Fix:

users = User.objects.select_related('organization').all()
# Now only 2 queries total!

Why:

• select_related() performs SQL JOIN
• Fetches related data in single query
• 500x faster for 1000 records!

Step 4: Auto-Apply Fix

# Optimized version
def get_users_with_organizations(self):
    users = User.objects.select_related('organization').all()
    return [
        {'user': u.email, 'org': u.organization.name}  # No DB hit!
        for u in users
    ]

Common Optimization Patterns

Foreign Key (One-to-One, Many-to-One)

# Use select_related() for foreign keys
User.objects.select_related('organization', 'created_by')

# Multiple levels
User.objects.select_related('organization__country')

Reverse Foreign Key (One-to-Many)

# Use prefetch_related() for reverse FK
Organization.objects.prefetch_related('users')

# With filtering
from django.db.models import Prefetch
Organization.objects.prefetch_related(
    Prefetch('users', queryset=User.objects.filter(is_active=True))
)

Many-to-Many

# Use prefetch_related() for M2M
User.objects.prefetch_related('roles', 'permissions')

Combined Optimization

# Optimize multiple relations
users = User.objects.select_related(
    'organization',  # FK
    'created_by'     # FK
).prefetch_related(
    'roles',         # M2M
    'permissions'    # M2M
)

Index Recommendations

Suggest indexes for:

Frequently Filtered Fields

class User(models.Model):
    email = models.EmailField()
    status = models.CharField(max_length=20)

    class Meta:
        indexes = [
            models.Index(fields=['email']),    # WHERE email = ...
            models.Index(fields=['status']),   # WHERE status = ...
        ]

Composite Indexes

class Order(models.Model):
    user = models.ForeignKey(User)
    status = models.CharField(max_length=20)
    created_at = models.DateTimeField()

    class Meta:
        indexes = [
            # Composite index for common query pattern
            models.Index(fields=['user', 'status']),
            # Index for time-based queries
            models.Index(fields=['-created_at']),  # DESC order
        ]

Foreign Key Indexes

# Django automatically creates indexes for:
# - Primary keys
# - Unique fields
# - Foreign keys

# Manual indexes needed for:
# - Composite queries
# - Ordering fields
# - Search fields

Caching Strategies

Query Caching

from django.core.cache import cache

def get_active_users():
    cache_key = 'active_users'
    users = cache.get(cache_key)

    if users is None:
        users = list(User.objects.filter(is_active=True).values())
        cache.set(cache_key, users, 300)  # Cache 5 minutes

    return users

Model Method Caching

from django.utils.functional import cached_property

class User(models.Model):
    @cached_property
    def full_name(self):
        """Expensive computation cached per instance."""
        return f"{self.first_name} {self.last_name}".strip()

Pagination Patterns

# DRF Pagination
from rest_framework.pagination import PageNumberPagination

class StandardResultsSetPagination(PageNumberPagination):
    page_size = 100
    page_size_query_param = 'page_size'
    max_page_size = 1000

class UserViewSet(viewsets.ModelViewSet):
    pagination_class = StandardResultsSetPagination

Database Query Analysis

Check queries using Django Debug Toolbar patterns:

from django.db import connection
from django.test.utils import override_settings

@override_settings(DEBUG=True)
def test_user_list_queries(self):
    """Test that user list doesn't have N+1 queries."""
    with self.assertNumQueries(2):  # Should be exactly 2 queries
        response = self.client.get('/api/users/')
        # 1 query: Fetch users
        # 1 query: Fetch organizations (prefetched)

Performance Checklist

For every queryset, verify:

• ✅ select_related() for accessed foreign keys
• ✅ prefetch_related() for reverse FKs and M2M
• ✅ .only() or .defer() if fetching many fields
• ✅ Indexes on filtered/ordered fields
• ✅ Pagination for lists
• ✅ .count() instead of len(queryset)
• ✅ .exists() instead of if queryset
• ✅ Bulk operations instead of loops

Before/After Examples

Example 1: User List with Organization

❌ Before (N+1):

# views.py
class UserViewSet(viewsets.ModelViewSet):
    queryset = User.objects.all()  # 1 query
    # serializer accesses user.organization.name → N queries

✅ After (Optimized):

# views.py
class UserViewSet(viewsets.ModelViewSet):
    queryset = User.objects.select_related('organization').all()  # 2 queries total

Example 2: Organization with Users

❌ Before (N+1):

def organization_summary(self):
    orgs = Organization.objects.all()  # 1 query
    return [
        {
            'name': org.name,
            'user_count': org.users.count()  # N queries!
        }
        for org in orgs
    ]

✅ After (Optimized):

from django.db.models import Count

def organization_summary(self):
    orgs = Organization.objects.annotate(
        user_count=Count('users')  # Single query with aggregation
    ).all()
    return [
        {
            'name': org.name,
            'user_count': org.user_count  # No DB hit!
        }
        for org in orgs
    ]

Integration with Testing

# Test that optimizations work
@pytest.mark.django_db
def test_user_list_performance(django_assert_num_queries):
    """User list should use select_related to avoid N+1."""
    UserFactory.create_batch(100)  # Create 100 users

    with django_assert_num_queries(2):  # Only 2 queries allowed
        users = list(User.objects.select_related('organization').all())
        for user in users:
            _ = user.organization.name  # Should not hit DB

Success Criteria

✅ NO N+1 queries in codebase ✅ All foreign key access uses select_related() ✅ All reverse FK/M2M use prefetch_related() ✅ Appropriate indexes on all models ✅ Pagination on all list endpoints ✅ Bulk operations used instead of loops

Behavior

Proactive enforcement:

• Detect N+1 queries automatically
• Suggest optimizations immediately
• Calculate performance impact (queries before/after)
• Add indexes to models
• Explain WHY the optimization matters

Never:

• Require explicit "optimize queries" request
• Wait for production slowness
• Just warn without fixing

Block completion if:

• N+1 queries detected
• Missing indexes on frequently queried fields
• No pagination on list endpoints
• Bulk creates/updates done in loops

This ensures code is performant from day one.