Django Filter Benchmark

Filter Benchmark

Performance-test Django ORM filters by generating bulk data, extracting the SQL query, and running EXPLAIN ANALYZE to verify index usage and query efficiency.

Overview

When adding or modifying filters in the Saleor GraphQL layer, it's critical to verify they perform well on large datasets — a filter that looks correct on 10 rows can cause full table scans on 100k+. This skill automates the full benchmarking workflow:

1. Identify — Read the filter code, understand what fields and joins it touches, check existing indexes
2. Generate data — Write a bulk population script that creates realistic, varied data targeting the exact fields the filter uses
3. Populate — Run the script in Django shell to reach 100k+ rows
4. Extract SQL — Get the actual query the filter produces from the Django ORM
5. EXPLAIN ANALYZE — Run the query plan analysis and check for seq scans, missing indexes, and performance issues
6. Report — Summarize findings and suggest concrete fixes (new indexes, query rewrites)

Step 1: Identify what to benchmark

Read the filter code to understand:

• Which model(s) are being filtered
• Which fields are involved (including related model fields for subquery filters)
• What indexes exist on those fields (check the model's Meta.indexes)
• What filter input combinations matter (e.g., date ranges, enum values, subqueries with Exists)

Look at the filter function source — it lives in saleor/graphql/<app>/filters.py. Understand the ORM query it builds (.filter(), Q(), Exists(), OuterRef(), etc.) because the test data must exercise all code paths.

Step 2: Generate bulk data script

Write a Python script that creates test data with enough variety to exercise the filter. The script should:

• Use bulk_create with ignore_conflicts=True for maximum speed
• Create data in batches (1000-5000 objects per batch) to avoid memory issues
• Vary the field values that the filter targets so the query planner sees realistic data distribution
• For date fields: spread values across a wide range (months/years), not just a narrow window
• For enum/choice fields: distribute across all possible values
• For related objects (e.g., events on a transaction): create multiple per parent with varied attributes
• Handle required foreign keys by creating or reusing minimal parent objects (orders, checkouts, etc.)
• Use timezone.now() and timedelta for date generation
• Override auto_now / auto_now_add fields via bulk_update after creation when needed

The script should be a function that creates ~1000 objects per call. The user will call it in a loop to reach 100k+.

Example structure:

import random
from datetime import timedelta
from decimal import Decimal

from django.utils import timezone

from saleor.payment.models import TransactionItem, TransactionEvent
from saleor.payment import TransactionEventType


def populate(batch_size=1000):
    """Create batch_size TransactionItems with varied events."""
    now = timezone.now()

    # Create TransactionItems
    transactions = TransactionItem.objects.bulk_create(
        [
            TransactionItem(
                currency="USD",
                charged_value=Decimal("10.00"),
                # Vary dates across 2 years
                created_at=now - timedelta(days=random.randint(0, 730)),
            )
            for _ in range(batch_size)
        ]
    )

    # Override auto_now/auto_now_add fields with varied values via bulk_update
    for t in transactions:
        t.created_at = now - timedelta(days=random.randint(0, 730))
        t.modified_at = now - timedelta(days=random.randint(0, 365))
    TransactionItem.objects.bulk_update(transactions, ["created_at", "modified_at"])

    # Create events for each transaction
    event_types = [e.value for e in TransactionEventType]
    events = []
    for t in transactions:
        num_events = random.randint(1, 4)
        for _ in range(num_events):
            events.append(
                TransactionEvent(
                    transaction=t,
                    type=random.choice(event_types),
                    amount_value=Decimal("10.00"),
                    currency="USD",
                    created_at=now - timedelta(days=random.randint(0, 730)),
                )
            )
    TransactionEvent.objects.bulk_create(events)
    print(
        f"Created {len(transactions)} transactions and {len(events)} events. "
        f"Total: {TransactionItem.objects.count()} transactions, "
        f"{TransactionEvent.objects.count()} events"
    )

Adapt this pattern to whatever model and filter is being tested. The key principle: the data must vary on exactly the fields the filter touches.

Step 3: Populate the database

Run the population script in Django shell. Activate the venv first:

source .venv/bin/activate && python manage.py shell

Then in the shell, run the function in a loop:

for i in range(100):
    populate()

This reaches 100k objects. Monitor the output to confirm counts are growing. If the database already has data from a previous run, check counts first and only add what's needed.

Step 4: Extract the SQL query

Get the actual SQL that the filter produces. There are two approaches — use whichever fits best:

Approach A: From the filter function directly

Open a Django shell and call the filter function with representative input values, then print the query:

from saleor.payment.models import TransactionItem
from saleor.graphql.payment.filters import filter_where_created_at_range

qs = TransactionItem.objects.all()
filtered = filter_where_created_at_range(qs, None, {"gte": "2025-01-01T00:00:00Z", "lte": "2025-06-01T00:00:00Z"})
print(str(filtered.query))