How to retrieve count objects faster on Django?

user_10972294 0
Viewed 441

My goal is to optimize the retrieval of the count of objects I have in my Django model.

I have two models:

  • Users
  • Prospects

It's a one-to-many relationship. One User can create many Prospects. One Prospect can only be created by one User.

I'm trying to get the Prospects created by the user in the last 24 hours.

Prospects model has roughly 7 millions rows on my PostgreSQL database. Users only 2000.

My current code is taking to much time to get the desired results.

I tried to use filter() and count():

import datetime

# get the date but 24 hours earlier
date_example = datetime.datetime.now() - datetime.timedelta(days = 1)

# Filter Prospects that are created by user_id_example
# and filter Prospects that got a date greater than date_example (so equal or sooner)
today_prospects = Prospect.objects.filter(user_id = 'user_id_example', create_date__gte = date_example)

# get the count of prospects that got created in the past 24 hours by user_id_example
# this is the problematic call that takes too long to process
count_total_today_prospects = today_prospects.count()

I works, but it takes too much time (5 minutes). Because it's checking the entire database instead of just checking, what I though it would: only the prospects that got created in the last 24 hours by the user.

I also tried using annotate but it's equally slow, because it's ultimately doing the same thing than the regular .count():

today_prospects.annotate(Count('id'))

How can I get the count in a more optimized way?

postgresql django python
2 Answers

Assuming that you don't have it already, I suggest adding an index that includes both user and date fields (make sure that they are in this order, first the user and then the date, because for the user you are looking for an exact match but for the date you only have a starting point). That should speed up the query.

For example:

class Prospect(models.Model):
    ...

    class Meta:
        ...
        indexes = [
            models.Index(fields=['user', 'create_date']),
        ]
        ...

This should create a new migration file (run makemigrations and migrate) where it adds the index to the database.

After that, your same code should run a bit faster:

count_total_today_prospects = Prospect.objects\
    .filter(user_id='user_id_example', create_date__gte=date_example)\
    .count()
user_92256710

Django's documentation:

A count() call performs a SELECT COUNT(*) behind the scenes, so you should always use count() rather than loading all of the record into Python objects and calling len() on the result (unless you need to load the objects into memory anyway, in which case len() will be faster). Note that if you want the number of items in a QuerySet and are also retrieving model instances from it (for example, by iterating over it), it’s probably more efficient to use len(queryset) which won’t cause an extra database query like count() would. If the queryset has already been fully retrieved, count() will use that length rather than perform an extra database query.

Take a look at this link: https://docs.djangoproject.com/en/3.2/ref/models/querysets/#count.

Try to use len().

user_143483280
Related