Model index reference¶
Index classes ease creating database indexes. They can be added using the
Meta.indexes option. This document
explains the API references of Index which includes the index
options.
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>.
Index options¶
-
class
Index(*expressions, fields=(), name=None, db_tablespace=None, opclasses=(), condition=None, include=None)¶ Creates an index (B-Tree) in the database.
expressions¶
-
Index.expressions¶
Positional argument *expressions allows creating functional indexes on
expressions and database functions.
For example:
Index(Lower('title').desc(), 'pub_date', name='lower_title_date_idx')
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:
Index(F('height') * F('weight'), Round('weight'), name='calc_idx')
creates an index on the result of multiplying fields height and weight
and the weight rounded to the nearest integer.
Index.name 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
Concat() 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.
By default, indexes are created with an ascending order for each column. To define an index with a descending order for a column, add a hyphen before the field’s name.
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.
name¶
-
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 Meta.indexes 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 database tablespace 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
db_tablespace 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.
opclasses¶
-
Index.opclasses¶
The names of the PostgreSQL operator classes 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.
Index.name is required when using condition.
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 Date functions and
Concat aren’t accepted. If you store
dates in DateTimeField, 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 lookups.
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
Case expressions.
MySQL and MariaDB
The condition argument is ignored with MySQL and MariaDB as neither
supports conditional indexes.
include¶
-
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 (fields).
For example:
Index(name='covering_index', fields=['headline'], include=['pub_date'])
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 covering indexes.
Restrictions on PostgreSQL
PostgreSQL < 12 only supports covering B-Tree indexes, PostgreSQL 12+ also
supports covering GiST indexes, and PostgreSQL 14+ also
supports covering SP-GiST indexes.
Support for covering SP-GiST indexes with PostgreSQL 14+ was added.
