Medorator Medorator - 1 month ago 10
SQL Question

Why does Django create an index on a unique field explicitly

Update: Simplifying the Q on experimenting with

psql
further:

For the following Django model:

class Book(models.Model):
name = models.TextField(unique=True)


pg_dump
(PostgreSQL 9.3) shows the following table & constraints:

CREATE TABLE book (
id integer NOT NULL,
name text NOT NULL,
);

ALTER TABLE ONLY book ADD CONSTRAINT book_name_key UNIQUE (name);

CREATE INDEX book_name_like ON book USING btree (name text_pattern_ops);


But PostgreSQL documentation says:


PostgreSQL automatically creates a unique index when a unique
constraint [...] is defined for a table.

[...] there's
no need to manually create indexes on unique columns; doing so would
just duplicate the automatically-created index.


Question: Why is Django creating an index on a unique column then? Maybe the justification is that it's using the operator class
text_pattern_ops
, hence Django needs to add another index. If that's the case, a better way would be to interpret
unique=True
constraint by Django as this:

CREATE UNIQUE INDEX book_name_like ON book USING btree (name text_pattern_ops);


and not have the
UNIQUE
constraint in the column at all. Thus a single
UNIQUE INDEX
with
text_pattern_ops
would result in DB not creating an implicit index for
UNIQUE
constraint.

Answer

Detailed discussion in bug report: https://code.djangoproject.com/ticket/24082

Triage: Accepted to skip index when db_index=False (with unique=True)

Comments