How to avoid distinct() when filtering on membership in multiple collections?

user_383804 0
Viewed 56

I'm wrestling with a fairly slow query in django that needs to display users filtered by membership of multiple groups. The resulting list needs to only contain unique users which is fine for small numbers, but becomes quite a problem when returning dealing with large querysets. Unfortunately, modfiying the database structure at this stage isn't really viable as this is a live product with a large active userbase.

Basic example of how the current setup looks:

class Group( models.Model ):
   name = models.CharField(max_length=50)

class User( models.Model ):
   name= models.CharField( max_length=100)
   groups = models.ManyToManyField( Group, related_name='members' )

class UserDisplayModule( models.Model ):
   display_groups = models.ManyToManyField( Group, related_name='display_modules' )

A view will typically load a module, then list all the users associated with the module via the assigned groups, eg:

class UserModuleMembers( ModelViewSet ):
    def get_queryset(self):
        user_module = self.get_module()
        return User.objects.filter(groups__in=module.display_groups.all()).distinct()[offset:offset+limit]

The actual models are quite a bit larger in terms of columns, but the relationships are as above really. A User may be a member of multiple groups, and multiple groups may be assigned to a module, hence the need for a distinct() clause here as an individual user may be selected multiple times if they feature in several groups that are attached to a module. The resulting queryset is then paginated.

The above has been working fine for small sized apps, but we're growing fast and are now dealing with situations where a view could contain 100,000+ users. Even with pagination this query is taking a couple of seconds to return. Without the distinct clause it's a lot faster, but we get duplicate records because of the multiple groups thing.

The underlying db is postgres, and I've tried doing .distinct( 'name', 'id' ) to restrict the columnds considered for 'uniqueness' but this makes no discernable difference.

If anyone can suggest an alternative approach I'd be really interested to hear!

django-rest-framework postgresql orm django python
1 Answers

I'm more concerned with the groups__in=module.display_groups.all() group part. While subqueries can be as fast as a simple JOIN, usually a lot of databases struggle with subqueries.

What might help is to write this with one condition so in terms of JOINs. This will also make the query simpler, which often means that the database can optimize the query better.

We can filter with:

User.objects.filter(groups__display_modules=module).distinct()
user_675790
Related