---
title: "Optimizing Django ORM Queries"
slug: django-orm-query-optimization
date: 2025-11-29
updated: 2025-11-29
tags:
  - code
description: "Practical patterns for eliminating N+1 queries, reducing p90 latency, and knowing when to split an API endpoint."
status: published
---

*Practical patterns for eliminating N+1 queries, reducing p90 latency, and knowing when to split an API endpoint.*

---

## Introduction

I spent a significant chunk of my first 1.5 years as a backend engineer optimizing slow API endpoints in a Django application. The product is an ATS (Applicant Tracking System) where recruiters view candidate profiles, stages, events, and evaluations — all loaded via REST APIs.

Our monitoring showed several endpoints with p90 latencies exceeding 2 seconds. After investigating, the root causes were almost always the same patterns:

1. **N+1 queries** — Fetching related objects inside a loop
2. **Redundant queries** — Fetching the same data multiple times
3. **Bloated endpoints** — One API doing too much, blocking page load
4. **Missing aggregation** — Using Python to compute what the database can do

This post covers the patterns I applied repeatedly, with real (anonymized) examples.

---

## Pattern 1: select_related for Foreign Keys

### The Problem

```python
# Fetching candidate opportunities
opportunities = Opportunity.objects.filter(candidate_id=can_id)

for opp in opportunities:
    job_title = opp.job.title              # Query per iteration
    employer_name = opp.job.employer.name   # Another query per iteration
    stage_name = opp.action_stage.name     # Yet another query
```

For a candidate with 10 opportunities, this fires 30+ queries instead of 1.

### The Fix

```python
candidate_opps = list(
    Opportunity.objects.filter(candidate_id=can_id)
    .select_related('job', 'job__employer', 'action_stage', 'candidate')
    .distinct()
)

# Now all access is from memory — zero additional queries
for opp in candidate_opps:
    job_title = opp.job.title
    employer_name = opp.job.employer.name
    stage_name = opp.action_stage.name
```

`select_related` works by doing a SQL JOIN. For our case, the single query with JOINs was ~5ms vs the original ~150ms from 30 round-trips.

### When to Use

- **One-to-one** or **many-to-one** (ForeignKey) relationships
- You know you'll access the related object for most/all items
- The related table isn't enormous (JOINs on billion-row tables have their own issues)

### When NOT to Use

- **Many-to-many** or **reverse foreign keys** — use `prefetch_related` instead
- You only need the related object's ID (it's already on the FK field: `opp.job_id`)

---

## Pattern 2: Pre-fetching to Eliminate N+1 in Business Logic

### The Problem

Sometimes the N+1 isn't in the ORM layer — it's in business logic:

```python
for opp in candidate_opps:
    job_id = opp.job_id

    # This hits the cache or DB for each job
    if job_id not in recruiter.subscribed_job_ids:
        owned_stages = recruiter.get_owned_stages_for_job(job_id)

    # This queries events table for each opportunity
    upcoming_event = get_upcoming_event(opp.id, recruiter.id)
```

### The Fix: Pre-fetch Outside the Loop

```python
# Pre-fetch owned stages for ALL jobs in one pass
job_ids = list(set(opp.job_id for opp in candidate_opps))
subscribed_job_ids = set(recruiter.subscribed_job_ids)

owned_stages_map = {}
for job_id in job_ids:
    if job_id not in subscribed_job_ids:
        owned_stages_map[job_id] = \
            recruiter.get_owned_stages_position_for_job(job_id)

# Pre-fetch ALL upcoming events for this recruiter in one query
opp_ids = set(opp.id for opp in candidate_opps)
all_upcoming_events = list(
    Event.objects.filter(
        opportunity_id__in=opp_ids,
        participants=recruiter,
        start_time__gt=timezone.now()
    ).select_related('opportunity')
)

# Build a lookup dict
events_by_opp = defaultdict(list)
for event in all_upcoming_events:
    events_by_opp[event.opportunity_id].append(event)

# Now the loop is O(1) lookups
for opp in candidate_opps:
    owned_stages = owned_stages_map.get(opp.job_id, [])
    upcoming = events_by_opp.get(opp.id, [])
```

This transformed a 15-query loop into 2 queries + dictionary lookups.

---

## Pattern 3: Aggregation Instead of Multiple Queries

### The Problem

We needed to check if a candidate was shared via email OR via excel sheet:

```python
# Original: Two separate queries
shared_via_email = ProfileShare.objects.filter(
    candidates=can_id, job_id=job_id,
    source=ProfileShare.SHARE_ACTION,
    to_emails__contains=recruiter_email
).exists()

shared_via_excel = ProfileShare.objects.filter(
    candidates=can_id, job_id=job_id,
    source=ProfileShare.EXCEL_SHEET,
    job__employer=employer
).exists()
```

Two database round-trips for something the DB can answer in one.

### The Fix: Single Query with Case/When Aggregation

```python
from django.db.models import Case, When, Value, IntegerField, Max

shares = ProfileShare.objects.filter(
    job__is_active=True,
    created_at__gte=last_30_days,
    candidates=can_id,
    job_id=job_id
)

result = shares.aggregate(
    has_email_share=Max(
        Case(
            When(shared_mail_query, then=Value(1)),
            default=Value(0),
            output_field=IntegerField()
        )
    ),
    has_excel_share=Max(
        Case(
            When(shared_via_excel_query, then=Value(1)),
            default=Value(0),
            output_field=IntegerField()
        )
    )
)

shared_with_email = bool(result.get('has_email_share'))
shared_via_excel = bool(result.get('has_excel_share'))
exists = shared_with_email or shared_via_excel
```

One query. The database evaluates both conditions in a single table scan.

### When to Use

- You need to check multiple conditions on the same queryset
- You need counts/existence checks across categories
- The alternative is multiple `.filter().exists()` or `.filter().count()` calls

---

## Pattern 4: Reusing Already-Fetched Objects

### The Problem

```python
opp = Opportunity.objects.get(id=opp_id)

# Later in the same view...
job = Job.objects.get(id=opp.job_id)  # Unnecessary — we could have JOINed
```

### The Fix

```python
opp = Opportunity.objects.select_related(
    'candidate',
    'candidate__job_search_preferences',
    'candidate__user',
    'job', 'job__employer', 'action_stage',
).get(id=opp_id)

# Reuse the already-loaded relation
job = opp.job  # No query — loaded via select_related
```

This sounds obvious, but in a large codebase with many contributors, it's common for code added later to re-fetch objects that are already available. A comment helps:

```python
# Reuse opp.job (loaded via select_related) when available,
# fall back to separate query otherwise.
if opp is not None:
    job = opp.job
else:
    job = Job.objects.get(id=job_id)
```

---

## Pattern 5: Splitting Heavy Endpoints

### The Problem

Our candidate profile page loaded everything in one API call:
- Candidate info
- Job stages
- Unread messages count
- Starred messages
- Upcoming events

The unread/starred messages query was slow (scanning a large messages table with complex filters), and it blocked the entire page from rendering.

### The Fix: Separate Endpoint

```python
# Before: Everything in SingleOpportunityResource.get_detail()
def get_detail(self, request):
    data = self.get_candidate_info()
    data['stages'] = self.get_stages()
    data['unread_count'] = self.get_unread_messages()  # SLOW
    data['starred'] = self.get_starred_messages()      # SLOW
    data['events'] = self.get_upcoming_events()
    return data

# After: Messages moved to their own endpoint
# GET /api/v1/candidate/messages_status/?candidate_id=123
class MessagesStatusResource(Resource):
    def get_detail(self, request):
        return {
            'unread_count': self.get_unread_messages(),
            'starred': self.get_starred_messages(),
        }
```

The frontend now fires both requests in parallel:

```javascript
// Load in parallel — page renders as soon as the fast one returns
$q.all([
    singleOpportunityService.getCandidateProfile(candidateId),
    messagesService.getMessagesStatus(candidateId)
]).then(function([profile, messages]) {
    $scope.profile = profile;
    $scope.unreadCount = messages.unread_count;
});
```

### When to Split

- One sub-query is significantly slower than the rest
- The slow data isn't needed for initial render
- The frontend can progressively load the data
- The slow query operates on a different table/index

---

## Pattern 6: Fixing LEFT OUTER JOINs

### The Problem

A query to check if a candidate had an interview scheduled:

```python
# This generates a LEFT OUTER JOIN on the events table
interviews = Opportunity.objects.filter(
    candidate_id=can_id
).filter(
    events__event_type='interview',
    events__status='scheduled'
)
```

LEFT OUTER JOINs are expensive when the right table (events) is large and doesn't have the right composite index.

### The Fix: Subquery or Exists

```python
from django.db.models import Exists, OuterRef

# Subquery approach — generates EXISTS instead of JOIN
has_interview = Event.objects.filter(
    opportunity=OuterRef('pk'),
    event_type='interview',
    status='scheduled'
)

interviews = Opportunity.objects.filter(
    candidate_id=can_id
).annotate(
    has_scheduled_interview=Exists(has_interview)
).filter(
    has_scheduled_interview=True
)
```

`EXISTS` stops scanning the events table at the first match. A JOIN loads all matching rows.

---

## Measuring Impact

Before and after each optimization, I checked:

1. **Query count** — Django Debug Toolbar or `connection.queries`
2. **p50/p90/p99** — From our APM tool (Datadog)
3. **Database time** — Sum of all query durations in the request

A typical result:

| Metric | Before | After |
|--------|--------|-------|
| Queries per request | 35 | 8 |
| p50 latency | 450ms | 120ms |
| p90 latency | 2100ms | 350ms |
| DB time | 380ms | 85ms |

---

## Debugging Tools

1. **Django Debug Toolbar** — Shows all queries, duplicates highlighted
2. **`connection.queries`** — Programmatic access to query log in development
3. **`EXPLAIN ANALYZE`** — Run the raw SQL in MySQL/PostgreSQL to see the execution plan
4. **APM (Datadog/New Relic)** — Production p90/p99 percentiles
5. **Silk** — Django middleware that profiles requests and stores results

Quick snippet to log queries in development:

```python
from django.db import connection, reset_queries
import functools

def query_debugger(func):
    @functools.wraps(func)
    def wrapper(*args, **kwargs):
        reset_queries()
        result = func(*args, **kwargs)
        queries = connection.queries
        print(f"Function: {func.__name__}")
        print(f"Number of Queries: {len(queries)}")
        print(f"Total Time: {sum(float(q['time']) for q in queries):.3f}s")
        return result
    return wrapper
```

---

## Summary of Patterns

| Pattern | When to Use | Typical Savings |
|---------|-------------|-----------------|
| `select_related` | FK/OneToOne access in loops | 5-50x fewer queries |
| Pre-fetch outside loop | Business logic N+1 | 3-20x fewer queries |
| Aggregate with Case/When | Multiple exists/count checks | 2-5x fewer queries |
| Reuse loaded objects | Same relation accessed twice | 1 fewer query per occurrence |
| Split endpoint | One slow sub-query blocks render | 50-80% faster perceived load |
| EXISTS instead of JOIN | Checking existence, not fetching data | 2-10x on large tables |

The key mindset shift: **think in sets, not loops.** Every time you write a `for` loop that accesses a related object, ask: "Can I fetch all of these in one query before the loop?"