---

name: database-query-optimizer description: Analyze and optimize Django ORM queries including N+1 problems, missing indexes, slow queries, and migration issues. Use when troubleshooting slow API responses, database performance, query optimization, or migration errors. allowed-tools: Read, Grep, Glob, Bash

Database Query Optimizer

Analyzes and optimizes Django ORM queries and database performance.

Project Context

• Database: PostgreSQL (in Docker)
• ORM: Django ORM
• Key models: Project, User, Tool, Technology, Company
• Search: Weaviate (vector DB) for semantic search

When to Use

• "API response is slow"
• "N+1 query problem"
• "Database query optimization"
• "Missing index"
• "Migration failing"
• "Slow queryset"

Common Performance Issues

N+1 Query Problem

Symptom: Many small queries instead of one efficient query

Copy# BAD - N+1 queries (1 + N queries)
projects = Project.objects.all()
for project in projects:
    print(project.user.username)  # Each access = 1 query!

# GOOD - select_related for ForeignKey/OneToOne
projects = Project.objects.select_related('user').all()

# GOOD - prefetch_related for ManyToMany/reverse FK
projects = Project.objects.prefetch_related('tools', 'technologies').all()

Missing select_related/prefetch_related

Check serializers for nested data:

Copy# If serializer includes nested user data:
class ProjectSerializer(serializers.ModelSerializer):
    user = UserSerializer()  # Needs select_related!

# View must optimize:
class ProjectViewSet(viewsets.ModelViewSet):
    def get_queryset(self):
        return Project.objects.select_related('user')

Unindexed Filters

Copy# If filtering frequently on a field, add index:
class Project(models.Model):
    slug = models.SlugField(db_index=True)  # Has index
    is_private = models.BooleanField(db_index=True)  # Add index!

Debugging Queries

1. Django Debug Toolbar

Already configured - check bottom of page in development.

2. Query Logging

Copy# settings.py - enable query logging
LOGGING = {
    'loggers': {
        'django.db.backends': {
            'level': 'DEBUG',
        }
    }
}

3. Shell Analysis

Copy# Django shell
from django.db import connection, reset_queries
from django.conf import settings
settings.DEBUG = True

reset_queries()
# Run your query
list(Project.objects.all())
print(f"Queries: {len(connection.queries)}")
for q in connection.queries:
    print(q['sql'][:100])

4. Explain Query

Copy# See query execution plan
print(Project.objects.filter(is_private=False).explain())

Key Optimizations

select_related (ForeignKey, OneToOne)

Copy# Single JOIN query
Project.objects.select_related('user', 'user__profile')

prefetch_related (ManyToMany, Reverse FK)

Copy# Separate query, joined in Python
Project.objects.prefetch_related('tools', 'likes')

only() / defer() - Load Specific Fields

Copy# Only load needed fields
Project.objects.only('id', 'title', 'slug')

# Exclude heavy fields
Project.objects.defer('description', 'content')

values() / values_list() - Skip Model Instantiation

Copy# Returns dicts, not model instances
Project.objects.values('id', 'title')

# Returns tuples
Project.objects.values_list('id', 'title')

Aggregation at DB Level

Copyfrom django.db.models import Count, Avg

# Do counting in database, not Python
Project.objects.annotate(like_count=Count('likes'))

Bulk Operations

Copy# BAD - N queries
for project in projects:
    project.views += 1
    project.save()

# GOOD - 1 query
Project.objects.filter(id__in=ids).update(views=F('views') + 1)

# Bulk create
Project.objects.bulk_create([Project(...), Project(...)])

Migration Issues

Check Migration Status

Copydocker compose exec web python manage.py showmigrations

Create Missing Migrations

Copydocker compose exec web python manage.py makemigrations

Fake Problematic Migration

Copydocker compose exec web python manage.py migrate --fake app_name 0001

Squash Migrations

Copydocker compose exec web python manage.py squashmigrations app_name 0001 0010

Key Files to Check

Copycore/
├── projects/
│   ├── models.py       # Project model, indexes
│   ├── views.py        # QuerySet optimization
│   └── serializers.py  # Nested serializers need optimization
├── users/
│   └── models.py       # User model
└── tools/
    └── models.py       # Tool model

config/
└── settings.py         # DATABASE config, DEBUG

PostgreSQL Commands

Copy# Connect to database
docker compose exec db psql -U postgres -d allthriveai

# List tables
\dt

# Describe table
\d core_project

# Show indexes
\di

# Analyze slow query
EXPLAIN ANALYZE SELECT * FROM core_project WHERE is_private = false;

# Show running queries
SELECT pid, query, state FROM pg_stat_activity WHERE state != 'idle';

Adding Indexes

Copy# In model
class Project(models.Model):
    class Meta:
        indexes = [
            models.Index(fields=['is_private', 'created_at']),
            models.Index(fields=['user', 'is_private']),
        ]

# Or on field
is_private = models.BooleanField(default=False, db_index=True)

Then run:

Copydocker compose exec web python manage.py makemigrations
docker compose exec web python manage.py migrate