使用DRF视图实现Django默认User模型接口时大量数据库查询的优化问题

Hey there, let's break down why your prefetch_related isn't cutting down those query counts and how to fix this:

First, why does user_permissions exist on the User model? #

Even though you don't see it defined explicitly in the User model, it comes from the PermissionsMixin that AbstractUser (which User inherits from) uses. This mixin adds the user_permissions many-to-many field behind the scenes, which is why it shows up in your API responses and DRF forms.

The main issue: a tiny spelling mistake! #

Looking at your prefetch code, you have a typo: Permission.obects is missing the letter j — it should be Permission.objects. That small error means your Prefetch isn't being applied at all, so DRF is still making N+1 queries for each permission entry.

The corrected code #

Here's the fixed version of your view's queryset:

from django.contrib.auth.models import Permission
from django.db.models import Prefetch

class UserDetailView(RetrieveUpdateDestroyAPIView):
    queryset = User.objects.prefetch_related(
        Prefetch('user_permissions', queryset=Permission.objects.select_related('content_type'))
    )
    serializer_class = SuperUserDetailSerializer

Why this works: #

• prefetch_related is necessary for user_permissions because it's a many-to-many field (Django can't join these in a single query like it does with foreign keys).
• Adding select_related('content_type') to the Permission queryset optimizes the lookup for each permission's associated content type, which would otherwise trigger another round of unnecessary queries.

Extra checks to ensure it's working: #

• Verify no custom serializer logic is overriding this: If you're using a SerializerMethodField for user_permissions, it might bypass the prefetched data. Stick with the default ModelSerializer handling for this field, or adjust your method to use obj.user_permissions.all() (which uses the prefetched data) instead of making a new query.
• Use django-debug-toolbar: This tool will show you exactly which queries are being executed, so you can confirm the prefetch reduces the count from 200+ to just a handful.
• Check for custom get_queryset overrides: If your view has a custom get_queryset method, make sure it returns the prefetched queryset instead of plain User.objects.all().

内容的提问来源于stack exchange，提问作者Manish Shah