ListAPIView with the querset User.objects.all() makes 7 queries with only two users in the test data-CodePudding

I was creating an endpoint using drf to list users. While testing the code, I realized that it calls 7 queries.

models.py:(I think using django's User model will achieve the same result)

class CustomUserManager(BaseUserManager):
    """
    Custom user model manager where email is the unique identifiers
    for authentication instead of usernames.
    """

    def create_user(self, email, password, **extra_fields):
        """
        Create and save a User with the given email and password.
        """
        if not email:
            raise ValueError(_("The Email must be set"))
        email = self.normalize_email(email)
        user = self.model(email=email, **extra_fields)
        user.set_password(password)
        user.save()
        return user

    def create_superuser(self, email, password, **extra_fields):
        """
        Create and save a SuperUser with the given email and password.
        """
        extra_fields.setdefault("is_staff", True)
        extra_fields.setdefault("is_superuser", True)
        extra_fields.setdefault("is_active", True)

        if extra_fields.get("is_staff") is not True:
            raise ValueError(_("Superuser must have is_staff=True."))
        if extra_fields.get("is_superuser") is not True:
            raise ValueError(_("Superuser must have is_superuser=True."))
        return self.create_user(email, password, **extra_fields)
class BaseUser(AbstractUser):
    email = models.EmailField(_("email address"), unique=True)
    id_number = models.CharField(max_length=MID_LENGTH)
    username = None

    USERNAME_FIELD = "email"
    REQUIRED_FIELDS = [email]

    objects = CustomUserManager()

    def __str__(self) -> str:
        return self.email

serializers.py:

class UserSerializer(serializers.ModelSerializer):
    class Meta:
        model = models.BaseUser
        fields = "__all__"

api.py:

class ListUserView(ListAPIView):
    permission_classes = [IsAdminUser]
    queryset = models.BaseUser.objects.all().order_by("id")
    serializer_class = serializers.UserSerializer

test.py:

from . import models
from django.db import connection
from django.test.utils import CaptureQueriesContext
from django.urls import reverse
from django_seed import Seed
from rest_framework.test import APIClient, APITestCase


seeder = Seed.seeder()

CREDENTIALS = ["[email protected]", "12345678"]


class UserViewsTest(APITestCase):
    def setUp(self) -> None:
        #   Create user and login
        self.user = models.BaseUser.objects.create(email=CREDENTIALS[0], is_staff=True)
        self.user.set_password(CREDENTIALS[1])
        self.user.save()

        self.assertTrue(
            self.client.login(email=CREDENTIALS[0], password=CREDENTIALS[1])
        )

        #   Seed users
        seeder.add_entity(models.BaseUser, 1)
        seeder.execute()

    def tearDown(self) -> None:
        self.client.logout()

    def test_list_users(self):
        """
        7 queries:
            1. Logged in user session
            2. 2 of each of:
                i.   1 query for base user
                ii.  1 query for base user's group
                iii. 1 query for base user's permission
        """
        with CaptureQueriesContext(connection) as queries:
            response = self.client.get(reverse("accounts-list-users"))

            self.assertEqual(200, response.status_code)
            self.assertIsNotNone(response.content)

        print(queries.captured_queries)

What could be the reason on why it make 7 queries? I know one query is for the session stuff. Additionally, is there a way I can reduce the number of queries made?

Below is the output of print(queries.captured_queries):

[
    {
        "sql": "SELECT `django_session`.`session_key`, `django_session`.`session_data`, `django_session`.`expire_date` FROM `django_session` WHERE (`django_session`.`expire_date` > '2022-02-22 08:41:16.815976' AND `django_session`.`session_key` = 'hebjioxebumhz0y1mumtafwk3lfoj81h') LIMIT 21",
        "time": "0.001",
    },
    {
        "sql": "SELECT `accounts_baseuser`.`id`, `accounts_baseuser`.`password`, `accounts_baseuser`.`last_login`, `accounts_baseuser`.`is_superuser`, `accounts_baseuser`.`first_name`, `accounts_baseuser`.`last_name`, `accounts_baseuser`.`is_staff`, `accounts_baseuser`.`is_active`, `accounts_baseuser`.`date_joined`, `accounts_baseuser`.`email`, `accounts_baseuser`.`id_number` FROM `accounts_baseuser` WHERE `accounts_baseuser`.`id` = 1 LIMIT 21",
        "time": "0.000",
    },
    {
        "sql": "SELECT `accounts_baseuser`.`id`, `accounts_baseuser`.`password`, `accounts_baseuser`.`last_login`, `accounts_baseuser`.`is_superuser`, `accounts_baseuser`.`first_name`, `accounts_baseuser`.`last_name`, `accounts_baseuser`.`is_staff`, `accounts_baseuser`.`is_active`, `accounts_baseuser`.`date_joined`, `accounts_baseuser`.`email`, `accounts_baseuser`.`id_number` FROM `accounts_baseuser` ORDER BY `accounts_baseuser`.`id` ASC",
        "time": "0.000",
    },
    {
        "sql": "SELECT `auth_group`.`id`, `auth_group`.`name` FROM `auth_group` INNER JOIN `accounts_baseuser_groups` ON (`auth_group`.`id` = `accounts_baseuser_groups`.`group_id`) WHERE `accounts_baseuser_groups`.`baseuser_id` = 1",
        "time": "0.001",
    },
    {
        "sql": "SELECT `auth_permission`.`id`, `auth_permission`.`name`, `auth_permission`.`content_type_id`, `auth_permission`.`codename` FROM `auth_permission` INNER JOIN `accounts_baseuser_user_permissions` ON (`auth_permission`.`id` = `accounts_baseuser_user_permissions`.`permission_id`) INNER JOIN `django_content_type` ON (`auth_permission`.`content_type_id` = `django_content_type`.`id`) WHERE `accounts_baseuser_user_permissions`.`baseuser_id` = 1 ORDER BY `django_content_type`.`app_label` ASC, `django_content_type`.`model` ASC, `auth_permission`.`codename` ASC",
        "time": "0.001",
    },
    {
        "sql": "SELECT `auth_group`.`id`, `auth_group`.`name` FROM `auth_group` INNER JOIN `accounts_baseuser_groups` ON (`auth_group`.`id` = `accounts_baseuser_groups`.`group_id`) WHERE `accounts_baseuser_groups`.`baseuser_id` = 2",
        "time": "0.000",
    },
    {
        "sql": "SELECT `auth_permission`.`id`, `auth_permission`.`name`, `auth_permission`.`content_type_id`, `auth_permission`.`codename` FROM `auth_permission` INNER JOIN `accounts_baseuser_user_permissions` ON (`auth_permission`.`id` = `accounts_baseuser_user_permissions`.`permission_id`) INNER JOIN `django_content_type` ON (`auth_permission`.`content_type_id` = `django_content_type`.`id`) WHERE `accounts_baseuser_user_permissions`.`baseuser_id` = 2 ORDER BY `django_content_type`.`app_label` ASC, `django_content_type`.`model` ASC, `auth_permission`.`codename` ASC",
        "time": "0.000",
    },
]

CodePudding user response：

The 7 queries:

Get the session. To avoid this query, you could use e.g. the cached_db session backend.
Get the logged in user's details from the database. This could also be avoided with a different session backend that gets that data from cache (or maybe the session object itself) instead (but you'd have to be careful with e.g. cache invalidation).
List the users. Since you're listing the users, that's to be expected.
Get user 1's assigned groups.
Get user 1's assigned permissions.
Get user 2's assigned groups.
Get user 2's assigned permissions.

Queries from 4 to 7 can be avoided either by:

Eliding the data from the serializer. (You're currently using fields = "__all__".) If you the serializer doesn't serialize the permissions and groups, they're not fetched either.
Adding a suitable .prefetch_related() (since these are M2Ms) clause to the viewset's queryset, so these are done in 2 queries, not 2N queries.