django-orm-query-optimization

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.

2. Choose the loading strategy.

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

3. Keep access consistent.

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

4. 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 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.