Django ORM Query Optimization

Use this skill for query-count and object-loading problems. The usual target is an N+1 pattern where each rendered row or serialized object triggers more database work.

Workflow

1. Prove the query pattern.

- Capture query count and duplicate SQL for the slow path. - Identify the loop, serializer field, template access, admin display, or model property triggering related queries.

1. Choose the loading strategy.

- Use selectrelated() for single-valued relationships: ForeignKey and OneToOneField. - Use prefetchrelated() for many-valued relationships: reverse FK and many-to-many. - Use Prefetch() when the related queryset needs filters, ordering, annotations, or nested selectrelated(). - Use values() or valueslist() when model instances are unnecessary. - Use only() or defer() only when large columns are truly unused.

1. Keep access consistent.

- If you prefetch items, do not later call items.filter(...) and expect the prefetch cache to help. - If using iterator() with prefetchrelated(), provide chunksize. - Avoid putting broad prefetches into default managers unless every caller needs them.

1. Add a focused regression check when useful.

- Use a query-count assertion around the exact view or serializer path. - Avoid brittle global query-count tests.

See [relationship-and-column-loading.md](references/relationship-and-column-loading.md) for examples and review cues.

Anti-Patterns

• Adding prefetch_related() to every queryset without checking memory.
• Using only() and then touching deferred fields in a loop.
• Optimizing a queryset but leaving serializer method fields to issue per-row queries.
• Hiding queries in str(), model properties, template includes, or admin list methods.

Verification

Show the query count and duplicate-query pattern before and after, plus any wall-time or memory change.