Django models: add index on date, desc order

Question

I am trying to make Django models create me an index on a date field on descending (DESC) order and I can't find a way to do it. Basically, I need to do something like the following SQL (in Posgres):

CREATE INDEX "idx_name" ON "table" ("date" DESC);

The closest I can get is to add db_index=True to the model which produces me the following SQL:

CREATE INDEX "idx_name" ON "table" ("date");

Close, but not quite it. The DESC makes a lot of difference here since my query returns me objects from the newest to the oldest.

I know I can add raw sql to the migrations, but it would be better if Django could figure it out for me.

Any ideas?

Thanks!

Answer 1

I believe what you're after is now supported as of Django 1.11.

class Meta:
    indexes = [
        models.Index(fields=['last_name', 'first_name',]),
        models.Index(fields=['-date_of_birth',]),
    ]

Official reference here, here, and release notes.

Answer 2

You would have to use your raw SQL to create the descending indexing as you want, but there are a few ways to achieve similar results without doing creating it.

You can accomplish your desired functionality by adding the db_index=True to the field and ordering = ['-date'] [4] to the metadata of the model.

If you are only ordering by a single column then using Django's ordering with Postgres is not necessarily expensive. The Postgres planner/optimizer will decide [2, 3] whether to use indices or a full table scan and sort to form the relation.

To only perform ordering on a single QuerySet basis you can use the order_by [1] method. From the Django docs:

Entry.objects.filter(pub_date__year=2005).order_by('-pub_date', 'headline')

The result above will be ordered by pub_date descending, then by headline ascending. The negative sign in front of "-pub_date" indicates descending order.

Another solution instead of using raw SQL would be to add the index field and then use the reverse() [0] method of the QuerySet with which you are trying to get the date field in descending order. This also requires the db_index=True and that a default order_by is defined for the field. This method is subject to the same caveats that setting ordering is, so steer clear of it if you are ordering based on multiple fields with differing scan directions.

Yet another solution is to override the default Django SQL template for index creation. The template is located at django.db.backends.base.schema.sql_create_index [5] but you might run in to unforeseen errors if you do this.

Citations since I can't post more than two links: https://bpaste.net/raw/6001004893c1