Django Admin Queryset Method Override; Poorly Performing Query

Question

I have a system with multiple Sites in it. Every User object in the system gets a Profile object attached to it which governs which Sites they have permission to interact with. The system also has an Article model which can belong to multiple sites. So, when a user loads up the Article admin page, I only want them to see Articles that are assigned to sites they have access to see. So, I override the queryset method on the Article's Admin class with something like this:

qs = model.objects.filter(sites__id__in=[x.id for x in user.get_profile().group_profile.allowed_sites.all()]).distinct()

Taking the user, getting the Sites they have access to, and pulling Articles whose site assignment is in that list. Problem is, this query becomes a real dog once you get up to a few hundred thousand articles and is exacerbated by the model itself being large, meaning that Django is pulling a lot of data and running a DISTINCT on a large column list.

I'm looking for ways to improve it but the queryset method doesn't seem to give me much of an out. I originally tried using the QuerySet function only() to pull only columns I need to draw the changelist, but then found that that caused post_save signals for the model not to fire when saved. I can't get rid of the distinct, because if I do, I'll get multiple results per site: i.e., if user can see Site A and Site B, and an Article belongs to both sites, the query would return the Article twice.

Answer 1

If you have a ManyToMany relationship you can't get rid off the distinct() because there have to be multiple joins done for every instance. Something you can do about your query (though I don't think that this will be some big performance boost), you don't have to do the '__in' querying with the ids:

qs = model.objects.filter(\
        sites__in=user.get_profile().group_profile.allowed_sites.all()).distinct()