Queries are too slow; prefetch_related not solving the problem

Question

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.

Answer 1

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.