| 1 |
===================== |
| 2 |
Model index reference |
| 3 |
===================== |
| 4 |
|
| 5 |
.. module:: django.db.models.indexes |
| 6 |
|
| 7 |
.. currentmodule:: django.db.models |
| 8 |
|
| 9 |
Index classes ease creating database indexes. They can be added using the |
| 10 |
:attr:`Meta.indexes <django.db.models.Options.indexes>` option. This document |
| 11 |
explains the API references of :class:`Index` which includes the `index |
| 12 |
options`_. |
| 13 |
|
| 14 |
.. admonition:: Referencing built-in indexes |
| 15 |
|
| 16 |
Indexes are defined in ``django.db.models.indexes``, but for convenience |
| 17 |
they're imported into :mod:`django.db.models`. The standard convention is |
| 18 |
to use ``from django.db import models`` and refer to the indexes as |
| 19 |
``models.<IndexClass>``. |
| 20 |
|
| 21 |
``Index`` options |
| 22 |
================= |
| 23 |
|
| 24 |
.. class:: Index(*expressions, fields=(), name=None, db_tablespace=None, opclasses=(), condition=None, include=None) |
| 25 |
|
| 26 |
Creates an index (B-Tree) in the database. |
| 27 |
|
| 28 |
``expressions`` |
| 29 |
--------------- |
| 30 |
|
| 31 |
.. attribute:: Index.expressions |
| 32 |
|
| 33 |
Positional argument ``*expressions`` allows creating functional indexes on |
| 34 |
expressions and database functions. |
| 35 |
|
| 36 |
For example:: |
| 37 |
|
| 38 |
Index(Lower("title").desc(), "pub_date", name="lower_title_date_idx") |
| 39 |
|
| 40 |
creates an index on the lowercased value of the ``title`` field in descending |
| 41 |
order and the ``pub_date`` field in the default ascending order. |
| 42 |
|
| 43 |
Another example:: |
| 44 |
|
| 45 |
Index(F("height") * F("weight"), Round("weight"), name="calc_idx") |
| 46 |
|
| 47 |
creates an index on the result of multiplying fields ``height`` and ``weight`` |
| 48 |
and the ``weight`` rounded to the nearest integer. |
| 49 |
|
| 50 |
:attr:`Index.name` is required when using ``*expressions``. |
| 51 |
|
| 52 |
.. admonition:: Restrictions on Oracle |
| 53 |
|
| 54 |
Oracle requires functions referenced in an index to be marked as |
| 55 |
``DETERMINISTIC``. Django doesn't validate this but Oracle will error. This |
| 56 |
means that functions such as |
| 57 |
:class:`Random() <django.db.models.functions.Random>` aren't accepted. |
| 58 |
|
| 59 |
.. admonition:: Restrictions on PostgreSQL |
| 60 |
|
| 61 |
PostgreSQL requires functions and operators referenced in an index to be |
| 62 |
marked as ``IMMUTABLE``. Django doesn't validate this but PostgreSQL will |
| 63 |
error. This means that functions such as |
| 64 |
:class:`Concat() <django.db.models.functions.Concat>` aren't accepted. |
| 65 |
|
| 66 |
.. admonition:: MySQL and MariaDB |
| 67 |
|
| 68 |
Functional indexes are ignored with MySQL < 8.0.13 and MariaDB as neither |
| 69 |
supports them. |
| 70 |
|
| 71 |
``fields`` |
| 72 |
---------- |
| 73 |
|
| 74 |
.. attribute:: Index.fields |
| 75 |
|
| 76 |
A list or tuple of the name of the fields on which the index is desired. |
| 77 |
|
| 78 |
By default, indexes are created with an ascending order for each column. To |
| 79 |
define an index with a descending order for a column, add a hyphen before the |
| 80 |
field's name. |
| 81 |
|
| 82 |
For example ``Index(fields=['headline', '-pub_date'])`` would create SQL with |
| 83 |
``(headline, pub_date DESC)``. |
| 84 |
|
| 85 |
.. admonition:: MariaDB |
| 86 |
|
| 87 |
Index ordering isn't supported on MariaDB < 10.8. In that case, a |
| 88 |
descending index is created as a normal index. |
| 89 |
|
| 90 |
``name`` |
| 91 |
-------- |
| 92 |
|
| 93 |
.. attribute:: Index.name |
| 94 |
|
| 95 |
The name of the index. If ``name`` isn't provided Django will auto-generate a |
| 96 |
name. For compatibility with different databases, index names cannot be longer |
| 97 |
than 30 characters and shouldn't start with a number (0-9) or underscore (_). |
| 98 |
|
| 99 |
.. admonition:: Partial indexes in abstract base classes |
| 100 |
|
| 101 |
You must always specify a unique name for an index. As such, you |
| 102 |
cannot normally specify a partial index on an abstract base class, since |
| 103 |
the :attr:`Meta.indexes <django.db.models.Options.indexes>` option is |
| 104 |
inherited by subclasses, with exactly the same values for the attributes |
| 105 |
(including ``name``) each time. To work around name collisions, part of the |
| 106 |
name may contain ``'%(app_label)s'`` and ``'%(class)s'``, which are |
| 107 |
replaced, respectively, by the lowercased app label and class name of the |
| 108 |
concrete model. For example ``Index(fields=['title'], |
| 109 |
name='%(app_label)s_%(class)s_title_index')``. |
| 110 |
|
| 111 |
``db_tablespace`` |
| 112 |
----------------- |
| 113 |
|
| 114 |
.. attribute:: Index.db_tablespace |
| 115 |
|
| 116 |
The name of the :doc:`database tablespace </topics/db/tablespaces>` to use for |
| 117 |
this index. For single field indexes, if ``db_tablespace`` isn't provided, the |
| 118 |
index is created in the ``db_tablespace`` of the field. |
| 119 |
|
| 120 |
If :attr:`.Field.db_tablespace` isn't specified (or if the index uses multiple |
| 121 |
fields), the index is created in tablespace specified in the |
| 122 |
:attr:`~django.db.models.Options.db_tablespace` option inside the model's |
| 123 |
``class Meta``. If neither of those tablespaces are set, the index is created |
| 124 |
in the same tablespace as the table. |
| 125 |
|
| 126 |
.. seealso:: |
| 127 |
|
| 128 |
For a list of PostgreSQL-specific indexes, see |
| 129 |
:mod:`django.contrib.postgres.indexes`. |
| 130 |
|
| 131 |
``opclasses`` |
| 132 |
------------- |
| 133 |
|
| 134 |
.. attribute:: Index.opclasses |
| 135 |
|
| 136 |
The names of the `PostgreSQL operator classes |
| 137 |
<https://www.postgresql.org/docs/current/indexes-opclass.html>`_ to use for |
| 138 |
this index. If you require a custom operator class, you must provide one for |
| 139 |
each field in the index. |
| 140 |
|
| 141 |
For example, ``GinIndex(name='json_index', fields=['jsonfield'], |
| 142 |
opclasses=['jsonb_path_ops'])`` creates a gin index on ``jsonfield`` using |
| 143 |
``jsonb_path_ops``. |
| 144 |
|
| 145 |
``opclasses`` are ignored for databases besides PostgreSQL. |
| 146 |
|
| 147 |
:attr:`Index.name` is required when using ``opclasses``. |
| 148 |
|
| 149 |
``condition`` |
| 150 |
------------- |
| 151 |
|
| 152 |
.. attribute:: Index.condition |
| 153 |
|
| 154 |
If the table is very large and your queries mostly target a subset of rows, |
| 155 |
it may be useful to restrict an index to that subset. Specify a condition as a |
| 156 |
:class:`~django.db.models.Q`. For example, ``condition=Q(pages__gt=400)`` |
| 157 |
indexes records with more than 400 pages. |
| 158 |
|
| 159 |
:attr:`Index.name` is required when using ``condition``. |
| 160 |
|
| 161 |
.. admonition:: Restrictions on PostgreSQL |
| 162 |
|
| 163 |
PostgreSQL requires functions referenced in the condition to be marked as |
| 164 |
IMMUTABLE. Django doesn't validate this but PostgreSQL will error. This |
| 165 |
means that functions such as :ref:`date-functions` and |
| 166 |
:class:`~django.db.models.functions.Concat` aren't accepted. If you store |
| 167 |
dates in :class:`~django.db.models.DateTimeField`, comparison to |
| 168 |
:class:`~datetime.datetime` objects may require the ``tzinfo`` argument |
| 169 |
to be provided because otherwise the comparison could result in a mutable |
| 170 |
function due to the casting Django does for :ref:`lookups <field-lookups>`. |
| 171 |
|
| 172 |
.. admonition:: Restrictions on SQLite |
| 173 |
|
| 174 |
SQLite `imposes restrictions <https://www.sqlite.org/partialindex.html>`_ |
| 175 |
on how a partial index can be constructed. |
| 176 |
|
| 177 |
.. admonition:: Oracle |
| 178 |
|
| 179 |
Oracle does not support partial indexes. Instead, partial indexes can be |
| 180 |
emulated by using functional indexes together with |
| 181 |
:class:`~django.db.models.expressions.Case` expressions. |
| 182 |
|
| 183 |
.. admonition:: MySQL and MariaDB |
| 184 |
|
| 185 |
The ``condition`` argument is ignored with MySQL and MariaDB as neither |
| 186 |
supports conditional indexes. |
| 187 |
|
| 188 |
``include`` |
| 189 |
----------- |
| 190 |
|
| 191 |
.. attribute:: Index.include |
| 192 |
|
| 193 |
A list or tuple of the names of the fields to be included in the covering index |
| 194 |
as non-key columns. This allows index-only scans to be used for queries that |
| 195 |
select only included fields (:attr:`~Index.include`) and filter only by indexed |
| 196 |
fields (:attr:`~Index.fields`). |
| 197 |
|
| 198 |
For example:: |
| 199 |
|
| 200 |
Index(name="covering_index", fields=["headline"], include=["pub_date"]) |
| 201 |
|
| 202 |
will allow filtering on ``headline``, also selecting ``pub_date``, while |
| 203 |
fetching data only from the index. |
| 204 |
|
| 205 |
Using ``include`` will produce a smaller index than using a multiple column |
| 206 |
index but with the drawback that non-key columns can not be used for sorting or |
| 207 |
filtering. |
| 208 |
|
| 209 |
``include`` is ignored for databases besides PostgreSQL. |
| 210 |
|
| 211 |
:attr:`Index.name` is required when using ``include``. |
| 212 |
|
| 213 |
See the PostgreSQL documentation for more details about `covering indexes`_. |
| 214 |
|
| 215 |
.. admonition:: Restrictions on PostgreSQL |
| 216 |
|
| 217 |
PostgreSQL supports covering B-Tree and :class:`GiST indexes |
| 218 |
<django.contrib.postgres.indexes.GistIndex>`. PostgreSQL 14+ also supports |
| 219 |
covering :class:`SP-GiST indexes |
| 220 |
<django.contrib.postgres.indexes.SpGistIndex>`. |
| 221 |
|
| 222 |
.. _covering indexes: https://www.postgresql.org/docs/current/indexes-index-only-scans.html |