Why does Django create an index on a unique field explicitly

Question

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 1

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

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

Answer 2

The heart of the issue is this guarantee made in the Django documentation:

Note that when unique is True you don’t need to specify db_index, because unique implies the creation of an index.

So by Django's contract, unique=True implies db_index=True, and db_index=True means that Django has to create the text_pattern_ops index to support all lookup types (see ticket 12234).

As for just using a single unique index, the PostgreSQL documentation says that that won't cover all lookup types:

Note that you should also create an index with the default operator class if you want queries involving ordinary <, <=, >, or >= comparisons to use an index. Such queries cannot use the xxx_pattern_ops operator classes.

You can try adding both unique=True and db_index=False.