Best index for a Django model when filtering on one field and ordering on another field

Question

I use Django 2.2 linked to PostgreSQL and would like to optimise my database queries. Given the following simplified model:

class Person(model.Models):
    name = models.CharField()
    age = models.Integerfield()

on which I have to do the following query, say,

Person.objects.filter(age__gt=20, age__lt=30).order_by('name')

What would be the best way to define the index in the model Meta field so as to optimise the query? Which of these four options would be best?

    class Meta
        indexes = [models.Index(fields=['age','name']),
                   models.Index(fields=['name','age']),
                   models.Index(fields=['name']),
                   models.Index(fields=['age'])]

Is it, for example, possible to prevent sorting when the query is done? Thank you.

Answer 1

This is really a postgres question, as much as a Django question, right?

I think there is a good chance that creating an index on your sort field will help with performance. But there are a lot of caveats and if it's really important to you, you might want to do some testing focused on Postgres (ie, just run some queries in psql and see what happens). Some caveats include:

• it might depend on which type of index is created for you by Django
• Postgres, of course, does not always use index anyway when running a query but it should if you've got the right one and the right query (and if there is enough data in the table to justify loading the index)
• it might matter how your SELECT is formatted by Django

I suggest you create your model and specify that you want the index. Then use Django Debug Toolbar to find out what SELECT query is really getting run. Then, open a dbshell with manage.py dbshell (aka psql) and run ANALYZE with that same select. Assuming you can interpret the output, you will see for yourself whether your index is coming in to play. Paste the ANALYZE output here, if you like.

According to this Postgres documentation ORDER BY can be assisted by a btree index. The b-tree type of index is what Django will create for you by default.

So, why don't you try this:

class Meta:
    indexes = [models.Index(fields=['age', 'name'])]

Then go run an EXPLAIN ANALYZE in dbshell and see whether it worked.