Model index reference

Index classes ease creating database indexes. They can be added using theMeta.indexes option. This documentexplains the API references of Index which includes the indexoptions.

Referencing built-in indexes

Indexes are defined in django.db.models.indexes, but for conveniencethey’re imported into django.db.models. The standard convention isto use from django.db import models and refer to the indexes asmodels.<IndexClass>.

Index options

  • class Index(fields=(), name=None, db_tablespace=None, opclasses=(), condition=None)
  • Creates an index (B-Tree) in the database.

fields

  • Index.fields
  • A list or tuple of the name of the fields on which the index is desired.

By default, indexes are created with an ascending order for each column. Todefine an index with a descending order for a column, add a hyphen before thefield’s name.

For example Index(fields=['headline', '-pub_date']) would create SQL with(headline, pub_date DESC). Index ordering isn’t supported on MySQL. In thatcase, a descending index is created as a normal index.

name

  • Index.name
  • The name of the index. If name isn’t provided Django will auto-generate aname. For compatibility with different databases, index names cannot be longerthan 30 characters and shouldn’t start with a number (0-9) or underscore (_).

Partial indexes in abstract base classes

You must always specify a unique name for an index. As such, youcannot normally specify a partial index on an abstract base class, sincethe Meta.indexes option isinherited by subclasses, with exactly the same values for the attributes(including name) each time. To work around name collisions, part of thename may contain '%(applabel)s' and '%(class)s', which arereplaced, respectively, by the lowercased app label and class name of theconcrete model. For example Index(fields=['title'],name='%(app_label)s%(class)s_title_index').

Changed in Django 3.0:Interpolation of '%(app_label)s' and '%(class)s' was added.

db_tablespace

  • Index.db_tablespace
  • The name of the database tablespace to use forthis index. For single field indexes, if db_tablespace isn’t provided, theindex is created in the db_tablespace of the field.

If Field.db_tablespace isn’t specified (or if the index uses multiplefields), the index is created in tablespace specified in thedb_tablespace option inside the model’sclass Meta. If neither of those tablespaces are set, the index is createdin the same tablespace as the table.

See also

For a list of PostgreSQL-specific indexes, seedjango.contrib.postgres.indexes.

opclasses

  • Index.opclasses
  • New in Django 2.2:

The names of the PostgreSQL operator classes to use forthis index. If you require a custom operator class, you must provide one foreach field in the index.

For example, GinIndex(name='json_index', fields=['jsonfield'],opclasses=['jsonb_path_ops']) creates a gin index on jsonfield usingjsonb_path_ops.

opclasses are ignored for databases besides PostgreSQL.

Index.name is required when using opclasses.

condition

  • Index.condition
  • New in Django 2.2:

If the table is very large and your queries mostly target a subset of rows,it may be useful to restrict an index to that subset. Specify a condition as aQ. For example, condition=Q(pages__gt=400)indexes records with more than 400 pages.

Index.name is required when using condition.

Restrictions on PostgreSQL

PostgreSQL requires functions referenced in the condition to be marked asIMMUTABLE. Django doesn’t validate this but PostgreSQL will error. Thismeans that functions such as Date functions andConcat aren’t accepted. If you storedates in DateTimeField, comparison todatetime objects may require the tzinfo argumentto be provided because otherwise the comparison could result in a mutablefunction due to the casting Django does for lookups.

Restrictions on SQLite

SQLite imposes restrictionson how a partial index can be constructed.

Oracle

Oracle does not support partial indexes. Instead, partial indexes can beemulated using functional indexes. Use a migration to add the index using RunSQL.

MySQL and MariaDB

The condition argument is ignored with MySQL and MariaDB as neithersupports conditional indexes.