Model index reference

    Referencing built-in indexes

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

    class Index(\expressions, fields=(), name=None, db_tablespace=None, opclasses=(), condition=None, include=None*)

    Creates an index (B-Tree) in the database.

    Index.expressions

    Positional argument *expressions allows creating functional indexes on expressions and database functions.

    For example:

    creates an index on the lowercased value of the title field in descending order and the pub_date field in the default ascending order.

    Another example:

    creates an index on the result of multiplying fields height and weight and the rounded to the nearest integer.

    is required when using *expressions.

    Restrictions on Oracle

    Oracle requires functions referenced in an index to be marked as DETERMINISTIC. Django doesn’t validate this but Oracle will error. This means that functions such as Random() aren’t accepted.

    Restrictions on PostgreSQL

    PostgreSQL requires functions and operators referenced in an index to be marked as IMMUTABLE. Django doesn’t validate this but PostgreSQL will error. This means that functions such as aren’t accepted.

    MySQL and MariaDB

    Functional indexes are ignored with MySQL < 8.0.13 and MariaDB as neither supports them.

    fields

    Index.fields

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

    For example Index(fields=['headline', '-pub_date']) would create SQL with (headline, pub_date DESC).

    MySQL and MariaDB

    Index ordering isn’t supported on MySQL < 8.0.1 and MariaDB < 10.8. In that case, a descending index is created as a normal index.

    Index.name

    The name of the index. If name isn’t provided Django will auto-generate a name. For compatibility with different databases, index names cannot be longer than 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, you cannot normally specify a partial index on an abstract base class, since the option is inherited by subclasses, with exactly the same values for the attributes (including name) each time. To work around name collisions, part of the name may contain '%(app_label)s' and '%(class)s', which are replaced, respectively, by the lowercased app label and class name of the concrete model. For example Index(fields=['title'], name='%(app_label)s_%(class)s_title_index').

    db_tablespace

    Index.db_tablespace

    The name of the to use for this index. For single field indexes, if db_tablespace isn’t provided, the index is created in the db_tablespace of the field.

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

    See also

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

    Index.opclasses

    The names of the to use for this index. If you require a custom operator class, you must provide one for each field in the index.

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

    opclasses are ignored for databases besides PostgreSQL.

    Index.name is required when using opclasses.

    condition

    Index.condition

    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 a Q. For example, condition=Q(pages__gt=400) indexes records with more than 400 pages.

    Restrictions on PostgreSQL

    PostgreSQL requires functions referenced in the condition to be marked as IMMUTABLE. Django doesn’t validate this but PostgreSQL will error. This means that functions such as and Concat aren’t accepted. If you store dates in , comparison to datetime objects may require the tzinfo argument to be provided because otherwise the comparison could result in a mutable function due to the casting Django does for .

    Restrictions on SQLite

    SQLite imposes restrictions on how a partial index can be constructed.

    Oracle

    Oracle does not support partial indexes. Instead, partial indexes can be emulated by using functional indexes together with expressions.

    MySQL and MariaDB

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

    Index.include

    A list or tuple of the names of the fields to be included in the covering index as non-key columns. This allows index-only scans to be used for queries that select only included fields (include) and filter only by indexed fields ().

    For example:

    will allow filtering on headline, also selecting pub_date, while fetching data only from the index.

    Using include will produce a smaller index than using a multiple column index but with the drawback that non-key columns can not be used for sorting or filtering.

    include is ignored for databases besides PostgreSQL.

    Index.name is required when using include.

    See the PostgreSQL documentation for more details about .

    Restrictions on PostgreSQL

    PostgreSQL < 12 only supports covering B-Tree indexes, PostgreSQL 12+ also supports covering GiST indexes, and PostgreSQL 14+ also supports covering .

    Changed in Django 4.1: