Queries are too slow; prefetch_related not solving the problem

We are using Django 2.1 for Speedy Net. I have pages which display about 96 users per page, and for each user I want to display how many friends he has on Speedy Match, with an active email address. The query checks for each user if (self.email_addresses.filter(is_confirmed=True).exists()) is true:

def has_confirmed_email(self):
    return (self.email_addresses.filter(is_confirmed=True).exists())

For each user of 96 users, it checks all his friends and runs this query - more than hundreds of times per page. The query for fetching the users is User.objects.all().order_by(<...>), and then for each user it checks this query:

qs = self.friends.all().prefetch_related("from_user", "from_user__{}".format(SpeedyNetSiteProfile.RELATED_NAME), "from_user__{}".format(SpeedyMatchSiteProfile.RELATED_NAME), "from_user__email_addresses").distinct().order_by('-from_user__{}__last_visit'.format(SiteProfile.RELATED_NAME))

I added prefetch_related in the User's manager model:

def get_queryset(self):
    from speedy.net.accounts.models import SiteProfile as SpeedyNetSiteProfile
    from speedy.match.accounts.models import SiteProfile as SpeedyMatchSiteProfile
    return super().get_queryset().prefetch_related(SpeedyNetSiteProfile.RELATED_NAME, SpeedyMatchSiteProfile.RELATED_NAME, "email_addresses").distinct()

But adding "email_addresses" and "from_user__email_addresses" to prefetch_related doesn't make the page load faster - it takes about 16 seconds to load the page. When loading the page without checking if each friend has a confirmed email address it takes about 3 seconds to load the page. Is there a way I can load all the email addresses of the users once and not each time a user is checked? Actually I would also like the friends query to be loaded once and not 96 times per page (once for each user), but the page loads in 3 seconds so it doesn't matter that much. But if I could query the friends table once it would have been better.

The queries are caused by the following line (link):

if ((self.user.has_confirmed_email()) and (step >= self.activation_step)):

This is called by is_active_and_valid which is called by get_matching_rank, to check if the user is a match of the specific user. This is called by method get_friends in the model.

Update #1: If I change to return True in def has_confirmed_email(...) in the model, the page loads only 3 seconds faster (13 instead of 16) so there might be more performance-related issues in this page.

If I disable the functionality of get_matching_rank and replace it with a plain return 5, the page loads much faster. But of course we need the functionality of this function. Maybe we can just cache for a few minutes the results of this function when called for sets of two specific users?

Update #2: I want to add a boolean field to the user model, which will be true if the user has a confirmed email address. And this field will be updated each time an email address is saved or deleted. I know how to override the save method, but how do I update this field when an email address gets deleted? It may also be deleted by the admin.

I think I should use signals such as post_save and post_delete.

For the prefetch to have any effect you would have to be using it on the User model - it's hard to tell if you're doing that from what you've included.

Without prefetching friends for each user doing self.friends.all() is going to cause a query. To get around the query using prefetch you could do one of the following:

User.objects.prefetch_related('friends')

Or you could use a Prefetch object to further filter:

User.objects.prefetch_related(Prefetch(
    'friends',
    queryset=Friend.objects.filter(is_confirmed=True)
)

A Count annotation using the filter keyword argument is going to be much quicker.

from djang.db.models import Count, Q

qs = User.objects.annotate(
    friend_count=Count('friends', filter=Q(friends__is_confirmed=True)
)

> But adding "email_addresses" and "from_user__email_addresses" to prefetch_related doesn't make the page load faster ...

That is because self.email_addresses.filter(is_confirmed=True).exists() doesn't use the prefetched QuerySet.

To use the prefetched self.email_addresses, filter in memory:

def has_confirmed_email(self):
    if self.email_addresses.all()._result_cache is not None:
        return any(email_address.is_confirmed for email_address in self.email_addresses.all())

    return (self.email_addresses.filter(is_confirmed=True).exists())

Note: If not prefetched, then the improved implementation still hits the database on every has_confirmed_email function call since the .filter still creates a new QuerySet. To handle this, make has_confirmed_email a Django @cached_property.

Explanation

From https://docs.djangoproject.com/en/3.0/ref/models/querysets/#prefetch-related:

> Remember that, as always with QuerySets, any subsequent chained methods which imply a different database query will ignore previously cached results, and retrieve data using a fresh database query. ...
>
> python
> >>> pizzas = Pizza.objects.prefetch_related('toppings')
> >>> [list(pizza.toppings.filter(spicy=True)) for pizza in pizzas]
>
>
> ... The prefetched cache can’t help here; in fact it hurts performance, since you have done a database query that you haven’t used. So use this feature with caution!

I added a field to the User model:

has_confirmed_email = models.BooleanField(default=False)

And the method:

def _update_has_confirmed_email_field(self):
    self.has_confirmed_email = (self.email_addresses.filter(is_confirmed=True).count() > 0)
    self.save_user_and_profile()

And:

@receiver(signal=models.signals.post_save, sender=UserEmailAddress)
def update_user_has_confirmed_email_field_after_saving_email_address(sender, instance: UserEmailAddress, **kwargs):
	instance.user._update_has_confirmed_email_field()


@receiver(signal=models.signals.post_delete, sender=UserEmailAddress)
def update_user_has_confirmed_email_field_after_deleting_email_address(sender, instance: UserEmailAddress, **kwargs):
	instance.user._update_has_confirmed_email_field()

And in the User model:

def delete(self, *args, **kwargs):
    if ((self.is_staff) or (self.is_superuser)):
        warnings.warn('Can’t delete staff user.')
        return False
    else:
        self.email_addresses.all().delete() # This is necessary because of the signal above.
        return super().delete(*args, **kwargs)

I also removed the friends count from the admin view, and now the admin view pages load in about 1.5 seconds.