Greater than Index in Django

Question

In Django-3.2 class Index get a positional argument expressions which allows to create functional indexes on expressions

Is it possible to create index on Integer field with expression greater? For example

My model:

class Product(models.Model):
    description = models.CharField(max_length=50)
    delivery_date = models.DateTimeField(null=True)
    quantity = models.IntegerField(validators=[MinValueValidator(0)])

Usually I have a filter (quantity>0). How to create expression index on that?

Answer 1

You can use an ExpressionWrapper to create a functional index:

from django.db.models import BooleanField, ExpressionWrapper, Index, Q

class Product(models.Model):
    # …

    class Meta:
        indexes = [
            Index(
                ExpressionWrapper(
                    Q(quantity__gt=0),
                    output_field=BooleanField()
                ),
            name='some_name_for_the_constraint'
          )
        ]

Which will be translated in SQL to:

CREATE INDEX `some_name_for_the_constraint`
          ON `app_name_product` ((`quantity` > 0));

Typically however a db_index=True [Django-doc], will suffice to speed up filter since these are typically implemented by some tree-like structure, and thus will determine the objects to retrieve in O(log n).

We thus can set this to:

class Product(models.Model):
    # …
    quantity = models.IntegerField(
        db_index=True,
        validators=[MinValueValidator(0)]
    )

This will work reasonable fast.