Reduce Postgresql queries in Django ManyToMany TabularAdminInline

Question

I'm experiencing some performance issues within Django's Admin interface & Postgres. I've narrowed it down to a query that's preformed for each IngredientInline in my RecipeControl model. The more ingredients that exist within a Recipe the longer the page takes to load because is seems to want to load the queryset (Almost 2,000 records) for the IngredientInline multiple times.

I'm convinced the solution is to somehow pre-cache the queryset prior to loading the page, but I'm confused at how this works and don't know what kind of problems this could cause down the road. I've looked into the difference between prefetch_related vs select_related and have tried to use both but there doesn't seem to be any change in performance when doing either. I found this question also but I'm using the admin interface, not writing my own view. So how/which admin module do I properly override to produce the desired effect? Thanks for any help.

I have a model as follows:

class RecipeControl(models.Model):
    #recipe_name choice field needs to be a query set of all records containing "FG-Finished Goods"
    recipe_name = models.ForeignKey(items.IngredientList, related_name='recipe_name', limit_choices_to={'category': 'FG'})
    customer_recipe = models.ForeignKey(custmods.CustomerProfile, verbose_name='customer', related_name='customer_recipe')
    ingredients = models.ManyToManyField(items.IngredientList, through='RecipeIngredients')
    active_recipe = models.BooleanField(default=False)
    active_by = models.CharField(max_length=64, editable=False)
    revision = models.IntegerField(default=0)
    last_updated = models.DateTimeField(auto_now_add=True, editable=False)
    mixer_volume = models.DecimalField(verbose_name='Mixer Volume(L)', max_digits=16,decimal_places=3, blank=True, null=True)
    fill_factor = models.DecimalField(verbose_name='Fill %', max_digits=6,decimal_places=2,blank=True,null=True)
    def __str__(self):
        return "%s" % (self.recipe_name)

class RecipeIngredients(models.Model):
    recipe = models.ForeignKey(RecipeControl, related_name='recipe')
    ingredient = models.ForeignKey(items.IngredientList, related_name='ingredient')
    weight_tolerance = models.DecimalField(verbose_name="PPH Tolerance",max_digits=8, decimal_places=3, blank=True, null=False)
    recipe_weight = models.DecimalField(verbose_name="PPH",max_digits=16, decimal_places=3, blank=True, null=True)

    def __str__(self):
        return "%s" % (self.ingredient)

My admin.py file:

class IngredientInline(admin.TabularInline):
    model = RecipeIngredients
    #prefetch_related = ('ingredient',)
    readonly_fields = ('percentage', 'item_price','ext_price','SPG')
    fieldsets = [(None,{'fields':[('ingredient','item_price','ext_price','SPG','percentage','weight_tolerance','recipe_weight')]})]
    extra = 0
    template = 'admin/recipes/recipeingredients/edit_inline/tabular.html'

class RecipeView(admin.ModelAdmin):
    def save_model(self, request, obj, form, change): 
        obj.active_by = request.user.username
        obj.save()

    list_select_related = ['recipe_name', 'customer_recipe']
    list_display = ['recipe_name','customer_recipe','active_recipe','last_updated','active_by']
    list_display_links = ['recipe_name']
    list_filter = ['active_recipe']
    search_fields = ['recipe_name__name', 'recipe_name__item_code','customer_recipe__name']
    readonly_fields = ('last_updated','active_by','batch_weight','calculated_batch', 'recipe_gravity')

    fieldsets = [
        ('Recipe Information',{'fields': [('recipe_name','customer_recipe','active_recipe')]}),
        ('Audit Trail', {'fields': [('active_by','revision','last_updated')]}),
        ('Batch Weight Info',{'fields': [('batch_weight', 'mixer_volume', 'fill_factor','recipe_gravity', 'calculated_batch')]})
    ]
    inlines = [IngredientInline]

Alasdair · Accepted Answer · 2017-08-01T18:46:23.763

1

The UI is not ideal, but the quickest fix is to use raw_id_fields for any foreign keys with many possibilities.

class IngredientInline(admin.TabularInline):
    model = RecipeIngredients
    raw_id_fields = ['ingredients']

If you need a nicer UI, you could look for an external package, such as django-select2.

edited Aug 01 '17 at 18:46

answered Aug 01 '17 at 18:22

Alasdair

298,606
55
578
516

This is possible a solution =D Hopefully the users agree! I'm doing a small presentation for them next week and will run it by them. Thanks for the input! – Dan2theR Aug 01 '17 at 18:37
In addition to Alasdair's excellent answer above I found a great solution to my above problem later on by upgrading Django to v2.0 which supports Select2 widgets now. [Check it out](https://docs.djangoproject.com/en/dev/ref/contrib/admin/#django.contrib.admin.ModelAdmin.autocomplete_fields) – Dan2theR Sep 08 '18 at 21:08

Reduce Postgresql queries in Django ManyToMany TabularAdminInline

1 Answers1