Is there a drawback to adding multiple indexes to a model in Django?

Question

I'm working on a django dashboard to display data in a series of charts, and thusly need to set up different dataframes from my models.

When I load the dashboard up in production, it takes about 15 seconds for the view to be rendered. Another dashboard takes 40. After analyzing my code, it turns out my queries are very time consuming. I thought they wouldn't be since my Order model has about 600k objects, while the order_date filters should get them down to some 30k at most.

Below is my model:

class Order(models.Model):
    order = models.IntegerField(db_index=True, default=0)
    region = models.CharField(max_length=3, default="")
    country = models.CharField(db_index=True, max_length=2, default="")
    order_date = models.DateTimeField(db_index=True, default=datetime.datetime.now)
    user_name = models.CharField(db_index=True, max_length=256, null=True)
    order_status = models.CharField(max_length=256, null=True)
    quote_status = models.CharField(max_length=256, null=True)
    purchase_status = models.CharField(max_length=256, null=True)
    q_to_r = models.FloatField(db_index=True, null=True)
    r_to_p = models.FloatField(db_index=True, null=True)

And here is the snippet of code that takes 15 seconds to evaluate:

month = [4, 2022]

country = ["US", "CA", "UK"]

user_list = ["Eric", "Jane", "Mark"]
    
all_objects = Order.objects

first_data = all_objects.filter(order_date__month=month[0], order_date__year=month[1],
                                country__in=country, order_status__in=order_status, 
                                quote_status__in=quote_status,
                                purchase_status__in=purchase_status, user_name__in=user_list)

order_data = first_data.values_list("order", flat=True)
country_data = first_data.values_list("country", flat=True)

df = pd.DataFrame({"Order": order_data,
                   "Country": country_data})

The df instance in particular is what takes the bulk of the time the view takes to render.

Any input is appreciated. I tried adding indexes to my model, to no avail.

Thanks in advance!

user_4379151 · Answer

Filters in Django are converted into WHERE statements with ANDs. You can in usually speed up those queries by putting an index on each field that is used in the filter statement, e.g. order_date, country, order_status, quote_status, purchase_status, user_name. Be aware though that which index a DBMS uses can vary and you want to run a query profiler, e.g. SQL EXPLAIN to understand which indexes have the expected effect.
I would have expected fields like country and user to be foreign keys, not simple lists. If you have FKs you want to add select_related() to your query to make sure you are doing a SQL join and not loading them one by one.
For best results I recommend using a profiler like django-debug-toolbar, which can help a lot to identify bottlenecks in your queries and find the right approach.

Is there a drawback to adding multiple indexes to a model in Django?

1 Answers