OccurrenceViewSet list: N+1 queries on detections, best_prediction, best_identification

[mihow]

Problem

/api/v2/occurrences/ (list) issues N+1 queries per row in the response. Under low traffic this is mostly hidden; under concurrent multi-user load it can pin gunicorn workers for tens of seconds per request and stall every other endpoint sharing the pool.

Tracked in Sentry as AMI-PLATFORM-API-BMY (performance issue type: N+1, ongoing). Most-recent observation 2026-04-28; first seen 2025-09-16. ~2,800 events.

Evidence

Sentry's representative span shows the offending query repeated per occurrence row:

SELECT "main_detection"."path"
FROM "main_detection"
WHERE ("main_detection"."occurrence_id" = %s
       AND NOT ("main_detection"."path" IS NULL))
ORDER BY "main_detection"."frame_num" ASC, "main_detection"."timestamp" ASC

Same trace shows interleaved redis (cachalot/cache-key) calls per row, amplifying the cost.

In a recent multi-user load period (4-worker gunicorn pool, ~30 concurrent UI sessions), 158 hits to this endpoint clustered into a 10-minute window with individual span durations of 34–40 seconds (max 40,417 ms). With 4 workers, ~3 concurrent occurrences requests was enough to fully exhaust the pool. UI list/detail endpoints sharing the pool (/captures/, /jobs/, /deployments/{pk}/) backed up behind it; participants experienced "every page feels frozen" during this window.

Where

ami/main/api/views.py:1217 — OccurrenceViewSet.get_queryset:

def get_queryset(self) -> QuerySet["Occurrence"]:
    ...
    qs = qs.select_related("determination", "deployment", "event")
    qs = qs.with_detections_count().with_timestamps()
    qs = qs.with_identifications()
    ...
    if self.action != "list":
        qs = qs.prefetch_related(
            Prefetch("detections", queryset=Detection.objects.order_by("-timestamp").select_related("source_image"))
        )
    return qs

The detections prefetch is gated on self.action != "list", so the list path has no prefetch on detections. The list serializer (OccurrenceListSerializer, ami/main/api/serializers.py:1316) then triggers per-row queries through:

• detection_images field → Occurrence.detection_images() (ami/main/models.py:3221) — does Detection.objects.filter(occurrence=self).exclude(path=None).values_list("path"). This is the exact query Sentry detected.
• determination_details (ami/main/api/serializers.py:1367) — already has a # @TODO convert this to query methods to avoid N+1 queries. Currently at 100+ queries per page of 10 occurrences. comment.
• best_machine_prediction → Occurrence.best_prediction (models.py:3231) — runs self.predictions().order_by(...).first() per row.
• identifications (many=True) — needs prefetch_related("identifications") on the list queryset; right now the comment block above suggests with_identifications() annotates a count but doesn't prefetch the related rows.

best_identification (models.py:3243) is called from get_determination_details and is another per-row hit.

Proposed fix

Make the list path explicitly prefetch what the list serializer touches. Sketch:

def get_queryset(self) -> QuerySet["Occurrence"]:
    ...
    qs = qs.select_related("determination", "deployment", "event")
    qs = qs.with_detections_count().with_timestamps()

    if self.action == "list":
        # Detections needed only for `detection_images` field — limit to the columns we read.
        qs = qs.prefetch_related(
            Prefetch(
                "detections",
                queryset=Detection.objects.exclude(path=None)
                                          .order_by("frame_num", "timestamp")
                                          .only("id", "occurrence_id", "path", "frame_num", "timestamp"),
                to_attr="prefetched_detections_for_images",
            ),
            "identifications",
            # Plus whatever predictions/best_prediction needs — likely:
            # Prefetch("detections__classifications", queryset=Classification.objects.order_by("-score").select_related("taxon", "algorithm")),
        )
        # Have `detection_images()` consume `prefetched_detections_for_images` when present
        # to avoid re-querying.
    else:
        qs = qs.prefetch_related(
            Prefetch("detections", queryset=Detection.objects.order_by("-timestamp").select_related("source_image"))
        )

    qs = qs.with_identifications()
    qs = qs.apply_default_filters(project, self.request)
    return qs

Plus update Occurrence.detection_images() to prefer the prefetched attribute when present, and audit best_prediction / best_identification for whether they can be replaced by query annotations or prefetches.

Verification

• Under assertNumQueries, fetching a page of 25 occurrences should land at a small constant number of queries (single-digit), not 25 + N×detections + N×predictions + N×identifications.
• Sentry's own profiles for this transaction (sampled at high rate on staging) should show view.response.render time drop sharply.

Related

• Sentry issue ID: AMI-PLATFORM-API-BMY (internal)
• Existing TODO comment in OccurrenceListSerializer.get_determination_details: "Currently at 100+ queries per page of 10 occurrences"
• Issue Change determination_details to determination #252 ("Change determination_details to determination") touches the same surface; can be sequenced against this if convenient.

[mihow]

Claude says: Implementation plan for this issue, drafted with Michael. Posting publicly so reviewers can push back before code lands.

Goal

Eliminate the per-row N+1 on /api/v2/occurrences/ (Sentry AMI-PLATFORM-API-BMY) and add a guardrail that makes it hard to silently reintroduce.

Approach (two parts)

Part 1 — Fix the N+1 with reusable Prefetch helpers.
Part 2 — Add a runtime guardrail (django-zen-queries) that raises if the list serializer triggers a query during render.

The helpers fix the bug. The guardrail catches future regressions (for example, when someone adds a new SerializerMethodField that touches a non-prefetched relation).

File layout

New module: ami/main/models_future/occurrence.py

Exports canonical Prefetch factories so the prefetch shape lives in one place and cannot drift between viewset / serializer / future export consumers:

# ami/main/models_future/occurrence.py
from django.db.models import Prefetch
from ami.main.models import Detection, Classification, Identification

def prefetch_detection_images() -> Prefetch:
    """Detections with non-null path, ordered for detection_images()."""
    return Prefetch(
        "detections",
        queryset=Detection.objects.exclude(path=None)
            .only("id", "occurrence_id", "path", "frame_num", "timestamp")
            .order_by("frame_num", "timestamp"),
        to_attr="prefetched_detection_images",
    )

def prefetch_classifications_for_best_prediction() -> Prefetch:
    """All classifications across detections, with taxon+algorithm, for picking best in Python."""
    return Prefetch(
        "detections__classifications",
        queryset=Classification.objects.select_related("taxon", "algorithm"),
    )

def prefetch_identifications_for_best() -> Prefetch:
    return Prefetch(
        "identifications",
        queryset=Identification.objects.filter(withdrawn=False)
            .select_related("user", "taxon")
            .order_by("-created_at"),
        to_attr="prefetched_identifications",
    )

def prefetches_for_list_serializer() -> list[Prefetch]:
    return [
        prefetch_detection_images(),
        prefetch_classifications_for_best_prediction(),
        prefetch_identifications_for_best(),
    ]

Then in OccurrenceQuerySet (in ami/main/models.py):

def with_list_prefetches(self):
    from ami.main.models_future.occurrence import prefetches_for_list_serializer
    return self.prefetch_related(*prefetches_for_list_serializer())

Serializer changes

OccurrenceListSerializer is updated to read from the prefetched attrs (no fallback queries):

• detection_images → SerializerMethodField that iterates obj.prefetched_detection_images and yields URLs
• best_machine_prediction → SerializerMethodField that walks obj.detections.all() → d.classifications.all() (prefetch cache) and picks winner using BEST_MACHINE_PREDICTION_ORDER
• determination_details.identification → uses first item of obj.prefetched_identifications (already filtered to withdrawn=False and ordered by -created_at)
• identifications (many=True) → uses obj.prefetched_identifications

Existing model methods (detection_images(), best_prediction, best_identification) are left untouched. They have non-list callers — update_occurrence_determination, Identification.save signal, exports, admin, fixtures — where lazy is fine. We don't force strictness on those paths.

Viewset change

def get_queryset(self):
    project = self.get_active_project()
    qs = super().get_queryset().valid()
    if project:
        qs = qs.filter(project=project)
    qs = qs.select_related("determination", "deployment", "event")
    qs = qs.with_detections_count().with_timestamps()
    qs = qs.with_identifications()  # already prefetches identifications/taxon/user
    qs = qs.apply_default_filters(project, self.request)

    if self.action == "list":
        qs = qs.with_list_prefetches()
    else:
        qs = qs.prefetch_related(
            Prefetch("detections", queryset=Detection.objects.order_by("-timestamp").select_related("source_image"))
        )
    return qs

(with_identifications() already does prefetch_related("identifications", "identifications__taxon", "identifications__user"). We may drop or merge that with prefetch_identifications_for_best() to avoid a duplicate prefetch — to be decided during implementation.)

Guardrail: django-zen-queries

Library: https://github.com/dabapps/django-zen-queries (active, 2026 commits).

• Add django-zen-queries to requirements/base.txt, register zen_queries app.
• Apply QueriesDisabledViewMixin to OccurrenceViewSet. The mixin only affects GET requests; on .data rendering, any DB query raises QueriesDisabledError.
• That is exactly the "you forgot a prefetch" signal we want — no thread-locals, no env flags, no model-base inheritance.

Why this over alternatives:

• nplusone — last release 2022, global middleware, false-positive prone.
• django-seal — requires SealableModel base class on Occurrence + related models. Too invasive.
• django-zen-queries — view-scoped, raises, actively maintained, lets us roll out per endpoint as we shore each one up.

Spike before merging the mixin: verify that django-cachalot's cache-key lookups don't count as queries from queries_disabled's perspective. If they do, scope queries_dangerously_enabled() around the cache hit, or whitelist.

Verification

• New test under ami/main/tests/: assertNumQueries for a page of 25 occurrences with detections + classifications + identifications. Target: single-digit query count, stable regardless of page size.
• Manual: hit the endpoint on staging with the prefetch fix only (no mixin yet), check Sentry sees the per-row span disappear.
• Then enable the mixin and re-run regression suite to confirm no other endpoints we proxy through OccurrenceViewSet regress.

Rollout

1. PR1: prefetch helpers + serializer + viewset wiring + assertNumQueries test. No mixin yet. This delivers the perf win.
2. PR2 (after staging soak): cachalot spike result + QueriesDisabledViewMixin on OccurrenceViewSet. If clean, follow up extending the mixin to other hot list endpoints (/captures/, /jobs/).

Out of scope

• Changing existing model methods (detection_images, best_prediction, best_identification) — left as-is for non-list callers.
• OccurrenceViewSet detail path — already prefetches detections.
• The composite-index PR Add composite index main_detection(occurrence_id, timestamp DESC) #1251 — orthogonal; reviewed separately.

Open questions:

• Should with_identifications() be removed once prefetch_identifications_for_best() lands, or kept for non-list callers? Lean toward consolidating, but check call sites first.
• Would a single Subquery annotation for best_prediction (along the lines of the existing with_best_machine_prediction() queryset method) be cheaper than prefetching all classifications? Worth benchmarking under realistic data — but the prefetch path is straightforward to ship first.

[mihow]

Local measurement: prefetch helpers (no zen-queries yet)

Tested OccurrenceViewSet.list query count via CaptureQueriesContext against an in-test project with 25 occurrences (each with one detection, one classification, no human identifications). Cachalot cache cleared between runs.

Page size	Baseline (no fix)	With prefetch helpers	Reduction
limit=5	24	13	1.8x
limit=25	64	7	9.1x

Baseline grows ~1.6 queries per occurrence (clear N+1). Post-fix is flat regardless of page size.

The 7-query floor for limit=25 is roughly: COUNT, the occurrences page, + select_related joins on determination/deployment/event, + the three prefetches (detections-for-images, detections, classifications, identifications, identifications__taxon, identifications__user) — the prefetches add a constant per-page cost rather than per-row.

What's in the (incoming) PR

Branch fix/occurrence-list-queries:

1. New module ami/main/models_future/occurrence.py with reusable Prefetch factories and Python-side pick helpers:
   • prefetch_detection_images() — populates Occurrence.prefetched_detection_images
   • prefetch_classifications_for_best_prediction() — populates detections__classifications cache
   • prefetches_for_list_serializer() — aggregator
   • best_prediction_from_prefetch() / best_identification_from_prefetch() / detection_image_urls_from_prefetch() — read prefetched data without lazy queries
2. OccurrenceQuerySet.with_list_prefetches() calls the aggregator
3. OccurrenceListSerializer switches detection_images to a SerializerMethodField and routes determination_details + best_machine_prediction through prefetched data, with model-method fallback for non-list callers (admin, exports, etc.)
4. OccurrenceViewSet.get_queryset calls with_list_prefetches() only on the list path; detail path retains its existing prefetch
5. TestOccurrenceListQueryCount regression guard fails if query count grows by more than 5 between page sizes 5 and 25

Deferred to follow-up

• django-zen-queries QueriesDisabledViewMixin as a guardrail against future N+1 reintroduction (needs a cachalot-interaction spike first — cache-key lookups may register as queries)
• Any strict-mode behavior on the model accessors themselves (best_prediction, best_identification, detection_images) — kept lazy for non-list callers