Django ORM Query Optimization

What this prompt does

This prompt makes the AI audit and optimize a slow Django page, targeting the N+1 queries that are the usual culprit. You describe the [page_description], the [current_query_count] and [current_duration], the [models_involved], and their [relationships], and the model walks each template loop and serializer to decide between select_related (ForeignKey/OneToOne) and prefetch_related (many-to-many/reverse FK).

The structure works because it attacks query count in layers and proves the result. It replaces Python-loop aggregation with annotate/aggregate (Count, Sum, Avg, F, Case/When) to compute [aggregation_needs] in one query, uses Prefetch objects with custom querysets for [prefetch_customization] (limiting to recent reviews, the primary image), trims data transfer with values/only/defer on [heavy_fields], adds indexes for [slow_filters] including GIN for [gin_candidates], and caches [cacheable_data] via [cache_strategy]. It ends with a before/after comparison of query count and time — the number you show to prove the win.

When to use it

• A Django page feels slow and you suspect N+1 queries from a loop or serializer.
• You're computing counts and averages in Python loops that should be a single annotated query.
• You need to limit or filter prefetched related objects rather than loading them all.
• A view transfers heavy TextField or JSONField data it doesn't actually need.
• Frequently filtered columns lack indexes and need BTree or GIN coverage.
• You want a measurable before/after query count and timing to show a client or teammate.

Example output

Expect a structured optimization: a list of identified N+1 patterns with the chosen fix (select_related vs prefetch_related) per relationship, annotate/aggregate rewrites for [aggregation_needs], Prefetch objects with custom querysets for [prefetch_customization], values/only/defer suggestions for [heavy_fields], index definitions for [slow_filters] and GIN indexes for [gin_candidates], a caching plan for [cacheable_data] using [cache_strategy], and a before/after table of query count and execution time with trade-offs noted.

Pro tips

• Give an accurate [current_query_count] and [current_duration] — the optimization's value is the delta, so a real baseline makes the before/after credible.
• Match the fix to the relationship: select_related for [relationships] that are FK/OneToOne, prefetch_related for reverse FK and many-to-many; mixing them up adds queries instead of removing them.
• Fold [aggregation_needs] like average rating and review count into a single annotate call rather than counting in a template loop — that's usually the biggest single win.
• Use Prefetch with a sliced/filtered queryset for [prefetch_customization] (only the 3 most recent reviews) instead of prefetching everything and trimming in Python.
• Add indexes for [slow_filters] only where the query actually filters; an unused index slows writes for no read benefit, and reserve GIN for [gin_candidates] like array or search-vector fields.
• Cache [cacheable_data] that genuinely changes infrequently (category tree) and confirm an invalidation path, or you'll trade a slow page for a stale one.