Debian Codegraph @

docs/ref/models/indexes.txt

raw
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