Optimizing a slow django queryset from a large and growing dataset

Question

I have a page that loads way too slow. Somehow I need to improve the way it's data is queried (caching? partial load/pages? etc.)

Note I am a django noob and haven't quite wrapped my head around model.Manager and and models.query.QuerySet so if this setup looks awkward....

Currently, the page is taking about 18 seconds to load the queryset, and it currently only has about 500 records. There will be on average about 100 new records a day.

The database is Postgresql

The slow view:

def approvals(request):
    ...
    approved_submissions = QuestSubmission.objects.all_approved()
    ...

The slow queryset:

class QuestSubmissionQuerySet(models.query.QuerySet):
    ...

    def approved(self):
        return self.filter(is_approved=True)

    def completed(self):
         return self.filter(is_completed=True).order_by('-time_completed')

    ...

class QuestSubmissionManager(models.Manager):
    def get_queryset(self):
        return QuestSubmissionQuerySet(self.model, using=self._db)

    def all_approved(self, user=None):
        return self.get_queryset().approved().completed()

    ...

Resulting SQL from QuestSubmission.objects.all_approved():

'SELECT "quest_manager_questsubmission"."id", "quest_manager_questsubmission"."quest_id", "quest_manager_questsubmission"."user_id", "quest_manager_questsubmission"."ordinal", "quest_manager_questsubmission"."is_completed", "quest_manager_questsubmission"."time_completed", "quest_manager_questsubmission"."is_approved", "quest_manager_questsubmission"."time_approved", "quest_manager_questsubmission"."timestamp", "quest_manager_questsubmission"."updated", "quest_manager_questsubmission"."game_lab_transfer" FROM "quest_manager_questsubmission" WHERE ("quest_manager_questsubmission"."is_approved" = True AND "quest_manager_questsubmission"."is_completed" = True) ORDER BY "quest_manager_questsubmission"."time_completed" DESC'

The slow model:

class QuestSubmission(models.Model):
    quest = models.ForeignKey(Quest)
    user = models.ForeignKey(settings.AUTH_USER_MODEL, related_name="quest_submission_user")
    ordinal = models.PositiveIntegerField(default = 1, help_text = 'indicating submissions beyond the first for repeatable quests')
    is_completed = models.BooleanField(default=False)
    time_completed = models.DateTimeField(null=True, blank=True)
    is_approved = models.BooleanField(default=False)
    time_approved = models.DateTimeField(null=True, blank=True)
    timestamp = models.DateTimeField(auto_now=True, auto_now_add=False)
    updated = models.DateTimeField(auto_now=False, auto_now_add=True)
    game_lab_transfer = models.BooleanField(default = False, help_text = 'XP not counted')

    class Meta:
        ordering = ["time_approved", "time_completed"]

    objects = QuestSubmissionManager()

    #other methods
    ....

What strategies are available to fix this? I tried using django's Paginator, but it only seems to display in pages, but it still loads the entire queryset.

Answer 1

The first thing to look at:

• Is this query slow because it is returning a very large result set?

or

• Is this query slow because it's taking a while to filter down the table?

Assuming the former, you don't have a lot of good options besides "return less data".

If it's the latter, you should probably run an EXPLAIN on the database, but right off the bat I'd say you probably want an index, possibly on (is_approved, is_completed). Which can be done with:

class Meta:
    index_together = [
        ["is_completed", "is_approved"],
    ]